15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

MySQL is a powerful open source Relational Database Management System or in short RDBMS. It was released back in 1995 (20 years old). It uses Structured Query Language which is probably the most popular choice for managing content within a database. The latest MySQL version is 5.6.25 and was released on 29 May 2015.

An interesting fact about MySQL is the fact that the name comes from Michael Widenius’s (MySQL’s creator) daughter My. Even though there are plenty of interesting facts about MySQL, this article is meant to show you some useful practices to help you manage your MySQL server.

MySQL Performance Tuning

MySQL Performance Tuning

In April 2009 the MySQL project was bought by Oracle. As a result a MySQL community fork called MariaDB was created. The main reason for creating the fork was to keep the project free under the General Public License.

Today MySQL and MariaDB are one of the most (if not the most) frequently used RDBMS used for web applications such as WordPress, Joomla, Magento and others.

This article will show you some basic, yet useful tips how to optimize the fine tune the performance of MySQL/MariaDB. Please keep in mind that this article assumes that you already have MySQL or MariaDB installed. If you are still wondering how to install them on your system, you can follow our extensive guides here:

  1. Installing LAMP on RHEL/CentOS 7
  2. Installing LAMP on Fedora 22
  3. Setting Up LAMP on Ubuntu 15.04
  4. Installing MariaDB on Debian 8
  5. Install MariaDB on Gentoo Linux
  6. Install MariaDB on Arch Linux

Important: Before we start – do not accept this suggestions blindly. Each MySQL setup is unique and requires additional thought, before making any changes.

Things you need to know:

  1. MySQL/MariaDB configuration file is located in /etc/my.cnf. Every time you modify this file you will need to restart the MySQL service so the new changes can take effect.
  2. For writing this article MySQL version 5.6 has been used as template.

1. Enable InnoDB file-per-table

First it is important to explain that InnoDB is a storage engine. MySQL and MariaDB use InnoDB as default storage engine. In the past MySQL used to keep database tables and indexes in a system tablespace. This approach was meant for servers which sole purpose is database processing and their storage disk is not used for any other purposes.

The InnoDB provides more flexible approach and each database information is kept in a .ibd data file. Each .ibd file represents a tablespace of its own. That way database operations such as “TRUNCATE” can be completed faster and you may also reclaim unused space when dropping or truncating a database table.

Another benefit of this configuration is the fact that you can keep some of the database tables in a separate storage device. This can greatly improve the I/O load on your disks.

The innodb_file_per_table is enabled by default in MySQL 5.6 and above. You can see that in /etc/my.cnf file. The directive looks like this:

innodb_file_per_table=1

2. Store MySQL Database Data on Separate Partition

Note: This setup only works with MySQL, but not with MariaDB.

Sometimes OS read/writes can slow down the performance of your MySQL server, especially if located on same hard drive. Instead, I would recommend using separate hard drive (preferably SSD) for the MySQL service.

To complete, this you will need to attach the new drive to your computer/server. For the purpose of this article, I will assume that the drive will be under /dev/sdb.

The next step is to prepare the new drive:

# fdisk /dev/sdb

Now press “n” to create new partition. Next press “p” to make the new partition primary. After that, set the partition number from 1-4. After that you will select the partition size. Press enter here. On the next step you will need to configure the size of the partition.

If you wish to use the entire disk press enter once more. Otherwise you can manually set the size of the new partition. When ready press “w” to write the changes. Now we will need to create a filesystem for our new partition. This can be easily done with:

# mkfs.ext4 /dev/sdb1

Now we will mount our new partition in a folder. I have named my folder “ssd” and created in the root directory:

# mkdir /ssd/

We are ready to mount the new partition we just made in the new folder:

# mount /dev/sdb1  /ssd/

You can perform the mount at startup by adding the following line in /etc/fstab file.

/dev/sdb1 /ssd ext3 defaults 0 0

Now you are ready to move MySQL to the new disk. First stop the MySQL service with:

# service mysqld stop

I would recommend you stopping Apache/nginx as well to prevent any attempts to write in the databases:

# service httpd stop
# service nginx stop

Now copy the entire MySQL directory in the new drive:

# cp /var/lib/mysql /ssd/ -Rp

This may take a while depending on the site of your MySQL databases. Once this process is complete rename the MySQL directory:

# mv /var/lib/mysql /var/lib/mysql-backup

Next we will create a symlink.

# ln -s /ssd/mysql /var/lib/mysql

Now you are ready to start your MySQL and web service:

# service mysqld start
# service httpd start
# service nginx start

At this point your MySQL databases will be accessed from the new drive.

Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Marin Todorov

I am a bachelor in computer science and a Linux Foundation Certified System Administrator. Currently working as a Senior Technical support in the hosting industry. In my free time I like testing new software and inline skating.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

14 Responses

  1. benyamin says:

    Hello ,

    I got a dedicated server with the following characteristics.

    Cpu:Intel(R) Xeon(R) CPU E3-1231 v3 @ 3.40GHz
    Ram:16 GB DDR3
    Hard:1TB Sata3
    

    On Linux server with Centos7 installs and more detailed specifications below.

    DirectAdmin 1.54.1
    Dovecot 2.3.3 (dcead646b)
    Exim 4.91
    MySQL 10.0.36
    Named 9.9.4
    Nginx 1.15.1
    php 7.0.32
    php2 7.1.22
    Pure-FTPd 1.0.47
    

    The problem with this is that I do not know the correct configuration for the error problem 504 Gateway Timeout Error, and I usually have this error on sites.

    Someone guides me?

    What are the best settings & config for “my.cnf” “nginx.cnf” “php.ini”? Guidance if possible?

    thanks .

  2. Jason Randle says:

    I have been learning more and more about tuning/hardening from your website when referencing Maria/DB. Thanks for the great presentations and the easy to copy one-liners for my SSH session. Cheers!

  3. Ashish Chauhan says:

    I really appreciate your explanation and problem solving methodologies. It’s Awesome article. Thank you very much for the time and effort.

Leave a Reply to Ashish Chauhan Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.