Menu
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'm running 16GB of RAM with the following MySQL settings:
key_buffer = 1024M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 1500
table_cache = 256
thread_concurrency = 4
query_cache_limit = 2M
query_cache_size = 32M
query_cache_type = 1
tmp_table_size = 512M
max_heap_table_size = 128M
join_buffer_size = 128M
myisam_sort_buffer_size = 512M
Here's the output of my tuner
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12.6-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 98M (Tables: 402)
[--] Data in InnoDB tables: 16K (Tables: 1)
[!!] Total fragmented tables: 17
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10s (1K q [132.400 qps], 443 conn, TX: 119K, RX: 82K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 1.2G global + 130.6M per thread (1500 max threads)
[!!] Maximum possible memory usage: 192.4G (1225% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 0% (2/1500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/72.5M
[!!] Key buffer hit rate: 72.3% (47 cached / 13 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 875 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 23% (48 on disk / 201 total)
[OK] Thread cache hit rate: 99% (2 created / 443 connections)
[!!] Table cache hit rate: 4% (128 open / 2K opened)
[OK] Open file limit used: 3% (257/7K)
[OK] Table locks acquired immediately: 100% (449 immediate / 449 locks)
[OK] InnoDB data size / buffer pool: 16.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 2M, or use smaller result sets)
table_cache (> 128)
When I decrease query_cache_limit and table_cache it doesn't appear to have any effect. I have restarted MySQL within the last 24 hours which could be part of the issue.
update
After running SHOW STATUS LIKE '%cache%' the output is
Variable_name Value
Binlog_cache_disk_use 0
Binlog_cache_use 0
Com_assign_to_keycache 0
Qcache_free_blocks 436
Qcache_free_memory 23551488
Qcache_hits 72553
Qcache_inserts 26954
Qcache_lowmem_prunes 0
Qcache_not_cached 7164
Qcache_queries_in_cache 5877
Qcache_total_blocks 12347
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 3
526
The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines. We considered what improvements we could make to query cache versus optimizations that we could make which provide improvements to all workloads.
The query_cache_limit value determines the maximum size of individual query results that can be cached. The default value is 1,048,576 bytes and this is equivalent to 1MB. MySQL does not handle cached data in one big chunk; instead it is handled in blocks.
They are very useful in improving query speed and database performance. Here are the steps to enable MySQL query cache for your database, set MySQL cache size and disable query cache. Please note, MySQL query cache has been deprecated in MySQL 5.7 and removed in MySQL 8.0.
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. The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries.
I found this website helpful in optimizing my own mysql server: http://www.omh.cc/mycnf/
It allows you to tune your variables and know what the total ram capacity will be. You need to optimize for 60% ram usage. So try to lower your total memory footprint to about 60% to 70% of your total ram. If you have other stuff running on the same machine, you probably need to reduce that number. Forget Query cache, it's not going to add too much value, but Table cache should improve your performance if done right.
Try reducing the number of connections and keeping your total memory footprint under 60% of total system memory.
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
