tutorial MySQL

I. Overview ▲

Everything is not to be taken literally, but if the SQL server runs for some time (at least a few hours, at best a few days), a quick shot of MySQLTuner can provide a good overview of the basic settings, its use and potential areas for improvement.

A good part of the variables that may interest us for the diagnosis are the variables of global status , so we will display them with a small:

Select

Code:

SHOW GLOBAL STATUS;

We can possibly filter all this with the operator likeif we look for something specific.

II. Connections

MySQLTuner has probably already mentioned, but we can check the maximum number of connections allowed. And the maximum number of connections that were opened at the same time.

Code:

 Select

SELECT @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|              100 |
+-------------------+

SHOW GLOBAL STATUS like "%used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 98    |
+----------------------+-------+

In this case, it may be wise to increase the limit. This will certainly not alleviate the resources consumed by the server, quite the opposite, but will serve more customers at a time. Otherwise, when the max_connections is reached, the new connections are queued until a slot is freed.

It is not a question of increasing this limit to infinity, but if your server can tolerate more connections and you approach or reach the maximum allowed, do not hesitate to increase it.

III. The caches
table_open_cache

To see the limit of the table-ope n - cache :
Select
1.
2.
3.
4.
5.
6.
SELECT @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
| 400 |
+--------------------+
Each concurrent session accesses the tables independently, this allows a gain in performance at the expense of memory consumption a little more important (forcing, when we put things in cache ...).

table_open_cache indicates the maximum number of tables that the server can leave open. Thus, these tables are reused instead of being reopened. When this number is reached, any new session ejects the table that has not been used for the longest time.

In order to know if it can be beneficial to increase this number, we look at the number of open tables:

Select
1.
2.
3.
4.
5.
6.
show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 211 |
+---------------+-------+
It is obviously related to the time from which the database is started. A few hundred open tables in several weeks are not a problem. On the other hand, if this number increases rapidly, it will be advantageous to increase this number until the number of open tables stabilizes.

query_cache

The query_cache saves the results of SELECTand allows to return them much more efficiently and quickly during SELECTconsecutive. It can be very interesting to activate it in the case of a website where the same requests are often repeated.
The query_cache is not active by default on all versions of MySQL or MariaDB. Before anything else, let's see if it is available and active:
Select
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT @@have_query_cache;
+--------------------+
| @@have_query_cache |
+--------------------+
| YES |
+--------------------+

# il est dispo, est-il actif ?
SELECT @@query_cache_size;
+--------------------+
| @@query_cache_size |
+--------------------+
| 0 |
+--------------------+
Starting with MariaDB version 10.1.7, the server automatically activates the cache if the query_cache_size is greater than zero. The state of the cache can be checked with the variable @@ query_cache_state .

Select
# réglage du cache à 1MB
SET GLOBAL query_cache_size = 1000000;
tmp_table_size

Although not a cache itself, tmp_table_size defines the maximum size that temporary tables can take in RAM. Beyond that they are written on the disc.
Just compare the Created_tmp_disk_tables and Created_tmp_tables variables to see if a large proportion of the tables are written to disk. If so, it may be worthwhile to grant more space to tmp_table_size and max_heap_table_size . This second variable is for MEMORY tables , but the lower of these two variables will apply to temporary tables.
innodb_buffer_pool_size

The buffer_pool stores the data and the indexes in memory so as to avoid as much disk I / O as possible. As you will have understood, this is an essential element of InnoDB engine performance.
On a server dedicated to the database, we can dedicate 70 to 80% of the total RAM of the server. On a machine where resources are shared with other services, it's up to you to see how much to allocate to the BDD and how much you have to book for the rest. Keep in mind that the allocated memory will usually be 10% greater than what you specify because the system uses extra memory for control structures and buffers .
innodb_flush_method

This variable specifies for UNIX systems (no effect on Windows) how InnoDB reads the data and clears the cache. Generally O_DIRECT is recommended to prevent the OS to buffer data already hidden by InnoDB.
innodb_log_file_size

With the buffer_pool_size , these two parameters condition a large part of InnoDB's performance. The table engine runs in memory, however, to survive a crash, all changes are logged in a transaction file.
Nevertheless, the bigger this file, the higher the recovery time after crash.
innodb_flush_log_at_trx_commit

On a system with lots of writes, if the ACID properties are not critical and you can afford to lose the last second of a transaction in case of OS crash (or power failure), you can put this value to 2 rather than 1 (default). The transaction buffer will then be written after each commit rather than after each transaction, so you will gain a little I / O.