1.4 Performance - Makes MySQL Very Busy?

[agehring]

I was having problems with 1.4/1.4.1 (SVN) where i was basically running out of memory. While it appeared to be a memory leak, Norbert posted a patch to my thread that solved the problem.

While none of that may seem relevant, the side effect was to make the system run at a more resonable rate.

It may have something to do with your performance issue, no?

http://www.zabbix.com/forum/showthread.php?t=6359

Andrew

[dlindquist]

There is no difference in a number of executed SQL statements per new value between 1.1.x and 1.4. If a distributed monitoring is enabled, it adds an additional insert statement, nothing more.

OK, that's good to know...

So why then, do I see this:

1.1.7: One Host, the Zabbix Server, "Linux" template, all defaults == ~0.0 load average
1.4: One Host, the Zabbix Server, "Linux" template, all defaults == ~0.4 load average

If I use "top", I can see that generally mysqld is the most busy, and zabbix_server is vying for top spot with it...

I still don't know why I would be seeing this...

[dlindquist]

Hmm, well a couple of things seemed to help a bit:

1. Turn off "discovery" -- it seems to generate a lot of log traffic, and turning it off reduced the mysql load some.
2. Check your zabbix server log for items that repeatedly say 'not supported', and disable those items.

[NOB]

I was having problems with 1.4/1.4.1 (SVN) where i was basically running out of memory. While it appeared to be a memory leak, Norbert posted a patch to my thread that solved the problem.

While none of that may seem relevant, the side effect was to make the system run at a more resonable rate.

It may have something to do with your performance issue, no?

http://www.zabbix.com/forum/showthread.php?t=6359

Andrew

Hi,

no. My fix just helps if you have setup the master node with nodeid = 0
and no slaves are defined, yet.
In that case there is an endless loop (recursive call of a function) in zabbix_server which uses up all memory until it crashes.
This is a very special case and has nothing to do with this performance
problem.
I did post my fix, to avoid that Alexei (and others) are looking for memory
leaks where there is none. Finding memory leaks is difficult and finding
non-existing ones even more

Sorry, but this fix is of no help for this problem.

I am using PostgreSQL and as fas as I have seen, the feature of
autoincrementing values (some SQL-dialects call it "serial") is not used.
That's why there was a report of heavy use of the DB table "ids", even
for MySQL.

Regards,

Norbert.

[Markus]

Hi Norbert

I am using PostgreSQL and as fas as I have seen, the feature of
autoincrementing values (some SQL-dialects call it "serial") is not used.
That's why there was a report of heavy use of the DB table "ids", even
for MySQL.

regarding the issue with the 'ids' table, have you investigated this any further and found a solution? I don't really understand how to interpret your comment.

Thanks

Markus

[bbrendon]

I'm not going to be scientific, but my zabbix 1.1.7 system runs between 75-300 qps and averages at about 80-85 qps

Zabbix has 76 hosts, 1127 items active, 574 triggers active.

It also has website databases, spamassassin, and other stuff on the same mysql server.

When the system had 768 MB of RAM, performance was poor. It now has 1.5 GB and works fine.

It will be interesting to see what happens with 1.4.

I have upgraded to 1.4.1. Its not a perfect comparison because during the upgrade I made a few changes to things. I now have:

78 hosts, 1240 items active, 653 triggers active.
average qps is now at about 120.

I'm wondering how I got so many new triggers... ahh! I think I imported some templates with enabled triggers. They aren't attached to hosts so I really don't have much more load. (in theory)

[YuriV]

Just fought with 1.4 for a few days, 99% cpu on mysql didnt matter what I did.

Machine: P4 1.6Ghz 512Ram 20GB IDE boot running Debian Linux & 36Gb Scsi DB drive (spare parts).

Tried messing with every option possibly on my.cnf ended up converting all tables to MYISAM from Innodb instantly solved the issue, now mysql runs between 5% - 35%

149 hosts monitored
495 items
127 triggers.

All are pinged once per minute.

for those that care...
# * Fine Tuning
#
key_buffer = 384M
max_allowed_packet = 16M
thread_stack = 1024K
thread_cache_size = 16
max_connections = 150
table_cache = 900
thread_concurrency = 20
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 32M


Key efficiency sitting at 99.7% so far (yay), i had -1000% before... (yes negative)

[Palmertree]

Found qps increasing by the second. Noticed in the mysqld.log file the following messages:

070711 21:02:38 [Warning] Aborted connection 33216 to db: 'zabbix' user: 'zabbix' host: 'localhost' (Got an error reading communication packets)

I increased the maximum size of a query packet in my.cnf and the errors and the increased qps stoped increasing.

# The maximum size of a query packet the server can handle as well as
# maximum query size server can process (Important when working with
# large BLOBs). enlarged dynamically, for each connection.
max_allowed_packet = 512M

[bbrendon]

My mysql.log is always 0 bytes. I hope that is okay.

[Alexei]

Just fought with 1.4 for a few days, 99% cpu on mysql didnt matter what I did.

Ubuntu and some other distributions automatically start checking of database integrity after server restart. It has nothing to do with ZABBIX.

You may run:

mysqladmin -uroot processlist

to see what MySQL is actually doing.

[Wido]

I had the same problem, my server was doing over 1k QPS with Zabbix.

Today there was a poweroutage (how sad ) at our DC which ofcourse lead to a reboot of our Zabbix server.

I just checked and now the server is doing 60 QPS and is still monitoring all items?

I don't know where it came from, but it seems to be working now?

[alj]

Hi Alexei,

So the constant "35 queries per second" type of load that I am seeing is expected?

I'm running an Athlon Thunderbird 1.2 GHz with 512M RAM.

I was expecting to see "bursts" of activity for a very short period of time around each 30s mark as the updates were done... I wasn't expecting to see such consistent load on the DB for such a small monitoring set.

By simple scaling, this would seem to indicate that I'd likely see constant, 100% load trying to monitor as few as 5 or 6 hosts -- and to monitor the 30 or so machines at my work would load the fastest machine on the market at 100%... Does this sound right?

TIA!

Here's some recommendation that might help to improve performance (increased about 5 times for me):

1. Use InnoDB, do not use MyISAM (myisam has very bad write performance).
2. Give 50-70% of your physical memory to
innodb_buffer_pool_size=
parameter of my.cnf (i have 2GB there).
3. Turn on delayed flush on commits:
innodb_flush_log_at_trx_commit=0
4. disable transaction logging
(comment out log-bin) - noone usually uses it but it takes alot of IO.

5 (optional) increase journal size to 25% of your buffer pool size set in (2):
innodb_log_file_size=500M
Note - after you change option 5 you need to delete both journals - ib_logfile0 and ib_logfile1
mysql will recreate them upon startup, but back your database up before you do that.

6. On SMP system set number of threads equal to number of cpus.
innodb_thread_concurrency=2
thread_concurrency = 2
(i have 2 cpu box).

If you set thread concurrency to 20 or something like that (as i saw somewhere in this thread) mysql performance will go down (possibly threads thrashing problem?)


Most of other options are less important as they mostly affect read performance when for zabbix write performance is the key.


Another thing to adjust is number of pollers in zabbix_server.conf.

default value is too small, it creates bottleneck.
if you set it too big - performance will be poor as well because of locking/thrashing.
Ofr 1.1.7 zabbix optimal value was 70 for me. For 1.4 it might be different (it will probably will be much smaller because poller processes are not being wasted for hosts that are down anymore). Do you experiments and measure how fast zabbix queue drains after you start it.

[alj]

Just fought with 1.4 for a few days, 99% cpu on mysql didnt matter what I did.

Machine: P4 1.6Ghz 512Ram 20GB IDE boot running Debian Linux & 36Gb Scsi DB drive (spare parts).

Tried messing with every option possibly on my.cnf ended up converting all tables to MYISAM from Innodb instantly solved the issue, now mysql runs between 5% - 35%

149 hosts monitored
495 items
127 triggers.

All are pinged once per minute.

for those that care...
# * Fine Tuning
#
key_buffer = 384M
max_allowed_packet = 16M
thread_stack = 1024K
thread_cache_size = 16
max_connections = 150
table_cache = 900
thread_concurrency = 20
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 32M


Key efficiency sitting at 99.7% so far (yay), i had -1000% before... (yes negative)

you didnt touch innodb buffer size which is too small by default.
I bet that you could achieve better performance with innodb if you tuned innodb specific parameters (see my post above).

Heres my parameters:

query_cache_limit = 1048576
query_cache_size = 16777216
query_cache_type = 1
key_buffer = 32M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 80

########################################
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
innodb_buffer_pool_size=2G
innodb_additional_mem_pool_size=20M
#
# Set the log file size to about 25% of the buffer pool size
# (have top kill all binary logs if you change that, otherwise comment out next line)
innodb_log_file_size=500M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=0
innodb_lock_wait_timeout=50
#
# Uncomment the next lines if you want to use them
innodb_thread_concurrency=2
thread_concurrency = 2


P.S.
137 monitored hosts, 7239 monitored items, 3418 monitored triggers.

About key efficiency - it is measured for MyISAM only (key buffer is for myisam tables only) so your -1000% number makes total sense if you used innodb, just ignore it and use different tools that are innodb aware.

For MyISAM you give all memory to key_buffer_size but if you use innodb you should give that memory to innodb_buffer_pool_size.

If you give all memory to key_buffer_size but use innodb - no wonder you get poor performance.

[BHG_2008]

My.cnf for CentOS 5 and MySQL 5.0.24 on big box

CentOS 5 on Dell 1950 x86_64 with 2 quad core processors and 16GB RAM.
ZABBIX 1.4.4, 25 pollers, 7 pingers - 2,048 hosts, 29,422 items, 17,879 triggers
MySQL version 5.0.45, running on /data partition.
Current queries per second average is 1,854

my.cnf:

[mysqld]
# Locations
datadir=/data/mysql
socket=/var/lib/mysql/mysql.sock
# Logging
innodb_log_file_size            = 200M  #InnoDB journal (log) file size
# Troubleshooting
#log-slow-queries               = /data/mysql/slow.log  #Log every query exceeding log_query_time below
#log-queries-not-using-indexes          #Logs of every unindexed query
#log-bin                        = /data/binary_logs/node300       #Space and I/O HOG
#max_binlog_size                = 50M   #Log file size
#expire_logs_days               = 2     #Days before deleting binary log files
# Connections
max_connections                 = 150
max_user_connections            = 120
max_allowed_packet              = 4M
# Memory
key_buffer_size                 = 768M
join_buffer_size                = 64M
query_cache_size                = 512M
query_cache_limit               = 512M
table_cache                     = 32M
thread_cache_size               = 20
sort_buffer_size                = 64M
read_buffer_size                = 32M
read_rnd_buffer_size            = 32M
tmp_table_size                  = 256M
max_heap_table_size             = 256M
innodb_buffer_pool_size         = 1G
# Options
long_query_time                 = 2
old_passwords                   = 1
skip-bdb                                #Disables BDB engine
innodb_file_per_table           = 1     #Creates idb for every table in db folders
innodb_thread_concurrency       = 8     #Should match number of processors

[mysql.server]
user=mysql
basedir=/data

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[BHG_2008]

Tuned using MySQLTuner 0.9.9