Zabbix性能调优:MySQL配置优化
对于大内存服务器,可使用tmpfs文件系统加速MySQL,一般情况下设成物理内存的8%-10%。
mkdir -p /zabbixtmp
chown mysql:mysql /zabbixtmp
echo "tmpfs /zabbixtmp tmpfs rw,size=400m,nr_inodes=10k,mod=0700,uid=mysql,gid=mysql 0 0" >>/etc/fstab
配置/etc/my.cnf
key_buffer_size = 256M
max_allowed_packet = 4M
table_open_cache = 512
sort_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 256M
tmpdir=/zabbixtmp
max_connections = 1000
max_connect_errors = 100
open_files_limit = 65535
innodb_buffer_pool_size = 4096M #一般设为服务器物理内存的70%-80%
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
slow_query_log=on
long_query_time=1
slow_query_log_file=/opt/logs/mysql/slow_query.log
log_queries_not_using_indexes=on
max_allowed_packet = 16M
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M