Optimizing MySQL InnoDB

This is something pretty short and useful for many mysql InnoDB users. Pretty much you will come across optimizing MySQL InnoDB due to performance issues or MySQL is causing a lot of 'slow' sql queries throwing in your way. Of course, there are pros and cons in doing every type of optimisation such as sacrificing reliability and etc.

MySQL InnoDB Configuration

Before i began explaining what the heck did i do, if you are lazy and just wish to try out whether my configuration works, just head over to your MySQL my.cnf file in /etc/my.cnf and place this on [mysqld]

P.S: this is a linux configuration

innodb_io_capacity = 100
innodb_thread_concurrency = 32
innodb_read_io_threads = 32
innodb_write_io_threads = 32

The above configuration will most likely helps to smoothed out most of your InnoDB problens. Especially if you are getting a 10-50 seconds for MySQL slow log.

MySQL InnoDB Configuration Explanation

Now let's go through one by one and explain what each does.


Screen Shot 2015-09-15 at 7.34.14 PM

innodb_flush_method defines the method used to flush data to the InnoDB data files and log files, which can affect I/O throughput. If you look at the image, the default value is NULL, and we have changed it to O_DIRECT to better control I/O throughput. We basicall still using fsync but only for write. read we will use a O_DIRECT instead of fsync. If you are interested to more head over to stackoverflow

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_method


Screen Shot 2015-09-15 at 7.43.52 PM
The timeout in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction waits at most this many seconds for write access to the row before issuing an error. We have set it 1 seconds as this is the only time we can wait for a row being lock or we will just fail the transaction if not we will have a pile of long queue in a busy server.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout


Screen Shot 2015-09-15 at 8.12.07 PM
The increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full. Mainly for sharding setting

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_autoextend_increment


Screen Shot 2015-09-15 at 8.14.53 PM
An upper limit on the I/O activity performed by the InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the insert buffer. The default value is 200. For busy systems capable of higher I/O rates, you can set a higher value at server startup, to help the server handle the background maintenance work associated with a high rate of row changes. For systems with individual 5400 RPM or 7200 RPM drives, you might lower the value to the former default of 100.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_io_capacity


Screen Shot 2015-09-15 at 8.17.16 PM
InnoDB tries to keep the number of operating system threads concurrently inside InnoDB less than or equal to the limit given by this variable (InnoDB uses operating system threads to process user transactions). Once the number of threads reaches this limit, additional threads are placed into a wait state within a “First In, First Out” (FIFO) queue for execution. Threads waiting for locks are not counted in the number of concurrently executing threads.

We have 32 CPU, hence, the 32 value. But you can lower this to ensure that it doesn't suck up all the resources.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_thread_concurrency


Screen Shot 2015-09-15 at 8.20.07 PM
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it.

Basically we are trying to tell InnoDB to not work too hard by setting it to '2'.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit


Screen Shot 2015-09-15 at 8.24.21 PM
The number of I/O threads for read operations in InnoDB. The default value is 4. We set it to 32.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_read_io_threads


Screen Shot 2015-09-15 at 8.22.32 PM
The number of I/O threads for write operations in InnoDB. The default value is 4. We set it to 32.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_write_io_threads


Screen Shot 2015-09-15 at 8.26.44 PM
The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. But you most likely won't be able to set to 80%, in our case, we just set it to 400M. (it can goes up to few GB but that depends on your mysqltuner advises would be better)

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size


Screen Shot 2015-09-15 at 8.28.56 PM
If innodb_file_per_table is disabled (the default), InnoDB creates tables in the system tablespace. If innodb_file_per_table is enabled, InnoDB creates each new table using its own .ibd file for storing data and indexes, rather than in the system tablespace. This is to prevet shits from happening.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_file_per_table


Screen Shot 2015-09-15 at 8.31.59 PM
When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. This is a bitch when you have a lot of InnoDB which will keep updating the statics and may cause your simple SQL to runs for more than 10-50 seconds.

For more information visit http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata


Proxmox NoVNC not working

Well, if you are having problem with NoVNC not working on your proxmox and has been ignoring it up until now, its time to make it work. NoVNC basically uses web socket and html5 to allow you to remote access your virtual machine. So make sure you use a browser such as Chrome instead of Safari which has a full compatibility of web socket implementation on the browser. If not, you will most likely get yourself an error such as this,

TASK ERROR: command '/bin/nc -l -p 5900 -w 10 -c '/usr/sbin/qm vncproxy 100 2>/dev/null'' failed: exit code 1

Due to compatibility issue, Proxmox NoVNC might not work with the default install. All you need to do is to find out which NoVNC works for your current Proxmox installation and down/upgrade it! And for me, the version NoVNC 0.47 works for me so i downgraded it from 0.53 by doing the following,

wget http://download1.proxmox.com/debian/dists/wheezy/pvetest/binary-amd64/novnc-pve_0.4-7_amd64.deb
dpkg -i novnc-pve_0.4-7_amd64.deb

And it will do the rest, and if you would like try other version, just head down to the following link


to get all the binary you need.


Restart MailScanner in cPanel

Weirdly, this isn't cover so i might as well just write it down here. If you have MailScanner installed on your cPanel, the best thing to do after you've made a changes is to restart the service, but you'll find out that MailScanner isn't around as a service. So to restart cPanel as a services, you'll need to restart via cPanel script

/scripts/restartsrv mailscanner

and you'll see something like this,

-bash-4.1# /scripts/restartsrv mailscanner
Waiting for “mailscanner” to restart …………………………………………waiting for “mailscanner” to initialize ………………………………

Service Status
	Checking MailScanner daemons:

The service “mailscanner” failed to start with the message: Checking MailScanner daemons:

Log Messages
	Jul 28 14:37:23 chopper MailScanner: MailScanner setting UID to mailnull (47)
	Jul 28 14:37:48 chopper MailScanner: Deleted 1 messages from processing-database
	Jul 28 14:37:47 chopper MailScanner: Uninfected: Delivered 1 messages
	Jul 28 14:37:47 chopper MailScanner: Delivery of nonspam: message 1ZJyQp-0007At-KW from emporium@chopper.twopiz.com to admin@emporiumblog.com with subject  [Emporium Blog 2] Please moderate: "Paypal Utilizes Credit Card Machinery for Malaysians to Withdraw Funds"

mailscanner has failed. Please contact your system administrator if the service does not automagically recover.

You'll notice that the service mentioned that mailscanner has failed, just ignore it. As long as the daemons is up, it's kicking.


Using Command line to start, stop or reboot remote server with iDrac racadm

I've always like to set my Dell iDrac network management ip to a private one as compared to a public ones. However, when a dell server is causing a problem, i might not be able to access the server web interface since its an internal ip and the server might not be able to connect directly via ssh. In this case, how do i reboot the server without calling help from the datacenter? Apparently, there is a tool call racadm which can be used to assist such incident.

If you have access to the other network servers where you can still ping the dell iDrac private ip, you can fire the following commands to ensure that your server will reboot itself.

racadm -u ADMIN -p ADMIN -r serveraction hardreset

In the above, case, i am connect to the idrac and login with the ADMIN and password ADMIN to do a 'serveraction' with a hardreset. You can specifies the action. The options for the string are:

  • powerdown – Powers down the server module.
  • powerup – Powers up the server module.
  • powercycle – Issues a power-cycle to the server module.
  • hardreset – Issues a hard reset to the server module.

But do remember to install OpenManage or racadm on your server or else you are on your own! For more options available using racadm visit their doc page.


Easy Setup OpenVPN in 5 minutes with Debian or Centos or Ubuntu

Ok, i bet some times you will want to setup OpenVPN real quick in less than 5 minutes but have to go through with a lot of instruction and it might not work! Especially if you are on a VPS! Now let me explain how i did it in 5 minutes thanks to Nyr. If you are installing this on an OpenVZ machine, please update your host file as instructed at the bottom of this article, if you are not, just continue reading by firing up a VPS machine or a physical machine and fire the following instruction.

wget git.io/vpn --no-check-certificate -O ~/openvpn-install.sh; bash openvpn-install.sh

regardless of Debian, Centos or Ubuntu, this will work fine! Now, the script will ask you a few questions and starts installing

Welcome to this quick OpenVPN "road warrior" installer

I need to ask you a few questions before starting the setup
You can leave the default options and just press enter if you are ok with them

First I need to know the IPv4 address of the network interface you want OpenVPN
listening to.
IP address:

What port do you want for OpenVPN?
Port: 1194

Do you want OpenVPN to be available at port 53 too?
This can be useful to connect under restrictive networks
Listen at port 53 [y/n]: y

Do you want to enable internal networking for the VPN?
This can allow VPN clients to communicate between them
Allow internal networking [y/n]: y

What DNS do you want to use with the VPN?
   1) Current system resolvers
   2) OpenDNS
   3) Level 3
   4) NTT
   5) Hurricane Electric
   6) Yandex
DNS [1-6]: 2

Finally, tell me your name for the client cert
Please, use one word only, no special characters
Client name: example

I am installing OpenVPN in a OpenVZ machine. Therefore, i am throwing in the private ip of the machine instead of the public ones. Once the script finish installing and setup, it will ask you the following questions,

If your server is NATed (LowEndSpirit), I need to know the external IP
If that's not the case, just ignore this and leave the next field blank
External IP:


Your client config is available at ~/cluster.ovpn
If you want to add more clients, you simply need to run this script another time!

And you will get a user ovpn file to install it into your computer! Now, if you would like to add more user, do the following

bash ~/openvpn-install.sh

and you will see the following screen.

Looks like OpenVPN is already installed
What do you want to do?

1) Add a cert for a new user
2) Revoke existing user cert
3) Remove OpenVPN
4) Exit

Select an option [1-4]:

This is specially easy for anyone to just setup your OpenVPN machine in less than 5 minutes and furthermore, you can easily config more users using the same old script. Pretty neat stuff if you asked me!

Installing OpenVPN in OpenVZ

Now, there are a few more things to do if you are in an OpenVZ, on the host machine, you might want to add the following criteria so that iptables is available and internet is forwarding to your client.

at the bottom of /etc/vz/vz.conf you will see the following configuration

## Defaults for containers

## Filesystem layout for new CTs: either simfs (default) or ploop

## Load vzwdog module

## IPv4 iptables kernel modules to be enabled in CTs by default
IPTABLES="ipt_REJECT ipt_tos ipt_limit ipt_multiport iptable_filter iptable_mangle ipt_TCPMSS ipt_tcpmss ipt_ttl ipt_length"
## IPv4 iptables kernel modules to be loaded by init.d/vz script

## Enable IPv6

## IPv6 ip6tables kernel modules
IP6TABLES="ip6_tables ip6table_filter ip6table_mangle ip6t_REJECT"

change it to the following

## Defaults for containers

## Filesystem layout for new CTs: either simfs (default) or ploop

## Load vzwdog module

## IPv4 iptables kernel modules to be enabled in CTs by default
#IPTABLES="ipt_REDIRECT ipt_LOG ipt_state ipt_recent xt_connlimit ipt_owner iptable_nat ipt_REJECT ipt_tos ipt_limit ipt_multiport iptable_filter iptable_mangle ipt_TCPMSS ipt_tcpmss ipt_ttl ipt_length"
## IPv4 iptables kernel modules to be loaded by init.d/vz script

## Enable IPv6

## IPv6 ip6tables kernel modules
#IP6TABLES="ip6t_REDIRECT ip6t_REJECT ip6t_tos ip6t_limit ip6t_multiport ip6t_TCPMSS ip6t_tcpmss ip6t_ttl ip6t_length ip6t_LOG ip6t_state ip6t_recent xt_connlimit ip6t_owner ip6table_nat ip6_tables ip6table_filter ip6table_mangle ip6t_REJECT"

## IPv4 iptables kernel modules to be enabled in CTs by default
IPTABLES="ipt_REDIRECT ipt_owner ipt_recent iptable_filter iptable_mangle ipt_limit ipt_multiport ipt_tos ipt_TOS ipt_REJECT ipt_TCPMSS ipt_tcpmss ipt_ttl ipt_LOG ipt_length ip_conntrack ip_conntrack_ftp ip_conntrack_irc ipt_conntrack ipt_state ipt_helper iptable_nat ip_nat_ftp ip_nat_irc ipt_state iptable_nat"
## IPv4 iptables kernel modules to be loaded by init.d/vz script

## Enable IPv6

## IPv6 ip6tables kernel modules
IP6TABLES="ip6_tables ip6table_filter ip6table_mangle ip6t_REJECT"

and make sure ip forward is enable by going to /etc/sysctl.conf and update the following to '1'

# Uncomment the next line to enable packet forwarding for IPv4

and make sure Tun/TAP is enable for your VPS

# cat /dev/net/tun
cat: /dev/net/tun: File descriptor in bad state

If you are not seeing the above, do the following on your host machine,

vzctl set 101 --devnodes net/tun:rw --save
vzctl set 101 --devices c:10:200:rw --save 
vzctl stop 101 
vzctl set 101 --capability net_admin:on --save
vzctl start 101 
vzctl exec 101 mkdir -p /dev/net
vzctl exec 101 chmod 600 /dev/net/tun

Once you've done the above, then starts installing OpenVPN with the scripts by Nyr.

And remember to port forward port 1194 and 53!

-A PREROUTING -i vmbr1 -p tcp -m tcp --dport 53 -j DNAT --to-destination
-A PREROUTING -i vmbr1 -p udp -m udp --dport 1194 -j DNAT --to-destination
-A PREROUTING -i vmbr1 -p tcp -m tcp --dport 1194 -j DNAT --to-destination