[mythtv-users] Is 4GB RAM overkill for mythtv?

[Matthew McClement]

Brad DerManouelian wrote:
*snip*
> due to high MySQL usage. Anyway, my current my.cnf looks like this:
> 
> old_passwords=1 key_buffer = 16M table_cache = 128 sort_buffer_size =
> 2M myisam_sort_buffer_size = 8M query_cache_size = 16M
> 
> Anyone have recommendations as to what I can set these to or is it
> more of a "raise it, see how it goes, raise it again, see how it
> goes" type of thing?

note: this turned out longer than expected. Might want to grab a cup of
tea/coffee/whatever.

Myth's database doesn't lend itself to much optimisation. Basically make
sure the per thread buffers are reasonable, you've given enough memory
for the indexes to sit in memory at all times and try to keep the
database on a separate set of spindles to any disk MythTV itself tries
to write to. On tuning the various variables:

sort_buffer, read_buffer, join_buffer and read_rnd_buffer should all be
~2M in size(I can't think of any common MythTV related queries that
would generate a result set over 2M).

myisam_sort_buffer_size is a special case variable that only applies
when a index repair/optimize is being done. Generally this should be, at
the very least, the size of your largest index. Doing a "du -sk *.MYI |
sort -n" in your mythconverg MySQL directory should give you a sorted
list of index sizes, with the largest one at the bottom. Assuming you
have the memory, just double it and set that as your
myisam_sort_buffer_size value.

key_buffer, table_cache and thread_cache are global variables(ie.
connection threads share these resources). Generally table_cache and
thread_cache can be left alone and key_buffer should be set to total
index size + 50%. "du -sk *.MYI | awk '{ s += $1 } END { print s }'"
will give you the total index size.

Finally, if it's not enabled already, turn on query cacheing. The
relevant config lines are:

query_cache_limit = 2M
query_cache_size  = 64M
query_cache_type  = 1

This'll buy you a small chunk of performance. query_cache_size might
need to be varied depending on how heavily the system is used. Set it
and then watch qcache_free_memory("show global status like '%qcache%';"
run from the MySQL command prompt) to see how much is actually being used.

However, I suspect the biggest problem with MySQL, especially when using
MyISAM, is that Myth's disk activities will result in the MySQL, not
currently being used, datafiles being flushed from the pagecache. This
is normally a good thing however whenever you try to access the flushed
out data you incur a disk read, which is expensive and slow.

There are also concurrency problems, in that MyISAM does table locks. If
you have a look at Table_locks_waited("show global status like
'%wait%';"), you'll probably find a fairly high value. MySQL 5.1 does
alleviate some of the concurrency issues, however the only real solution
at the moment is to switch to InnoDB.

InnoDB also buys you an extra advantage. The innodb_buffer_pool
variables lets you allocate memory to be used both both indexes *and* 
data, so with appropriate tuning you can guarantee that data is always 
sitting in memory and never flushed out of the pagecache.

This mail is long enough however, so I won't go into converting over to
InnoDB. I'm also not sure what the MythTV devs viewpoint on InnoDB is,
however it appears to work fine for me(~6 months now). If I get time
I'll try to put something in the Wiki about doing the conversion.

Matt