Really heavy database usage

[jpawlowski]

Hi there!

I just had to delete the table "history" in order to solve my big performance problem... there where about 6mio entries in it all created within the last 4 days... the effect was that MySQL (version 4.1.11 with InnoDB) had a lot to do with reorganisation of the internal databse structure for really every update or delete command... the innodb file now has about 1.2GB size - is that really normal even after deleting the table with the corresponding data?

is that all normal for usage of MySQL with zabbix? How can I solve this issue? My testing machine is a PentiumIV 2.4Ghz with 2GB DDR-Ram and ICP SCSI RAID1... i think this should be enough... my monitored items where only a hand full linux servers and two windows 2k3 servers...


:-(

[Alexei]

It is not normal, absolutely! 1GB database is nothing for the hardware. Come on, the database fits into physical memory! :)

I don't know how InnoDB works (internal DB structure, data reorganization, etc), however the hardware can easily handle 100GB ZABBIX database provided MyISAM is used. Perhaps some tuning of MySQL settings required.

As far as I know, InnoDB does not decrease size of database space even if there is no data.

[angrox]

It is not normal, absolutely! 1GB database is nothing for the hardware. Come on, the database fits into physical memory! :)

I don't know how InnoDB works (internal DB structure, data reorganization, etc), however the hardware can easily handle 100GB ZABBIX database provided MyISAM is used. Perhaps some tuning of MySQL settings required.

Hi Alexei

I encounter the same problem. MySQL DB structure is InnoDB and i have heavy DB usage. Database size is about 500 MB. According to the posts here in forum myISAM is prefered over innoDB - but why are all tables in create/mysql/schema.sql are created as innoDB (at least in zabbix 1.0) --> "... type=innoDB ... "


best regards,

Martin

[cadbury]

i used to use innoDB type for my zabbix tables,
for the little number of items i used, in 3 days it was about 20Mo
i switched to myisam for the tables history and history_str,
and the database was 10Mo after that.
it is now growing slower.

With innodb, sometimes even after deleting a large amount of values, the database size does not reduce.
I think as innodb is the prefered type for parallelisme, the innodb engine has some specific behaviors to save time, and not disk space.

i recommend switching to myisam type if you can't afford innodb growth, but if your server is ok with innoDB (disk space, memory and cpu usage), why change?
as you configure your items, the database will grow for 7 or 14 days, perhaps 30, as long as you set the "keep history" parameter, and then the trends will only take a few size compared to history.

[cadbury]

see also http://www.zabbix.com/forum/showthread.php?t=739 to see the evolution of your database size