Quick MySQL cache tuning tips using Mysqltuner and Mysqladmin

MySQL is the default relational database for web today as part of the ubiquitous LAMP stack. Postgres has a reputation for better data integrity (whereas MySQL has a reputation for losing data) where MySQL runs faster at the cost of data robustness. But when you're running a few "heavy" websites, especially running Drupal or other CMS sites, then you'll be depending on your database server to perform a lot of queries quickly.

1. You want to see just how busy your database server is. Use mysqladmin (possibly logging in as a mysql user with full access):

$ mysqladmin status
Uptime: 18564 Threads: 1 Questions: 1156605 Slow queries: 4 Opens: 10731 Flush tables: 1 Open tables: 600 Queries per second avg: 62.303
$ mysqladmin status
Uptime: 6837 Threads: 1 Questions: 766237 Slow queries: 4 Opens: 4506 Flush tables: 1 Open tables: 600 Queries per second avg: 112.072

We see that we're servicing up to over a hundred queries a second, continuously. And for the most part, the queries are returning quickly, but there are 4 slow queries in recent history. The database admin should log slow queries, then show this to the web developer to rewrite or optimize those sql queries, but let's continue seeing how to evaluate and tune mysqld in general.

2. Now run 'top' in Linux/Unix on your database server (often your only server) and if you sort by memory (hit shift-m) you'll see mysqld up at the top. It's bad because ram is expensive to rent. But it may be a good sign that mysqld is humming along smoothly and processing queries efficiently.

You'll see something like this:

28786 mysql 20 0 1321132 365876 3668 S 0.0 17.8 4:46.11 mysqld

Don't believe what 'top' reports. The memory consumed by each process, including mysqld, isn't necessarily the full size shown in top, which includes memory currently paged out. So mysqld isn't really using 1321132 bytes of real RAM (if your VPS is even giving you all that memory as real RAM). And the free memory reported by top is also only the memory the OS is using including buffers and caches. It may say you're low on available memory and that you're swapping but that's not necessarily true.

3. To see how pressed you really are for memory, use the 'free' command with argument "-m", reporting bytes as megabytes.

Example:$ free -m total used free shared buffers cachedMem: 490 474 15 51 5 115-/+ buffers/cache: 353 136Swap: 1535 318 1217

$ free -m
total used free shared buffers cached
Mem: 2001 1907 94 118 175 465
-/+ buffers/cache: 1266 735
Swap: 976 474 502

It looks like both servers have a low percentage of free memory and. Although 'top' would show 15 megs free in the first case, which would seem dangerously low, actually your apps will still be able to get 136 mb more memory before swapping or getting OOM errors.

Now you know how your server is doing on free memory. Now if you want to take advantage of remaining memory on your server, let's try to improve the database server's cache hit rate.

4. MySQL has a few caches. The important one though is the query cache which is described in the docs as:

The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.

When using the query cache and actually getting hits from the cache, you can see 238% faster performance. So it's not going to make up for a site which is hitting the db 1000s of times per page but it could still make it twice as fast (for just the db queries) with caching than without.

There's a catch with the query cache though. Queries are cached for a table only until that table is modified. This is good for tables that are selected from often but rarely inserted into, like most web content for sites which aren't updated with new content hourly. But resetting last-accessed date on content or last-logged-in dates on users could result in blowing away caches.

You can tweak your query cache by changing 'query_cache_size' in your server's my.cnf. Query cache should be turned on by default but the size may be only 16M or smaller. So what is a good size for your query cache, assuming you have some available memory on your server?

5. Install mysqltuner. Mysqltuner is an old script that pulls out stats from your running MySQL database and then presents suggestions based on what it assesses. It can warn of any problems going against best practices, e.g. security, and problems with the state of your tables, which may be fragmented. It gives suggestions on tuning variables. It's best to run mysqltuner after the database has been running for a day or so to collect relevant statistics. Here's a sample run:

# mysqltuner

>> MySQLTuner 1.1.1 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.46-0ubuntu0.14.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in InnoDB tables: 56M (Tables: 713)
[--] Data in MyISAM tables: 165M (Tables: 300)
[!!] Total fragmented tables: 770

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 42m 25s (1M q [58.541 qps], 4K conn, TX: 4B, RX: 245M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 368.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 773.8M (38% of installed RAM)
[OK] Slow queries: 0% (4/1M)
[OK] Highest usage of available connections: 3% (6/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/144.0M
[OK] Key buffer hit rate: 99.8% (15M cached / 35K reads)
[OK] Query cache efficiency: 92.2% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (210 temp sorts / 29K sorts)
[OK] Temporary tables created on disk: 0% (85 on disk / 15K total)
[OK] Thread cache hit rate: 99% (6 created / 4K connections)
[!!] Table cache hit rate: 5% (600 open / 10K opened)
[OK] Open file limit used: 12% (168/1K)
[OK] Table locks acquired immediately: 99% (280K immediate / 280K locks)
[OK] InnoDB data size / buffer pool: 56.2M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 600)

What we're interested in is "Query cache efficiency" and "Query cache prunes per day". The latter, the number of prunes after mysqld has been running for a few days, tells you if your query cache is too small. You want all your common queries to stay in the query cache at least until a row modification would invalidate caches for queries on that table. If it's pruning a lot, try to increase the query cache size.

The other cache setting to pay attention to is "Key buffer size" which applies if you're using MyISAM storage engine for some tables rather than InnoDB. Tweak key_buffer_size in your my.cnf. For InnoDB instead, tweak innodb_buffer_pool_size. Keys here are the keys for table indices and the idea is to fit an entire index in memory if possible.

6. Notice the "Table cache hit rate"? To see what's going on, run: SHOW OPEN TABLES;

Each table that mysql is using will cause 2 or 3 file descriptors to be open for the files representing the database table on disk. If it's using the table cache (table open cache) then it will keep a file descriptor open and around for other sessions which need to read from that same table. Of course, if it's not cached, then mysql needs to request the OS to open the files which may not be trivial if it's having to do this thousands of times.

The simple fix is to always have a table_cache (or table_open_cache in different versions of MySQL) high enough for all tables in all databases. But your user/process may have a default OS open file limit that's lower than that. In the above case it's 1000 when there's 10x as many tables. You could use ulimit to set the limit higher before running mysqld and then increase your table_open_cache.

You can also run "SHOW GLOBAL STATUS" and look at Open_tables to see if you need a bigger table_open_cache or if you really only ever use/open a smaller subset of your tables. "FLUSH TABLES;" will clear the cache.

Summary: Given some free ram, run mysqltuner after a day, then tweak variables query_cache_size, key_buffer_size/innodb_buffer_pool_size, and table_cache/table_open_cache and recheck your cache hit rates and prunes after a day.