[mythtv] innodb vs myism preformance

[Chris Pinkham]

> > So that suggests that the next most likely place to look is the recorded
> > markup table.  If you have an mpeg2 recording then you have a huge bunch
> > of rows stored in this table which have to be deleted.  Can you try just
> > changing this single table and re-benching to see what happens?
> 
> I can give it a shot, but all my recordings are mpeg4 (if that matters
> to the size of recordedmarkup). Ok, the results are:

Here's a quick description of the order of events (related to disk usage):

1) Frontend sends backend a delete request.
2) Backend checks to see if the file exists
   If EXISTS then
       a) set the deletepending flag in the DB
       b) fire off the delete thread
   If NOT exists then print a verbose message and an entry to the DB log
3) Backend returns status to the frontend.
4) Frontend re-retrieves the recordings list from the backend and updates
   the screen.  The new list should not include the program being deleted
   since it has it's 'deletepending' flag set.

Inside the delete thread, here's what happens: (maybe we should put a sleep(5)
to give the frontend time to reget the recordings list from the backend
before we start hitting the DB and filesystem deleting stuff)

1) Get the Delete thread lock
2) Open a DB connection
3) Retreive the ProgramInfo for the program being deleted
4) Check if the file exists
   If NOT exists then
       a) turn off deletepending flag
       b) release the Delete thread lock
       c) exit thread
5) Stop all running JobQueue jobs and delete any jobs in the queue.
6) Delete the recording file
7) Sleep 2 seconds
8) Check if the recording file still exists (probably can change this to
   just check the result of the unlink call)
   If EXISTS
       a) turn off deletepending flag
       b) release the Delete thread lock
       c) exit thread
9) Delete the preview pixmap .png file
10) Delete the recorded table entry
11) Sleep 1 second
12) Sending a RECORDING_LIST_CHANGE change event so the frontends know to
    retrieve the list of recordings (again).  This lets other frontends
    know that the file is gone.
	The main reason for this event being sent here is to update the
	free space on the frontends and to let the other frontends know the
	file is now gone.
13) Delete from recordedmarkup table (this is the biggie).  This is done
    as a whole, not row-by-row.
14) Call ScheduledRecording::signalChange(0) to let the scheduler know
    it needs to recompute.
15) Release the Delete thread lock.


After typing all that in, I see several improvements I'll look at making
that should help lessen the impact of deletes and improve the interaction.

1) remove the manual FillList() call from the doRemove() method on the
   frontend and let the fill be triggered by the backend in #2 below.
2) Have MainServer::DoHandleDeleteRecording send a RECORDING_LIST_CHANGE event
   after sending the delete request response back to the frontend.  This
   will cause all frontends to refresh their recordings list and cause the
   recording being deleted to disappear from all frontends' watch recordings
   screens.
3) At the top of the Delete thread, and before we acquire the lock, sleep for
   3 seconds.  This will give the frontends time to respond to the
   RECORDING_LIST_CHANGE event which was sent by the backend right after
   firing off the delete thread.
4) Send a RECORDING_LIST_CHANGE event in the delete thread in the two cases
   where the delete thread aborts and sets the deletepending flag back to 0.
   This way the recording will reappear on the frontend so the user can see
   that it was not deleted.
5) Add a sleep(3) in the Delete thread after sending the RECORDING_LIST_CHANGE
   and before deleting the entries in the recordedmarkup table.  This will
   prevent all the frontends from hitting the backend & DB at the same time
   we are deleting a huge amount of info from the recordedmarkup table.


> convert oldrecorded back to MYISAM........ The first delete is 1/2
> second, the second takes 6 seconds.

This could probably be cut short by #3 above since we would give the
frontends time to load the new recordings list before we started hitting
the disks hard doing deletes.

> convert recorded to MYISAM, oldrecoreded back to innnodb........first
> delete takes 10 seconds, AND a a new stutter appears after the
> recording is done (this may be uncorrelated).

This stutter could be the actual delete going on in the background.

-- 

Chris