Re: [pgsql-fr-generale] Augmentation de taille incontrôlée d'une base

Francois Suter a écrit :
> Bonjour à tous,
>
> J'ai un souci avec une base de données qui explose régulièrement en
> taille et je ne comprends pas bien pourquoi. La base tourne en
> PostgreSQL 7.4.7. Certaines tables sont relativement grosses (quelques
> centaines de milliers d'enregistrement), mais pas de quoi fouetter un
> chat non plus. La vaste majorité de ces données sont mises à jour tous
> les soirs à partir de données exportées d'un ERP.
>
> Pour donner un exemple, voici une table assez typique:
>
> Table "public.tariff_table"
> Column | Type |
> Modifiers
> ----------------------------------+--------------------+--------------------
>
> tariff_table_id | integer | not null
> tariff_table_price | numeric(16,2) | not null default 0
> tariff_table_tariff_id | integer | not null
> tariff_table_from_quantity | numeric(15,4) | not null default 0
> tariff_table_to_quantity | numeric(15,4) | not null default 0
> tariff_table_control | integer | not null
> default 0
>
> Indexes:
> "tariff_table_pkey" primary key, btree (tariff_table_id)
> "tariff_table_control_index" btree (tariff_table_control)
> "tariff_table_index" btree (tariff_table_id)
>
> Foreign-key constraints:
> "tariff_table_tariff_id_key" FOREIGN KEY (tariff_table_tariff_id)
> REFERENCES tariff(tariff_id) ON UPDATE CASCADE ON DELETE CASCADE
>
> Elle contient 59'446 enregistrements. En faisant un VACUUM FULL VERBOSE
> dessus (et partant du principe que cette opération n'avait pas été
> effectuée depuis plusieurs jours), j'ai obtenu (outre un gain de place
> certain), les informations suivantes:
>
> INFO: ?tariff_table?: 2908726 versions de ligne supprimables, 59446 non
> supprimables parmi 26161 pages
> DETAIL: 0 versions de lignes mortes ne peuvent pas encore être supprimées.
> Les versions non supprimables de ligne vont de 64 to 72 octets.
> Il existait 6880 pointeurs d'éléments inutilisés.Espace libre total
> (incluant les versions supprimables de ligne) est de 197857540 octets.
> 25634 pages sont ou deviendront vides, ceci incluant 0 à la fin de la
> table.
> 25650 pages contenant 197839900 octets libres sont des destinations de
> d?placement disponibles.
> CPU 0.80s/0.32u sec elapsed 5.48 sec.
> INFO: L'index ?tariff_table_pkey? contient maintenant 59446 versions de
> ligne dans 9602 pages
> DETAIL: 2908726 versions de ligne d'index ont été supprimées.
> 3501 pages d'index ont été supprim?es, 3501 sont actuellement
> réutilisables.
> CPU 1.29s/7.34u sec elapsed 61.27 sec.
> INFO: L'index ?tariff_table_index? contient maintenant 59446 versions
> de ligne dans 9607 pages
> DETAIL: 2908726 versions de ligne d'index ont été supprimées.
> 3499 pages d'index ont été supprimées, 3499 sont actuellement
> réutilisables.
> CPU 1.50s/7.20u sec elapsed 56.45 sec.
> INFO: L'index ?tariff_table_control_index? contient maintenant 59446
> versions de ligne dans 8518 pages
> DETAIL: 2908726 versions de ligne d'index ont été supprimées.
> 8310 pages d'index ont été supprimées, 8310 sont actuellement
> réutilisables.
> CPU 1.24s/6.25u sec elapsed 30.92 sec.
> INFO: ?tariff_table?: moved 59446 row versions, truncated 26161 to 525
> pages
> DETAIL: CPU 3.29s/6.08u sec elapsed 83.63 sec.
> INFO: L'index ?tariff_table_pkey? contient maintenant 59446 versions de
> ligne dans 9602 pages
> DETAIL: 59446 versions de ligne d'index ont été supprimées.3501 pages
> d'index ont été supprimées, 3501 sont actuellement réutilisables.
> CPU 0.84s/0.59u sec elapsed 18.95 sec.
> INFO: L'index ?tariff_table_index? contient maintenant 59446 versions
> de ligne dans 9607 pages
> DETAIL: 59446 versions de ligne d'index ont été supprimées.3499 pages
> d'index ont été supprimées, 3499 sont actuellement r?utilisables.
> CPU 0.84s/0.70u sec elapsed 21.93 sec.
> INFO: L'index ?tariff_table_control_index? contient maintenant 59446
> versions de ligne dans 8518 pages
> DETAIL: 59446 versions de ligne d'index ont été supprimées.
> 8342 pages d'index ont été supprimées, 8342 sont actuellement
> réutilisables.
> CPU 0.16s/0.13u sec elapsed 0.82 sec.
>
> Vous paraît-il normal qu'il y ait tant de choses à nettoyer? Par exemple
> 2'908'726 pour une table qui ne compte que 59'446 enregistrements?
>

Même conseil que jpa, passe le plus rapidement à une 7.4.19.

Dans le cas d'un grossissement inconsidéré des tables, le conseil
habituel est de vérifier que des VACUUM sont fait régulièrement et que
les paramètres max_fsm_* sont bien configurés. Est-le cas ?

Si tu te trouves dans une solution aussi difficile que ça, mon premier
conseil serait de faire un VACUUM FULL et de reconfigurer tes max_fsm_*
pour qu'il soit en adéquation avec l'ensemble de tes bases. Ensuite,
revois avec beaucoup d'attention la fréquence des VACUUM, quitte à faire
des VACUUM super fréquents sur une table particulière.

De plus, si tu fais des VACUUM FULL régulièrement (ce que je ne
conseille pas, mais bon certains le font parfois, notamment par
méconnaissance du VACUUM et des paramèters max_fsm_*), n'oublie pas
l'opération de REINDEX après. (Ça pourrait expliquer l'état de tes index)

Voilà, voilà. Bon courage :)

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com