Performance Tuning MySQL

[MrKen]

Forget to mention, when using this UserParameter

UserParameter=mysqld_cpu.pc,ps auxw|grep mysqld|grep -v grep| awk '{s+=$3} END {print s}'

you need to divide by the number of cpu's. In the Zabbix Item that means using a Custom Multiplier, for example, 2 cpu's X 0.5, 4 cpu's X 0.25, etc.

MrKen

[tchjts1]

Ok... so, 16 CPU's = 0.0625

[MrKen]

Ok... so, 16 CPU's = 0.0625

That looks to be correct.

tchjts1, you must be burning the midnight oil. Quick calculation, must be about 3 AM in California.

[tchjts1]

That looks to be correct.

tchjts1, you must be burning the midnight oil. Quick calculation, must be about 3 AM in California.

Feels like 3am... but it is only 1:30am. Plenty to do in preparation for our 1.8.2 prod upgrade Friday.

Yeah, the CPU usage looks much better being divided by the number of CPU's. Too bad that's not built in functionality.

Jeff

[chivo]

Thanks for the information. Here are some tips I have from my configuration:

949 hosts, 64638 items monitored

I use a SAN for storage, so I put the innodb logs on a separate set of LUNs and in a separate volume. This will significantly reduce the IO contention. I suspect some of the CPU utilization you are seeing is now for IO. If you are using a server with dedicated RAID controller(s), a separate raid group will help, (with the caveat that you will be wasting some space.)

I also sized the innodb logs to be larger. Larger log files allow mysql and the fs to perform more efficient writes to the underlying filesystem:

innodb_log_buffer_size = 8M
innodb_log_file_size = 768M

However, don't go over board on the log file size. First, it can not be larger than 2048MB. Second, the idea is to have something large enough to allow for efficient writes. If your log file size is too big, mysql will be waiting to write for too long. It should be sized based on the number of inserts per second. In my case, my server is doing about 3k qps.

Otherwise, I have very similar mysql settings to what was posted by Lamont. I wish I had a consultant to go over this with me, instead I had to read a few O'Reilly books.

[nelsonab]

I wanted to add this one I recently discovered with ext4 file systems. In your /etc/fstab file add the following option to your filesystem which is used for mysql, barrier=0.

Example:

/dev/sda4     /var/lib/mysql      ext4      defaults,noatime,barrier=0,uid=60,gid=107      1 2

Here is a before and after example of just how much of a difference this can make. This was from the initial schema creation for Zabbix using MySQL.

Before:

time cat schema/mysql.sql | mysql zabbix
real    0m33.916s
user    0m0.011s
sys     0m0.006s

After:

time cat schema/mysql.sql | mysql zabbix
real    0m0.992s
user    0m0.013s
sys     0m0.003s

As you can see the performance change is HUGE. If you are using OpenSuSE 11.2 with ext4 you *will* need to make these changes as the before numbers are the default ones from a standard OpenSuSE 11.2 install.

[elvar]

I wanted to add this one I recently discovered with ext4 file systems. In your /etc/fstab file add the following option to your filesystem which is used for mysql, barrier=0.

Example:

/dev/sda4     /var/lib/mysql      ext4      defaults,noatime,barrier=0,uid=60,gid=107      1 2

Here is a before and after example of just how much of a difference this can make. This was from the initial schema creation for Zabbix using MySQL.

Before:

time cat schema/mysql.sql | mysql zabbix
real    0m33.916s
user    0m0.011s
sys     0m0.006s

After:

time cat schema/mysql.sql | mysql zabbix
real    0m0.992s
user    0m0.013s
sys     0m0.003s

As you can see the performance change is HUGE. If you are using OpenSuSE 11.2 with ext4 you *will* need to make these changes as the before numbers are the default ones from a standard OpenSuSE 11.2 install.

Got any cool tweaks like this for JFS?

[nelsonab]

Got any cool tweaks like this for JFS?

Unfortuantely not, but I did come across the following which has helped me inprove the performance even more for Zabbix. kjournald2 it turns out was taking up a lot of cpu time. This is a pseudo process within the kernel which flushes the journal for the ext(3,4). Turns out it's possible to change the amount of time it waits before doing it's commits, which defaults to 5 seconds which means if you system dies you loose 5 seconds of data. The higher the value the less overhead. :-)

http://www.mjmwired.net/kernel/Documentation/filesystems/ext4.txt

Though in testing I'm finding the commit option to be overall pretty useless for the Zabbix DB.

[bashman]

I’ve seen MySQL DB opens a lot of threads which seem not be closed. I've defined “max_connections” variable to 400 connections, and Zabbix opens around 300 threads.

Is this behavior normal?, I'm using MySQL 5.0.75 with MyISAM Engine and all tables in zabbix and zabbix proxy schemas use InnoDB tables.

Should I change the engine to InnoDB?

Thanks in advance.
Regards,

bashman

[lamont]

- what is your operating system ?

CentOS 5.x

- which Zabbix version (1.6 vs 1.8), i saw a huge improvement ( load / 3)

1.6

1.8 wasn't released yet when i did the tuning. these servers have become someone else's problem and they haven't pulled the trigger yet for 1.8

- how many items/second ?

287 new values / second

279 active hosts, 17432 active items, 7751 enabled triggers.

- how many sql queries/second ? (graph with item: mysqladmin extended-status | grep Com_)
- disk/mysql cache/hit ? (mysqladmin extended-status,
innodb_buffer_pool_read_requests / innodb_data_read

i don't have those.

[lamont]

I wanted to add this one I recently discovered with ext4 file systems. In your /etc/fstab file add the following option to your filesystem which is used for mysql, barrier=0.

You probably only want to do that if your RAID card has a battery backed cache where there's a reasonable guarantee that all the write will complete, even if they get reordered. Otherwise you can make a bit of a mess of your filesystem if the system ever crashes due to an panic or a power loss -- ext2 used to reliably corrupt the filesystem after a hard crash (although it was blazingly fast due to being fully asynchronous) and you're making ext3 a little more like ext2 here.

If not, you might want to have a startup script or cronjob do a "touch /forcefsck" so that you'll do a full consistency check on every startup (or i think you could set the reboot count or time in tune2fs so low that it forces a full fsck on essentially every reboot...).

[lamont]

How this came about was that I had often wondered what would be the optimum number of StartPollers and StartTrappers. Too few Pollers/Trappers is not good, and too many is also not good. So how does one know where that 'happy medium' is. I created the following UserParameters, and created a 'stacked' graph. The graph clearly showed that most of my mysql threads were spending their day Sleeping (using up resources).

Actually that doesn't make any sense. If they're Sleeping, they're not using any resources (well, some memory, but nothing in the CPU). So, something else is going on.

I've got my Pollers and Trackers jacked up in the belief that running out was worse than wasting a little memory and a slot in the process list for each one (Linux should be able to scale to 10,000 threads -- it got engineered to deal with java people in the past and they're all thread-crazy developers).

I'll have to experiment with finding the right amount of those threads and see what it does to the CPU.

Should really do some oprofile work to figure out what is actually going on here...

[MrKen]

Actually that doesn't make any sense. If they're Sleeping, they're not using any resources (well, some memory, but nothing in the CPU). So, something else is going on.

I've got my Pollers and Trackers jacked up in the belief that running out was worse than wasting a little memory and a slot in the process list for each one (Linux should be able to scale to 10,000 threads -- it got engineered to deal with java people in the past and they're all thread-crazy developers).

I'll have to experiment with finding the right amount of those threads and see what it does to the CPU.

Should really do some oprofile work to figure out what is actually going on here...

Mmm, Intriqued by your comments I decided to experiment myself. I shall do some oprofile work when I have more time.

See the image below. Despite the name on the graphs, this is actually running Zabbix 1.6.6. Mysql is nicely tuned, thanks in part to yourself, and this server purrs along nicely with only the default 5 Pollers and Trappers.
Yesterday I increased the StartPollers to 40 and StartTrappers to 40, you can clearly see the result of that action. The server is only running zabbix and mysql, and no other changes were made.

[lamont]

Mmm, Intriqued by your comments I decided to experiment myself. I shall do some oprofile work when I have more time.

See the image below. Despite the name on the graphs, this is actually running Zabbix 1.6.6. Mysql is nicely tuned, thanks in part to yourself, and this server purrs along nicely with only the default 5 Pollers and Trappers.
Yesterday I increased the StartPollers to 40 and StartTrappers to 40, you can clearly see the result of that action. The server is only running zabbix and mysql, and no other changes were made.

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.

[Samd]

First, thanks for sharing your mysql tuning experience, it has been a huge help!

Second, regarding CPU consumption of mysql. Up until a week ago, mysql on my zabbix server had always stayed pretty constant at around 18% - 19% of cpu. Now I have it down to 7%! Look at the graph below:

How this came about was that I had often wondered what would be the optimum number of StartPollers and StartTrappers. Too few Pollers/Trappers is not good, and too many is also not good. So how does one know where that 'happy medium' is. I created the following UserParameters, and created a 'stacked' graph. The graph clearly showed that most of my mysql threads were spending their day Sleeping (using up resources).

UserParameter=mysql.threads.sleep,mysqladmin -u admin -padmin processlist | grep Sleep | wc -l
UserParameter=mysql.threads.active,mysqladmin -u admin -padmin processlist | grep -v Sleep | wc -l

[Sorry, having trouble placing the graphs in the desired place. So they are both at the bottom. You'll see, one is 'mysql threads', the other 'mysql cpu'].

On the graph below, at point #1 I reduced both Pollers and Trappers from 50 to 30. The difference is obvious. And I still have plenty of sleeping mysql threads.
At point #2 (today) I reduced them further, from 30 down to 15. And still plenty of mysql threads to carry the workload. Oh, and nothing in the queue!

The graph shown below is from this UserParameter:

UserParameter=mysqld_cpu.pc,ps auxw|grep mysqld|grep -v grep| awk '{s+=$3} END {print s}'

I would appreciate any feedback. Try it and see if it helps.

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.