MySQL Query cache efficiency

Question

I'm using MySQLTuner.pl to optimize my site.... though I'm not entirely sure how to resolve some of these issues and am wondering if someone can help me out.

I have about 4GB ram free if needed for MySQL with the following MySQL settings:

key_buffer      = 100M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 800

myisam-recover         = BACKUP
max_connections        = 750
table_cache            = 125000
thread_concurrency     = 500

query_cache_type=1
query_cache_limit = 128M
query_cache_size        = 128M

tmp_table_size = 300M
max_heap_table_size = 300M
innodb_buffer_pool_size = 2G
innodb_file_per_table = 0
innodb_flush_log_at_trx_commit = 2

Here's the output of my tuner

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 7K (Tables: 10)
[--] Data in InnoDB tables: 27M (Tables: 5)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 5

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15d 5h 4m 19s (1B q [1K qps], 208M conn, TX: 172B, RX: 98B)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 2.5G global + 2.7M per thread (750 max threads)
[OK] Maximum possible memory usage: 4.5G (57% of installed RAM)
[OK] Slow queries: 0% (0/1B)
[OK] Highest usage of available connections: 15% (118/750)
[OK] Key buffer size / total MyISAM indexes: 100.0M/119.0K
[OK] Key buffer hit rate: 100.0% (22M cached / 0 reads)
[!!] Query cache efficiency: 0.1% (703K cached / 519M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (13 temp sorts / 13M sorts)
[OK] Temporary tables created on disk: 25% (4M on disk / 18M total)
[OK] Thread cache hit rate: 99% (752 created / 208M connections)
[OK] Table cache hit rate: 74% (992 open / 1K opened)
[OK] Open file limit used: 0% (68/250K)
[OK] Table locks acquired immediately: 100% (216M immediate / 216M locks)
[OK] InnoDB data size / buffer pool: 27.8M/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
query_cache_limit (> 128M, or use smaller result sets)

The output of SHOW STATUS LIKE '%cache%' is

Binlog_cache_disk_use   0
Binlog_cache_use    0
Binlog_stmt_cache_disk_use  0
Binlog_stmt_cache_use   0
Com_assign_to_keycache  0
Qcache_free_blocks  19
Qcache_free_memory  134026728
Qcache_hits 704192
Qcache_inserts  143569852
Qcache_lowmem_prunes    0
Qcache_not_cached   11043040
Qcache_queries_in_cache 94
Qcache_total_blocks 217
Ssl_callback_cache_hits 0
Ssl_session_cache_hits  0
Ssl_session_cache_misses    0
Ssl_session_cache_mode  NONE
Ssl_session_cache_overflows 0
Ssl_session_cache_size  0
Ssl_session_cache_timeouts  0
Ssl_used_session_cache_entries  0
Threads_cached  748

Is there anything I also can improve for better performance?

Thank you

Answer 1

"Query cache efficiency: 0.1% (703K cached / 519M selects)" means that you have no repeated queries within the cache lifetime cycle.

This may be due to three factors: the cache lifetime is too small for repeats to show, or your queries are too large or different to be cached.

For example, some frameworks will run a large SELECT and then "filter" it server side (maybe even cache it server side). What MySQL sees is a large query that might be deemed best not cached, and MySQL efficiency goes down while overall efficiency stays the same.

Or you might have lots of different clients and every one of them runs some query with custom data (e.g., their message table contents). If you have one thousand customers, each occupying one megabyte, and every one of them checks for message every minute, if you have one gigabyte to dedicate to this category of queries you will see one thousand misses and fifty-nine thousand hits per hour. But if you have only 999 megs, the last client will flush the first client out from the cache, then the first client comes again and gets a miss AND flushes the second client out, and so on; and in that same one hour you see sixty thousand misses, a drop in efficiency from 98% to 0%.

So first of all you need to get a hard look at what queries you're running. Maybe some of them can be optimized, cache-wise: one big query run twice and two refinements instead of two big queries, and the first one gets cached. But keep in mind that you may be doing this at the expense of efficiency down the line.

From your other results, this seems to be the case. You have lots of inserts, lots of free query cache memory, and yet very few hits. So apparently all your queries are different one from the other: in this situation you want to decrease query cache memory to free up memory for any other purpose. Even if the hits decrease further, Amdahl's Law will ensure that query-related performances will not be so relevant (you lose 50% of 0.01%, you're really losing 0.005% overall; do not let that "fifty percent loss!" scare you).

Qcache_free_memory  134,026,728
Qcache_hits             704,192
Qcache_inserts      143,569,852

What surely can't hurt is to check what queries will probably not be worthwhile, and those can be upgraded with a SQL_NO_CACHE. They will still become cache misses, but won't degrade performance of other queries, which may find more memory and improve their performance. This is at zero cost down the line anyway, so can always be done (for queries big enough that they're worth the trouble, of course!).

After you've done this, you can experiment with the query_cache_size parameter. Check how much memory the other components are using, and the performances of the file system cache. You don't want to speed up a query and see the page coming out in twice the time because the view and controller components retrieval has got it in the neck.

Another thing you may want to look at are the indexes. You can lower the slow query limit and check out whether there are queries running significantly slower than others, and check why by looking through their query plan.