Zabbix DB Performance Issues (MySQL and InnoDB)

[Tim.May]

Hi All,

I have configured my SQL server with the following settings, using the mysqltuner.pl script as guidance:

[client]
user=zabbix
password=zabbix
port = 3306
[mysqld]
bind-address = 127.0.0.1
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
query_cache_size=7M
query_cache_limit=10M
join_buffer_size=128k
thread_cache_size=10
table_cache=55
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections = 400
innodb_buffer_pool_size=10G
tmpdir=/dev/shm
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

and I have the following information in my Zabbix Status window on the Dashboard:

Status of Zabbix
Parameter Value Details
Zabbix server is running Yes localhost:10051
Number of hosts (monitored/not monitored/templates) 161 95 / 0 / 66
Number of items (monitored/disabled/not supported) 6193 6043 / 30 / 120
Number of triggers (enabled/disabled) [problem/ok] 1150 1149 / 1 [14 / 1135]
Number of users (online) 14 3
Required server performance, new values per second 169.64 -

However performance is still significantly affected and when clicking any links results in very long wait times querying data.

The server is running with 8 vCPU's which are Xeon processors running @ 2 Ghz. It has 12 GB of RAM and plenty of storage.

Please can someone assist in tuning the DB to resolve these performance issues ?

Thank you

[tchjts1]

What version of Zabbix are you using?

Is your Zabbix App server and DB server on separate VM's or the same one?

It may not be MySql where the issue lies. It is also dependent on how you have Zabbix App configured.

Take a look at this post. In particular, the last paragraph and the graphs that follow it. This is valuable information on how well you have the App tuned. If you decide to share the graphs from your setup, please provide a 24 hour view in the graphs. 1 hour is too short to get a good idea of what is happening.

https://www.zabbix.com/forum/showthread.php?t=41219

[tchjts1]

Just as a comparison, this is my setup for my 2.0.9 version that I am currently running. This is on a RHEL6 VM with 4vCPU's and 16GB of RAM.

mysql Ver 14.14 Distrib 5.1.61, for redhat-linux-gnu (x86_64)

# Zabbix parameters
innodb_file_per_table
max_allowed_packet = 16M
innodb_data_home_dir = /data/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 32M
innodb_lock_wait_timeout = 120
innodb_log_file_size = 120M
innodb_thread_concurrency = 8
key_buffer_size = 512M
max_connections=512
table_cache=4096
query_cache_size = 128M
tmp_table_size = 8M
thread_cache_size = 64
sort_buffer_size = 16M

[syndeysider]

Also... I hate to be "that guy", but like you, I used to post before searching the Forums.

I've been through this exact same issue and had the community help me to resolution. The forums are full of useful information.

To answer your query. If the gui is "slow" how's your PHP configured? Tweaking memory allocation for PHP can help with the gui. Also, RAMDISK for MySQL is an absolute necessity. I went from 40 seconds page load times to 5 second load times with a 2GB MySQL RAMDISK.

Post your Zabbix Solution graphs so we can see what zabbix is doing.

[syndeysider]

SLES 11 SP3, mysql 5.5.33 32GB Ram, zabbix 2.0.7, 8 cpu's (x64)

800 hosts
600+nvps

skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 128M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 1000
query_cache_size = 256M
query_cache_limit = 2M
table_cache = 1024
innodb_buffer_pool_size = 10240M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
thread_cache = 16
innodb_flush_log_at_trx_commit = 2

log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysqld-slowquries.log

[Tim.May]

More Info

Hi All,

Thank you for all your help so far

In response to tchjts1 questions:

What version of Zabbix are you using?

This is our server information (run from a zabbix_server --version command):
Zabbix server v2.2.0 (revision 40163) (12 November 2013)
Compilation time: Nov 12 2013 11:24:15

Our OS version is (run from a cat /etc/redhat-release command):
CentOS release 6.4 (Final)

Is your Zabbix App server and DB server on separate VM's or the same one?

Our Zabbix server and DB is hosted on one VM. It is currently the only VM on the ESX node and the node is NOT clustered.

We did try to search the forums for an answer first, and had some luck. But we found some of the advice didn't improve our performance and in the end wanted to clarify with our specific configuration. (We are predominantly a Wintel organisation, and so Linux is not our strong suit ).

So I can try and be more specific on our problems, we are finding very long load times when initially opening Zabbix (we use LDAP, but are not experiencing any login issues). Also when clicking any link we often have to wait 20 - 30 seconds for the page to load. Previously this would be almost instantaneous.

We hadn't initially considered PHP, and so will do some forum research before requesting any further help

Finally I have attached the performance graphs from our server. Please note we had been running a Table Optimization process overnight, as it had been recommended by the MySQL tuner.

I hope this answers all the questions, and thank you for all your help so far.

[Colttt]

please use the version 2.2.1, there are some performance improvments.. there was a bug for this.. i think it will helps

[stav13]

What version of MySQL are you using? I would suggest upgrading as I have see significant performance increase from going from 5.1 to 5.5

[syndeysider]

What speed disks you got?

What's our IO doing?

[Tim.May]

Resolution

Hi All,

Firstly thank you for all your help. especially Stav13 who managed to identify the fault.

We were running MySQL 5.1 which it appears only uses a single core processor and so when we upgrade to MySQL 5.5 we saw a vast performance improvement as it was then using all of the available power.

So it appears that was the cause

Thank you for all your help.

Tim