我最近接管了我们其中一个数据库的管理职责。现有的 my.cnf 有一些疯狂的价值观。我应该提到一个表有 18 亿行大部分不需要的历史数据,所以我的首要任务之一是删除 15 亿行。一些跳出来的值:read_rnd_buffer、sort_buffer_size 比样本 my.cnf 值大了几个数量级,thread_stack 看起来太小了。
系统信息:OpenBSD 5.4 amd64
# sysctl -a | egrep -i 'hw.machine|hw.model|hw.ncpu'
hw.machine=amd64
hw.model=Intel(R) Xeon(R) CPU X3430 @ 2.40GHz
hw.ncpu=4
hw.ncpufound=4
# dmesg | grep mem
real mem = 34334113792 (32743MB)
avail mem = 33412423680 (31864MB)
这是 mysqltuner.pl 的结果
mysqltuner.pl
# perl mysqltuner.pl
>> MySQLTuner 1.3.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: ******
Please enter your MySQL administrative password:
[OK] Currently running supported MySQL version 5.1.62-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 198G (Tables: 94)
[!!] Total fragmented tables: 94
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 55m 40s (411K q [123.263 qps], 713 conn, TX: 446M, RX: 59M)
[--] Reads / Writes: 23% / 77%
[--] Total buffers: 4.8G global + 736.1M per thread (300 max threads)
[!!] Maximum possible memory usage: 220.4G (689% of installed RAM)
[OK] Slow queries: 0% (14/411K)
[OK] Highest usage of available connections: 73% (221/300)
[OK] Key buffer size / total MyISAM indexes: 64.0M/91.0K
[OK] Key buffer hit rate: 100.0% (4K cached / 2 reads)
[!!] Query cache efficiency: 8.9% (8K cached / 96K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[OK] Temporary tables created on disk: 1% (74 on disk / 4K total)
[OK] Thread cache hit rate: 69% (221 created / 713 connections)
[OK] Table cache hit rate: 98% (348 open / 355 opened)
[OK] Open file limit used: 0% (48/8K)
[OK] Table locks acquired immediately: 100% (397K immediate / 397K locks)
[!!] InnoDB buffer pool / data size: 4.0G/198.7G
[OK] InnoDB log waits: 0
-------- 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
Enable the slow query log to troubleshoot bad queries
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_limit (> 16M, or use smaller result sets)
innodb_buffer_pool_size (>= 198G)
这是来自 my.cnf 的信息
# grep -e '^[a-zA-z]' /etc/my.cnf
[client]
port = 3306
socket = /var/www/var/run/mysql/mysql.sock
[mysqld]
port = 3306
socket = /var/www/var/run/mysql/mysql.sock
max_connections = 300
max_connect_errors = 10
table_open_cache = 2048
max_allowed_packet = 32M
binlog_cache_size = 32M
max_heap_table_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 384M
sort_buffer_size = 256M
join_buffer_size = 32M
thread_cache_size = 8
thread_concurrency = 4
query_cache_size = 256M
query_cache_limit = 16M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 128K
transaction_isolation = READ-UNCOMMITTED
tmp_table_size = 128M
log-bin=mysql-bin
binlog_format=mixed
long_query_time = 10
server-id = 1
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 4G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 256M
innodb_buffer_pool_size = 4G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 128M
innodb_log_file_size = 1024M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 50
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192
一些想法:
关闭查询缓存(将使用率降低为 0)。对你来说似乎不是一个大胜利,所以这是一个不必要的性能损失(除了内存使用之外)。
除非我读错了报告,否则您似乎没有很多 MyISAM 表。我会将 read_buffer_size 减少到默认值 (128K),将 read_rnd_buffer_size 减少到默认值 (256K)。但是仔细检查 MyISAM 计数,因为一定有人有理由将它们调得如此之高。
您也可以将 sort_buffer_size 设置为默认值 (2MB)。
这让你更接近你的可用内存。
您的每个连接缓冲区太高。查看
mysqltuner.pl
输出。它说736.1M per thread
。这对220GB
Estimate 的影响最大。如何 ?您要求 MySQL 每次只打开一个数据库连接时分配 736MB。请参阅我的帖子打开和关闭数据库连接的成本如何?打开和关闭数据库连接的成本。
您应该关注的是降低以下设置:
对于 32 GB 数据库服务器,这些数字要合理得多。
试试看 !!!