Performance Tuning MySQL for Zabbix

On my previous post I've shared some tips on how to tune ZABBIX configuration to get better results,however the most important tunning you have to do is to the data base server. Remember that this values depend on how much memory you have available on your server, here is how I've configured my MySQL server:

1. use a tmpfs tmpdir, create a folder like /mytmp and In /etc/my.cnf configure:

tmpdir=/mytmp

in /etc/fstab i put:

tmpfs /mytmp tmpfs size=1g,nr_inodes=10k,mode=700,uid=102,gid=105 0 0

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. I don't know about the size and nr_inodes options there, I just saw those in linux tmpfs docs on the web and they seemed reasonable to me.

2. Buffer cache/pool settings.

In /etc/my.cnf jack up innodb_buffer_pool_size as much as possible. If you use /usr/bin/free the value in the "+/- buffer cache" row under the "free" column shows you how much buffer cache you have. I've 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=8000M
innodb_flush_method=O_DIRECT

3. Size the log files.

The correct way to resize this is documented here:

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

In /etc/my.cnf the value I'm going to try is:

innodb_log_file_size=64M

A too small value means that MySQL is constantly flushing from the logfiles to the table spaces. It is better to increase this size on write-mostly databases to keep zabbix streaming to the logfiles and not flushing into the tablespaces constantly. However, the penalty is slower shutdown and startup times.

4. 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.
Turn on slow query logging:

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

This setting seems to affect the hit rate of Threads_created per Connection.

thread_cache_size=4

query_cache_limit=1M
query_cache_size=128M
tmp_table_size=256M
max_heap_table_size=256M
table_cache=256
max_connections = 400
join_buffer_size=256k
read_buffer_size=256k
read_rnd_buffer_size=256k 

This should help a lot for high volume writes.

innodb_flush_log_at_trx_commit=2