Performance Tuning MySQL

[exkg]

Hi,

My two cents: ZABBIX 1.8.x is much better talking about performance. In ZABBIX 1.6 (and before) that didn't have any Cache or Buffer capability ... in 1.8.x the new features are working like a charm.

All performance problems we have had with 1.6.x are gone with 1.8.x

[]s,
Luciano

[MrKen]

Don't suppose you've replicated this for 1.8?

Anyway the only point of my "doesn't make any sense" comment was that it didn't make any sense to say that they were taking up resources while they were sleeping. Sleeping is sleeping, and shouldn't take any resources.

Clearly, they *aren't* sleeping, and they're waking up and tripping all over each other. It looks vaguely like there might a thundering herd issue or something like that.

First, sorry for the late reply.

1. No, haven't upgraded the Production server yet. I do have a 1.8.3 set up but it is totally dedicated to Zabbix_Trapping on about 60 items once daily, so replication can't be done yet.

2. Sleeping threads taking up resources. Mmm, maybe a bad choice of words. What I meant was that in the past I had jacked up the StartPollers/Trappers as that was the 'standard advice' for resolving such issues as slow server performance, slow housekeeper, gaps in graphs, . . .
However, what I found after tuning my.cnf with the advice from this forum thread, was that I didn't really need all those StartPollers/Trappers. I returned them to the default values and everything was fine; fast housekeeper, low cpu load, in fact everything was running so much better than before.

3. A thundering herd? Maybe, not sure yet. Despite the fact that everything is running smoothly, I have noticed that for each additional StartPoller/Trapper there is a slight increase in cpu load. But, with such a finely tuned database I have no need for anymore than the default StartPollers/Trappers anyway.
In mysqladmin, I see that the Sleeping threads are only in that state for a short time anyway, usually less than 60 seconds.

For now I shall just leave things as they are.

MrKen

[MrKen]

I wonder if you can comment on how your UserParameter and monitoring work, since "ps auxw" is supposed to give you average CPU usage over the entire lifetime of the process. Therefore, a drastic change in performance shouldn't be noticed immediately. It will get watered down, inside the "average" calculation. Yet - your graph displays a discontinuity. This doesn't make sense. Unless, perhaps, a process got killed at that moment or something similar, so it's eliminated from the calculation.

You are correct! 'ps auxw' does give an average over the lifetime of the process. However, for me that is fine as I can see the trending data. This UserParameter will not tell you that suddenly the cpu usage of a particular process has just gone through the roof. (I have no triggers on these items)

The UserParameter was not devised by me, it came from a rather old forum thread http://www.zabbix.com/forum/showthread.php?t=210

If you want to be able to trigger when a process starts using excessive cpu, there are other threads that, I think, run a script to identify the offending process. Here's one, give it a try (I haven't yet) http://www.zabbix.com/forum/showthre...ht=CPU+Process

MrKen

[bernard]

Old post, but VERY interesting

Hi everyone,

Thanks a lot for all the tips !

I just spend few hours to think and test mySQL settings for better tuning.
Links I have used are:

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

http://dev.mysql.com/doc/refman/5.0/...iguration.html
http://dev.mysql.com/doc/refman/5.0/...variables.html

I'm runing Zabbix 1.8.4 on SLES 10 virtual machine (2vCPU, 4GB RAM), DB is 40GB (340 hosts, 12k items). Required server performance, new values per second: 183.66.

I have changed the following settings: in () old values or defaults:

innodb_buffer_pool_size = 3G (16M)
innodb_log_file_size = 64M (5M)
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2 (1)
tmp_table_size=256M (32M)
max_heap_table_size=256M (16M)
table_cache=256 (64)

Does it really improve performance ? YES (and reduce a lot I/O and MBs on disks)
Have a look on pictures:
CPU "2011-07-07_141032.png",
Memory "2011-07-07_141312.png",
Disks I/O "2011-07-07_141607.png"

My storage admin is very happy now !

Regards,
Bernard

[Alexei]

Hey, why didn't you use Zabbix for the graphs?

[bernard]

Still waiting for "vfs.dev.read/write[device,bytes]" for linux ...

[Alexei]

Still waiting for "vfs.dev.read/write[device,bytes]" for linux ...

I see, sorry for this.

[PieterB]

Thx for sharing, we just started tuning the DB

[ufocek]

Maybe someone who has a large environment and running zabbix on mysql can check this server http://www.percona.com/software/percona-server/ vs a mysql and run some benchmark and show results?

[schukido]

Hi everyone,

Thanks a lot for all the tips !

I just spend few hours to think and test mySQL settings for better tuning.
Links I have used are:

http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

http://dev.mysql.com/doc/refman/5.0/...iguration.html
http://dev.mysql.com/doc/refman/5.0/...variables.html

I'm runing Zabbix 1.8.4 on SLES 10 virtual machine (2vCPU, 4GB RAM), DB is 40GB (340 hosts, 12k items). Required server performance, new values per second: 183.66.

I have changed the following settings: in () old values or defaults:

innodb_buffer_pool_size = 3G (16M)
innodb_log_file_size = 64M (5M)
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2 (1)
tmp_table_size=256M (32M)
max_heap_table_size=256M (16M)
table_cache=256 (64)

Does it really improve performance ? YES (and reduce a lot I/O and MBs on disks)
Have a look on pictures:
CPU "2011-07-07_141032.png",
Memory "2011-07-07_141312.png",
Disks I/O "2011-07-07_141607.png"

My storage admin is very happy now !

Regards,
Bernard

Dear Benard,

After I apply some config in my.cnf like you and I have a problem with zabbix GUI. So many error about sql in the log in screen. How can i fix it. I monitored over 300 host and 4k items, used zabbix 1.8.4 and the queue delay over 20 minutes after several of time .

Thanks and Best Regards,