Discussion: Zabbix server performance tweaks.

[burtininkai]

Mates,

Forum has a lot of info, but still have to rise same questions again:

1. Which DB would be better to use? Mysql or Postgres?
2. If mysql is used, the which engine would be more stable?
3. Any sugestions regarding using zabbix server + db on same vmware vm?
4. Currently using deflate with apache, any other things to tweak web gui?
5. Support from Zabbix SIA, would be nice to hear oppinions.

My install is VM, 2 cores CPU, 2 GB ram, Fedora 32 bit, Mysql, MyISAM. DB accessed using sock, Zabbix 1.6.6, appache + dqflate module.

Number of hosts (monitored/not monitored/templates) 94 85 / 0 / 9
Number of items (monitored/disabled/not supported) 26733 6777 / 19954 / 2
Number of triggers (enabled/disabled)[true/unknown/false] 1691 1691 / 0 [0 / 821 / 870]
Number of users (online) 7 1
Required server performance, new values per second 16.5017 -

Current main problem is long respond from mysql db. Ideas are:

• Use 64 bits os with mo ram, but ram usage is not so high on the server.
• Move Zabbix and DB to phisical host.
• Get different DB.
• Optimize queries using some tweaks from http://www.zabbix.com/forum/printthread.php?t=12902

All ideas are welcomed, thank You for support.

[richlv]

Mates,

Forum has a lot of info, but still have to rise same questions again:
[*]Which DB would be better to use? Mysql or Postgres?

a bit of a religious topic, but currently mysql is better tested.

[*]If mysql is used, the which engine would be more stable?

innodb, definitely

[*]Any sugestions regarding using zabbix server + db on same vmware vm?

there have been reports about performance problems of db under vm

My install is VM, 2 cores CPU, 2 GB ram, Fedora 32 bit, Mysql, MyISAM. DB accessed using sock, Zabbix 1.6.6, appache + dqflate module.

Number of hosts (monitored/not monitored/templates) 94 85 / 0 / 9
Number of items (monitored/disabled/not supported) 26733 6777 / 19954 / 2
Number of triggers (enabled/disabled)[true/unknown/false] 1691 1691 / 0 [0 / 821 / 870]
Number of users (online) 7 1
Required server performance, new values per second 16.5017 -

Current main problem is long respond from mysql db. Ideas are:

ouch. with 16 new values per second it should not be slow, really

[*]Move Zabbix and DB to phisical host.

start with moving the db to a physical host, see if that helps. also check buffers configured for mysql

[burtininkai]

innodb, definitely

In that case how to optimize tables? Even in case of use file per table, optimize still not working.


Thank You for respond.

[NOB]

Hi

I agree with richlv.

How about the load on the ESX Server ?
We have bad experiences with the scheduling of the ESX server.

How about the CPU load of the VMware host ?
Do you see peaks like 100% for a short time and then 0 ?

Some metrics to check.

We are monitoring a lot of systems but use an HP 380 GL5 without
any problems.
Quad or Eight core Intel Xeon, 4 GB RAM, 15k SAS disks, 64-bit OS,
MySQL RPM from Ourdelta (http://ourdelta.org/), RedHat 5.2/5.3

HTH

Norbert.

[burtininkai]

How about the load on the ESX Server ?
We have bad experiences with the scheduling of the ESX server.

Take a look to ESX CPU usage for last month, seems to ok for me.



VM cpu usage is a litle bit higher but almost the same.



TOP shows me higher cpu usage on host, system load changes from 0.62 to 5.00 time to time, depends on Mysqld cpu usage. Memory usage seems to be ok. Mysqltunner shows only queries warnings.

One more graph (disk usage):

Do you see peaks like 100% for a short time and then 0 ?

Yes these one exist time to time, may be search for best practices on VMware for MySQL servers. Guys could You share Innodb settings from my.cnf files? Do You use any tweaks for Apache and PHP?

Thanks in advance.

p.s. Still same question, how do You optimize InnoDB tables? Do You use ALTER Tables and safe-mod or something else?

[burtininkai]

Frounded some interesting Best Practice from VMware for use with MySQL, hope this will give some effect.

http://www.vmware.com/resources/techresources/10007

[burtininkai]

Fcuk, only easy theoretical things, almost no details. So recreated MyISAM DB to InnoDB with my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mytmp
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

##### Performance tunning
safe-mode

innodb
innodb_data_home_dir=/var/lib/mysql/
slow_query_log_file=/var/log/mysql.slow.log
slow_query_log=1
log-queries-not-using-indexes

innodb_buffer_pool_size=12000M
innodb_flush_method=O_DIRECT

innodb_log_file_size=64M
innodb_log_buffer_size=8M
innodb_thread_concurrency=8
innodb_file_per_table

thread_cache_size=16K

query_cache_size=1K
query_cache_type=1
query_cache_limit=256M



tmp_table_size=196M
max_heap_table_size=196M

table_cache=512
max_connections = 300

innodb_flush_log_at_trx_commit=2 
innodb_buffer_pool_size=483M


join_buffer_size=375k
read_buffer_size=375k
read_rnd_buffer_size=375k

###########################################


# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

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

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
#connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
# connect-string="host=localhost:1186"

Current performance:



It's very slow...

[burtininkai]

P.S. Tryed one thing, reconfigured VM to use only one CPU, all work at high speed. Is it possible, that this could cause a problem?

[burtininkai]

P.S. Tryed one thing, reconfigured VM to use only one CPU, all work at high speed. Is it possible, that this could cause a problem?

Seems to be same thing, load is high, zabbix dying...

[mlange]

We had also problems running the mysql server on a virtual machine. In case the IO performance suddenly decreased Zabbix was unable to execute the requests in time and queued horribly. All these problems disappeared when we switched to a physical machine. And innodb is definitely the way to go.

[Alexei]

By the way, our latest tests clearly demonstrate that PostgreSQL 8.3 performs much better than MySQL 5.0.x InnoDB. It requires much less disk IO comparing to MySQL.

It is very surprising result taking into account our past experience with terrible performance of PostgreSQL 7.x.

[mlange]

btw. we are using MySQL 5.1.

[Alexei]

I do not think that MySQL 5.1 delivers noticeably better performance. Great advantage of 5.1.x is support of partitioned tables, which makes possible disabling of Zabbix housekeeper and managing historical tables on database side.

[burtininkai]

By the way, our latest tests clearly demonstrate that PostgreSQL 8.3 performs much better than MySQL 5.0.x InnoDB. It requires much less disk IO comparing to MySQL.

It is very surprising result taking into account our past experience with terrible performance of PostgreSQL 7.x.

This one quite interesting, as I remember most common problem I had with postgres was vacuuming.

Currently installed new phisical server IBM 3550 64 bit Fedora 4 GB RAM, RAID 10, 2x Intel 5150. Made backup of old MySQL server, so how do U think, it's better to use MySQL or convert to Porstgres?

[Alexei]

This one quite interesting, as I remember most common problem I had with postgres was vacuuming.

Currently installed new phisical server IBM 3550 64 bit Fedora 4 GB RAM, RAID 10, 2x Intel 5150. Made backup of old MySQL server, so how do U think, it's better to use MySQL or convert to Porstgres?

Our tests were performed with disabled Zabbix housekeeper and database size around 200GB on HP DL380 G5, 1xQuad Xeon, 6GB, RAD10, 4x176GB with BBWC. It would be interesting to see how both databases perform with enabled table partitioning for historical tables.

For Zabbix 1.8 I would recommend PostgreSQL 8.3, for Zabbix 1.6 I would go with MySQL 5.1.x. Anyway conversion from one engine to another is quite straight-forward.