database tuning

[Crazy Marty]

I really need some help w.r.t. database tuning. I'm running zabbix-1.4.1, mysql-5.0.22 on a 4-core, 4GB RAM machine, and there are 93 rows in the hosts table (I'm guessing that's 1 row for each template associated with each host). Most of those are SNMPv1 monitors only, though there are about 20-30 hosts that are running the zabbix agent (Linux). Everything is stock CentOS-5.0, untuned.

The database is driving the poor machine to its knees with continuous writes, at the rate of some 5 MB/sec! I dunno beans about tuning InnoDB or mysql in general.

Please help!

[Elshaa]

MySQL InnoDB tuning

Hi,
I've been through this kind of problem with a much less powefull machine (bi-pro P3 900, 512M of ram) having 200 hosts monitored on 20 active checks avery 5 minutes.
Machine was writing 3.5M/sec continuously. BD size was 1.1G after 4 days
First, you should read the InnoDB tuning doc :
http://dev.mysql.com/doc/refman/5.0/...db-tuning.html
and the InnoDB system variables doc :
http://dev.mysql.com/doc/refman/5.0/...arameters.html

Here is my InnoDB tuning that reduced the disk IO from 3.5M/sec to 150K/sec :

[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=350M
innodb_log_buffer_size=8M

## Produce informations about wrong informations in tables.
##innodb_log_file_size=1M

innodb_support_xa=0
nnodb_flush_log_at_trx_commit=0
innodb_checksums=0
innodb_doublewrite=0

query_cache_type=1
query_cache_size=10M

Despite that the Zabbix doc tell performance increase of 1.5 when using InnoDB, I've been experiencing a 1.5 better performances using MyISAM without tuning anything on the mysql Server. Also the DB size shrinked from 1.1G to 300M...
InnoDB seems a powerfull DB engine for powerfull machine, otherwise, I would keep using MyISAM...

[Crazy Marty]

Awesome! That made the same difference for me that you observed on your system! Thanks much!