15 Essential MySQL & MariaDB Performance Tuning Tips for Faster Databases

MySQL is a powerful open‑source relational database management system (RDBMS) first released in 1995; the latest version mentioned is 5.6.25 (May 29, 2015). Its name comes from the founder Michael Widenius’s daughter, My.

In April 2009 Oracle acquired MySQL, leading to the creation of the MariaDB fork to preserve GPL freedom. Today MySQL and MariaDB are among the most popular RDBMS for web applications such as WordPress, Joomla, and Magento.

The article assumes MySQL or MariaDB is already installed; for installation on various Linux distributions see the list below.

Install LAMP on RHEL/CentOS 7

Install LAMP on Fedora 22

Install LAMP on Ubuntu 15.04

Install MariaDB on Debian 8

Install MariaDB on Gentoo Linux

Install MariaDB on Arch Linux

Important note: Do not blindly apply these suggestions; each MySQL/MariaDB setup is unique and changes should be considered carefully.

You need to know that the MySQL/MariaDB configuration file is located at /etc/my.cnf. After any change you must restart the MySQL service.

1. Enable per‑table InnoDB data files

InnoDB is the default storage engine. Enabling innodb_file_per_table stores each table’s data in its own .ibd file, allowing faster TRUNCATE operations and the ability to place tables on separate storage devices for better I/O performance.

innodb_file_per_table=1

2. Store MySQL data on a dedicated partition

Place MySQL data on a separate SSD or disk to avoid I/O contention with the operating system. Example steps (assuming the new disk is /dev/sdb):

# fdisk /dev/sdb

# mkfs.ext4 /dev/sdb1

# mkdir /ssd/

# mount /dev/sdb1 /ssd/

/dev/sdb1 /ssd ext3 defaults 0 0

Then move the data directory and create a symlink:

# service mysqld stop

# service httpd stop

# service nginx stop

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

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

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

# service mysqld start

# service httpd start

# service nginx start

3. Optimize the InnoDB buffer pool

Set innodb_buffer_pool_size to 60‑70 % of RAM on a dedicated MySQL server, or a lower percentage if other services share the machine.

innodb_buffer_pool_size

4. Disable Linux swappiness

Reduce swapping by setting vm.swappiness to 0.

# sysctl -w vm.swappiness=0

5. Set max_connections

Adjust max_connections according to expected concurrent users; values typically range from 100‑200 for small sites to 500‑800 for larger workloads.

# mysql -u root -pmysql -e "set global max_connections = 300;"

6. Configure thread_cache_size

Increase thread_cache_size to improve thread reuse. Check hit rate with:

mysql> show status like 'Threads_created';

mysql> show status like 'Connections';

Calculate hit rate: 100 - ((Threads_created / Connections) * 100). Adjust the variable as needed:

mysql> set global thread_cache_size = 16;

7. Disable reverse DNS lookups

Add skip-name-resolve to the MySQL configuration to avoid DNS delays, then restart MySQL.

[mysqld]
skip-name-resolve

8. Tune the query cache

Set reasonable values (e.g., 64‑300 MB) for query_cache_size, query_cache_type, and related settings.

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

9. Adjust temporary table sizes

Set tmp_table_size and max_heap_table_size to the same value (e.g., 64 M per GB of RAM) to reduce disk‑based temporary tables.

tmp_table_size=64M
max_heap_table_size=64M

10. Enable the slow‑query log

Log queries that exceed a threshold to help identify performance bottlenecks.

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

11. Monitor idle connections

Identify sleeping connections with: # mysqladmin processlist -u root -p | grep "Sleep" Reduce wait_timeout if necessary (e.g., to 60 seconds).

wait_timeout=60

12. Choose the right file system

Recommended file systems for MariaDB/MySQL data are XFS, ext4, and Btrfs, which support large files and volumes.

13. Increase max_allowed_packet

Set max_allowed_packet large enough to handle biggest rows to avoid slow queries or errors.

14. Test performance with mysqltuner

Download and run mysqltuner for a detailed performance report:

# wget https://github.com/major/MySQLTuner-perl/tarball/master
# tar xf master
# cd major-MySQLTuner-perl-*/
# ./mysqltuner.pl

15. Repair and optimize tables

Use mysqlcheck to automatically check, repair, and optimize all databases.

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

These steps provide a comprehensive approach to tuning MySQL/MariaDB for better performance and stability.