Optimisation base de données

[MathieuLagarde]

Bonjour,

En Bretagne, nous sommes sur le point d'ouvrir une plateforme permettant au public de visualiser et télécharger les données naturalistes en région. Nous utilisons GéoNature (2.4.1) et GéoNature Atlas (1.4.1), sur Debian 10.3. Nous avons actuellement centralisé près de 1,3 millions de données dans la table synthèse. Notre serveur actuel a les caractéristiques suivantes: 2 cpu / 6 GO de RAM avec 100GO de disque. A moyen terme (1-2 ans ?), j'estime que nous pourrions aisément arriver à 5 millions de données.

Je constate désormais des lenteurs à deux niveaux:

• lors de la consultation des données (visualisation via Atlas, requêtage via Synthèse). La plateforme n'est pas fluide à l'utilisation ;
• lors de l'import de données dans la table synthèse (j'ai notamment un clause vérifiant que l'UUID des nouvelles données n'est pas déjà présent dans la base).

J'ai regardé le poids des différentes tables de ma base, et voici les plus volumineuses:	table_schema	table_name	total_size	data_size	external_size
gn_synthese	synthese	12 GB	5557 MB	7215 MB
gn_synthese	cor_area_synthese	2992 MB	1720 MB	1272 MB
gn_synthese	cor_area_taxon	1766 MB	924 MB	842 MB
ref_geo	l_areas	648 MB	434 MB	214 MB

D'où mes questions:

• Ai-je moyen d'optimiser certaines tables ? Le paramétrage de base de l'outil (MNT, zonages administratifs...) a-t'il un impact significatif sur l'administration de la base ?
• Quelles sont les caractéristiques de vos serveurs / volume de données traitées ?

D'avance, merci pour vos réponses.

Mathieu

[jbrieuclp]

Salut Mathieu, Tu peux mettre en place une tâche panifiée pour passer l'aspi dans ta BD chaque nuit : VACUUM ANALYZE; Rien que ça, ça devrait changer du tout au tout ! Tu peux aussi le faire individuellement avec VACUUM ANALYZE gn_synthese.synthese; pour voir la diff au niveau l'espace disque utilisé. Et si t'es chaud tu peux aussi passer l'aspi dans les recoins et sur les plaintes en faisant un VACUUM FULL table; mais là ça la bloque en lecture/écriture donc faut mettre ton app en maintenance au cas où.

[jbdesbas]

Salut, Voir aussi ici pour les paramétrage de base de postgre/postgis: http://www.postgis.fr/chrome/site/docs/workshop-foss4g/doc/tuning.html (ca date, mais ca reste mieux que les paramètre par défaut). Pour moi ça a changer pas mal de chose (volume de données comparable). L'import dans la synthèse est souvent assez long, car il y a des triggers pour les jointures spatiales. Certains peuvent être désactivé (par exemple celui pour cor_area_taxon, aujourd'hui inutile si tu n'utilise pas occtax-mobile). Si des lenteurs persiste, EXPLAIN permet de voir si des indexs pourrait être ajoutés. Le REINDEX sur la synthese peut aussi aider si tu as fait plusieurs tests suivis de suppressions.

@jbrieuclp par défaut, c'est pas automatique le VACUUM ?

[jbrieuclp]

@jbdesbas Si tu as bien une option dans le postgresql.conf d'autovacuum, mais visiblement, au vu de la comparaison des perf entre avant et après ma mise en œuvre du vacuum analyze en tâche planifiée, ben je ne sais pas où il était exécuté mon autovacuum, mais il restait de la poussière sous le lit... Là chaque nuit j'ai 20 minutes qui sont prises pour faire le vacuum analyze sur toute la BD, je trouve ça plutôt correct.

[jbdesbas]

En effet, j'ai lancé un vacuum, et il m'a nettoyé des index sur des tables que je n'avais pas utilisé depuis plusieurs semaines. Je vais donc programmer un vacuum analyze régulier aussi merci !

[camillemonchicourt]

Si le champs de recherche des taxons dans la Synthèse est long à s'afficher, vous pouvez réindexer la vue matérialisée sur laquelle il s'appuie :

REINDEX TABLE taxonomie.vm_taxref_list_forautocomplete;

Quelques autres exemples de requêtes de nettoyage et réindexation :

-- Nettoyer et réindexer la synthèse
VACUUM FULL gn_synthese.synthese;
VACUUM ANALYSE gn_synthese.synthese;
REINDEX TABLE gn_synthese.synthese;

-- Nettoyer les autres tables de la synthèse
VACUUM FULL gn_synthese.cor_area_synthese;
VACUUM FULL gn_synthese.cor_area_taxon;
VACUUM FULL gn_synthese.cor_observer_synthese;

-- Nettoyer et réindexer la table des zonages géographiques
VACUUM FULL ref_geo.l_areas;
VACUUM ANALYSE ref_geo.l_areas;
REINDEX TABLE ref_geo.l_areas;

-- Réindexer toute la BDD
REINDEX DATABASE geonature2db;

Pour automatiser l'éxecution d'une de ces fonctions (tous les jours à 23h dans cet exemple), ajoutez la dans le crontab de l'utilisateur postgres :

sudo su postgres
crontab -e

Puis ajouter la ligne suivante en adaptant éventuellement le nom de la base de données et la commande exécutée :

0 23 * * * psql -d geonature2db -c "VACUUM ANALYSE;"

Pour enregistrer et quitter : Ctrl + O, ENTER puis Ctrl + X.

[jbrieuclp]

Attention à ne pas utiliser le VACUUM FULL à tout va. A moins d'avoir fait du grand n'imp' sur la base il vaut mieux s'en passer. Par contre une tache CRON quotidienne pour le VACUUM ANALYZE oui, une pourquoi pas plus espacée pour le REINDEX aussi !