:: Yves Jacolin :: Ludovic Granjon :: Softlibre :: OSGeo-fr ::
"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.

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 serveur1).

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_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 :

  • 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 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 :

  • 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.

Quelques liens

Documentation en anglais :

Logiciels de monitoring :

  • PQA, plus pour le mode développement de la base/appli
  • vmstat

Contact - Information et copyright - Statistique

Recent changes RSS feed Creative Commons License Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki