Ce document a pour objectif de présenter une méthodologie d'optimisation d'une base de données. Un serveur PostgreSQL/PostGIS sera utilisé comme exemple mais la méthodologie peut être utilisé pour toute optimisation. Les valeurs affichées correspondent à une utilisation réelle d'une base de données, présentées un peu plus loin.
Pour réellement optimiser une base de données, il faut pouvoir la tester en production, ce qui n'est pas évident du fait que, justement, le serveur n'est pas optimisé (lenteur des requêtes, etc.). Il s'agira donc de s'approcher d'une utilisation la plus proche possible du mode “en production”. Le lancement d'une version bêta et/ou d'un buzz permettra d'avoir une utilisation de la base quasiment en production, tout en ayant la possibilité de tester les paramètres de configuration. Pour tester ces paramètres et connaître leur influence sur le serveur on met en place un plan d'expérimentation, celui-ci va permettre de lister les paramètres à modifier pour la configuration du serveur et donc de tester leur influence sur le serveur1).
L'optimisation d'un serveur de base de données se compose grosso-modo de trois parties :
L'optimisation matérielle consiste à choisir un matériel adapté à vos besoins (RAM, CPU, RAID, Disque dur, etc.) ce qui se fait dés le début, lors de l'achat du serveur. L'optimisation des requêtes SQL, se fait lorsque l'application est fonctionnelle et avant sa sortie en beta. Vous pouvez vous aider de l'outil (certes un peu ancien) PQA, qui permet de connaître les requêtes les plus longues. C'est la dernière étape du développement d'une application. Enfin l'optimisation du serveur en lui même, objet de cet article. Il est primordial de définir l'objectif de cette optimisation sinon vous vous exposerez à une recherche sans but et à errer sans fin dans les bas fond des paramètres de configuration du serveur (et ce n'est pas très joyeux ;) ). Nous prendrons comme objectif : «diminuer la durée du script SQL blabla.sql lancé en cron (c'est à dire à heure fixe, le soir généralement)». Essayez d'utiliser un script SQL réel qui prend un peu de temps à se terminer. Celui utiliser prend environ 1h30 tous les soirs.
Voici quelques informations sur la base utilisée :
Le serveur a 4 Go de RAM.
Comme dit plus haut la première étape est de créer un plan factoriel qui présentera la liste des tests à effectué et la valeur (haute-basse) de chaque paramètre. Il est conseillé de ne pas utiliser trop de paramètre car cela peut vite devenir compliqué et long !
Listons les paramètres que l'on va faire évoluer :
ipcs
. Une valeur idéale est de 1,2 à 2 fois le pic d'utilisation de la mémoire.
Pour connaître la taille de la mémoire partagée, utilisez la commande cat /proc/sys/kernel/shmmax
, pour la modifier : echo 134217728 > /proc/sys/kernel/shmmax
. Les valeurs proposées dans la littérature utilise une valeur de BLCKSZ de 8192 bytes (taille du bloc sur le disque dur créé lors du formatage du disquedur).
Pour déterminer les valeurs de shmmax
, effectuez ce calcul : 250 Ko + 8.2 Ko * shared_buffers + 14.2 Ko * max_connections
, si max_connection = 100, nous aurons shmmax = 1793
, même valeur pour le paramètre de shmall
.
sysctl -w kernel.shmmax=<valeur>
pour écrire dans le fichier sysctl.conf la valeur à garder (sinon elle sera effacée au prochain reboot)
sysctl -p /etc/sysctl.conf
pour lire/recharger les valeurs à partir de sysctl.conf.
Paramètre | N° du facteur | Valeur basse | Valeur haute |
---|---|---|---|
shared_buffers | A | 8192 | 15200 |
work_mem | B | 1 024 | 6 148 |
effective_cache_size | C | 1 000 | 2 000 |
random_page_cost | D | 2 | 4 |
Variables mises de côté | |||
maintenance_work_mem2) | 8 192 | 98 304 | |
max_fsm_relations | 1 000 | 2 000 | |
max_fsm_pages | 2 000 | 81 456 | |
autovacuum_naptime | 60 | 300 |
D'autres valeurs :
Plan factoriel :
Exp | I | shared_buffers | work_mem | effective_cache_size | random_page_cost | Yexp |
---|---|---|---|---|---|---|
1 | +1 | -1 | -1 | -1 | -1 | |
2 | +1 | +1 | -1 | -1 | -1 | |
3 | +1 | -1 | +1 | -1 | -1 | |
4 | +1 | +1 | +1 | -1 | -1 | |
5 | +1 | -1 | -1 | +1 | -1 | |
6 | +1 | +1 | -1 | +1 | -1 | |
7 | +1 | -1 | +1 | +1 | -1 | |
8 | +1 | +1 | +1 | +1 | -1 | |
9 | +1 | -1 | -1 | -1 | +1 | |
10 | +1 | +1 | -1 | -1 | +1 | |
11 | +1 | -1 | +1 | -1 | +1 | |
12 | +1 | +1 | +1 | -1 | +1 | |
13 | +1 | -1 | -1 | +1 | +1 | |
14 | +1 | +1 | -1 | +1 | +1 | |
15 | +1 | -1 | +1 | +1 | +1 | |
16 | +1 | +1 | +1 | +1 | +1 | |
effets | a'0 | a'1 | a'2 | a'3 | a'4 |
Plan d'expérience et de résultat : Pour on remplace chacun des niveau bas et haut par leur valeur correspondant définie plus haut. Cela donne le plan d'expérimentation suivant :
Exp | I | shared_buffers | work_mem | effective_cache_size | random_page_cost | Yexp (en s.) | ipcs |
---|---|---|---|---|---|---|---|
1 | +1 | 8192 | 1024 | 1000 | 2 | 96.50 | 70705152 |
2 | +1 | 15200 | 1024 | 1000 | 2 | 74.09 | 128983040 |
3 | +1 | 8192 | 6148 | 1000 | 2 | 62.25 | 70705152 |
4 | +1 | 15200 | 6148 | 1000 | 2 | 62.67 | 128983040 |
5 | +1 | 8192 | 1024 | 2000 | 2 | 98.33 | 70705152 |
6 | +1 | 15200 | 1024 | 2000 | 2 | 73.98 | |
7 | +1 | 8192 | 6148 | 2000 | 2 | 62.20 | |
8 | +1 | 15200 | 6148 | 2000 | 2 | 61.88 | |
9 | +1 | 8192 | 1024 | 1000 | 4 | 100.25 | |
10 | +1 | 15200 | 1024 | 1000 | 4 | 100.00 | |
11 | +1 | 8192 | 6148 | 1000 | 4 | 64.55 | |
12 | +1 | 15200 | 6148 | 1000 | 4 | 64.88 | |
13 | +1 | 8192 | 1024 | 2000 | 4 | 100.50 | |
14 | +1 | 15200 | 1024 | 2000 | 4 | 76.00 | |
15 | +1 | 8192 | 6148 | 2000 | 4 | 64.50 | |
16 | +1 | 15200 | 6148 | 2000 | 4 | 62.48 | |
effets | a'0 | a'1 | a'2 | a'3 | a'4 |
Comment calcul t-on les effets ? Comme ceci :
Après lancement des 16 tests, nous obtenons les résultats suivants :
Exp | I | shared_buffers | work_mem | effective_cache_size | random_page_cost | Yexp |
---|---|---|---|---|---|---|
1 | +1 | -1 | -1 | -1 | -1 | 96.50 |
2 | +1 | +1 | -1 | -1 | -1 | 74.09 |
3 | +1 | -1 | +1 | -1 | -1 | 62.25 |
4 | +1 | +1 | +1 | -1 | -1 | 62.67 |
5 | +1 | -1 | -1 | +1 | -1 | 98.33 |
6 | +1 | +1 | -1 | +1 | -1 | 73.98 |
7 | +1 | -1 | +1 | +1 | -1 | 62.20 |
8 | +1 | +1 | +1 | +1 | -1 | 61.88 |
9 | +1 | -1 | -1 | -1 | +1 | 100.25 |
10 | +1 | +1 | -1 | -1 | +1 | 100.00 |
11 | +1 | -1 | +1 | -1 | +1 | 64.55 |
12 | +1 | +1 | +1 | -1 | +1 | 64.88 |
13 | +1 | -1 | -1 | +1 | +1 | 100.50 |
14 | +1 | +1 | -1 | +1 | +1 | 76.00 |
15 | +1 | -1 | +1 | +1 | +1 | 64.50 |
16 | +1 | +1 | +1 | +1 | +1 | 62.48 |
effets | a'0 | a'1 | a'2 | a'3 | a'4 | |
effets | 76,57 | -4,57 | -13,39 | -1,58 | 2,58 |
L'influence des différents paramètres sont les suivants : Y = 76,57 – 4,57 * shared_buffer – 13,39 * work_mem – 1,58 * effective_cache_size + 2,58 * random_page_cost
La meilleure manière d'optimiser le serveur est donc d'augmenter autant que possible les paramètres work_mem
et shared_buffer
ainsi que le paramètre effective_cache_size
dans une moindre mesure. Le paramètre random_page_cost
quant à lui doit être diminuer le plus possible.
Comment augmenter le paramètre work_mem
?
L'augmentation de ce paramètre doit être limité. Chaque connexion utilisant la mémoire disponible, c'est à dire celle définie par ce paramètre, il faut faire attention à ce qu'il reste une marge importante.
Documentation en anglais :
Logiciels de monitoring :