How to optimize MySQL/MariaDB Performance on Server

MySQL and MariaDB are popular choices for free database management systems. Both use the SQL querying language to manipulate and query data. If your application or website have a lot of data and you still use default configuration of MySQL/MariaDB, it will decrease performance and efficiency on your system. Below is some tips you can apply in your MySQL/MariaDB config to increase MySQL/MariaDB performance.

Tips 1: Store MySQL Database Data on SSD Partition

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 SSD disk for the MySQL service.

Tips 2: Enable InnoDB file-per-table

MySQL and MariaDB use InnoDB as default storage engine.

The InnoDB provides more flexible approach and each database information is kept in a .ibddata 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

Tip 3: Increase InoDB buffer pool size

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.

Rule: you should adjust InoDb buffer pool size = 80% RAM

Example:

At lower numbers, our 80% rule looks pretty reasonable.  However, as we get into large servers, it starts to seem less sane.  For the rule to hold true, it must mean that workload memory consumption increases in proportion to needed size of the buffer pool, but that usually isn’t the case.

innodb_buffer_pool_size 800M

Tips 4: Set MySQL Max Connections

The max_connections directive tells your server how many concurrent connections are permitted.  The connection is opened only for the time MySQL query is executed – after that it is closed and new connection can take its place.

The default setting for max_connections is 100. You can see what the current setting is by running the following SQL command from the command:

show variables like "max_connections";

This will return a resultset like this

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   |
+-----------------+-------+

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 SQL command:

set global max_connections := 300;

Or you can set in mysql.cnf like this

max_connections = 200

Rule for max connection:
You can calculate max connection using the formula:

Available RAM = Global Buffers + (Thread Buffers x max_connections)
max_connections = (Available RAM – Global Buffers) / Thread Buffers

To get the list of buffers and their values:

SHOW VARIABLES LIKE '%buffer%';

Here’s a list of the buffers and whether they’re Global or Thread:

Global Buffers: key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size

Thread Buffers: sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack

Tips 5: Configure MySQL query_cache_size

From MySQL 5.6.8,query_cache_type is set to OFF by default. So if you haven’t explicitly turned it ON on old version, it may not work anymore!

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.

Check current status of query_cache

mysql -e "show variables like 'query_cache_%'"

+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 2097152   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 268435456 |
| query_cache_strip_comments   | ON        |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+

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

Meaning of variables

• query_cache_type = 1: just turn on query cache
• query_cache_size: Default is 1MB. You can set it upto 4GB but very high values are not recommend for sites where tables are modified quite frequently.
• query_cache_limit: Default is 1MB. You can set it upto 4GB. Again very high values are not recommended.

Below my configuration file for MySQL on VPS 1Gb RAM you can reference:

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking

key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP

query_cache_type = 1
query_cache_limit   = 10M
query_cache_size        = 80M
query_cache_min_res_unit = 2k

tmp_table_size= 64M
max_heap_table_size= 64M

log_error = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size   = 100M
innodb_buffer_pool_size=800M
innodb_buffer_pool_instances = 3