mysql數據庫配置優化

有一個流量很大的數據庫主要執行 innodb，並希望更好地優化 mysql 配置。伺服器有 32Gb 記憶體，最大的表是 ~18Gb innodb。我想我已經做了所有顯而易見的事情來嘗試提高性能，但想知道其他人會推薦什麼。

[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 正在顯示這個

>>  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)

MySQLtuner 建議您將 ram 添加到盒子或減少記憶體設置。這個盒子使用它的硬碟交換文件嗎？

*** MySQL 的最大記憶體使用量非常高*****在增加 MySQL 緩衝區變數之前添加 RAM *** query_cache_size (> 512M)$$ see warning above $$ join_buffer_size (> 256.0M, 或總是使用連接索引) table_cache (> 8096) innodb_buffer_pool_size (>= 61G)*

引用自：https://serverfault.com/questions/488489