We are running Magento 1.7 and created below reports from two different script. Can you please help me if I need to change any values in my.cnf. to get the best out of the server.
- Magento 18.104.22.168 - Have full page cache extension running on redis with cache warmer crawler
- PHP Version 5.3.25
- MySQL Version 5.1.70-cll
- Server 24 CPU/32GB RAM
MYSQL PERFORMANCE TUNING PRIMER
MySQL Version 5.1.70-cll x86_64
- Uptime = 5 days 7 hrs 1 min 35 sec
- Avg. qps = 1112
- Total Questions = 508743068
- Threads Connected = 11
Server has been running for over 48hrs.
It should be safe to follow these recommendations
- The slow query log is NOT enabled.
- Current long_query_time = 10.000000 sec.
- You have 153 out of 508743144 that take longer than 10.000000 sec. to complete
- Your long_query_time seems to be fine
BINARY UPDATE LOG
The binary update log is NOT enabled. You will not be able to do point in time recovery
- Current thread_cache_size = 8
- Current threads_cached = 2
- Current threads_per_sec = 0
- Historic threads_per_sec = 0
- Your thread_cache_size is fine
- Current max_connections = 500
- Current threads_connected = 12
- Historic max_used_connections = 218
- The number of used connections is 43% of the configured maximum.
- Your max_connections variable seems to be fine.
- Current InnoDB index space = 604 M
- Current InnoDB data space = 1.20 G
- Current InnoDB buffer pool free = 51 % Current
- innodb_buffer_pool_size = 6.00 G Depending on how much space your
- innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory
- Max Memory Ever Allocated : 116.49 G
- Configured Max Per-thread Buffers : 253.05 G
- Configured Max Global Buffers : 6.16 G
- Configured Max Memory Limit : 259.21 G
- Physical Memory : 31.32 G
- Max memory limit exceeds 90% of physical memory
- Current MyISAM index space = 54 M
- Current key_buffer_size = 100 M
- Key cache miss rate is 1 : 39157
- Key buffer free ratio = 70 %
- Your key_buffer_size seems to be too high.
- Perhaps you can use these resources elsewhere
- Query cache is enabled
- Current query_cache_size = 50 M
- Current query_cache_used = 37 M
- Current query_cache_limit = 1 M
- Current Query cache Memory fill ratio = 75.64 %
- Current query_cache_min_res_unit = 4 K
- MySQL won't cache query results that are larger than query_cache_limit in size
- Current sort_buffer_size = 512 M
- Current read_rnd_buffer_size = 2 M
- Sort buffer seems to be fine
- Current join_buffer_size = 132.00 K
- You have had 290151 queries where a join could not use an index properly
- You should enable "log-queries-not-using-indexes"
- Then look for non indexed joins in the slow query log.
- If you are unable to optimize your queries you may want to increase your
- join_buffer_size to accommodate larger joins in one pass.
- Note! This script will still suggest raising the join_buffer_size when
- ANY joins not using indexes are found.
OPEN FILES LIMIT
- Current open_files_limit = 20000 files
- The open_files_limit should typically be set to at least 2x-3x
- that of table_cache if you have heavy MyISAM usage.
- Your open_files_limit value seems to be fine
- Current table_open_cache = 4064 tables
- Current table_definition_cache = 256 tables
- You have a total of 1328 tables
- You have 1850 open tables.
- The table_cache value seems to be fine
- You should probably increase your table_definition_cache value.
- Current max_heap_table_size = 16 M
- Current tmp_table_size = 512 M
- Of 45418461 temp tables, 34% were created on disk
- Effective in-memory tmp_table_size is limited to max_heap_table_size.
- Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables
- Note! BLOB and TEXT columns are not allow in memory tables.
- If you are using these columns raising these values might not impact your ratio of on disk temp tables.
- Current read_buffer_size = 4 M
- Current table scan ratio = 40 : 1
- read_buffer_size seems to be fine
- Current Lock Wait ratio = 1 : 127383
- Your table locking seems to be fine
MySQLTuner 1.2.0 - Major Hayden
-------- General Statistics
- [--] Skipped version check for MySQLTuner script
- [OK] Currently running supported MySQL version 5.1.70-cll
- [OK] Operating on 64-bit architecture
-------- Storage Engine Statistics
- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
- [--] Data in MyISAM tables: 324M (Tables: 119)
- [--] Data in InnoDB tables: 1G (Tables: 1135)
- [--] Data in MEMORY tables: 1M (Tables: 51)
- [!!] Total fragmented tables: 160
-------- Security Recommendations
- [OK] All database users have passwords assigned
-------- Performance Metrics
- [--] Up for: 5d 7h 1m 42s (508M q [1K qps], 3M conn, TX: 1300B, RX: 249B)
- [--] Reads / Writes: 95% / 5%
- [--] Total buffers: 6.2G global + 518.2M per thread (500 max threads)
- [!!] Maximum possible memory usage: 259.2G (827% of installed RAM)
- [OK] Slow queries: 0% (153/508M)
- [OK] Highest usage of available connections: 43% (218/500)
- [OK] Key buffer size / total MyISAM indexes: 100.0M/54.3M
- [OK] Key buffer hit rate: 100.0% (352M cached / 9K reads)
- [OK] Query cache efficiency: 73.4% (355M cached / 483M selects)
- [!!] Query cache prunes per day: 21304412
- [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 53M sorts)
- [!!] Joins performed without indexes: 290151
- [!!] Temporary tables created on disk: 34% (23M on disk / 69M total)
- [OK] Thread cache hit rate: 99% (17K created / 3M connections)
- [!!] Table cache hit rate: 7% (1K open / 26K opened)
- [OK] Open file limit used: 1% (321/20K) [OK] Table locks acquired
- immediately: 99% (362M immediate / 362M locks)
- [OK] InnoDB data size / buffer pool: 1.2G/6.0G
- Run OPTIMIZE TABLE to defragment tables for better performance
- Reduce your overall MySQL memory footprint for system stability
- Enable the slow query log to troubleshoot bad queries
- Adjust your join queries to always utilize indexes
- When making adjustments, make tmp_table_size/max_heap_table_size equal
- Reduce your SELECT DISTINCT queries without LIMIT clauses
- 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_size (> 50M)
- join_buffer_size (> 128.0K, or always use indexes with joins)
- tmp_table_size (> 512M)
- max_heap_table_size (> 16M)
- table_cache (> 4064)
MY.CNF CURRENT SETTINGS BELOW:
- innodb_lock_wait_timeout = 600
- innodb_buffer_pool_size = 6G
- innodb_additional_mem_pool_size = 20M
- #log-error = /var/log/mysql/errors.log