Simple Benchmark of your MySQL Server

Installing Sysbench

The easiest way to perform benchmark on a MySQL server is by using sysbench. To install:

Debian/Ubuntu based:

$ apt-get install sysbench

RedHat/CentOS/Fedora based:

$ yum install sysbench

Preparing Sysbench

If we have a MySQL server ready, let’s create a database for sysbench called sbtest.

mysql> CREATE SCHEMA sbtest;

It is better to increase the default MySQL max_connections (which is too low for this test):

mysql> SET GLOBAL max_connections = 1000;

Let’s prepare the sample data for benchmarking with 2 million rows:

$ sysbench --db-driver=mysql --test=oltp --mysql-table-engine=innodb --oltp-table-size=2000000 --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd prepare

Perform the Benchmark

Now let’s the benchmarking begin. We will start an OLTP (online transactions processing) benchmark test with 8 threads and 10000 max-requests then send the output to a file called sysbench.log:

$ sysbench --db-driver=mysql --test=oltp --num-threads=8 --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log

Repeat the steps for 16, 32, 64, 128, 256 and 512 threads and append the output to sysbench.log:

$ for i in 16 32 64 128 256 512; do sysbench --db-driver=mysql --test=oltp --num-threads=$i --max-requests=10000 --oltp-table-size=2000000 --oltp-test-mode=complex --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=myR00tP4ssW0rd run >> sysbench.log ; done

We will then need to convert the sysbench output to CSV format so we could plot it to a graph using gnuplot.

$ cat sysbench.log | egrep " cat|threads:|transactions:" | tr -d "\n" | sed 's/Number of threads: /\n/g' | sed 's/\[/\n/g' | sed 's/[A-Za-z\/]\{1,\}://g'| sed 's/ \.//g' | awk {'print $1 $3'} | sed 's/(/\t/g' > sysbench.csv

The content of sysbench.csv should be similar as below:

8    274.53
16   397.47
32   442.79
64   516.90
128  503.36
256  414.35
512  445.67

Plotting Graph

I will use GNUplot for this task. To install:

yum install gnuplot #redhat/centos based
apt-get install gnuplot #debian/ubuntu based

Create a file called mygraph, and then paste following lines:

# output as png image
set terminal png
# save file to "benchmark.png"
set output "benchmark.png"
# graph title
set title "Benchmark for Sysbench"
# aspect ratio for image size
set size 1,1
# enable grid on y and x axis
set grid y
set grid x
# x-axis label
set xlabel "Threads"
# y-axis label
set ylabel "Transactions (tps)"
# plot data from sysbench.csv
plot "sysbench.csv" using (log($1)):2:xtic(1) with linesp notitle

Run following command to produce the graph based on our template:

$ gnuplot mygraph

And you should see it generates a png image which you can view it similar as below:


That’s it. The benchmark results are well-presented in the graph. We can conclude that our MySQL server was performing nicely up until 64 threads then it appears to have some dropping after that.

Importing Big mysqldump with Progress Bar

I have been facing hard time to import a big dump file (25 GB in size to be exact). It could take hours to load data in so it could be worth to watch the progress of this import. Fortunately, Linux has always had a convenient way to do this. I just need to install pv,  which can monitor the progress of piping data.

Enough with talking, let’s start installing!

I am using CentOS 6.4 box, so it requires me to install EPEL repo at the first place:

$ rpm -Uhv

Install pv using yum:

$ yum install -y pv

Installation done. Let the importing begin!

$ pv /home/user/my_big_dump.sql | mysql -uroot -p
928MB 0:07:41 [ 2.3MB/s] [==>                                                      ]  4% ETA 2:35:52


So now I can sit back and relax while watching up the import progress. If no error occurred, the import could finish within two and a half hours. Cheers!

CentOS: Install Nagios – The Simple Way

Nagios is the most popular open-source infrastructure monitoring tools. Nagios offers monitoring and alerting for servers, switches, applications, and services. It alerts users when things go wrong and alerts them again when the problem has been resolved.

I have created a script to install Nagios and Nagious plugin in RHEL/CentOS:

# Install nagios and nagios plugin in RHEL/CentOS/Fedora
# Disable SElinux
sed -i.bak 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
setenforce 0
# Nagios requirement
yum install gd gd-devel httpd php gcc glibc glibc-common make openssl openssl-devel -y
# Installation directory
[ ! -d $installdir ] && mkdir -p $installdir
rm -Rf $installdir/*
cd $installdir
wget $nagios_latest_url
wget $nagios_plugin_latest_url
# Nagios
nagios_package=`ls -1 | grep nagios | grep -v plugin`
tar -xzf $nagios_package
cd nagios
echo "Installing Nagios.."
useradd nagios
make all
make install
make install-init
make install-commandmode
make install-config
make install-webconf
echo "Create .htpasswd for nagios"
htpasswd -c /usr/local/nagios/etc/htpasswd.users nagiosadmin
cd $installdir
# Nagios Plugin
nagios_plugin_package=`ls -1 | grep nagios-plugin`
tar -xzf $nagios_plugin_package
cd nagios-plugin*
echo "Installing Nagios Plugin.."
make install
echo "Starting Nagios.."
chkconfig nagios on
service nagios start
echo "Staring Apache.."
service httpd restart
chkconfig httpd on
# Configure IPtables
iptables -I INPUT -m tcp -p tcp --dport 80 -j ACCEPT
service iptables save
ip_add=`hostname -I | tr -d ' '`
echo "Installation done.."
echo "Connect using browser http://$ip_add/nagios/"
echo "username: nagiosadmin"
echo "password: (nagios password)"

You can download the script directly here:

$ wget

Changethe  script permission and run script:

$ chmod +x
$ ./

Once completed, you can directly open Nagios page using http://<your_ip_address>/nagios and login with username nagiosadmin with same password you enter during the installation. You should see the Nagios page similar to screenshot below:




Installation done!


CentOS 6: Install Remote Logging Server (rsyslog)

In my office network, we have a lot of small devices like router and switches in our environment. My boss wants me to have a report on all of our network device for auditing purposes. To accomplish this objective, I need to have a server which run as logging server, accepting various type of logging from several devices. This method will ease up my auditing trail in one centralized location.

I will use my development server which run on CentOS to receive logs from my Mikrotik router, as picture below:


I am using following variables:

Rsyslog OS: CentOS 6.0 64bit
Rsyslog Server IP:
Router hostname:
Router IP:

Rsyslog Server

1. Install Rsyslog package:

$ yum install rsyslog -y

2. Make sure you have following line uncommented in /etc/rsyslog.conf:

$UDPServerRun 514
$InputTCPServerRun 514
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
*.info;mail.none;authpriv.none;cron.none /var/log/messages
authpriv.* /var/log/secure
mail.* -/var/log/maillog
cron.* /var/log/cron
*.emerg *
uucp,news.crit /var/log/spooler
local7.* /var/log/boot.log
$AllowedSender TCP,,
$AllowedSender TCP,

3. We need to add following rules into /etc/rsyslog.conf so logs received from the router will be output into a file called /var/log/router.log:

:fromhost-ip,isequal,""                      /var/log/router.log

There are a lot of options you can use to define your remote logging rules, which you can refer to this page:

4. Open firewall port 514 on TCP and UDP:

$ iptables -A INPUT -m tcp -p tcp --dport 514 -j ACCEPT
$ iptables -A INPUT -m udp -p udp --dport 514 -j ACCEPT

5. Restart Rsyslog daemon to apply the configuration:

$ service rsyslog restart

6. We also need to rotate this log file so it will need eating up the server’s disk space. Create a new text file called router under /etc/logrotate.d/ directory:

$ vim /etc/logrotate.d/router

And add following line:

    rotate 5
    /bin/kill -HUP `cat /var/run/ 2> /dev/null` 2> /dev/null || true

Router (Rsyslog Client)

1. Mikrotik router supports remote logging. I just need to login into the Winbox > System > Logging and configure Actions as screenshot below:


2. The next thing, is we need to create the rules on which logging level do we want to be sent to the rsyslog server. Go to Winbox > System > Logging and configure Rules as screenshot below:



Now, the router should send the log remotely to the rsyslog server and we can check the router logs by running following command:

$ tail -f /var/log/router.log
Jan 8 17:23:28 system,info log action changed by admin
Jan 8 17:26:09 system,info filter rule changed by admin
Jan 8 17:26:09 system,info filter rule changed by admin
Jan 8 17:26:23 system,info PPP AAA settings changed by admin
Jan 8 17:26:40 system,info L2TP Server settings changed by admin
Jan 8 17:26:49 system,info filter rule changed by admin
Jan 8 17:26:50 system,info filter rule changed by admin



Install and Configure Zenoss as Centralized Monitoring System

Good system administrator should know that they need to do these 3 most important things after delivering a server:

  • secure
  • monitor
  • backup

As part of monitoring task, in this tutorial, I am going to show on how to build a centralized monitoring system using Zenoss. Zenoss will need to use SNMP agent in every server that need to be monitored. If you have high number of servers, you should use Zenoss instead of Cacti or Nagios because it is easier to setup and maintain. The web-based management portal is really easy to understand and you can monitor a lot of things, not limited to SNMP MIB reporting only.

Zenoss has the community version which is free. The latest version of Zenoss at this date is version 4. Zenoss installer recommending user to use a clean server. But in my case, I will use a cPanel server which really not heavy in usage for this purpose. In order to run Zenoss in ‘not-so-clean’ server, I need to use Zenoss stack version 3. I will be using following variables:

OS: RHEL 5.3 64bit (Tikanga)
cPanel: 11.32.3 (build 23)
Zenoss server IP:
Server to be monitored: and

Install Zenoss

1. Download the Zenoss stack version 3 at here The file you should download is zenoss-stack-3.2.1-linux-x64.bin.

2. Give execute permission and install it. Follow and accept the default value of installation wizard:

$ chmod +x zenoss-stack-3.2.1-linux-x64.bin
$ ./zenoss-stack-3.2.1-linux-x64.bin

3. Make sure you open following port for Zenoss usage:

TCP: 8080
UDP: 514
UDP: 161
UDP: 162

4. Navigate to your browser and access Zenoss to create the admin user at

Enable SNMP

In all servers that need to be monitored, we need to install and enable SNMP agent. RHEL/CentOS 5 and 6 have some kind of different way on activating SNMP as example below:

Server #1 ( – CentOS 5.6 64bit

1. Install and enable SNMP on startup:

$ yum install net-snmp
$ chkconfig snmpd on

2. Open /etc/hosts.allow using text editor and add following line:


This will allow Zenoss collector to connect to SNMP service of this server.

3. Run following command to add SNMP community string for Zenoss collector:

echo "rocommunity zenossreporting" > /etc/snmp/snmpd.conf

4. Disable SNMP informational logging. If you are not doing this, SNMP will flooding out your /var/log/message. Open /etc/sysconfig/snmpd.options and add following line:

OPTIONS="-LS 5 d -Lf /dev/null -p /var/run/ -a"

5. Save and restart SNMP service:

$ service snmpd start

6. Make sure UDP port 161 is open for SNMP collection.

Server #2 ( – CentOS 6.2 64bit

1. Install and enable SNMP on startup:

$ yum install net-snmp
$ chkconfig snmpd on

2. Run following command to add SNMP community string for Zenoss collector:

echo "rocommunity zenossreporting" > /etc/snmp/snmpd.conf

3. Disable SNMP informational logging. If you are not doing this, SNMP will flooding out your /var/log/message. Open /etc/sysconfig/snmpd and add following line:

OPTIONS="-LS 5 d -Lf /dev/null -p /var/run/ -a"

4. Save and restart SNMP service:

$ service snmpd start

5. Make sure UDP port 161 is open for SNMP collection.

Configure Zenoss

1. Login into the Zenoss management portal at . Lets add a node to monitor. Go to INFRASTRUCTURE > Device > click the plus “+” icon and enter required information as screenshot below:

2. After added, we need to model the device. Select the device in the device list and select “Model Device” on the bottom left menu as screen host below:

3. This will build up the model based from Zenoss collector. You should see the left-side menu for the respective device appear following added menu:

4. Done. Your server load average, CPU utilization, memory utilization, disk IO, disk usage and network interface are being monitored now. You can repeat this step to all servers that need to be monitored.


Monitor Apache & MySQL using Zenoss

Zenoss has some kind of extension called ZenPacks used to monitor other services or processes. You just need to download and install the ZenPacks using Zenoss management portal. I will use ZenPacks to monitor Apache and MySQL as well.

1. Download ZenPacks for Apache and MySQL at here


2. Upload and install both ZenPacks into Zenoss. Go to Zenoss > ADVANCED > ZenPacks > click the gear icon > Install ZenPack..

3. You need to restart Zenoss for this. Login into the server via SSH and run following command:

$ service zenoss-stack restart

4. Login into the server that we need to monitor Apache and MySQL via SSH. We need to create the Apache server-status page for Apache monitoring plugin. Open main httpd.conf file which usually located under /etc/httpd/conf/httpd.conf and add following line:

ExtendedStatus On
<Location /server-status>
   SetHandler server-status
   Order deny,allow
   Deny from all
   Allow from

5. For cPanel servers, you may need to run following command to retain the httpd.conf in case if cPanel rebuild the httpd.conf file:

$ /usr/local/cpanel/bin/apache_conf_distiller --update --main

6. Restart Apache to apply the changes:

$ service httpd restart

7. Login into MySQL via console and run following SQL line:

mysql> grant usage on *.* to [email protected]'%' identified by 'zenP455w0rd';

8. Go back to Zenoss portal for the device. Go to Configuration Properties and find following fields and update the value:

zMySqlPassword = zenP455w0rd
zCollectorClientTimeout = 300
zSnmpTimeout = 5
zSnmpTries = 4

9. Last step is to bind the Apache and MySQL monitoring templates into the device monitoring properties. Go to gear icon on bottom-left menu and click Bind Templates and select both templates to the right side as screen shot below:

Open Zenoss > INFRASTRUCTURE > Devices > server name > Graphs and wait for a while. You should see a complete monitoring graph as screenshot below in this page:

Snmpd: Connection from UDP: REFUSED and SNMP Logging

Connection from UDP: [IP]:[port] REFUSED

During setting up my monitoring system using Zenoss, I see some of the servers which run on RHEL 5 were returning following logs under /var/log/message :

Aug 2 10:47:55 server1 snmpd[30684]: Connection from UDP: []:56295 REFUSED
Aug 2 10:47:57 server1 snmpd[30684]: Connection from UDP: []:56295 REFUSED

To solve this, we need to allow the source connection IP in /etc/hosts.allow file by adding following line:


After changing the value, restart snmpd service:

$ service snmpd restart

Monitor the /var/log/message as below:

Aug 2 10:51:44 server1 snmpd[1771]: Connection from UDP: []:56797
Aug 2 10:51:44 server1 snmpd[1771]: Received SNMP packet(s) from UDP: []:56797
Aug 2 10:51:44 server1 snmpd[1771]: Connection from UDP: []:56797


SNMP Logging Flooding into /var/log/message

Another problem I have been facing with default installation of SNMP is the /var/log/message will be flooding with snmpd log as example above. To overcome this, add following line into /etc/sysconfig/snmpd using text editor:


Save and restart SNMPD to get affected. To verify, just run following command and make sure the options value is included:

$ ps aux | grep snmpd
root   32382   0.0   0.1   197160   5076   ? S   13:04   0:00        /usr/sbin/snmpd -LS 5 d

This options will log from level 0 to 4 based on log level below:

0 – Emergencies – System is unusable
1 – Alerts – Immediate action needed
2 – Critical – Critical conditions
3 – Errors – Error conditions
4 – Warnings – Warning conditions
5 – Notifications – Informational messages
6 – Informational – Normal but significant conditions
7 – Debugging – Debugging messages

cPanel: Trigger Alert from Munin

For cPanel servers, Munin is one of important plugin that need to be enabled in the server. cPanel has provide this plugin to be install with just one click under WHM > cPanel > Manage Plugins > Munin. We can monitor and understand what is going on with our server resources while predicting future problems like hard disk temperature, available disk space, mysql throughput and many more.

As for me, we can take advantage of this monitoring tools to send me alert via email on certain critical services like Apache processes, Exim mail queue and load average of the server. I will use following information to configure Munin:

Email to: [email protected]
Server hostname:
Service need to monitor: Apache processes, mail queue, load average
Alerting level: warning and critical

Apache processes

Usually when the server being attacked via port 80 or 443, it will fully utilize the whole Apache slot. Default cPanel Apache configuration will allow 256 process to be run and the normal usage for this server is below 50 processes. This is represented in Munin under plugin apache_processes under /etc/munin/plugins/apache_processes and can be access in WHM > Plugins > Munin Service Monitor > ::Apache processes as refer to following screen shot:

If you see the picture above, I will need to monitor busy80 by specifying 50 processes as warning and 180 processes as critical:

apache_processes.busy80.warning 50
apache_processes.busy80.critical 180

Exim mail queue

Mail queue monitor is to help us prevent from getting spammed or become spammer. We surely not aware how our mail users use their mailbox and some of them might have their account compromised and being used to spam mails by spammer. If your mail queue is higher than usual, something not good is leading towards you. You might be accused to be hosting spammer, server IP being blacklisted by anti-spam organization and server connection’s might have been blocked to connect to others.

Exim mail queue is being monitor by Munin by a plugin called exim_mailqueue under /etc/munin/plugins/exim_mailqueue. You can access it in browser at WHM > Plugins > Munin Service Monitor > ::Exim Mailqueue as refer to following screenshot:

As you can see that my mail queue average is 480 mails. I will need to monitor mails by specifying 500 as warning and 1000 as critical:

exim_mailqueue.mails.warning 500
exim_mailqueue.mails.critical 1000

Load average

This is the most important part on checking up the stability of a server. Load average is a value that represent your server work load over a period of time. From the Munin monthly load average, I can see my server is running on 0.82 average:

My warning value should be 4 and critical is 8 (usually no of CPU x 2).

load.load.warning 4
load.load.critical 8

Munin Alert

Munin configuration file in cPanel is located under /etc/munin/munin.conf. Open this file via text editor and add following line: mail -s "Munin notification ${var:host}" warning critical

This will enable email notification with warning and critical status. Now, we need to configure which monitoring plugins that we want to trigger alert as what we have describe above. In the same file, you should notice following line:

     use_node_name yes

Add all the warning and critical triggers (as describe above) under this directive as below:

     use_node_name yes
     apache_processes.busy80.warning 50
     apache_processes.busy80.critical 180
     exim_mailqueue.mails.warning 500
     exim_mailqueue.mails.critical 1000
     load.load.warning 4
     load.load.critical 8

Save and restart munin-node service:

$ service munin-node restart

From now on, you will receive email like below if warning or critical alerts triggered: :: :: Apache processes
     WARNINGs: busy servers 80 is 112.83 (outside range[:50]).

Web Server Benchmarking using Apache Benchmark and gnuplot

Apache Benchmark (aka ab) is a tool to benchmark the HTTP web server. It is recommended to test your web server performance before switch it to production environment. I am using this tools to benchmark and do some stress test to our development server before it goes live.

Make sure you have following points prepared before we do benchmarking:

  • URL is accessible via public domain or IP:
  • Expect number of clients that your server ready to serve: 50 concurrent users/seconds
  • Expect number of requests per client that your server ready to serve: 10 requests/user/seconds
  • Verify whether keepalived is supported. You can use phpinfo or examine the server header: Keepalived supported
  • How the output should be present: Graph

I will use terminal server CentOS to do this benchmarking remotely. This test should be done at least 3 times so we can see the pattern. We will use gnuplot to present the data in graph. So we need to export the Apache benchmark output to a file that gnuplot understand  in TSV format (tab separated values) called bench1.tsv, and the next test will use respective number like bench2.tsv and so on.

1. Install required tools using yum. We will need to install httpd and gnuplot:

$ yum install -y httpd httpd-tools gnuplot

2. For first test, we can start to do benchmarking by using following command:

$ ab -c 10 -n 50 -k -g /var/www/html/bench1.tsv

Repeat the step above for 2nd and 3rd test using following command:

$ ab -c 10 -n 50 -k -g /var/www/html/bench2.tsv
$ ab -c 10 -n 50 -k -g /var/www/html/bench3.tsv

3. Now TSV files ready to be plot. Lets generate the graph in PNG format:

$ cd /var/www/html
$ gnuplot

You will be entering gnuplot console mode. Run following command to generate the image:

gnuplot> set terminal png
Terminal type set to 'png'
gnuplot> set output "benchmark.png"
gnuplot> set title "Benchmark for"
gnuplot> set size 1,1
gnuplot> set grid y
gnuplot> set xlabel 'Request'
gnuplot> set ylabel 'Response Time (ms)'
gnuplot> plot "bench1.tsv" using 10 smooth sbezier with lines title "Benchmark 1:", "bench2.tsv" using 10 smooth sbezier with lines title "Benchmark 2:", "bench3.tsv" using 10 smooth sbezier with lines title "Benchmark 3:"
gnuplot> exit

Done! You should now able to see the image via browser. Example benchmarking output as below:


We can create a template script for gnuplot to simplify the process to generate graph. Following template is the same action performed as step #3. We will name this template files as benchmark.tpl:

# output as png image
set terminal png
# save file to "benchmark.png"
set output "benchmark.png"
# graph title
set title "Benchmark for"
# aspect ratio for image size
set size 1,1
# enable grid on y-axis
set grid y
# x-axis label
set xlabel "Request"
# y-axis label
set ylabel "Response Time (ms)"
# plot data from bench1.tsv,bench2.tsv and bench3.tsv using column 10 with smooth sbezier lines
plot "bench1.tsv" using 10 smooth sbezier with lines title "Benchmark 1:", \
"bench2.tsv" using 10 smooth sbezier with lines title "Benchmark 2:", \
"bench3.tsv" using 10 smooth sbezier with lines title "Benchmark 3:"

To execute the template, just run following command:

$ gnuplot benchmark.tpl

If you examine TSV files created by ab, you should the column header which is ctime, dtime, ttime and wait. Definition as below:

ctime: Connection Time
dtime: Processing Time
ttime: Total Time
wait: Waiting Time