Have a heavily trafficed database running mostly innodb and would like to better optimize the mysql config. Server has 32Gb ram and the biggest tables are ~18Gb innodb. I think I have done all the obvious things to try and improve performance, but wondering what others would recommend.
[mysqld]
datadir=/mysql/data
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
key_buffer_size=1G
read_buffer_size=16M
sort_buffer_size=16M
innodb_data_home_dir=/mysql/innodb
innodb_log_group_home_dir = /mysql/log
innodb_file_format=barracuda
innodb_file_per_table=true
innodb_thread_concurrency=8
innodb_buffer_pool_size=20G
innodb_additional_mem_pool_size=128M
innodb_log_buffer_size=512M
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout=50
myisam_sort_buffer_size=64M
read_rnd_buffer_size=32M
max_connections=125
max_user_connections=90
sql-mode=traditional
tmpdir=/mysql/tmp
slow-query-log=1
slow-query-log-file=/mysql/tmp/mysql_slow_queries.log
max_allowed_packet=32M
tmp_table_size=128M
max_heap_table_size=128M
open_files_limit=8096
join_buffer_size=256M
thread_cache_size=150
table_cache=8096
query_cache_size=512M
query_cache_limit=32M
expire-logs-days=3
log-error = /mysql/log/mysqlderror.log
max_binlog_size=1G
interactive_timeout=3600
wait_timeout=28800
[mysql.server]
user=mysql
[safe_mysqld]
err-log=/mysql/log/mysqld-err.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqldump]
max_allowed_packet=1024M
mysqltuner is showing this
>> MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>> 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.21-enterprise-commercial-advanced-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 88)
[--] Data in InnoDB tables: 61G (Tables: 114)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 83
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 27d 18h 19m 53s (1B q [487.302 qps], 5M conn, TX: 751B, RX: 326B)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 22.2G global + 320.2M per thread (125 max threads)
[!!] Maximum possible memory usage: 61.3G (195% of installed RAM)
[OK] Slow queries: 0% (13K/1B)
[OK] Highest usage of available connections: 76% (95/125)
[OK] Key buffer size / total MyISAM indexes: 1.0G/625.9M
[OK] Key buffer hit rate: 100.0% (18B cached / 2M reads)
[OK] Query cache efficiency: 50.2% (433M cached / 863M selects)
[!!] Query cache prunes per day: 2389385
[OK] Sorts requiring temporary tables: 0% (13K temp sorts / 30M sorts)
[!!] Joins performed without indexes: 4230069
[OK] Temporary tables created on disk: 3% (964K on disk / 30M total)
[OK] Thread cache hit rate: 99% (95 created / 5M connections)
[!!] Table cache hit rate: 4% (836 open / 17K opened)
[OK] Open file limit used: 2% (418/16K)
[OK] Table locks acquired immediately: 99% (912M immediate / 912M locks)
[!!] InnoDB data size / buffer pool: 61.6G/20.0G
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
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 (> 512M) [see warning above]
join_buffer_size (> 256.0M, or always use indexes with joins)
table_cache (> 8096)
innodb_buffer_pool_size (>= 61G)
Best Answer
MySQLtuner is suggesting you add ram to the box or reduce the memory settings. does this box use its hard disk swap file?
* MySQL's maximum memory usage is dangerously high Add RAM before increasing MySQL buffer variables * query_cache_size (> 512M) [see warning above] join_buffer_size (> 256.0M, or always use indexes with joins) table_cache (> 8096) innodb_buffer_pool_size (>= 61G)