Boost Zabbix Performance: MySQL SSD Tuning and Configuration Tips

Zabbix and MySQL

In large Zabbix environments the main challenge is MySQL, especially disk I/O.

Below are optimization recommendations to make MySQL use disks efficiently and to maximize the hardware resources available to Zabbix.

SSD is a game‑changer

“Will MySQL run better on SSD?” is a common question. If I/O is your bottleneck—whether queries spend a long time waiting for disk reads (diskstat shows 100‑250 reads per second) or the request load exceeds disk capacity— SSD will provide a huge benefit, not just a marginal one .

The fastest rotating disks can perform about 250 random I/O operations per second, a physical limit. Even with 16 disks in RAID10 a single query reads from only one disk, so reading 15 000 data points for a graph could take 60 seconds.

Enterprise SSDs can handle 15 000‑plus random 16 KB reads per second with a single thread, reducing the same query to roughly 1 second. This allows many more requests in the same time, while a spinning disk would have to share only 250 I/O operations among all requests.

SSD’s only disadvantage is sequential write performance for single‑threaded workloads; if your workload is almost exclusively data collection with little reading, consider other strategies.

MySQL configuration

Beyond improving the storage subsystem, reduce I/O pressure by tuning key my.cnf variables. Most of these tweaks apply to any high‑performance MySQL server, though some are especially beneficial for Zabbix.

innodb_buffer_pool_size – Set to about 75 % of available memory on a dedicated MySQL server; for a shared Zabbix server use a similarly high value.

innodb_buffer_pool_instances – 4 for MySQL 5.5, 8‑16 for MySQL 5.6.

innodb_flush_log_at_trx_commit = 0 – Improves write throughput at the cost of up to one second of data loss on crash; acceptable for many Zabbix setups.

innodb_flush_method = O_DIRECT – Recommended on Linux.

innodb_log_file_size – Choose a size that keeps the log file space for 1‑2 hours of writes (e.g., 128 M or 256 M). Example command:

mysql> pager grep seq; show engine innodb status; select sleep(3600); show engine innodb status;

innodb_read_io_threads, innodb_write_io_threads – Set to 8 if you are not using asynchronous I/O; otherwise leave defaults.

innodb_old_blocks_time = 1000 – Helps avoid buffer‑pool pollution from occasional scans; default in MySQL 5.6.

innodb_io_capacity – Set to several thousand for SSDs (2000 is a good start); 500‑800 for spinning disks; the default 200 is too low for most systems.

sync_binlog = 0 – Keep default unless you need binary‑log synchronization for replication.

query_cache_size = 0, query_cache_type = 0 – Disable the query cache, which can cause contention.

sort_buffer_size, join_buffer_size, read_rnd_buffer_size – Leave at default values; changing them rarely helps.

tmpdir – Using /dev/shm can speed up temporary tables, but on MySQL 5.5 it disables AIO, so monitor and switch back to /tmp if problems arise.

MySQL partitioning

Zabbix can use partitioning to make data pruning easier. Partitioning by date (or sub‑partitioning) can improve the “new values per second” metric that often limits Zabbix throughput.

If you have already applied the I/O optimizations and still cannot achieve the desired insert rate, consider hash partitioning or sub‑partitioning on a key column.

MySQL high availability

Several options exist for MySQL high availability; see external resources such as Yves’ post on HA options, Jay’s Percona XtraDB Cluster webinar, and Fernando’s and Martin’s MySQL Fabric webinar.