Best MySQL storage engine for ZABBIX

[Andriy Fedorov]

Hi there,

What is the best MySQL storage engine for ZABBIX and why?

InnoDB or MyISAM?

Thanks.

Regards,
Andriy.

[Vince2]

http://http://www.zabbix.com/manual/v1.1/config_tuning.php

[Alexei]

I would suggest using InnoDB if you have decent hardware. The biggest disadvantage of MyISAM (leaving along table-level locking) is very slow repair of corrupted tables. MySQL will spend hours trying to repair 10GB history table...

[alj]

Hi there,

What is the best MySQL storage engine for ZABBIX and why?

InnoDB or MyISAM?

Thanks.

Regards,
Andriy.

Some performance data:

http://monstera.man.poznan.pl/wiki/index.php/Mysql_vs_postgres

MyISAM will be the fastest but it has no log so no data integrity and long repair times.
Postgres beats InnoDB on insert speed (which is most important for zabbix probably) so it might work a bit faster than innodb or may not. It would be interesting to know if somebody tried both.

[Alexei]

PostgreSQL works slower than MySQL InnoDB, in ZABBIX environment. It was true 2-3 years ago, perhaps something changed in most recent releases. ZABBIX does many update operations which PostgreSQL does not handle efficiently.

[mruk]

I stopped using MySQL for Zabbix. I`ve had enough. The ibdata files can only expand, so even after deletion of historical data disk space cannot be reused for other files. The InnoDB can repair it`s table, but if it can`t - sorry you have no data. How can InnoDB be crashed? I found 3 ways that led to a crash which InnoDB was unable to recover from:
- out of disk space while running 2 transactions on 2 databases (when using 1 ibdata file)
- running a stored procedure and got out of memory - mysql killed by kernel
- got a bad sector in ibdata file

Maybe I`m just unlucky...

[pdwalker]

PostgreSQL works slower than MySQL InnoDB, in ZABBIX environment. It was true 2-3 years ago, perhaps something changed in most recent releases. ZABBIX does many update operations which PostgreSQL does not handle efficiently.

There have been a number of performance increases in postgres over the last couple of years.

Probably the biggest change that will directly affect postgres performance is the autovacuum setting. Previously, without constant vacuuming of the database, the db would fill with "dead" tuples that would not get reused until the vacuum command was run. This dead space would cause db operations to take longer as it had to scan more rows of data.

It might be worth comparing the performance numbers again to see if postgres meets your performance targets.

- Paul

[pdwalker]

MyISAM will be the fastest but it has no log so no data integrity and long repair times.

This is only true as long as there is only one reader or writer to the database. As soon as you start putting in multiple writers, MYISAM falls down because of the very course locking mechanism used with MYISAM tables.

INNODB tables have a finer grain of locking which allow multiple writers and readers to use the same table without (usually, there are still some issues with how INNODB locks rows) locking each other out.

- Paul