High performance MySQL hints

[marcel]

Just some hints for my own reference and to help others

- use RAID10 and ext4 for database files
- check available and cached threads for MySQL (monitor)
- use InnoDB - whole database in ONE FILE (!)
- run mysqltuner.pl now and then
- monitor IO wait
- monitor size of tables via MySQL and overall size of InnoDB file itself

SELECT table_name,sum(data_length+index_length ) / 1024 / 1024 "MB" 
FROM information_schema.TABLES WHERE table_schema'"zabbix";

[nelsonab]

You may also want noatime set for the file system which runs the database. This will not set the access time meta information thus reducing the need for one additional write.

Also consider putting indexes on a faster filesystem than the database. Even better if you can use a different controller as this will create two distinct io paths and thus will not have contention issues between the two.

Create a RAM file system for temporary files, 500MB worked well for me and have MySQL use this file system for all of it's temporary tables. The performance gain from this is huge. No matter how hard you tune MySQL wants to set up temporary files constantly.

Be sure your innodb file is set to grow.

Use a hardware RAID controller with a battery backup unit and enable write cache. 3ware controllers are good.

[fmrapid]

Has anyone tested Zabbix with MySQL 5.5, there should be notable performance improvements with it.

I have yet to learn of solid 1000+ event per second setups. MySQl with good hardware and lots of RAM should support up to 3-4K transactions. As Zabbix DBs cannot be held in memory, the new limits of 7K+ DB transactions are not realistically attainable.

From previous MySQL performance recommendations, it was said that having multiple InnoDB files splitting history from trends would lead to performance improvements?!

Cheers

fmrapid

[walterheck]

Has anyone tested Zabbix with MySQL 5.5, there should be notable performance improvements with it.

Depending on your hardware, you are probably better off with MariaDB 5.2.4. Even though currently the two don't have too much of a difference in most features, MariaDB has a far better future perspective. The company that develops it, Monty Program is founded by Michael Widenius, original founder of mysql. He currently employs most of the old core of the MySQL team, and they work hard to make improvments in MariaDB.

I have yet to learn of solid 1000+ event per second setups. MySQl with good hardware and lots of RAM should support up to 3-4K transactions. As Zabbix DBs cannot be held in memory, the new limits of 7K+ DB transactions are not realistically attainable.

Those kind of numbers are attainable. I do consulting for a company that does 9000K+ DB transactions per second. The hardware is substantial though, and it's no easy feat

From previous MySQL performance recommendations, it was said that having multiple InnoDB files splitting history from trends would lead to performance improvements?!

Do yourself a favor and set innodb_file_per_table to true, thereby creating an .idb file per innodb table. The performance difference is small, but the administration benefit is gigantic.

[fmrapid]

Increasing server scalability to truly awe inspiring numbers of data points requires that the following feature be implemented:

Network load reduction by assuming missing values ZBXNEXT-113

This is a feature found in the open-source ganglia and also in all industrial SCADA systems that use the OPC protocol. (power plants, chemical factories, pipelines, railways, ATM networks).

Only value changes and periodic value updates (heartbeat) are returned to the server.
Thus, reducing the actual server values per second to manage, reducing trigger processing, storage requirements, value retrieval speed.

Please vote for the feature, https://support.zabbix.com/browse/ZBXNEXT-113, as new hardware and faster databases are not always the answer.

Cheers

fmrapid

[fmrapid]

Depending on your hardware, you are probably better off with MariaDB 5.2.4. Even though currently the two don't have too much of a difference in most features, MariaDB has a far better future perspective.

MySQL at this point is explicitely supported by Zabbix. MariaDB is should be a seemless drop-in replacement. (same as ExtraDB)

Though I would be curious to know how many Zabbix new values per second those implementations translate to. As DB updates per second != Zabbix new values per second.

[richlv]

anybody feels like distilling non-discussion bits of this thread into a page at zabbix.org ?

[nelsonab]

anybody feels like distilling non-discussion bits of this thread into a page at zabbix.org ?

<Insert Like Button Here>

[walterheck]

MySQL at this point is explicitely supported by Zabbix. MariaDB is should be a seemless drop-in replacement. (same as ExtraDB)

MariaDB is indeed a drop-in replacement for MySQL, and offers a lot more. I do consulting for a MySQL consulting firm and one of the first things we recommend people is to move from stock mysql to MariaDB. It's a 2 minute operation: /etc/init.d/mysql stop, apt-get remove mysql && apt-get install mariadb is pretty much all there is to it

Better tuning of InnoDB/XtraDB, better instrumentation to see what is going on, and a bunch of performance improvements, especially in 5.2.4 and the upcoming 5.3

Though I would be curious to know how many Zabbix new values per second those implementations translate to. As DB updates per second != Zabbix new values per second.

Me too, we should start a wiki page on zabbix.org having statistics and configs for people to see..

[richlv]

Me too, we should start a wiki page on zabbix.org having statistics and configs for people to see..

i had this idea as well, although for the numbers to be meaningful we'd need quite some information (db parameters, cpu/memory/disk info and much more).

getting the list of data that would be needed would be the first step

[walterheck]

@richlv: I could see a future feature that would allow zabbix to gather statistics, like the popularity thing in debian: http://popcon.debian.org/
Optional, accurate and low-effort. It would also help zabbix SIA get an overview of what people use most so you can focus development there

[richlv]

then you might want to vote on https://support.zabbix.com/browse/ZBXNEXT-486

[walterheck]

then you might want to vote on https://support.zabbix.com/browse/ZBXNEXT-486

Done, voted on a bunch of other issues as well while I was at it