CEK FAKTA
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:
| Total Server RAM | Buffer pool with 80% rule | Remaining RAM |
|---|---|---|
| 1G | 800MB | 200MB |
| 16G | 13G | 3G |
| 32G | 26G | 6G |
| 64G | 51G | 13G |
| 128G | 102G | 26G |
| 256G | 205G | 51G |
| 512G | 409G | 103G |
| 1024G | 819G | 205G |
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.
