MySQL benchmark with SysBench (I)

By taking advantage of the previous article about MySQL optimization, I am going to introduce a handy tool called SysBench and aimed at measuring the performance of a MySQL database among other things. In addition, it is also able to evaluate the I/O, scheduler and threads implementation performance, and memory allocation and transfer speed.

So I am going to use this tool in order to verify the improvements commented in the preceding article and related to some parameters of MySQL. The test will be run on Ubuntu Server 12.10 virtualized through VMware. The virtual machine will made up by a 6 GB hard drive, 2 GB of RAM and a couple of virtual cores.

First of all, let's install MySQL and SysBench and increase the default number of maximum connections to 512.

root@ubuntu-server:~# aptitude install mysql-server sysbench

root@ubuntu-server:~# vim /etc/mysql/my.cnf
...
max_connections = 512

root@ubuntu-server:~# service mysql restart

Now let's create a table of 1.000.000 of rows in a database called test by using SysBench.

root@ubuntu-server:~# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=xxxxxx prepare

Then a straightforward script taken care of running the tests will be developed. This bash script executes the OLTP (OnLine Transaction Processing) test on a table of 1.000.000 of rows. The time limit for the whole execution will be 300 seconds and read, update, delete and insert queries will be performed. The total number of maximum requests will be unlimited.

root@ubuntu-server:~# cat script.sh 
#!/bin/bash

for i in 8 16 32 64 128 256 512
do
    service mysql restart ; sleep 5
    sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=xxxxxx --max-time=300 --oltp-read-only=off --max-requests=0 --num-threads=$i run
done

As you can see in the above script, the number of worked threads to be created will be different in each loop iteration, from 8 to 512. So the idea is to run the script with various MySQL combinations and calculate the number of transactions per second.

MySQL optimization (V)

I am going to reopen the last article that I wrote about MySQL optimization because I came across three new parameters (for me) which enhance the performance of a MySQL database, and I would like to note down them on my blog.

The first parameter is innodb-flush-log-at-trx-commit, which manages both when the log buffer is written out to the log file and the flush to disk operation is performed. Its default value is 1, which means that the log buffer is dumped to the log file at each transaction commit and the flush to disk operation is carried out directly on the log file.

When its value is 0, the log buffer is sent to the log file once per second, so in this way, you are turning down the disk accesses. In respect of the flush to disk, the operation is also effected on the log file but not coinciding with the commit, but taking advantage of free periods. And when it takes the value of 2 (less aggressive than 0), the log buffer is written out to the log file at each commit but as in the previous case, the flush is done at any free moment for the server.

The other parameter that I would like to talk about is innodb_buffer_pool_instances (in case of you are using the InnoDB engine), which represents the number of regions that the InnoDB buffer is broken up.  This parameter is really useful when you are using a server with several cores, and thereby, each core (thread) can work on a separate instance. A good recommendation is to set it to the same value as the number of cores, but another popular option is to follow the next rule: (innodb_buffer_pool_size [in GB] + number of cores) / 2.

And finally, the last parameter is innodb_log_file_size, related to the InnoDB log file. Its default value is 5 MB and I consider that is not enough for production environments. The larger the value is, the less control flush activity is needed in the buffer pool, saving disk I/O operations. I think that a right value would be between 64 and 256 MB.

Zabbix poller processes more than 75% busy and queue delay (III)

Let's complete the last article about Zabbix poller processes more than 75% busy and queue delay. In this section, I am going to tackle the part of the client, that is, those things which can be modified on the agent so as to remove or attenuate the issues mentioned in the first article.

Remember that this is the continuation of the two previous articles:

• Zabbix poller processes more than 75% busy and queue delay (I)
• Zabbix poller processes more than 75% busy and queue delay (II)

First up, I changed the number of pre-forked instances of the Zabbix client which process passive checks (StartAgents) to 64. This parameter is really meaningful, because its default value is 5, that is to say, only five processes will be started in order to obtain the data requested by the server. So if you have a lot of items and a small monitoring period (as my case), you will need more processes to be able to attend all requests.

root@zabbix-client:~# cat /etc/zabbix/zabbix_agentd.conf
...
StartAgents=64

So let's see now in the graphs, how this change impacts on the results. Let's first with the Zabbix server performance.

And then, the Zabbix data gathering process.

As you can see on the first picture, the server has gone from a Zabbix queue of 30 to 0 (although you can observe 5 on the figure, think that the graph has been cut out). And on the second one, the Zabbix busy poller processes went from 24% to 0%.

Other parameters that you can play with are the number of seconds that the data can be stored in the buffer and its maximum number of values.

root@zabbix-client:~# cat /etc/zabbix/zabbix_agentd.conf
...
BufferSend=3600

BufferSize=65535

Also keep in mind that you should have a small value for the timeout (I am using five seconds on my installation).

Lastly, in order to solve the problem that I mentioned in the first article about from time to time, the processes break down and the zabbix agent is stopped, I developed a simple bash script to work around this issue.

root@zabbix-client:~# tail -f /var/log/zabbix/zabbix_agentd.log
...
zabbix_agentd [17271]: [file:'cpustat.c',line:155] lock failed: [22] Invalid argument
 17270:20121015:092010.216 One child process died (PID:17271,exitcode/signal:255). Exiting ...
...
 17270:20121015:092012.216 Zabbix Agent stopped. Zabbix 2.0.3 (revision 30485).


root@zabbix-client:~# cat /etc/zabbix/monitor_zabbix.sh
#!/bin/bash

while [ 1 ];
do
        if ! pgrep -f "/usr/local/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf" &> /dev/null ; then
                /etc/zabbix/zabbix.sh start
        fi
        sleep 15
done

This script is run in batch mode and takes care of monitoring the status of the agent processes and starting over when they drop . It uses another bash script to start and stop the agents.

root@zabbix-client:~# cat /etc/zabbix/zabbix.sh
#!/bin/bash

case $1 in
        "start")
                taskset -c $(($(cat /proc/cpuinfo | grep processor | wc -l) - 1)) /usr/local/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf;;
        "stop")
                pkill -f "/usr/local/sbin/zabbix_agentd -c /etc/zabbix/zabbix_agentd.conf";;
        *)
                printf "./zabbix.sh start|stop\n\n"
esac