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.

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.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

14 thoughts on “15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips”

  1. 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 .

    Reply
  2. 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!

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

    Reply
  4. Does not work anymore: “Error: mysqlcheck doesn’t support multiple contradicting commands.”

    mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
    

    You will have to execute the repair and optimize separately.

    Reply
  5. “2. Store MySQL Database Data on Separate Partition Note: This setup only works with MySQL, but not with MariaDB.”

    Same question as “Asd”, why? Would have been better to include that in the article.

    Also, rather than link, I just mounted the second drive at “/var/lib/mysql”.

    Reply
  6. At RHEL based systems you should call fdisk with “-c” to disable dos compatible mode which results in proper alignment of your disks.

    Nice article :)

    Reply
  7. This seems to be incorrect/typo. Section 14 has “The tool is called musqltunner”. Should be “mysqltuner”

    Reply
  8. “2. Store MySQL Database Data on Separate Partition
    Note: This setup only works with MySQL, but not with MariaDB.”

    Why? And why in 2015 ext3? :)

    Reply

Got something to say? Join the discussion.

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.