[mythtv] Why are there so many instances of recordedseek?

[Richard Gering]

What Nick said :-)

I have over 2.9 million entries in this table, spanning 485 recordings going all the way back to 2004. There's not a single orphaned entry among them.

Try running Nicks query to check whether or not you actually have orphaned entries. If you also get an empty set, this is just business as usual for MythTV. The 'recordedseek' table truly makes a huge difference being able to see through a recording.

With respect to the freezes at the top of the hour: look for disk I/O on your video volume, for example using 'sar'. Any chance the drive is maxing out at that time?

Cheers,

- Richard.

----- Original Message -----
From: "Nick Morrott" <knowledgejunkie at gmail.com>
To: "MythTV-dev" <mythtv-dev at mythtv.org>
Sent: Monday, April 20, 2015 6:04:57 PM
Subject: Re: [mythtv] Why are there so many instances of recordedseek?

On 21 April 2015 at 01:09, Steve E <simon.sinister at sbcglobal.net> wrote:
>
> I was looking through the MythTV mysql database to try to
> figure out where the current position in a recording is stored.
> Based on the name, I thought recordedseek might be the spot.

> mysql> select * from recordedseek;

<snip>

> 2280198 rows in set (3.00 sec)

> This message rather surprised me. I've got 2.28 million recordedseek rows
> in my database? Granted, I currently have a large number of recorded shows
> (234),
> but that's averaging 9744 recordedseek instances per recording, which is
> ridiculous.

recordedseek stores locations of all keyframes in recorded video - not
your current bookmark - so that number seems perfectly normal. You can
have several keyframes per second of video, depending on the
compression. You'd certainly have more keyframes if you have longer
than average recordings, but one might expect the average number of
entries per recorded hour of video to be /roughly/ the same for all
users.

> It seems likely that a large number of recordedseek instances are not being
> deleted from the database when the corresponding recording is deleted. Some
> of the dates listed above are from 2009, so I've been accumulating them for
> at
> least 6 years.
>
> Do other developers / users have really large numbers of these rows in
> their databases?

100+ recordings currently. 900K entries. No orphans.

> You can retrieve an approximate count using the following syntax:
>
> mysql> show table status like 'recordedseek';
> +--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
> | Name         | Engine | Version | Row_format | Rows    | Avg_row_length |
> Data_length | Max_data_length  | Index_length | Data_free | Auto_increment |
> Create_time         | Update_time         | Check_time          | Collation
> | Checksum | Create_options | Comment |
> +--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
> | recordedseek | MyISAM |      10 | Fixed      | 2280198 |             25 |
> 61642050 | 7036874417766399 |     67974144 |   4637100 |           NULL |
> 2014-12-06 17:48:53 | 2015-04-19 23:49:30 | 2015-04-18 07:42:10 |
> utf8_general_ci |     NULL |                |         |
> +--------------+--------+---------+------------+---------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
> 1 row in set (0.00 sec)


You can get an exact count using:

mysql> SELECT COUNT(*) FROM `recordedseek`;


> Any theories on why these keep accumulating?

They should be deleted when you delete the recording. Maybe you've
lost recordings in the past and the entries became orphaned then?

You can find the start times of recordedseek entries that do not
relate to current recordings using some brute force:

SELECT DISTINCT `starttime`
FROM `recordedseek`
WHERE `starttime` NOT IN (SELECT `starttime` FROM `recorded`)
ORDER By `starttime` ASC;

I get an empty result set in this case.

Cheers,
Nick
_______________________________________________
mythtv-dev mailing list
mythtv-dev at mythtv.org
http://lists.mythtv.org/mailman/listinfo/mythtv-dev
http://wiki.mythtv.org/Mailing_List_etiquette
MythTV Forums: https://forum.mythtv.org