Zabbix - MySQL server settings
Using Zabbix now since beginning of October 2006 . I get quite a few hits from Google off of it including from places that raise my curiosity. In any case, I added 20 new machines and suddenly the machine got boggled down due to the amount of IO writes being made to the MySQL database. After the jump (Click on Continue Reading) are the settings that I came up with and now my loads are back to the 1.00 - 1.10 range and the queue is always empty.
Hopefully sometime in the future I’ll post some other tweaks I made, especially regarding the monitoring of processes. I don’t appreciate getting false positives thus I’ve tweaked a number of default settings in order to bring this number down to maybe a handful a month.
My /etc/mysql/my.cnf file for Zabbix:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_log_arch_dir = /data/mysql
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 128M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
max_connections = 200
table_cache = 2048
sort_buffer_size = 16M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
myisam_sort_buffer_size = 64M
skip-external-locking
bind-address = 127.0.0.1
key_buffer_size = 128M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
tmp_table_size = 128M
query_cache_limit = 1048576
query_cache_size = 32M
query_cache_type = 1
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
Leave a Reply