15 Useful MySQL/MariaDB Performance Tuning and Optimization Tips

8. Configure MySQL query_cache_size

If you have many repetitive queries and your data does not change often – use query cache. People often do not understand the concept behind the query_cache_size and set this value to gigabytes, which can actually cause degradation in the performance.

The reason behind that is the fact that threads need to lock the cache during updates. Usually value of 200-300 MB should be more than enough. If your website is relatively small, you can try giving the value of 64M and increase in time.

You will have to add the following settings in the MySQL configuration file:

query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M

9. Configure tmp_table_size and max_heap_table_size

Both directives should have the same size and will help you prevent disk writes. The tmp_table_size is the maximum amount of size of internal in-memory tables. In case the limit in question is exceeded the table will be converted to on-disk MyISAM table.

This will affect the database performance. Administrators usually recommend giving 64M for both values for every GB of RAM on the server.

tmp_table_size= 64M
max_heap_table_size= 64M

10. Enable MySQL Slow query Logs

Logging slow queries can help you determine issues with your database and help you debug them. This can be easily enabled by adding the following values in your MySQL configuration file:

slow-query-log = 1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
long_query_time = 1

The first directive enables the logging of slow queries, while the second one tells MySQL where to store the actual log file. Use long_query_time to define the amount of time that is considered long for MySQL query to be completed.

11. Check for MySQL idle Connections

Idle connections consume resources and should be interrupted or refreshed when possible. Such connections are in “sleep” state and usually stay that way for long period of time. To look for idled connections you can run the following command:

# mysqladmin processlist -u root -p | grep “Sleep”

This will show you list of processes that are in sleep state. The event appears when the code is using persistent connection to the database. When using PHP this event can appear when using mysql_pconnect which opens the connection, after that executes queries, removes the authentication and leaves the connection open. This will cause any per-thread buffers to be kept in memory until the thread dies.

The first thing you would do here is to check the code and fix it. If you don’t have access to the code that is being ran, you can change the wait_timeout directive. The default value is 28800 seconds, while you can safely decrease it to something like 60:

If you liked this article, then do subscribe to email alerts for Linux tutorials. If you have any questions or doubts? do ask for help in the comments section.

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

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

Support Us

We are thankful for your never ending support.

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 .

  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!

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


Got something to say? Join the discussion.

Have a question or suggestion? Please leave a comment to start the discussion. Please keep in mind that all comments are moderated and your email address will NOT be published.

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