Optimisation de la mémoire dans PostgreSQL

Paramètres dans postgresql.conf

Pour faciliter le chargement de grandes quantités de données dans une base de données PostgreSQL, vous pouvez avoir besoin d'augmenter les valeurs shared_buffers et max_locks_per_transaction du fichier postgresql.conf.

shared_buffers

Le paramètre shared_buffers désigne la quantité de mémoire utilisée pour les zones tampon de mémoire partagée. La documentation sur PostgreSQL indique que, pour des raisons de performance, vous avez probablement besoin d'utiliser un paramètre supérieur à la valeur minimale de 128 Ko ou de 16 Ko fois le nombre défini pour la valeur max_connections. Il est recommandé de définir le paramètre shared_buffers pour pouvoir utiliser plusieurs dizaines de Mo pour les installations de production.

Lorsque vous chargez de grandes quantités de données, vous avez probablement besoin d'un paramètre shared_buffers défini sur une valeur supérieure à la valeur de 32 Mo par défaut. Une fois ce paramètre modifié dans le fichier postgresql.conf, vous devez relancer le cluster de bases de données.

max_locks_per_transaction

La valeur max_locks_per_transaction indique le nombre d'objets de base de données susceptibles d'être verrouillés simultanément. Dans la plupart des cas, la valeur par défaut de 64 est suffisante. Cependant, lors du chargement simultané d'un grand nombre de jeux de données (par exemple, plusieurs milliers), le nombre de verrous d'objet simultanés pour la transaction peut dépasser 64.

Il ne s'agit pas d'un rapport un à un entre les verrous simultanés et le nombre de jeux de données. En d'autres termes, si vous chargez 3 000 jeux de données, il n'est pas nécessaire d'augmenter la valeur du paramètre max_locks_per_transaction à 3 000. Commencez en augmentant la valeur à 100 avant les chargements massifs de données.

Si vous modifiez la valeur du paramètre max_locks_per_transaction, vous devez relancer le serveur.

L'augmentation de la valeur de l'un ou l'autre de ces paramètres peut entraîner la base de données à demander davantage de mémoire partagée que celle dont votre système d'exploitation (OS) UNIX dispose. Pour en savoir plus sur la façon d'augmenter le réglage de la mémoire partagée en fonction du système d'exploitation utilisée, consultez la rubrique « Gestion des ressources de noyau » de la Documentation PostgreSQL.

Amélioration des performances de la requête spatiale SQL

Lorsque vous exécutez des requêtes SQL qui renvoient des colonnes spatiales ST_Geometry d'une table métier, vous pouvez améliorer les performances de la requête en définissant une variable d'environnement système, ST_GEOMETRY_OUTPUT_FORMAT, pour exporter vers le type ST_Geometry plutôt que la représentation textuelle connue (WKT) étendue.

Par défaut, ST_GEOMETRY_OUTPUT_FORMAT est défini sur TYPE, ce qui signifie qu'une représentation hexabinaire est renvoyée. Cela est nécessaire pour générer une sauvegarde exploitable de la géodatabase. Remplacez cette variable par ST_GEOMETRY uniquement si vous souhaitez améliorer les performances des requêtes SQL. Si vous définissez cette variable parce que vous envisagez d'effectuer un certain nombre de requêtes spatiales SQL, supprimez cette variable, une fois les requêtes effectuées, puis relancez le cluster de bases de données PostgreSQL.

La variable doit être définie sur l'ordinateur où PostgreSQL s'exécute.

Si vous utilisez un système d'exploitation Linux, définissez la variable du shell à partir duquel vous émettez des requêtes SQL. Pour un shell Bash, la syntaxe est la suivante :

ST_GEOMETRY_OUTPUT_FORMAT=ST_GEOMETRY

Pour un shell Csh, la syntaxe est la suivante :

setenv ST_GEOMETRY_OUTPUT_FORMAT ST_GEOMETRY

Sous Windows, créez une variable d'environnement système dans les propriétés système.

Nom de la variable : ST_GEOMETRY_OUTPUT_FORMAT

Valeur de la variable : ST_GEOMETRY

Après avoir défini la variable, vous devez relancer le cluster de bases de données PostgreSQL.