What is the best parameters of mysql ??

[molmi]

I need for increase performance of server zabbix, tuning mysql ... of standard installation.

can you give me the best parameters of conf file for zabbix server installatio ?

Thanks

[James Wells]

Greetings,

I need for increase performance of server zabbix, tuning mysql ... of standard installation.

I can't give a lot, but some of the things I recommend looking into are;

1. Split DB File -- To do this, you modify MySQL to use innodb_file_per_table. This will break up the DB so that each table has it's own file, which can greatly improve your IOPS.
2. Partition History & Trends -- The history tables are the most heavily used tables in the DB with the trend tables coming a close second or third. Unfortunately, the code as current written in Zabbix performs row deletion for these two table sets which is extremely IO intensive. A better solution is to partition the tables and drop the partitions instead of performing the row deletions.

[fjrial]

I don't know if you've already read this topic:
Mysql Database Grow | How Optimize parameters

http://www.zabbix.com/forum/showthread.php?t=9925&highlight=mysql+tunning

Very usefull topic.
Cheers.

[James Wells]

Mysql Database Grow | How Optimize parameters
http://www.zabbix.com/forum/showthre...=mysql+tunning

WOW, that is very helpful. Thanx Javier.

[walterheck]

[*]Split DB File -- To do this, you modify MySQL to use innodb_file_per_table. This will break up the DB so that each table has it's own file, which can greatly improve your IOPS.

Be advised that this requires a bit more effort: after you set innodb_file_per_table, you have to do the following to actually move them out of the innodb tablespace:
1) restart the mysql server
2) issue an "alter table xx engine = innodb;" (or any other statement that would rewrite the whole table) to rewrite the table outside the inndob tablespace. If you don't do this, only new tables will be written outside the tablespace. Downside of this is that you need loads of diskspace.

[James Wells]

Be advised that this requires a bit more effort: after you set innodb_file_per_table, you have to do the following to actually move them out of the innodb tablespace:
1) restart the mysql server
2) issue an "alter table xx engine = innodb;" (or any other statement that would rewrite the whole table) to rewrite the table outside the inndob tablespace. If you don't do this, only new tables will be written outside the tablespace. Downside of this is that you need loads of diskspace.

Generally, I do a mysqldump and restore instead of the alter table, but you are correct.

To be honest, there is a lot more that is needed to do this anyway, which is why I recommended that they look into it. Some of the things that need to be done, to go this route are;

• Increase Max File Handles -- MySQL now has to open multiple file handles for each table now.
• Increase Memory Allocation -- Because of the extra open file handles, MySQL now will require more memory.
• Increase Thread Count -- As there are more file handles to manage, you will need more threads to manage them.
• Etc...
• Etc...

Please note that I am not trying to make light of the situation, but the fact is that there is no magic bullet to MySQL tuning for Zabbix. The best we can really offer are 10k foot views of what needs to be done, because though the schema / code may be identical, the way they are used are completely different for each and every Zabbix implementation out there.