Here's important mysql config variables (my.cnf) that affect zabbix performance:
========
#binary log which has to be disabled if you dont really need it, avouds unnecessary disk IO.
#log-bin
========
#this has to be set to 50-80% of your physical memory (depends what else you run in this machine), but not higher than 2G for 32bit platform.
innodb_buffer_pool_size=2G
========
# this has to be set to about 25% of innodb_buffer_pool_size variable.
innodb_log_file_size=500M
# WARNING!!! you have to gracefully shutdown database, remove (or better move to different location untill new logs will be created) old transaction log files before you change this variable and start database. The log files located here: /var/lib/mysql/ib_logfile* . it will take some time for mysql to create new log files and my debian startup script sais "failed" even though mysql starts, works for a while then goes online with new logs created. Its better to do mysqldump just for a case before you do this.
============
# 0 here disables flush on every commit, instead of that mysql flushes logs every second. So if your box goes down you might loose 1 second of data which is not important for zabbix but might be important for other application. Consider this before disabling flush on commit. This improves write performance alot.
innodb_flush_log_at_trx_commit=0
============
# Following variables might improve or might not improve your performance. If your box has several CPUs and fast disk system set this to the number of CPUs you have. If you however have slow disk this might create thread tharshing and work towards performance degradation. If not sure set small value like 2 to have 2 threads running instead of 1.
innodb_thread_concurrency=2
thread_concurrency = 2
============
# this should be more than number of zabbix_server daemons, if you have queue backup and your box is not very busy most likely you need to increase number of zabbix_server daemons, dont forget to increase this number as well as because zabbix server completely crashes if any daemon is not able to connect to mysql.
max_connections = 200
============
Now after you change that look how fast your queue drains compared to what it was before.
========
#binary log which has to be disabled if you dont really need it, avouds unnecessary disk IO.
#log-bin
========
#this has to be set to 50-80% of your physical memory (depends what else you run in this machine), but not higher than 2G for 32bit platform.
innodb_buffer_pool_size=2G
========
# this has to be set to about 25% of innodb_buffer_pool_size variable.
innodb_log_file_size=500M
# WARNING!!! you have to gracefully shutdown database, remove (or better move to different location untill new logs will be created) old transaction log files before you change this variable and start database. The log files located here: /var/lib/mysql/ib_logfile* . it will take some time for mysql to create new log files and my debian startup script sais "failed" even though mysql starts, works for a while then goes online with new logs created. Its better to do mysqldump just for a case before you do this.
============
# 0 here disables flush on every commit, instead of that mysql flushes logs every second. So if your box goes down you might loose 1 second of data which is not important for zabbix but might be important for other application. Consider this before disabling flush on commit. This improves write performance alot.
innodb_flush_log_at_trx_commit=0
============
# Following variables might improve or might not improve your performance. If your box has several CPUs and fast disk system set this to the number of CPUs you have. If you however have slow disk this might create thread tharshing and work towards performance degradation. If not sure set small value like 2 to have 2 threads running instead of 1.
innodb_thread_concurrency=2
thread_concurrency = 2
============
# this should be more than number of zabbix_server daemons, if you have queue backup and your box is not very busy most likely you need to increase number of zabbix_server daemons, dont forget to increase this number as well as because zabbix server completely crashes if any daemon is not able to connect to mysql.
max_connections = 200
============
Now after you change that look how fast your queue drains compared to what it was before.