Playback start taking ages due to slow loading of the seek table from a heavily fragmented MyISAM table

[Karl Egly]

​http://www.psce.com/blog/2012/07/09/data-fragmentation-problem-in-mysql-myisam/ hints that fragmented MyISAM tables can have very bad performance once the table gets to big for the memory caches. On a backend that records and deletes recordings this will happen to the seek table. There is a statement for live defragmentation on a running system (even in parallel to recordings)

ALTER TABLE recordedseek ORDER BY chanid, starttime;

attached is a proof of concept patch that adds the defragmentation to the daily database maintenance job after the removal of stale entries in the recordedseek table. As the query runs more then four minutes on my backend I'd prefer some testing by others before I commit this as is.

[Gary Buhrmaster <gary.buhrmaster@…>]

Given that this can be an extremely resource intensive process, I would recommend that it not be automatically scheduled by the BE (unless you provide the user with an extensive scheduling control and capability), but be part of an external script that performs backup, check, optimize, and analyze that can be scheduled by the user as appropriate and as required. I will also note that unless your database is "special", it is not considered necessary to perform a optimize/analyze daily (backup, on the other hand...)

[Karl Dietz <dekarl@…>]

In de66bcd62dcf39d7ee685fe44edafde7072bb606/mythtv:

add defragmentation of the recordedseek table to maintenance

This fixed very slow (more then 10 seconds) waiting times on start of
recording playback caused by very slow loading of the seek table from
the database.

See ​http://www.psce.com/blog/2012/07/09/data-fragmentation-problem-in-mysql-myisam/
for an elaborate explanation of the problem.

Also sort program and filemarkup while here. (the second and third
biggest tables on my backend)

Refs #12451

[Karl Dietz <dekarl@…>]

In b7afc9b8e7d25e643e60daa07d7b8fae94d99a7f/mythtv:

add defragmentation of the recordedseek table to maintenance

This fixed very slow (more then 10 seconds) waiting times on start of
recording playback caused by very slow loading of the seek table from
the database.

See ​http://www.psce.com/blog/2012/07/09/data-fragmentation-problem-in-mysql-myisam/
for an elaborate explanation of the problem.

Also sort program and filemarkup while here. (the second and third
biggest tables on my backend)

Refs #12451

(cherry picked from commit de66bcd62dcf39d7ee685fe44edafde7072bb606)

[J.Pilk@…]

When the optimize.. script is run from a terminal the last line displayed gives the name of the table last processed; usually that's one of minor importance. I've just posted a patch that displays the current activity and the time it took. Works for me. The perl could be streamlined but I doubt it's worth it. Could be used as an alternative to the released version.