[mythtv-users] sluggish system, potential HD failure, and how best to get back up and runnin

[Simon Hobson]

Greg Thompson <gthompson20 at gmail.com> wrote:

> Mind sharing your DB Tuning Tips?


With the disclaimer that I'm no expert on DB tuning ...

A good starting point is mysqltuner.pl, look at the results and twiddle settings.

However, even after reading the docs, some of the settings are a tad obscure. You also start getting into tradeoffs - some memory allocation are global, while some are per-connection. If you keep the allowable connections up then you find the total possible memory usage also goes up.

Extract from my current results :

[--] Data in MyISAM tables: 268M (Tables: 96)
[--] Data in InnoDB tables: 48K (Tables: 3)
Gives a total data size - ideally you want the memory allocation to be larger than this so all the tables can stay resident.

[!!] Total fragmented tables: 27
You can optimise the tables - but they'll fragment again sooner or later. Don't worry too much about this.

[--] Up for: 142d 21h 7m 22s (66M q [5.404 qps], 22K conn, TX: 244B, RX: 46B)
This is good, it means you get good statistics. Just after a restart you don't get meaningful numbers or recommendations.

[--] Total buffers: 930.0M global + 4.6M per thread (20 max threads)
[OK] Maximum possible memory usage: 1021.2M (51% of installed RAM)
Start to worry if this gets to "most of your RAM", although it's unlikely that every connection will use all it's possible memory.

[!!] Highest connection usage: 100%  (20/20)
Hmm, looks like I need to tweak it up a bit, but then I've not seen any odd effects. Not sure when all the connections get used, because I've tried "making the system busy" and never got that high during testing.

[OK] Key buffer size / total MyISAM indexes: 384.0M/323.6M
IIRC this is one of the biggies - the buffer is big enough to keep all indexes in memory. That means any indexed operation (select, join, etc) shouldn't need to hit the disk.

[OK] Key buffer hit rate: 99.9% (1B cached / 1M reads)
And the result is a good figure

[OK] Sorts requiring temporary tables: 0% (288 temp sorts / 445K sorts)
That's good, and suggests some good use of indexes etc for sorts.

[!!] Joins performed without indexes: 94335
Ideally all joins would be via indexes, but largely that's out of the user's control as it's controlled by the programming (how it does joins) and the database schema (what indexes there are). I suspect "bad" custom criteria in recording rules could contribute to this figure.

[OK] Temporary tables created on disk: 7% (11K on disk / 152K total)
That's good, most of the temporary tables created were in memory.

[!!] Table cache hit rate: 0% (236 open / 361K opened)
Dunno about this one !

Then at the end if gives a list of recommendations :

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    max_connections (> 20)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (> 24M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    table_cache (> 8192)

I suggest that you don't just blindly follow the instructions, but go and read the MySQL docs and see what each setting does first. If nothing else, it'll make you realise just what a complex area it is :-O
Also, after making a change, MySQL tuner won't give meaningful results for some aspects. So it can be a lengthy process of making adjustments, leaving the system to run for a few days, and then looking again. Where you can get immediate feedback is the total possible memory consumption and how your changes have affected it.


From memory, the defaults are "fairly conservative - with the result that a lot of disk I/O is created which is not necessary. On a system doing nothing but running a DB it wouldn't be as bad as OS caching would hide the problem, but with a Myth system, there's so much other I/O that you're guaranteed to flush DB data from the OS cache and so it's a lot more important to tune the DB itself to keep it's own cache. More memory is often a good investment - and if there isn't enough memory to tune out virtually all disk access, then adding more memory will probably be a better investment than an SSD (or faster drive).


Hope that's some help.