Configuration du serveur

1. Paramétrage lors de la compilation

Une des manières d’installer le serveur MariaDB consiste à compiler ses sources. Sous Linux, lors du lancement du script configure, il est alors possible de personnaliser votre installation en lui passant les options adéquates. Cette solution est souvent utilisée dans les grands comptes pour faire correspondre l’installation du serveur à la norme en vigueur dans l’entreprise ou encore pour des raisons de performance. Les directives ainsi spécifiées constitueront le paramétrage par défaut, sauf si les valeurs sont changées à l’un des autres niveaux de configuration.

La compilation de MariaDB sur les différentes plates-formes est détaillée dans la documentation en ligne : https://mariadb.com/kb/en/mariadb/compiling-mariadb-from-source/

2. Paramétrage dans le fichier de configuration

1. Paramètres essentiels

Les paramètres évoqués dans cette section ne sont pas nécessairement à modifier par rapport à la configuration par défaut, mais il est important que vous ayez réfléchi à leur bonne valeur pour votre application.

1. Le journal binaire

Le journal binaire ou binlog est chargé de stocker, sous un format binaire, toutes les requêtes qui modifient les objets de la base de données (INSERT, UPDATE, DELETE, DROP, CREATE, ALTER...). C’est l’élément central de la réplication MariaDB (voir chapitre Réplication). Il est également très utile à la restauration des données (voir chapitre Sauvegarde et restauration). Pour activer la journalisation binaire, utilisez l’option log-bin, et pour définir son fichier d’index, il faut paramétrer l’option log-bin-index. Ce fichier, qui contient la liste de tous les journaux binaires depuis la dernière purge, permet au serveur de connaître le nom du fichier courant, qui est obtenu avec la commande SHOW MASTER STATUS, mais aussi d’afficher la liste de tous les journaux binaires présents sur le serveur avec SHOW BINARY LOGS. La journalisation binaire peut être désactivée à chaud, mais seulement pour la session d’un client, avec l’option SQL_LOG_BIN.

Une configuration de base pourrait être :

[mysqld]  
log-bin = /var/lib/mysql/mysql-bin 
 

1. Les modes usuels

Avec MariaDB 10.1, la valeur par défaut de la variable sql_mode est NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION (le comportement de ce mode est expliqué un peu plus bas).

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';  
+---------------+--------------------------------------------+ 
| Variable_name | Value                                      | 
+---------------+--------------------------------------------+ 
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | 
+---------------+--------------------------------------------+
 

Prenons maintenant un exemple pour illustrer le rôle du mode SQL.

La table char3tinyint contient deux colonnes : c en CHAR(3), chaîne contenant trois caractères maximum, et i en TINYINT, qui n’accepte que les nombres entiers de -128 à 127.

mysql> SHOW CREATE TABLE char3tinyint \G 
*************************** 1. row *************************** 
Table: char3tinyint 
Create Table:  CREATE TABLE `char3tinyint` (  
  `c` char(3) DEFAULT NULL,  
  `i` tinyint(4) DEFAULT NULL  
); 
 
 
mysql> INSERT INTO char3tinyint VALUES('abc',127); 
Query OK, 1 row affected (0,00 sec) 
 
mysql> SELECT * FROM char3tinyint; 
+------+------+ 
| c    | i    | 
+------+------+ 
| abc  | 127  | 
+------+------+ 
 

La chaîne de caractères LeMUG est trop longue pour être stockée entièrement dans une colonne de type CHAR(3), le mode SQL par défaut étant permissif, le serveur va stocker ce qu’il peut, c’est-à-dire les trois premiers caractères et ignorer les deux suivants. Il tronque donc la chaîne de caractères. Dans la deuxième colonne, l’entier 2008 dépasse la borne maximale du type TINYINT qui vaut 127. Étant au-dessus de cette borne, c’est la valeur maximale, c’est-à-dire 127, qui va être stockée (si le nombre était inférieur à -128, c’est ce dernier nombre qui aurait été stocké). Pour chacune de ces colonnes, le serveur renvoie un avertissement qui décrit l’opération qu’il a implicitement réalisée.

mysql> INSERT INTO char3tinyint VALUES('LeMUG',2008); 
Query OK, 1 row affected, 2 warnings (0,00 sec) 
 
mysql> SHOW WARNINGS; 
+---------+------+--------------------------------------------+ 
| Level   | Code | Message                                    | 
+---------+------+--------------------------------------------+ 
| Warning | 1265 | Data truncated for column 'c' at row 1     | 
| Warning | 1264 | Out of range value for column 'i' at row 1 | 
+---------+------+--------------------------------------------+ 
 
mysql> SELECT * FROM char3tinyint; 
+------+------+ 
| c    | i    | 
+------+------+ 
| abc  | 127  | 
| LeM  | 127  | 
+------+------+ 
 

Le mode SQL devient strict pour la session grâce à la valeur STRICT_ALL_TABLES. Les données invalides sont rejetées, et une erreur est renvoyée car l’insertion ne se fait pas.

mysql> SET SESSION 
sql_mode=CONCAT(@@sql_mode,',','STRICT_ALL_TABLES'); 
Query OK, 0 rows affected (0,00 sec) 
 
mysql [localhost] {msandbox} (test) > SHOW SESSION VARIABLES LIKE 
'sql_mode'; 
+---------------+------------------------------------------+  
| Variable_name | Value                                    |  
+---------------+------------------------------------------+  
| sql_mode      | STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION |  
+---------------+------------------------------------------+  
1 row in set (0,00 sec) 
 
mysql> INSERT char3tinyint VALUES('LeMUG',2008); 
ERROR 1406 (22001): Data too long for column 'c' at row 1 
 
mysql> SELECT * FROM char3tinyint; 
+------+------+ 
| c    | i    | 
+------+------+ 
| abc  |  127 | 
| LeM  |  127 | 
+------+------+ 
 

Parmi les variables qui permettent de renforcer la cohérence des données, on trouve aussi :

mysql> SET SESSION sql_mode=''; 
 
mysql> SHOW SESSION VARIABLES LIKE 'sql_mode'; 
+---------------+-------+ 
| Variable_name | Value | 
+---------------+-------+ 
| sql_mode      |       |  
+---------------+-------+ 
 
mysql> CREATE TABLE t_ARIA(i int)ENGINE = ARIA; 
 
mysql> SHOW WARNINGS; 
+---------+------+-----------------------------------------------+ 
| Level   | Code | Message                                       | 
+---------+------+-----------------------------------------------+ 
| Warning | 1286 | Unknown table engine 'ARIA'                   | 
| Warning | 1266 | Using storage engine InnoDB for table 't_ARIA'| 
+---------+------+-----------------------------------------------+ 
 
mysql> SHOW CREATE TABLE t_ARIA \G 
*************************** 1. row *************************** 
Table: t_ARIA 
Create Table: CREATE TABLE `t_ARIA` ( 
`i` int(11) DEFAULT NULL 
) ENGINE=InnoDB; 
 
mysql> SET SESSION sql_mode='NO_ENGINE_SUBSTITUTION'; 
 
mysql> CREATE TABLE t_ARIA2(i int)ENGINE = ARIA;
 

Avec le mode STRICT_ALL_TABLES, le serveur renvoie une erreur à la première donnée invalide. Le moteur n’étant pas transactionnel, les données déjà insérées restent, ce qui peut entraîner des problèmes de cohérences. La solution pour ne pas se retrouver dans un tel cas est d’écrire vos requêtes INSERT avec seulement un enregistrement à la fois. Alors que, dans le même contexte, avec le mode STRICT_TRANS_TABLES, les données invalides sont insérées en étant converties en la valeur valide la plus proche. Le serveur génère néanmoins un avertissement.

À noter que si les deux modes sont activés, c’est le comportement du mode STRICT_ALL_TABLES qui prend le dessus.

mysql> SET SESSION sql_mode='STRICT_ALL_TABLES'; 
 
mysql> INSERT INTO t_myisam VALUES ('a',100),('ab',200); 
ERROR 1264 (22003): Out of range value for column 'i' at row 2 
 
mysql> SELECT * FROM t_myisam; 
+------+------+ 
| c    | i    | 
+------+------+ 
| a    | 100  | 
+------+------+ 
 
mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES'; 
 
mysql> INSERT INTO t_myisam VALUES ('b',111),('ab',200); 
Query OK, 2 rows affected, 1 warning (0,00 sec) 
Records: 2 Duplicates: 0 Warnings: 1 
 
mysql> SHOW WARNINGS; 
+---------+------+--------------------------------------------+ 
| Level   | Code | Message                                    | 
+---------+------+--------------------------------------------+ 
| Warning | 1264 | Out of range value for column 'i' at row 2 | 
+---------+------+--------------------------------------------+ 
 
mysql> SELECT * FROM t_myisam; 
+----+-----+ 
| c  | i   | 
+----+-----+ 
| a  | 100 | 
| b  | 111 | 
| ab | 127 | 
+----+-----+ 
 
mysql> SET SESSION sql_mode='STRICT_TRANS_TABLES, 
STRICT_ALL_TABLES'; 
 
mysql> INSERT INTO t_myisam VALUES ('abc',1),('abcdef',50); 
ERROR 1406 (22001): Data too long for column 'c' at row 2 
 
mysql> SELECT * FROM t_myisam; 
+------+------+ 
| c    | i    | 
+------+------+ 
| a    | 100  | 
| b    | 111  | 
| ab   | 127  | 
| abc  | 1    | 
+------+------+ 
4 row in set (0,00 sec)
 

mysql> SET SESSION sql_mode='ERROR_FOR_DIVISION_BY_ZERO'; 
 
mysql> INSERT ma_table VALUES (1/0); 
Query OK, 1 row affected, 1 warning (0,00 sec) 
 
mysql> SHOW WARNINGS; 
+-------+------+---------------+ 
| Level | Code | Message       | 
+-------+------+---------------+ 
| Error | 1365 | Division by 0 | 
+-------+------+---------------+ 
 

mysql> SET SESSION sql_mode='NO_ZERO_DATE,STRICT_TRANS_TABLES'; 
 
mysql> INSERT INTO ma_table values('0000-00-00 00:00:00'); 
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 
00:00:00' for column 'd' at row 1
 

mysql> SET SESSION sql_mode ='STRICT_ALL_TABLES,NO_ZERO_IN_DATE'; 
 
mysql> INSERT INTO t1 VALUES ('0000-00-00'); 
Query OK, 1 row affected (0,00 sec) 
 
mysql> INSERT INTO t1 VALUES ('0000-00-01'); 
ERROR 1292 (22007): Incorrect datetime value: '0000-00-01' for 
column 'd' at row 1 
 
mysql> INSERT INTO t1 VALUES ('0000-01-00'); 
ERROR 1292 (22007): Incorrect datetime value: '0000-01-00' for 
column 'd' at row 1 
 
mysql> INSERT INTO t1 VALUES ('0001-00-00'); 
ERROR 1292 (22007): Incorrect datetime value: '0001-00-00' for 
column 'd' at row 1 
 
mysql> INSERT INTO t1 VALUES ('1974-11-12'); 
Query OK, 1 row affected (0,00 sec)
 

mysql> GRANT USAGE ON *.* TO 'IT'; 
ERROR 1133 (42000): Can't find any matching row in the user table 
 
mysql> GRANT USAGE ON *.* TO 'IT' IDENTIFIED BY 'mot2pass3'; 
Query OK, 0 rows affected (0,00 sec) 
 
mysql> CREATE USER 'viadeo'; 
Query OK, 0 rows affected (0,00 sec) 
 

2. Les combinaisons de modes

Vous pouvez combiner plusieurs modes SQL, il suffit de les mettre à la suite, séparés par une virgule et sans espaces, le tout en une seule commande. Cette manipulation peut se faire à chaud, pour la session ou de manière globale. Cependant comme pour les autres options, il est préférable de la définir dans le fichier de configuration.

[mysqld] 
sql_mode=STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE, 
NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
 

L’option sql_mode peut aussi prendre comme valeur des combinaisons de modes prédéfinis. Pour avoir un comportement strict du serveur, utilisez le mode SQL TRADITIONAL. Il est équivalent aux modes ’STRICT_ TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_ DATE, ERROR_FOR_DIVISION_BY_ZERO...

1. Paramètres MyISAM

Le seul réel paramètre important pour les tables MyISAM est la taille du cache d’index, commandée par la variable key_buffer_size.

Pour vous aider à trouver une bonne taille, vous allez regarder les valeurs de trois variables de statut parmi le résultat de la commande :

mysql> SHOW GLOBAL STATUS LIKE 'Key_%';
 

Les deux premières variables à considérer sont Key_reads et Key_read_requests. Key_reads indique le nombre de requêtes de lectures d’index qui n’ont pas pu être satisfaites par le cache et Key_read_requests indique le nombre de lectures faites dans l’index. Si vous calculez le taux d’utilisation du cache par la formule (1 - Key_reads / Key_read_requests) x 100, votre cache devrait être bien paramétré si votre taux est proche de 100 %. Si le taux est faible, votre cache est sans doute trop petit et vous pouvez augmenter la valeur de key_buffer_size.

La troisième variable à regarder est Key_blocks_unused, qui vous donne le nombre de blocs disponibles dans le cache. Si votre taux d’utilisation est faible et que le nombre de blocs disponibles est également faible, votre cache est très certainement trop petit.

2. Cache de requêtes