JanHvizdak.COM

Optimisation of MySQL - Parameters, memory and filesystem

MySQL is the most used database engine when it comes to websites, let’s explain how to improve performance and how to optimise all parameters in order to improve speed and execution of SQL statements.

Prerequisites

Of course there should be a MySQL database installed on your machine. If you’re missing it, get one here. The following examples were made on openSUSE Linux, therefore same OS commands cannot be used on a Windows machine. However the principle is still same. The below-shown example assumes we create a table in the database “test”, then we fill the table with data and we’re going to execute certain SQL commands prior to / after performing optimisation. Ideally we should have two additional drives available for mounting.

Optimisation of filesystem

MySQL is installed to /var/lib/mysql by default, however we should use a separate filesystem for the purpose of this testing. In addition there’s a tmpdir parameter, usually commented out, in my.cnf which can be found in /etc/. Therefore we have to modify my.cnf by executing following commands. Bear in mind syntax/paths may be different depending on your Linux distribution.

Words of warning

In the case below we’re going to play with filesystems! Make sure your OS isn’t currently using any /dev/sdb1 and /dev/sdc1 drive before executing commands below! Eventually if you know what you’re doing, use other empty filesystems or create virtual drives.

#if your MySQL is running, stop it
service mysql stop

#ensure your /dev/sdb1 /dev/sdc1 filesystems aren’t mounted
#umount them if needed by executing following:
umount /dev/sdc1
umount /dev/sdb1

#create XFS filesystem on the above-mentioned drives
mkfs.xfs -f /dev/sdb1
mkfs.xfs -f /dev/sdc1

#let’s create a new mysql directory,
#apparently you have to be logged in as root
mkdir /mysql

#now mount two standalone drives
mount /dev/sdb1 /mysql      #this is drive for MySQL database & datafiles
mkdir /mysql/data && mkdir /mysql/tmp
mount /dev/sdc1 /mysql/tmp  #this is drive for tmp directory

#change owner and group of the directories
chown -R mysql:mysql /mysql

#now modify my.cnf file
#find command isn’t necessary if your my.cnf is in present in /etc/
find / -name my.cnf 2>/dev/null
nano /etc/my.cnf  #use different path if necessary

#comment out datadir = /var/lib/mysql and add new path:
#datadir = /var/lib/mysql
datadir = /mysql/data

#also add tmpdir:
tmpdir = /mysql/tmp

#now start MySQL server to ensure filesystems are 100% ready
service mysql start

#also review the log
cat /var/log/mysql/mysqld.log  #path may differ!

##we should see more than one line, if the process hasn’t been started yet,
##execute the command below (valid for openSUSE/SUSE Linux)
service mysql start

#login to mysql console and enter password when prompted
#since you’ve created a new datadir, password will be most likely empty
mysql -u root -p -A

--when we’re in, we’re going to create our new database called test1
CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */;

--now let’s switch to database test and create a procedure that inserts data
use test1;

delimiter $$  
CREATE PROCEDURE `test_table`()
begin                                                                                                                                                                  
declare i int Default 0 ;   
declare random char(20) ;                                                                                                                                     
myloop: loop
set random=conv(floor(rand() * 99999999999999), 20, 36) ;
INSERT INTO `test_table` VALUES ( NOW() , random );
set i=i+1;
if i=1000000 then
SHOW VARIABLES LIKE 'datadir';
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;

--also create our testing table
CREATE TABLE `test_table` (
  `datecol` datetime NOT NULL,
  `name` varchar(25) NOT NULL,
  KEY `datecol` (`datecol`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--now fill it by executing our recent procedure
--bear in mind it’s going to be approximately 400MB large
--ensure your /mysql filesystem offers enough space
--you can execute following command from within MySQL shell:
\! df -h /mysql

--if there is enough free space, execute the function and wait
call test_table();

--this took approximately 1 minute and 37 seconds in my VMWare machine:
--Query OK, 0 rows affected (1 min 36.39 sec)

Comparison to /var/lib/mysql

Let’s test the speed of INSERTS when there’s no XFS dedicated filesystem. All we have to do is to shutdown the database, modify my.cnf and create the table & procedure again. Open new terminal and follow the instructions below.

#stop the database
service mysql stop

#modify my.cnf - if it isn’t in /etc , then use another path
nano /etc/my.cnf

#uncomment original datadir and comment out /mysql
#also comment out tmpdir
datadir = /var/lib/mysql
#datadir = /mysql/data
#tmpdir = /mysql/tmp

#now start MySQL database again
service mysql start

#login to MySQL console, create database test1 and our procedure along with table
mysql -u root -p -A

--when we’re in, we’re going to create our new database called test1
CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */;

--now let’s switch to database test and create a procedure that inserts data
use test1;

delimiter $$  
CREATE PROCEDURE `test_table`()
begin                                                                                                                                                                  
declare i int Default 0 ;   
declare random char(20) ;                                                                                                                                     
myloop: loop
set random=conv(floor(rand() * 99999999999999), 20, 36) ;
INSERT INTO `test_table` VALUES ( NOW() , random );
set i=i+1;
if i=1000000 then
SHOW VARIABLES LIKE 'datadir';
leave myloop;
end if;
end loop myloop;
end $$
delimiter ;

--also create our testing table
CREATE TABLE `test_table` (
  `datecol` datetime NOT NULL,
  `name` varchar(25) NOT NULL,
  KEY `datecol` (`datecol`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--now fill it by executing our recent procedure
--bear in mind it’s going to be approximately 400MB large
--ensure your /var/lib/mysql filesystem offers enough space
--you can execute following command from within MySQL shell:
\! df -h /var/lib/mysql

--if there is enough free space, execute the function and wait
call test_table();

--this took approximately 1 minute and 55 seconds in my VMWare machine:
--Query OK, 0 rows affected (1 min 55.22 sec)

Conclusion 1: Filesystems

External drive is definitely faster and each MySQL installation should be given a standalone drive. Separate tmpdir is ideal for fast SELECT statements as it’s used for sorting purposes.

Tuning memory parameters

There are several parameters in /etc/my.cnf that can be modified in order to improve sorting, SELECT statements, etc. Here below is a list of the applicable ones as well as example of pre and post-tuning.

key_buffer_size should be at least 128M
table_open_cache should be at least 512
sort_buffer_size should be at least 64M
net_buffer_length should be at least 2M
net_buffer_length should be at least 2M
read_buffer_size should be at least 8M
read_rnd_buffer_size should be at least 16M

Let’s tell the difference now. Modify the my.cnf file so it uses our external drive and then restart the database. Ideally open a new terminal and execute following commands to do so.

#stop MySQL server service mysql stop #modify /etc/my.cnf file nano /etc/my.cnf #comment out original datadir and uncomment new datadir and tmpdir #datadir = /var/lib/mysql datadir = /mysql/data tmpdir = /mysql/tmp #now start MySQL server service mysql start #login to MySQL console, create database test1 and our procedure along with table mysql -u root -p -A --now let’s switch to database test and create a procedure that inserts data use test1; --now execute following SELECT: select * from test_table order by rand(); --I’ve got anything between 3 and 16 seconds. --now modify /etc/my.cnf in another terminal by executing following commands nano /etc/my.cnf #ensure following parameters are set to listed values: key_buffer_size = 128M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 64M net_buffer_length = 2M read_buffer_size = 8M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 8M #then save the file and restart MySQL server service mysql restart #and return back to the original terminal/MySQL console --execute our SELECT statement again: select * from test_table order by rand(); --I haven’t got a result that was greater than 3.10 second

Conclusion and results

It simply makese sense that more memory and dedicated filesystem along with XFS partition are needed in order to optimise a MySQL server. If your database contains say 10MB of data, then there’s no need to worry about performance as it’s going to be fast anyway. However if your application connects to gigabytes of data, then it’s wise to test various setups and confirguration files in order to tell when queries are executed the best. Please, note that if your hosting provider gives you for example 100-200 inserts per second, then such a database might be overloaded or may suffer from wrong configuration. MySQL is capable of performing tens of thousands inserts per second even when there’s over 10GB of data in a non-partitioned table.

Cleanup

If everything is clear, execute following commands in order to cleanup all above.

--drop the database test1 and tables
DROP database test1;

--now unmount drives and restore original MySQL datadir/tmpdir
--first of all close MySQL console by typing:
exit;

#now edit the my.cnf file
nano /etc/my.cnf

#let’s modify paths - comment out newly created ones and uncomment the original one
datadir = /var/lib/mysql
#datadir = /mysql/data

#tmpdir = /mysql/tmp

#save the file and restart MySQL server
service mysql restart

JanHvizdak.COM

Secure Browsing

Recent News

Cross-Linker update

January 23^rd, 2014

Worth visiting

Optimisation of MySQL - Parameters, memory and filesystem

Prerequisites

Optimisation of filesystem

Words of warning

Comparison to /var/lib/mysql

Conclusion 1: Filesystems

Tuning memory parameters

Conclusion and results

Cleanup

Further reading & help

Secure Browsing

Recent News

Cross-Linker update

January 23rd, 2014

Worth visiting

Optimisation of MySQL - Parameters, memory and filesystem

Prerequisites

Optimisation of filesystem

Words of warning

Comparison to /var/lib/mysql

Conclusion 1: Filesystems

Tuning memory parameters

Conclusion and results

Cleanup

Further reading & help

January 23^rd, 2014