[mythtv-users] DB Tuning (Was: Replace backend 3.5 system disk with 2 2.5 raid1?)

Simon Hobson linux at thehobsons.co.uk
Sat Sep 27 11:32:11 UTC 2014


Mike Carron <jmcarron at starstream.net> wrote:

> What is a good way to determine values for tuning the DB?

mysqltuner.pl is a useful tool.
You need to restart MySQL to reset all the stats, then run your system for a day or two - though a few hours might be enough to start with, then see what mysqltuner says. Some of the settings will be obvious, others it's well worth reading the documentation to find out what they do.

Actually, I'd suggest setting aside some time and working through the MySQL documentation - there's a whole section on tuning. Though even with a basic knowledge of the internals of databases, I found it took a while to "get" some of it. If you can't get your head round it all, then just make incremental adjustments and see how the stats change - in particular making sure you can't hog too much memory.

Some settings are global - ie it's one memory pool for the whole database, while others are per connection and so memory scales by size allocation times quantity.
There is no way to say "these settings will work for you" as they are all a tradeoff.

The main aim is to tune the DB so that *most* of the time it does *most* of it's operations using in-memory cached data - that way it minimises disk activity which is a real performance killer. However, unless you have oodles of RAM, then increasing the size of MySQL caches takes away from other tasks which may then become an issue. So generally it's not practical to set the MySQL sizing to cater for all usage - but you can make big improvements over standard (on my Debian system, the defaults are way too small).

This is what I get today :
>> MySQLTuner 1.2.0 - Major Hayden <major at mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 335M (Tables: 96)
[--] Data in InnoDB tables: 48K (Tables: 3)
[!!] Total fragmented tables: 19

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 205d 22h 43m 5s (87M q [4.894 qps], 12K conn, TX: 385B, RX: 75B)
[--] Reads / Writes: 55% / 45%
[--] Total buffers: 930.0M global + 4.6M per thread (25 max threads)
[OK] Maximum possible memory usage: 1.0G (52% of installed RAM)
[OK] Slow queries: 0% (33/87M)
[OK] Highest usage of available connections: 68% (17/25)
[OK] Key buffer size / total MyISAM indexes: 384.0M/406.8M
[OK] Key buffer hit rate: 99.6% (2B cached / 8M reads)
[OK] Query cache efficiency: 73.0% (51M cached / 71M selects)
[!!] Query cache prunes per day: 270
[OK] Sorts requiring temporary tables: 0% (392 temp sorts / 349K sorts)
[!!] Joins performed without indexes: 105012
[OK] Temporary tables created on disk: 15% (11K on disk / 77K total)
[OK] Thread cache hit rate: 99% (79 created / 12K connections)
[!!] Table cache hit rate: 0% (235 open / 293K opened)
[OK] Open file limit used: 2% (348/16K)
[OK] Table locks acquired immediately: 99% (74M immediate / 74M locks)
[OK] InnoDB data size / buffer pool: 48.0K/8.0M

-------- 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:
   query_cache_size (> 24M)
   join_buffer_size (> 2.0M, or always use indexes with joins)
   table_cache (> 8192)

From memory one of the hardest was setting the max number of connections. As noted, there is a "per connection' memory usage and so setting a high limit for connections means a high potential memory usage. But you don't want to limit the ability to handle clients. While testing, I found that the number of connections was highly variable - if I set it at 20 then I'd find I'd have maxed out at some point, but as you see it's been max 17 of 25 in the last 7 months.
But it was worth it - it's made a huge difference to responsiveness. With the defaults, many operations would thrash the disk while the DB churned away.

NB - some of the things you can't really fix. Overt time the tables will get fragmented - while you can optimise them, they will fragment again. Non-indexed joins are a matter of coding - so short of diving into the code to find out what's doing them there isn't anything you can do about them.




More information about the mythtv-users mailing list