Performance Tuning for Zabbix

Recommendation 1.

Use a tmpfs tmpdir. In /etc/my.cnf configure:

tmpdir=/mytmp

/etc/fstab should look like:
tmpfs /mytmp tmpfs size=1g,nr_inodes=10k,mode=700,uid=27,gid=27 0 0
Ofcourse depending the Linux distribution. The above is taken from a RedHat Enterprise Server 5.

You’ll have to mkdir /mytmp and the numeric uid and gid values for your mysql user+group need to go on that line. Then you should be able to mount /mytmp and use tmpfs for mysql’s temp directory.

Recommendation 2.

Buffer cache/pool settings.

In /etc/my.cnf jack up innodb_buffer_pool_size as much as possible. I had a long-running system and i sized it so that on a 16GB box that i should only have about 1GB of buffer cache available. If you use /usr/bin/free on a redhat system value in the “+/- buffer cache” row under the “free” column shows you how much buffer cache you have. We also setup innodb to use O_DIRECT so that the data cached in the innodb buffer pool would not be duplicated in the filesystem buffer cache. So, in /etc/my.cnf:

innodb_buffer_pool_size=12000M
innodb_flush_method=O_DIRECT

Recommendation 3.

Other parameters

innodb_file_per_table

Use file_per_table to keep tablespaces more compact and use “optimize table” periodically. And when you set this value in my.cnf you don’t get an actual file_per_table until you run an optimize on all the tables. This’ll take a long time on the large zabbix history* and trends* tables.

log_slow_queries=/var/log/mysql.slow.log

Turn on slow query logging.

thread_cache_size=4

It seems to affect the hit rate of Threads_created per Connection in ‘show global status’ — with it set to 4 i had 3228483 Connections and 5840 Threads_created, which is a 99.2% hit rate — higher number of Threads_created is worse.

query_cache_limit=1M
query_cache_size=128M
tmp_table_size=256M
max_heap_table_size=256M
table_cache=256

This was also based on other tuning docs, but based on the number of tables in a zabbix database with 73 tables in zabbix plus temp tables and mysql tables and stuff that this was appropriate.

max_connections = 300

I jacked this up because i had a measured max conns of 50 and wanted headroom and configuring connections is reasonably cheap in mysql

innodb_flush_log_at_trx_commit=2

I had tried this before and not seen a huge benefit, but the consultant advised that since i didn’t care about strict ACID that this would help a lot for high volume writes.

join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k

Try keeping these values no higher than this, because they are per-thread buffers. I had run into conflicting recommendations on the net to increase the size of the join_buffer_size in particular.