[mythtv-users] Database tuning

[Simon Hobson]

I've been trying to tune my database to reduce times on activities like rescheduling. However I seem to have hit something of a wall.

The backend is an HP Microserver with 2G of RAM - running Debian Wheezy and MythBackend 0.24.1*. The database in it's entirety is 200 to 250M. I've been usingmysqltuner.pl, but some of the recommendations it comes up with either don't have an effect or are out of my control. Key bits from mysqltuner :

[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture

[--] Data in MyISAM tables: 231M (Tables: 96)
[--] Data in InnoDB tables: 48K (Tables: 3)
[--] Total buffers: 434.0M global + 3.6M per thread (30 max threads)
[OK] Maximum possible memory usage: 540.9M (27% of installed RAM)
[OK] Highest usage of available connections: 46% (14/30)
[OK] Key buffer size / total MyISAM indexes: 384.0M/278.1M
[OK] Key buffer hit rate: 99.9% (49M cached / 28K reads)
[OK] Sorts requiring temporary tables: 0% (8 temp sorts / 8K sorts)
[!!] Joins performed without indexes: 2512
[OK] Temporary tables created on disk: 16% (449 on disk / 2K total)
[!!] Table cache hit rate: 1% (145 open / 7K opened)
[OK] Open file limit used: 1% (261/16K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
  Run OPTIMIZE TABLE to defragment tables for better performance
  Enable the slow query log to troubleshoot bad queries
  Adjust your join queries to always utilize indexes
  Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  join_buffer_size (> 1.0M, or always use indexes with joins)
  table_cache (> 8192)

So as far as I can tell, I've not got buffers large enough to hold indexes in memory, the data in memory, and should have "fast" response times.
But if (for example) I bring up the upcoming recordings screen in MythWeb, and click on "Don't Record" for a program - it takes perhaps 10s to refresh the page. During that time, top shows that :
Wait I/O goes to 40% or more - I've seen 90+%. And cached memory jumps from around 50M (I recently did a "echo 3 > /proc/sys/vm/drop_caches" to empty the caches) to 250M or more and then drops back to 50M or so. This is much the same after multiple tests - so any data really ought to have been read into cache after the first one or two goes.

I'm thinking that MySQL is creating some large temporary tables and for some reason this is hitting disk i/o - either for the temp tables, or by flushing out data that should be held in MySQL's caches.

I stopped increasingtable_cache as a found a note that having this value constantly increasing is normal if the system is creating temporary tables. See comments to
http://dev.mysql.com/doc/refman/5.1/en/table-cache.html

At the moment I;ve got these settings in /etc/mysql/my.cnf :
key_buffer = 16M
key_buffer_size     = 384M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size    = 8
max_connections     = 30
table_cache       = 8192
#thread_concurrency   = 10
join_buffer_size    = 1M
query_cache_limit = 1M
query_cache_size    = 24M

Could anyone suggest what else I might be needing to adjust ? I've tried turningjoin_buffer_size up to 16M, but other than considerably increasing MySQL's max memory usage it's not made any difference.

* Yes, I know it's a few versions behind, but if it aint broke ...