Perfomance degradation every hour

[nbv]

Hi,
I have Zabbix running on two machines. One with server & front-end, another with MySQL database (i've made partitioning).
Zabbix ver: v2.0.4 (revision 31984)
MySQL ver: 5.5.29
OS: Debian 6 (both)

It works fine, but every hour we have perfomance degradation and it seems that Zabbix died.
Here are some graphs:


My zabbix_server.conf:

...
StartPollers=80
StartPollersUnreachable=80
StartTrappers=20
StartPingers=20
StartDiscoverers=10
DisableHousekeeping=1
CacheSize=64M
StartDBSyncers=30
HistoryCacheSize=20M
TrendCacheSize=16M
HistoryTextCacheSize=8M
...

MySQL my.conf:

[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 8M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 4M
read_rnd_buffer_size = 3M
myisam_sort_buffer_size = 64M
thread_cache = 16
thread_cache_size = 10

query_cache_size = 64M
query_cache_limit = 32M
query_cache_type=1
thread_concurrency = 8
table_cache = 2048

# paths
datadir                         = /opt/mysql/server-5.5/data
tmpdir                          = /tmp/mysqltmp

# network
connect_timeout                 = 60
wait_timeout                    = 28800
max_connections                 = 300
max_allowed_packet              = 64M
max_connect_errors              = 1000

# logs
log_error                       = /var/log/mysql/mysql-error.log
slow_query_log_file             = /var/log/mysql/mysql-slow.log
slow_query_log                  = 1
long_query_time                 = 10

# innodb
innodb_data_home_dir            = /opt/mysql/server-5.5/data
#innodb_data_file_path           = ibdata1:128M;ibdata2:128M:autoextend:max:4096M
innodb_file_per_table           = 1
innodb_status_file              = 1
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size         = 1G
innodb_flush_method             = O_DIRECT
innodb_io_capacity              = 1000
innodb_flush_log_at_trx_commit  = 2
innodb_support_xa               = 0
innodb_log_file_size            = 1G
innodb_log_buffer_size          = 128M

# other stuff
event_scheduler                 = 1
query_cache_type                = 0

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# binary logging is required for replication
log-bin=mysql-bin
expire_logs_days=7

Some Mysql graphs:


Please help me to resolve this problem.

[nbv]

May be it will help.
Before zabbix-server become slow it write in log something like this:

 24802:20130228:100117.386 [Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update items set name='Admin status of interface $1',key_='ifAdminStatus[e0/1]',type=4,value_type=3,data_type=0,delay=60,delay_flex='50/1-7,00:00-24:00',history=7,trends=365,trapper_hosts='',units='',multiplier=0,delta=0,formula='1',logtimefmt='',valuemapid=11,params='',ipmi_sensor='',snmp_community='{$SNMP_COMMUNITY}',snmp_oid='.1.3.6.1.2.1.2.2.1.7.1',port='',snmpv3_securityname='',snmpv3_securitylevel=0,snmpv3_authpassphrase='',snmpv3_privpassphrase='',authtype=0,username='',password='',publickey='',privatekey='',description='The desired state of the interface.',interfaceid=54,flags=4 where itemid=82753;
......

[Colttt]

do you use housekeeper?? if yes, turn it off!!

otherwise, every hour zabbix create trends from history-table..

because the lock-wait.. please create a bug-report

please dont use so many pollers!! stoo many pollers the system goes down!

[nms_user]

Hello,

your INNODB buffer pool size is only 1G (ONE gigabyte)!?

How big is your zabbix db? Enlarge the buffer pool as much as you can, but check that the system doesn't start swapping!

I bet your disks are completely stuck when this gaps are occuring. Interpret the error message - "Lock wait timeout".

Ideally the database can be loaded completely into RAM. My zabbix db is 28GB, my db-server has 128GB of RAM and I set buffer pool to 60GB (means nearly in-memory if mysql would support asynchronous writes) :-) So for DB-writes you need a fast disk subsystem (here: 14x 73GB 15k SAS in hardware-RAID10). And I have "only" 390 NVPs. This zabbix installation is fast as a rocket...

Regards

[nbv]

Thanks for answers!
To Colttt
I'm not using housekeeper. It's disabled.

otherwise, every hour zabbix create trends from history-table..

Can we do something with it? Does it have any settings?

please dont use so many pollers!!

Today i experiment with pollers number. I will find minimum value.

To nms_user
I have 6GB RAM on my DB Server. I realize that i should increase innodb buffer size. What maximum value can i set with this amount of RAM? My database size is about 40 GB.
But we have rather fast HDD! It's HP EVA.

[nms_user]

Upgrade RAM if somehow possible, give MySQL as much RAM so that the system is just NOT swapping. How much - it is your system - you have to know the load factors.

The disk IO not occuring because of RAM buffering is the best IO you can have...

HP EVA - Shared SAN for how much servers? MySQL doesn't like shared disks. My installation is much faster since I use dedicated disks instead of enterprise SAN.

Look here: http://zabbixzone.com/zabbix/mysql-p...ps-for-zabbix/

A great article for tuning MySQL running zabbix db.

Regards

[nbv]

Tomorrow i will try to increase RAM and increase buffer size.

MySQL doesn't like shared disks

I wil look for real server (not virtual) with real raid.
So, you say my problem is in database?

[nbv]

Look here: http://zabbixzone.com/zabbix/mysql-p...ps-for-zabbix/
A great article for tuning MySQL running zabbix db.

It was my primary "how-to". Really good article.

[nms_user]

In detail, MySQL (and every other DBMS) doesn't like the load spikes caused from other machines on the disks.

Maybe your EVA is that fast that you never ever can reach this IO-rates with a dedicated subsystem. Once again: only YOU are (maybe) knowing the ressources of your machines!

The very first step is to upgrade RAM. Give at least half of the DB-size (here: ~20GB) to the machine (and buffer-pool!) and LOOK. Then search for the next bottleneck, if the speed isn't adequate to your needs...

Regards

[nbv]

Thanks for advices. I will try to follow them!

[Colttt]

you can also use mysql-primer!!
its a script ftom a mysql-developer..

[nbv]

It seems that nms_user was right!
I gave 16Gb RAM to mysql server and made innodb_buffer=14GB. And it works!
I'm new in mysql administering and was scared by ./mysqltuner.pl that i don't have enough RAM, so i should reduce buffers.
The only thing I don't like is that virtual memory usage

Is that ok? Or i should reduce innodb buffer size?

[nms_user]

Nice to hear that things are getting better :-)

14GB pool on 16GB physical RAM is a bit too much - you have to adjust. Swapping is the worst that can happen. Ricardo (zabbixzone.com) for example suggests to use 60-70% of installed RAM, but you read this already as we know.

As long as you don't have a bigger buffer-pool than DB-size all the tuners (mysqlprimer, mysqltuner) always criticize something. Try to follow the suggestions as much as you can, but don't overestimate them.

Regards

[nbv]

Hi to all!
Well, my perfomance graph looks much better with 20Gb RAM for mysql.

But it seems zabbix become slower when innodb buffer fills. May be solution is to restart mysql one time a day to clear innodb cache? Is it a good idea?

[Colttt]

no!! that is the cache!!

please run 3-4days or 1week zabbix+mysql..
after this run the mysql-script and please post the output.. dont use the .pl script!! the .sh script is much better!!