"Quand on veut reprendre avec utilité, et montrer à un autre qu'il se trompe, il faut observer par quel côté il envisage la chose, car elle est vraie ordinairement de ce côté-là, et lui avouer cette vérité, mais lui découvrir le côté où elle est fausse." Pascal, Pensées Br. 9, Lafuma 5.

Table des matières

Procédure d'optimisation d'un serveur PostgreSQL

Procédure d'optimisation d'un serveur PostgreSQL

Introduction

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.

Cette méthodologie n'est qu'une proposition, elle a certainement ses limites mais je la trouve intéressante car elle donne une méthodologie pour réaliser les tests. Elle issus de la chimie expérimentale, entendez par là la chimie qui réalise des expériences (d'optimisation par exemple) en opposition de celle en production.

Tester sa base de données en production

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 serveur¹⁾.

L'optimisation d'un serveur de base de données se compose grosso-modo de trois parties :

optimisation matérielle ;
optimisation des requêtes SQL ;
optimisation du serveur en lui même.

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.

Présentation de la base

Voici quelques informations sur la base utilisée :

Taille de la base : 53 Go
Nombre de table : environ 450 tables
Nombre maximal d'enregistrement dans la table la plus importante : > 2 000 000 de lignes
Nombre de connection par jour : une 30e par jour au minimum, ce nombre va évoluer vers une 100e de personne ( différent du nombre de connection simultanée).

Le serveur a 4 Go de RAM.

Préparation du plan factoriel

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 :

shared_buffers : la mémoire utilisée pour réaliser les requêtes. L'augmentation de ce paramètre augmente les performances jusqu'à un certain point et peut être dommageable à partir d'un certain niveau ! Les informations données dans la littérature donne 2 048 à 4 096 pour des machines qui possèdent 256 à 512 Mo de mémoire, et 8 192-32 768 pour des machines qui possèdent de 1 Go à 4 Go (128 Mo peut être excessif pour une mémoire RAM de 1 Go).

Pour déterminer la valeur idéale, définissez la valeur la plus haute possible, utilisez votre base en production et faites un suivit de votre mémoire partagé avec la commandeipcs. 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.

sort_memory/work_mem : définit la mémoire limite maximale qu'une connection à une base de données peut utiliser pour réaliser les classements (ORDER BY et GROUP BY). Faites attention en augmentant cette valeur qui agit pour un utilisateur et par connection, si vous multipliez les connections et les utilisateurs, cela peut être catastrophique !
effective_cache_size : taille de la mémoire de mise en cache pour les données importantes. À définir au 2/3 de la taille de la mémoire, pour 3 Go : 1.6 Go
max_fsm_pages : est déterminé en lançant une requête «VACUUM VERBOSE ANALYZE», le rapport vous donnera quelques indications pour certain paramètre.

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_mem²⁾		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 :

fsync = false
stats_start_collector = on
stats_command_string = on
max_stack_depth = 3072 (?? valeur à déterminer avec ulimit -s, retrancher 1 Mo soit 1024)

Plan factoriel :

Définition : Pour k variables (ou facteurs), la matrice d'expérience comporte k colonnes et 2^k lignes. Lors de la réalisation d'un plan d'expérience à deux niveaux, le nombre d'expériences à réaliser augmente d'une manière significative avec le nombre de variables prises en compte. Pour k variables, le plan comporte 2k expériences à réaliser. Un plan 2^7 nécessite 128 expérimentations !

Exp	I	shared_buffers	work_mem	effective_cache_size	random_page_cost
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 :

a'0 est égale à la somme de Yexp divisé par 16.
a'1 est égale à la somme des valeurs de Yexp multiplié par le coefficient de la colonne shared_buffers : (-1 * Y1 + 1 *Y2 - 1 * Y3 + 1 * Y4 - 1 * Y5 + …- 1 * Y15 + 1 * Y16)/4.
a'2 = (-1 * Y1 - 1 *Y2 + 1 * Y3 + 1 * Y4 - 1 * Y5 - …+ 1 * Y15 + 1 * Y16)/4 etc pour chacune des colonnes.

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

Attention : cela ne signifie pas que si on prend des valeurs pour ces paramètres on obtiendra la duré que prendra le script, ces coefficients indiquent le poids (et l'influence) du paramètre.

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.