A few tricks to speed up MySQL Response - Advanced


In MySQL 5.5, and above, there are a few basic tricks you can use to speed up MySQL response for larger queries.

Below are a few examples.

Optimiser Search Depth

A flag in MySQL introduced from MySQL 5.5 and above, has a habit of causing slowness in larger queries, due to the "Depth" it goes to to optimise a query. To limit this which is by default a LARGE number, you can set it to automatically optimise the depth to a lower level by adding the below in your '[mysqld]' section within /etc/my.cnf

optimizer_search_depth = 0

This will set it from a large number such as 21, to a nice number around 7 or below, and speed up the query runs.

MysqlTMP Directory in RAM

Another effective option you have, is by moving the MySQL TMP directory into RAM, and forcing queries over 2K to run in RAM.

First, locate the MySQL Users UID (User), and GID (Group).

root@server [~]# grep mysql /etc/passwd
mysql:x:498:499:MySQL server:/var/lib/mysql:/bin/bash

In the above example, 498 is the UID, and 499 is the GID

Next, we need to create a line, to add to fstab in RAM, an example is as follows based on the information above.

echo "tmpfs /var/mysqltmp tmpfs rw,uid=498,gid=499,size=1G,nr_inodes=10k,mode=0700 0 0" >> /etc/fstab

mkdir /var/mysqltmp

mount /var/mysqltmp

In the example above, we're assigning a possible maximum of 1GB for the Temporary MySQL Folder, and then creating the folder, then mounting it.

In some cases if you're using SELinux in CentOS, or Apparmor in Ubuntu, you will have to allow the /var/mysqltmp for the binaries, and in general via SELinux.

Next, you will need to go ahead and add the configuration to the '[mysqld]' section in /etc/my.cnf

tmp_table_size= 2K

The above changes will force all queries over 2K to immediately go into RAM on the TMPFS RAM partition, instead of doing the write to the local drive, then pushing the query into RAM straight away, instead of waiting until the query is a certain size, then copying it from RAM onto the Disk, slowing down the query process.

Have more questions? Submit a request