15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

3. Optimizing InnoDB buffer pool Usage

The InnoDB engine has a buffer pool used for caching data and indexes in memory. This of course will help your MySQL/MariaDB queries be executed significantly faster. Choosing the proper size here requires some very important decisions and good knowledge on your system’s memory consumption.

Here is what you need to consider:

  1. How much memory you need for other processes. This includes your system processes, page tables, socket buffers.
  2. Is your server dedicated for MySQL or you will be running other memory hungry services.

On a dedicated box, you would probably want to give about 60-70% of the memory to the innodb_buffer_pool_size. If you plan on running more services on a single box, you should re-consider the amount of memory you dedicate for your innodb_buffer_pool_size.

The value that you should edit in my.cnf is:

innodb_buffer_pool_size

4. Avoid Swappiness in MySQL

Swapping is process that occurs when system moves part of memory to a special disk space called “swap”. The event usually appears when your system runs out of physical memory and instead of freeing up some RAM, the system pushed the information into disk. As you might have guess the disk is much slower than your RAM.

By default the option is enabled:

# sysctl vm.swappiness 

vm.swappiness = 60

To disable swappiness, run the following command:

# sysctl -w vm.swappiness=0

5. Set MySQL Max Connections

The max_connections directive tells your server how many concurrent connections are permitted. The MySQL/MariaDB server allows the value given in max_connections + 1 for user with SUPER privileges. The connection is opened only for the time MySQL query is executed – after that it is closed and new connection can take its place.

Keep in mind that too many connections can cause high RAM usage and lock up your MySQL server. Usually small websites will require between 100-200 connections while larger may require 500-800 or even more. The value you apply here strongly depends on your particular MySQL/MariaDB usage.

You can dynamically change the value of max_connections, without having to restart the MySQL service by running:

# mysql -u root -p
mysql> set global max_connections := 300;

6. Configure MySQL thread_cache_size

The thread_cache_size directive sets the amount of threads that your server should cache. As the client disconnects, his threads are put in the cache if they are less than the thread_cache_size. Further requests are completed by using the threads stored in the cache.

To improve your performance you can set the thread_cache_size to a relatively high number. To find the thread cache hit rate, you can use the following technique:

mysql> show status like 'Threads_created';
mysql> show status like 'Connections';

Now use the following formula to calculate the thread cache hit rate percentage:

100 - ((Threads_created / Connections) * 100)

If you get a low number, it means that most of the new mysql connections are starting new thread instead of loading from cache. You will surely want to increase the thread_cache_size in such cases.

The good thing here is that the thread_cache_size can be dynamically changed without having to restart the MySQL service. You can achieve this by running:

mysql> set global thread_cache_size = 16;

7. Disable MySQL Reverse DNS Lookups

By default MySQL/MariaDB perform a DNS lookup of the user’s IP address/Hostname from which the connection is coming. For each client connection, the IP address is checked by resolving it to a host name. After that the host name is resolved back to an IP to verify that both match.

This unfortunately may cause delays in case of badly configured DNS or problems with DNS server. This is why you can disable the reverse DNS lookup by adding the following in your configuration file:

[mysqld]
# Skip reverse DNS lookup of clients
skip-name-resolve

You will have to restart the MySQL service after applying these changes.

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

Leave a Reply to Jason Randle Cancel reply

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.