Cet article est la traduction d'un billet posté par Josh Berkus et Joe Conway sur leur site, powerpostgresql.com. D'autres documents devraient suivre...

Voici un ensemble de règles de base pour votre serveur PostgreSQL 8.0. Beaucoup de ces règles sont basées sur des anecdoctes ou des tests pratiques ; il y a encore beaucoup de travail à effectuer en ce qui concerne les performances des bases de données et nous essayons de les mettre en place avec OSDL. Néanmoins, ceci devrait vous permettre de commencer. Toutes les informations ci-dessous sont utiles. Ce document, du 12 janvier 2005, sera vraisemblablement mis à jour plus tard. Les indications sur les paramètrages ci-dessous sont préférées aux indications que j'ai pû donner sur General Bits.

Cinq principes sur le matériel de votre serveur PostgreSQL

  1. Disques > RAM > CPU
    Si vous devez dépenser de l'argent dans un serveur PostgreSQL, dépensez-le sur des baies de disques à haute performance et obtenez des processeurs moyens et une quantité de mémoire adéquate. PostgreSQL, comme les autres RDBMS compatibles ACID, est très demandeur au niveau entrées/sorties. Il est rare que son utilisation du CPU soit plus importante que celle de la carte SCSI (bien que cela soit possible). Ceci s'applique aux petits serveurs mais cela s'applique aussi aux huit voix avec NetApps ; obtenez un CPU à bas prix si cela vous permet d'acheter la toute dernière carte RAID et un grand nombre de disques.
  2. Plus de têtes de lecture == Mieux
    Avec de nombreux disques, PostgreSQL et la plupart des systèmes d'exploitation parallèliseront les demandes de lecture et d'écriture sur la base de données. Ceci fera une énorme différence sur les systèmes de traitement des transactions et sera une amélioration significative pour toute application où la base de données ne tient pas entièrement en mémoire. Étant donné les tailles minimales des disques de nos jours (72 Go), vous pourriez être tenté de n'utiliser qu'un seul disque ou une seule paire en RAID 1 ; néanmoins, vous trouverez que l'utilisation de 4, 6, voire même 14 disques amèneront un surplus de puissance. Évidemment, le SCSI est toujours significativement meilleur que l'IDE, même avec SATA.
  3. Séparez les journaux des transactions de la base de données
    En supposant que vous avez déjà placé de l'argent sur une baie de dimension raisonnable, il existe quelques autres options intelligentes plutôt que de tout placer dans un RAID simple. En particulier, placer les journaux de la base de données (pg_xlog) sur sa propre ressource disque dédiée (une baie ou un disque) peut fournir un gain jusqu'à 12 % sur des bases de données comprenant une grosse activité en écriture. Ceci est tout particulièrement vitale sur les petits systèmes avec des disques SCSI ou IDE lents. Ceci fonctionne aussi dans le cas d'un serveur comprenant deux disques ; vous pouvez placer les journaux dans le disque du système d'exploitation et obtenir ainsi un gain simple.
  4. RAID 1+0/0+1 > RAID 5
    Le RAID 5 avec trois disques est devenu un standard malheureux parmi les serveurs économiques des gros vendeurs. Ceci est la configuration de baie la plus lente qu'il soit pour PostgreSQL ; vous pouvez espérer un petit 50 % par rapport à ce que vous pourriez obtenir avec un simple disque SCSI. À la place, concentrez-vous sur du RAID 1 ou 1+0 ou 0+1 avec un ensemble de 2, 4 ou 6 disques. Avec plus de six disques, RAID 5 commence à se comporter de façon acceptable au niveau des performances et la comparaison se fera plus au niveau de la qualité de votre contrôleur individuel. Peut-être plus important, une carte RAID bon marché peut être un risque ; il est quelque fois préférable d'utiliser du RAID logiciel plutôt que la carte Adaptec intégrée fournie avec votre serveur.
  5. Les applications doivent bien se comporter mutuellement
    L'autre grosse erreur que j'ai vu dans bon nombre de société est de placer PostgreSQL avec plusieurs autres applications sur un seul serveur, où tout le monde finit par se battre pour les mêmes ressources. Encore pire, placer PostgreSQL et d'autres RDBMS sur la même machine ; les systèmes de bases de données vont se battre pour obtenir la bande passante du disque et le cache disque du système d'exploitation, et ils en pâtiront tous. Les serveurs de documents et les programmes créant des traces de sécurité sont pratiquement aussi mauvais. PostgreSQL peut gérer une machine avec des applications qui ont des besoins principalement au niveau CPU et RAM, comme Apache, en supposant qu'il y ait suffisamment de RAM.

Douze paramètrages que vous voudrez ajuster dans votre fichier PostgreSQL.Conf

Il existe vraiment un nombre effrayant de nouvelles options dans le fichier PostgreSQL.conf. Même les options familières des cinq dernières versions ont vu leurs noms ou le format de leur paramètres changés. Le but est de vous donner plus de contrôle (vous, les administrateurs des bases de données). Mais cela vous peut vous demander du temps.

Ce qui suit sont les paramètrages que la plupart des administrateurs de bases de données voudront modifier, avec une attention portée plus fortement vers la performance. Il existe quelques paramètres spécialisés que la plupart des utilisateurs ne toucheront pas mais que ceux qui les utilisent trouveront indispensables. Pour ceux-là, il leur faudra attendre le livre.

Rappelez-vous : les paramètres de PostgreSQL.conf ne doivent pas être commentés pour être pris en compte, et les mettre de nouveau en commentaire ne restaure pas forcément leur valeur par défaut !

Connexions

listen_addresses : Remplace à la fois les paramètres tcp_ip et virtual_hosts de la version 7.4. Vaut, par défaut, localhost dans la plupart des installations, autorisant uniquement les connexions sur la console. La plupart des administrateurs de bases de données voudront le configurer à « * », signifiant toutes les interfaces disponibles, une fois que les bons droits ont été configurés dans le fichier pg_hba.conf, pour que PostgreSQL soit accessible du réseau. Comme amélioration par rapport aux versions précédentes, la valeur « localhost » par défaut permet les connexions sur l'interface « loopback », 127.0.0.1, activant les nombreux outils basés sur les navigateurs.

max_connections : Exactement comme dans les versions précédentes, cette variable a besoin d'être configurée pour atteindre le nombre réel de connexions dont vous pensez avoir besoin. Une valeur haute va requérir plus de mémoire partagée (shared_buffers). Comme la surcharge pour chaque connexion, à la fois du côté de PostgreSQL et de celui du système hôte, il est important d'utiliser une queue de connexion si vous avez besoin d'un grand nombre d'utilisateurs. Par exemple, 150 connexions actives sur un seul processeur 32 bit de qualité moyenne sur un serveur Linux consommera des ressources significatives. 600 est la limite sur ce matériel. Bien sûr, un matériel plus costaud permettra un plus grand nombre de connexions.

Mémoire

shared_buffers : Rappel : cette variable n'est PAS la mémoire totale qu'utilise PostgreSQL. C'est le bloc de mémoire dédié que PostgreSQL utilise pour ses opérations actives et devrait correspondre à une quantité mineure de la mémoire totale sur votre machine car PostgreSQL utilise aussi le cache disque du système d'exploitation. Malheureusement, le nombre exact de tampons partagés requis est un calcul complexe entre la RAM totale, la taille de la base de données, le nombre de connexions et la complexité des requêtes. Donc, il est préférable d'utiliser quelques règles de base dans l'allocation et de surveiller le serveur (particulièrement via les vues pg_statio) pour déterminer les ajustements nécessaires.
Sur des serveurs dédiés, les valeurs possibles semblent se situer entre 8 Mo et 400 Mo (entre 1 000 et 50 000 pages de 8 Ko). Les facteurs qui élèvent les tampons partagés désirés sont des portions actives plus importantes, des grosses requêtes complexes, de nombreuses requêtes simultanées, des procédures ou des transactions s'exécutant sur une grosse période, plus de RAM disponible et des processeurs plus nombreux ou plus rapides. Et, bien sûr, d'autres applications sur la machine. Contrairement à certaines attentes, allouer bien trop de shared_buffers peut réellement baisser les performances, le temps normalement requis pour le parcours. Voici quelques exemples basés sur des anecdotes et sur des tests TPC sur machines Linux :

  • Portable, processeur Celeron, 384 Mo de RAM, base de données de 25 Mo : 12 Mo/1500
  • Serveur Athlon, 1 Go de RAM, base de données décisionnelle 10 Go : 120 Mo/15000
  • Serveur Quadri PIII, 4 Go de RAM, 40 Go, base de données de traitements de grosses transactions (150 connexions) : 240 Mo/30000
  • Serveur Quadri Xeon, 8 Go de RAM, 200 Go, base de données de traitement de grosses transactions (300 connexions) : 400MB/50000
Merci de noter qu'augmenter shared_buffers, et quelques autres paramètres mémoire, vous demandera de modifier les paramètres mémoire System V du système d'exploitation. Voir la documentation de PostgreSQL pour des instructions sur ceci.

work_mem : Auparavant appelé sort_mem, mais renommé depuis car il couvre aussi les tris, les agrégats et quelques autres opérations. C'est une mémoire non partagée qui est allouée par opération (une à plusieurs fois par requête) ; ce paramètre est situé ici pour placer une limite sur la mémoire RAM que chaque opération peut allouer avant d'être forcé à écrire sur disque. Il devrait être configuré suivant un calcul basé en divisant la RAM disponible (après les applications et shared_buffers) par le nombre de requêtes maximum attendu, multiplié par le nombre moyen d'opérations par requête utilisant de la mémoire.
Une certaine attention doit aussi être portée sur la valeur de work_mem nécessaire à chaque requête ; traiter de grands ensembles de données en requiert plus. Les applications web utilisant des bases de données configurent généralement une valeur assez basse car le nombre de connexions est assez grand et les requêtes sont simples ; de 512 Ko à 2048 Ko suffit généralement. Au contraire, les applications de support de décision avec leur requêtes de 160 lignes et les agrégats de plus de 10 millions de lignes demandent souvent beaucoup plus, pratiquement 500 Mo sur un serveur disposant de beaucoup de mémoire. Pour des bases de données à utilisation mixe, ce paramètre peut être configuré par connexion, au moment du lancement de la requête, pour donner plus de RAM à certaines requêtes spécifiques.

maintenance_work_mem : Appelée auparavant vacuum_mem, ceci est la quantité de RAM que PostgreSQL utilise pour VACUUM, ANALYZE, CREATE INDEX et l'ajout de clés étrangères. Vous devez l'augmenter suivant la taille des tables de votre base de données et la quantité de mémoire que vous pouvez donner pour rendre les opérations aussi rapides que possibles. Un paramètrage de 50 % à 75 % de la taille (sur disque) de votre plus grosse table ou index est une bonne règle, ou entre 32 et 256 Mo lorsqu'il n'est pas possible de le déterminer.

Disque et WAL

checkpoint_segments : Définit la taille cache sur disque du journal des transactions pour les opérations d'écriture. Vous pouvez ignorer ceci dans les applications web utilisant des bases de données car elles sont principalement utilisées pour de la lecture. Mais, dans le cas des bases de données utilisant des transactions ou créant des rapports impliquant de gros volumes de données, l'augmenter est critique pour les performances. Suivant le volume des données, augmentez-le de 12 à 256 segments, en commençant avec des valeurs basses et en l'augmentant lorsque vous apercevez des messages d'avertissements dans les journaux de trace. L'espace requis sur disque est égal à (checkpoint_segments * 2 + 1) * 16MB, donc assurez-vous que vous disposez d'assez de place disque (32 signifiant plus d'1 Go).

max_fsm_pages : Taille du registre traçant partiellement les pages de données vides pour les peupler plus tard avec de nouvelles données ; si elle est bien configurée, elle rend le VACUUM plus rapide et supprime le besoin de VACUUM FULL et de REINDEX. Devrait être légèrement supérieure au nombre total de pages de données qui sera touché par les mises à jour et suppressions entre VACUUM. Les deux façons de déterminer ce nombre sont de lancer VACUUM VERBOSE ANALYZE ou, dans le cas de l'utilisation d'autovacuum (voir ci-dessous), de configurer ceci suivant la valeur du paramètre -V en tant que pourcentage des pages de données totales utilisées par votre base de données. fsm_pages requiert très peu de mémoire, donc il vaut mieux être généreux ici.

vacuum_cost_delay : Si vous avez des tables importantes et un nombre significatif d'écritures concurrentes, vous pourriez vouloir utiliser une nouvelle fonctionnalité qui diminue l'activité I/O des VACUUM au prix de les rendre plus long. Comme il s'agit d'une toute nouvelle fonctionnalité, c'est une valeur complexe comprenant cinq paramètres indépendants pour lesquels nous n'avons que quelques tests de performance. Augmenter vacuum_cost_delay à une valeur non nulle active cette fonctionnalité ; utilisez un délai raisonnable, quelque part entre 50 et 200 ms. Pour une configuration plus fine, augmenter vacuum_cost_page_hit et décroître vacuum_cost_page_limit amoindrira l'impact des opérations VACUUM et les rendra plus lentes ; dans les tests de Jan Wieck pour des traitements de transaction, un délai de 200, un page_hit de 6 et une limite de 100 fait décroître l'impact du VACUUM de plus de 80 % en triplant le temps d'exécution.

Planificateur de requêtes

Ces paramètres permettent au planificateur de requêtes de disposer d'estimations plus justes et, du coup, de choisir le meilleur plan possible. Il existe deux paramètres globaux dont il vaut mieux s'occuper :

effective_cache_size : Indique au planificateur de requêtes l'objet en base de données le plus gros qu'il peut s'attendre à avoir à placer en cache. Devrait généralement être configuré au 2/3 de la RAM totale, dans le cas d'un serveur dédié. Sur un serveur à utilisation mixe, vous aurez à estimer le nombre total de mémoire RAM et de cache disque du système d'exploitation utilisé par les applications et le soustraire.

random_page_cost : Une variable qui estime le coût moyen des recherches pour des pages de données récupérées via l'index. Sur des machines très rapides, avec des baies de disques très rapides, elle devrait être diminuée, à 3,0, 2,5 voire même 2,0. Néanmoins, si la portion active de votre base de données est bien plus grosse que votre mémoire, vous augmenterez le facteur à la valeur par défaut de 4. Autrement, vous basez vos ajustements sur la performance des requêtes. Si le planificateur semble favoriser sans raison les parcours séquentiels par rapport aux parcours d'index, baissez-le. Assurez-vous de tester une grande variété de requêtes. Ne passez pas en-dessous de 2,0 ; si cela semble nécessaire, vous avez besoin d'ajuster des variables comprises dans des domaines, comme les statistiques de planification.

Traces

log_destination : Ceci remplace le paramètrage non intuitif de syslog dans les versions précédentes. Vos choix sont d'utiliser le journal du système d'exploitation (syslog ou eventlog) ou d'utiliser un journal de trace séparé pour PostgreSQL (stderr). Le premier choix est meilleur en ce qui concerne la surveillance du système ; le deuxième est préférable pour corriger les problèmes de la base de données et pour améliorer sa configuration.

redirect_stderr : Si vous décidez d'utiliser un journal de traces séparé pour PostgreSQL, ce paramètrage vous permet de tracer dans un fichier en utilisant un outil de PostgreSQL au lieu de la redirection de la ligne de commande, vous permettant une rotation automatisée des traces. Configurez-le à True, puis configurez log_directory en lui indiquant où placer les traces. Les configurations par défaut pour log_filename, log_rotation_size et log_rotation_age conviennent à la plupart des gens.

Autovacuum et vous

Lors de votre progression vers la production en 8.0, vous devrez configurer un plan de maintenance qui inclut les VACUUM et ANALYZE. Si votre base de données doit gérer un flot important d'écriture de données mais ne requiert pas de chargement ou de suppression massifs de données ou de redémarrages fréquents, ceci pourrait impliquer la configuration de pg_autovacuum. C'est bien mieux qu'un VACUUM régulier parce que :

  • Les tables sont VACUUMées suivant leur activité, ceci permettant d'éviter de lancer des VACUUM sur des tables en lecture seule.
  • La fréquence des VACUUM augmente automatiquement avec l'acroissement de l'activité de la base de données.
  • Il est plus simple de calculer les besoins pour le "free space map" et éviter ainsi un engorgement de la base de données.

Configurer autovacuum requiert une construction facile du module compris dans le répertoire contrib/pg_autovacuum de vos sources PostgreSQL (les utilisateurs Windows devraient trouver autovaccuum inclus dans le paquetage PGInstaller). Vous activerez les paramètrages de configuration des statistiques détaillées dans le fichier README. Ensuite, vous lancez autovacuum une fois que PostgreSQL a démarré et en tant que processus séparé ; il s'arrêtera automatiquement lors de l'arrêt de PostgreSQL.

La configuration par défaut d'autovacuum est très basse et tout à fait convenable pour une très petite base de données. J'utilise généralement quelque chose de plus agressif comme :
-D -v 400 -V 0.4 -a 100 -A 0.3
Ceci lance VACUUM sur les tables après que 400 lignes + 40 % de la table ont été insérées, mises à jour ou supprimées. La configuration ci-dessus vous laisse aussi configurer max_fsm_pages à 50 % des pages de données dans la base avec la certitude que ce nombre ne sera pas dépassé, causant un débordement de la base de données. Nous testons actuellement différents paramètrages à OSDL et il y aura quelques cas difficiles plus bas.

Notez que vous pouvez aussi utiliser autovacuum pour configurer les options de délai du VACUUM, au lieu de le faire dans PostgreSQL.conf. Le délai du vacuum peut être d'une importance vitale pour les systèmes avec de très grosses tables ou index ; sinon, un appel autovacuum (non planifié) peut arrêter une opération importante.

Malheureusement, il existe un ensemble de limitations sérieuses à l'autovacuum de la 8.0, limitations qui seront éliminées dans les versions futures :

  • Pas de mémoire à long terme : autovacuum oublie toutes ces traces d'activité lors du redémarrage de la base de données. Donc, si vous effectuez des redémarrages réguliers, vous devrez faire un VACUUM ANALYZE complet des bases de données tout juste après.
  • Ne fait pas attention à l'activité du serveur : il existait des plans pour vérifier la charge du système avant le VACUUM, mais ce n'est pas une fonctionnalité actuelle. Donc, si vous avez des pics de charge extrême, autovacuum pourrait ne pas être le bon choix pour vous.
Copyright Josh Berkus et Joe Conway, OPL Licensed
Traduction française, Guillaume Lelarge.