[mythtv-users] HowTo tune MySQL for schedule time and in general?

David Engel david at istwok.net
Sun May 5 03:27:48 UTC 2013


[Top posting fixed]

On Sun, May 05, 2013 at 01:54:37AM +0200, Thomas Börkel wrote:
> On 05.05.2013 00:15, Thomas Börkel wrote:
> > HI!
> > 
> > On my production system, it takes very long to run the scheduler:
> > 
> > 2013-05-05 00:00:19.630711 I  Scheduled 740 items in 6.6 = 0.35 match +
> > 0.39 check + 5.88 place
> > 
> > This is much faster on my test system with similar MySQL settings. The
> > test system has less channels, but the same guide data (for the existing
> > channels):
> > 
> > 2013-05-04 20:35:04.849792 I  Scheduled 943 items in 0.9 = 0.00 match +
> > 0.00 check + 0.88 place
> > 
> > Both systems have a pretty similar Core i5 processor.
> > 
> > In MythTV wiki, there is some tuning explained, but it talks about
> > InnoDB engine.
> > 
> > Apparently, when I look at the mysql dump of my databases, I am using
> > MyIsam engine.
> > 
> > I already ran the optimize database script. Also I followed advice from
> > the mysqltuner.pl script.
> > 
> > Anything else I can try?
> > 
> > Running MythTV 0.26 from March 2013.
> > 
> > These are my settings:
> > key_buffer = 64M
> > max_allowed_packet = 8M
> > table_cache = 300
> > sort_buffer_size = 48M
> > net_buffer_length = 8M
> > query_cache_type = 1
> > query_cache_size = 32M
> > join_buffer_size = 1M
> > tmp_table_size = 64M
> > max_heap_table_size = 64M
> > thread_cache_size = 4
> > key_buffer_size = 64M
> > query_cache_limit = 8M
> > 
> > Thanks!
> 
> Answering my own question:
> 
> After reading some MySQL performance tips, I changed the settings like
> this (note that I also removed some):
> 
> key_buffer = 16M
> max_allowed_packet = 1M
> table_cache = 128
> sort_buffer_size = 1M
> net_buffer_length = 8K
> query_cache_size = 16M
> join_buffer_size = 1M
> tmp_table_size = 128M
> max_heap_table_size = 128M
> thread_cache_size = 4
> 
> Scheduling is now very fast (0.2s). I think, it was mainly
> tmp_table_size and max_heap_table_size...
> 
> Max. memory usage according to mysqltuner.pl is 629 MB, which sounds
> good to me.

Something doesn't add up here.

In your first timing, the majority of time (5.88s) was spent in the
place phase.  That phase shouldn't be affected that much by DB
changes.  To get a later run in the 0.2s range, the place phase would
have had to sped up a lot and I can't see that happening with just a
DB change.  It is the match and check phases that are DB sensitive.

In addition, the scheduler normally only runs the match and check
phases on things that might have changed.  That means you can't
arbitrarily pick scheduler runs and compare them because they might
not have done the same amount of work.  You can compare runs initiated
with "mythutil --resched" because those cause the scheduler to re-run
everything.  You can also compare runs by making a do-nothing change
to a single recording rule because those cause the scheduler to only
re-run the parts for that rule.

David
-- 
David Engel
david at istwok.net


More information about the mythtv-users mailing list