Optimisation

Il faut toujours essayer de structurer vos données et vos requêtes pour une performance optimale, en utilisant les techniques déjà vues dans ce cours :

• Formes normalisées, contraintes etc

• Requêtes :

  • Pas de select * (récupérez uniquement les attributs nécessaires)

  • Utilisation de la clause where pour bien filtrer les données avant la transmission

  • Utilisation de la pagination

Mais, quand une table devient très grande, on commence à constater des problèmes de performance.

• On fait une requête sur un attribut qui n’est pas une clé ?

• On demande trop de jointures ou de sous-requêtes ?

Comment réagir en temps réel ?

Avant d'optimiser, mesurer !

Identifier et mesurer

Vous constatez une lenteur dans votre application web ou smartphone. Il faut réagir avant de perdre des clients !

La première étape est de lancer une enquête de la source de la lenteur. En revanche, ce n'est pas si simple parce que la source peut-être à plusieurs endroits de votre stack :

• Votre logique et/ou code et pas optimale (exemple, dans PHP le templating prend trop de temps ?)

• La transmission de données prend trop de temps ?

  • Vous transmettez trop de données, ou des données inutiles ?

  • Votre architecture a suffisamment de bande-passante pour la demande ?

• Le SGBDR répond trop lentement ?

  • Les requêtes SQL produisent trop de produits cartésiens ?

  • La SGBDR répond vite, mais cette opération est très souvent sollicitée

  • Les ressources disponibles au SGBDR ne sont pas suffisants ?

Comme vous voyez, il y a plusieurs sources potentielles du problème.

Souvent, la solution la plus rapide, mais la plus chère, est-ce qu'on appelle vertical scaling. C'est-à-dire, on paye pour les ressources supplémentaires :

• payer une meilleure bande passante

• payer une infrastructure plus puissante pour le SGBDR

En revanche, cette solution n'est pas seulement chère, elle ne traite que les symptômes du problème.

Pour trouver la cause du problème de performance, il faut maîtriser tous les outils à notre disposition afin de trouver la cause exacte :

• Dans votre API, est-ce qu'il y a des outils d'observation ? console.time et console.timeEnd() dans JavaScript sont des outils simples mais efficaces pour observer où se trouve des bouchons dans votre code.

• Est-ce que vous avez des journaux que vous pouvez analyser, pour créer des synthèses, et identifier les requêtes les plus lentes ou les plus fréquemment appelés ?

• Est-ce que les différentes briques de votre architecture (comme votre SGBDR) disposent des modules d'observation ?

Le processus d'optimisation

1. Constatez : on commence par constater des ralentissements à certains endroits

2. Mesurer : on utilise (ou met en place) des outils d'observation afin de noter exactement où se trouve de ralentissements

3. Identifier : suite aux mesures, on identifie des requêtes, modules, bloques de code, sous-systèmes qui prennent le plus de temps

4. Répéter 2 et 3 : on répète de façon drill down, en rentrant dans le détail dans le but de trouver le module le plus bas niveau responsable du problème

5. Tests automatiques : est-ce qu'on a des tests automatiques que valide le comportement des modules concernés par le problème ? Sinon, avant l'intervention, rédiger les !

6. Intervention : on effectue l'opération d'optimisation. Une reformulation d'une requête SQL, la mise en place d'une cache, remanier du code, etc.

7. Mesurer : est-que notre intervention a amélioré la performance ? Sinon, l'intervention ne sert à rien !

8. Tester : tourner vos tests automatiques. Votre intervention n'a rien cassé ?

Outils d'observation

MariaDB, comme tous les SGBDR, dispose des outils d'observation qui nous aident dans nos efforts d'observation.

Nous utiliserons une base de données réelle afin de tester nous outils d'observation et optimisation :

• La base nutrition

Téléchargez et importer cette base dans votre SGBDR local.

Journaux

Il y a deux sortes de journal possible dans MariaDB :

• slow query log : enregistre des requêtes qui prennent trop de temps à exécuter

• general log: enregistre toutes les requêtes

Pour savoir si ces journaux sont activés ou pas :

show variables like 'general_log';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+

Et :

show variables like 'slow_query_log';

+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

On voit que par défaut ses journaux sont désactivés (pour optimiser la performance et l'utilisation de la place disque).

On peut les activer en modifiant le fichier de configuration de MariaDB :

# mariadb.cnf

[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
[mariadbd]
skip-host-cache
skip-name-resolve
max_connections=1000
wait_timeout=130

# Activer le journal classic
general_log
log_output=TABLE

# Activer le journal sur les requêtes lentes
slow_query_log
long_query_time=1.0

Exercice Créez le fichier mariadb.cnf et l'inclure dans votre Container local en suivant les instructions sur cette page. Validez avec les commandes dessus que les journaux soient bien activés.

Nous allons faire quelques requêtes sur notre base de donnés nutrition :

/* Requête 1 */
select email, nom, prenom from User where departement like '%haut%'; 

/* Requête 2 */
select count(type) from Repas where type = 'petit_dejeuner';

Vous constatez des problèmes de performance ?

Regardons les journaux !

select * from mysql.general_log order by event_time desc limit 5;

event_time	user_host	thread_id	server_id	command_type	argument
2023-04-18 07:32:27.810283	root[root] @ localhost []	3	1	Query	select * from mysql.general_log order by event_time desc limit 5
2023-04-18 07:31:11.464773	root[root] @ localhost []	3	1	Query	select count(type) from Repas where type = 'petit_dejeuner'
2023-04-18 07:30:06.848528	root[root] @ localhost []	3	1	Query	select email, nom, prenom from User where departement like '%haut%'

Ici, on voit les requêtes triées par la date d'événement. Mais, on n'a pas de notion de la durée de chaque requête.

Regardons l'autre journal :

select * from mysql.slow_log order by start_time desc limit 5;

start_time	user_host	query_time	lock_time	rows_sent	rows_examined	db	last_insert_id	insert_id	server_id	sql_text	thread_id	rows_affected
2023-04-18 07:31:31.304461	root[root] @ localhost []	00:00:19.839693	00:00:00.003751	1	838915	nutrition	0	0	1	select count(type) from Repas where type = 'petit_dejeuner'	3	0

Ici, on constate toutes les requêtes qui ont pris plus qu'une seconde (le seuil étant défini dans la variable long_query_time dans mariadb.cnf).

On a donc mesuré et identifié la source de notre problème.

À noter, en pratique, il serait préférable d’enregistrer les journaux vers un fichier au lieu d’une table (log_output=FILE) avec l’option logrotate. Détails ici : https://mariadb.com/kb/en/rotating-logs-on-unix-and-linux/

Explain

Le mot clé explain devant une requête va plutôt démontrer le plan d'action formulé par le SGBDR au lieu d'exécuter la requête. Il peut être utile pour optimiser nos requêtes :

• Valider que la clé primaire est utilisé, ou bien un attribut indexé est utilisé

• Valider qu'il n'y a pas trop d'étapes inutiles dans le plan d'action

• ...

Pour plus d'information sur l'outil explain, je vous dirige vers la page de documentation : https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Vous vous souvenez de notre requête lente dans la base saas ?

/** Cette requête prenait plus de 2 minutes à exécuter */
select distinct S.actionId, S.name, S.budget, S.address_french_regionId
from action as S, action as A
where 
  S.address_french_regionId != 'IDF' and S.address_french_regionId is not null 
  and 
  A.address_french_regionId = 'IDF' 
  and 
  S.budget > A.budget;

Regardons le plan d'action :

explain select distinct S.actionId, S.name, S.budget, S.address_french_regionId
from action as S, action as A
where 
  S.address_french_regionId != 'IDF' and S.address_french_regionId is not null 
  and 
  A.address_french_regionId = 'IDF' 
  and 
  S.budget > A.budget;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	address_french_regionId	NULL	NULL	NULL	31521	Using where; Using temporary
1	SIMPLE	S	ALL	address_french_regionId	NULL	NULL	NULL	31521	Using where; Using join buffer (flat, BNL join)

Nous avons pu optimiser la requête en utilisant des sous-requêtes :

explain select actionId, name, budget, address_french_regionId 
 from action
 where
   address_french_regionId != 'IDF' and 
   address_french_regionId is not null and
   budget > some (
     select budget
     from action
     where 
     address_french_regionId = 'IDF'
   );

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	action	ALL	address_french_regionId	NULL	NULL	NULL	31521	Using where
2	SUBQUERY	action	ALL	address_french_regionId	NULL	NULL	NULL	31521	Using where

Il y a deux différences majeures :

• On voit que dans les deux cas, on n'agit pas sur des clés primaires ni des attributs indexés : le champ possible_keys ne contient pas le mot PRIMARY ni de fk....

• En revanche, on voit dans la colonne extra que la première requête utilise des tampons mémoire pour satisfaire la requête, ainsi qu'un join buffer (pour le produit cartésien)

• On voit que la deuxième requête se compose en deux types : PRIMARY puis SUBQUERY qui nous donne un indice que si la sous-requête s'exécute en premier, elle va réduire le champ de possibilités pour la requête PRIMARY

Regardons pour la requête qu'on vient de faire sur la base nutrition :

explain select count(type) from Repas where type = 'petit_dejeuner';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Repas	ALL	NULL	NULL	NULL	NULL	818892	Using where

On voit qu'il n'y a aucune clé primaire, étrangère ni indexe utilisé pour la requête, et qu'on a 818k lignes à traiter ! Il y a de la place pour optimiser !

Dans cette requête, on filtre par l'attribut type, qui en soit, n'est pas une clé primaire. Si on peut organiser nos données pour plus facilement extraire des repas d'un certain type, on pourrait éventuellement optimiser notre requête.

Pour cela, on utilise des index.

Indexes

Ajouter un index est une façon d’optimiser nos requêtes. On sait déjà ce qu'une recherche par dichotomie est plus optimisée :

Alors pourquoi pas en profiter dans notre SGBDR !

En créant un index, on dit au SGBDR d'en effet créer une arborescence de données comme dans l'image pour plus rapidement trouver des données.

Créons un index pour l'attribut type :

create index ind_repas_type on Repas (type);

Vous allez remarquer que la création prend un peu de temps : c'est la construction de l'arborescence.

À tout moment, on peut afficher tous les index déjà en place :

show index from Repas;

Vous allez noter le mot BTREE dans les résultats. Cela veut dire binary tree qui est le nom anglais de la structure utilisé pour la recherche par dichotomie.

Ou bien supprimer un index :

drop index ind_repas_type on Repas;

Clés primaires, valeurs uniques, contraintes par clé étrangère

On sait comment toutes ses fonctionnalités sont implémentées dans notre SGBDR ? Grâce aux index ! Quand on crée une clé primaire, ou désigne un attribut comme unique, le SGBDR crée automatiquement un index pour l'attribut en question.

Par la suite, il serait plus facile et rapide pour le SGBDR de savoir si on crée des doublons !

Analysons le plan d'action pour notre requête lente maintenant :

explain select count(type) from Repas where type = 'petit_dejeuner';

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	Repas	ref	ind_repas_type	ind_repas_type	1023	const	409446	Using where; Using index

On voit maintenant qu'on utilise bien l'indexe !

Testons ! Est-ce qu'on a réussi à optimiser la requête ?

select count(type) from Repas where type = 'petit_dejeuner';
1 row in set (0.064 sec)

Index pour les recherches spéciales

On utilise aussi des index quand une requête agit sur un type de donnée non-standard. À la place de la dichotomie, les données seront organisées d'une façon d'optimiser la requête dans le domaine particulier de l'attribut.

Par exemple, pour les coordonnées géométriques, par exemple GPS, on va utiliser une représentation 2D de nos points de données, et ensuite, on va utiliser une structure comme un BSPTree (binary space partition tree) pour plus rapidement chercher des points proches ou dans un rayon précis.

Dans MariaDB, il y a des index non-standards :

FULLTEXT

• Effectuer des requêtes de type match qui fonctionnent plus comme un moteur de recherche.

• https://mariadb.com/kb/en/full-text-index-overview/

SPATIAL

• Effectuer des requêtes géo-spatial avec les coordonnées GPS (par exemple).

• https://mariadb.com/kb/en/spatial-index/