将my.conf值返回到正常级别(内存总占用量=220 to )

Question

我最近接管了我们的一个数据库的管理职责。现有的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 <major@mhtx.net>
 >>  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

Answer 1

以下是一些想法：

关闭查询缓存(将这种使用减少到0)。对你来说似乎不是一个大赢家，所以这是一个不必要的性能打击(除了内存的使用)。

除非我看错了报告，否则您似乎没有太多的MyISAM表。我会将read_buffer_size降为默认(128 K)，将read_rnd_buffer_size降为默认(256 K)。但是仔细检查MyISAM计数，因为一定有人有理由把这些调得这么高。

您可能也可以将sort_buffer_size设置为默认(2MB)。

这会让你更接近你的可用记忆。