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 .ibd data 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 the needed size of the buffer pool, but that usually is not in all cases.
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 often change, you should use a 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 increasing in time.
Check the 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 up to 4GB but very high values are not recommended for sites where tables are modified quite frequently.
- query_cache_limit: Default is 1MB. You can set it up to 4GB. Again very high values are not recommended.
Below is 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
