Opened 4 years ago

Closed 4 years ago

#12451 closed Patch - Feature (Fixed)

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

Reported by: Karl Egly Owned by: Karl Egly
Priority: minor Milestone: 0.27.6
Component: Contributed Scripts & Apps Version: Unspecified
Severity: medium Keywords:
Cc: Ticket locked: no

Description

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.

Attachments (2)

0001-add-daily-defragmentation-of-the-recordedseek-table.patch (1.4 KB) - added by Karl Egly 4 years ago.
optimizedb.diff (2.4 KB) - added by J.Pilk@… 4 years ago.

Download all attachments as: .zip

Change History (9)

Changed 4 years ago by Karl Egly

comment:1 Changed 4 years ago by 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...)

comment:2 Changed 4 years ago by 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

comment:3 Changed 4 years ago by Karl Egly

Component: MythTV - GeneralContributed Scripts & Apps
Milestone: unknown0.27.5
Owner: set to Karl Egly
Status: newassigned

comment:4 Changed 4 years ago by 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)

comment:5 Changed 4 years ago by Stuart Auchterlonie

Milestone: 0.27.50.27.6

Changed 4 years ago by J.Pilk@…

Attachment: optimizedb.diff added

comment:6 Changed 4 years ago by 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.

comment:7 Changed 4 years ago by paulh

Resolution: Fixed
Status: assignedclosed
Note: See TracTickets for help on using tickets.