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 par supprimer les commentaires de la propriété max_locks_per_transaction et par augmenter sa valeur sur 100 avant de procéder aux chargements par lots des 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 dispose. Pour savoir comment augmenter le réglage de la mémoire partagée sur votre système d'exploitation, consultez la rubrique "Gestion des ressources de noyau" de la documentation PostgreSQL.

cursor_tuple_fraction

La valeur cursor_tuple_fraction est utilisée par le planificateur PostgreSQL pour estimer quelle fraction des lignes renvoyées par une requête est nécessaire. Par défaut, la valeur de cursor_tuple_fraction est définie sur 0.1 dans le fichier postgresql.conf. Cela signifie que les premiers 10 pour cent des lignes (ou entités) dans le jeu de résultat sont renvoyés rapidement, mais que le renvoi du reste prend plus de temps.

Si vous souhaitez modifier le pourcentage du jeu de résultat que le planificateur PostgreSQL affiche en cas de connexion à des applications autres qu'ArcGIS, vous pouvez modifier la valeur de la propriété cursor_tuple_fraction dans le fichier postgresql.conf.

Lorsque vous utilisez ArcGIS 10.2 ou des versions ultérieures pour interroger les données, un paramètre de 1.0 est toujours utilisé, ce qui signifie que les données ne s'affichent pas tant que le jeu de résultat n'a pas été renvoyé à 100 pour cent. La modification de la valeur de la propriété cursor_tuple_fraction dans le fichier postgresql.conf ne s'applique pas en cas d'accès à la base de données via ArcGIS. Pour modifier la valeur qu'ArcGIS utilise, vous devez définir la variable d'environnement cursor_tuple_fraction pour votre session ou système.

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

Lorsque vous exécutez des requêtes SQL en dehors d'ArcGIS 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 ST_GEOMETRY, ce qui signifie qu'une représentation hexabinaire est renvoyée. Cela est nécessaire pour créer une sauvegarde utilisable de la géodatabase. Vous pouvez définir cette variable sur TYPE si vous souhaitez améliorer les performances de la requête 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.

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

ST_GEOMETRY_OUTPUT_FORMAT=TYPE

Pour un shell Csh, la syntaxe est la suivante :

setenv ST_GEOMETRY_OUTPUT_FORMAT TYPE

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

Nom de la variable : TYPE

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