mysql数据库配置优化

2024-6-2 • tag-icon

有一个流量很大的数据库，主要运行 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 <[email protected]>
 >>  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)

答案1

MySQLtuner 建议您向盒子中添加 RAM 或减少内存设置。这个盒子是否使用其硬盘交换文件？

* MySQL 的最大内存使用量非常高增加 MySQL 缓冲变量之前添加 RAM * query_cache_size (> 512M) [参见上面的警告] join_buffer_size (> 256.0M，或始终使用带有连接的索引) table_cache (> 8096) innodb_buffer_pool_size (>= 61G)

答案1

相关内容