For those who have no clue about what Zabbix is here is a short description as stated on Zabbix web site.
Zabbix is an enterprise-class open source distributed monitoring solution. In this article we will discuss about how to optimize MySQL as load on Zabbix might lead to various problems.
Use a tmpfs tmpdir. In /etc/my.cnf configure:
/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.
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:
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.
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.
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
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.
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.