Étudier le besoin de VACUUM sur la base de production

thbar commented 1 year ago

Je commence à prendre quelques notes là dessus.

Référence:

Actuellement on a les réglages généraux suivants (https://dba.stackexchange.com/questions/35814):

select *
from pg_settings 
where name like '%autovacuum%'

| name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val | enumvals | boot_val | reset_val | sourcefile | sourceline | pending_restart | |---------------------------------------|-----------|------|-------------------------------------|-------------------------------------------------------------------------------------------|-----------------------------------------------------------|------------|---------|--------------------|---------|------------|----------|-----------|-----------|------------|------------|-----------------| | autovacuum | on | | Autovacuum | Starts the autovacuum subprocess. | | sighup | bool | configuration file | | | | on | on | | | f | | autovacuum_analyze_scale_factor | 0.1 | | Autovacuum | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.1 | 0.1 | | | f | | autovacuum_analyze_threshold | 50 | | Autovacuum | Minimum number of tuple inserts, updates, or deletes prior to analyze. | | sighup | integer | default | 0 | 2147483647 | | 50 | 50 | | | f | | autovacuum_freeze_max_age | 200000000 | | Autovacuum | Age at which to autovacuum a table to prevent transaction ID wraparound. | | postmaster | integer | default | 100000 | 2000000000 | | 200000000 | 200000000 | | | f | | autovacuum_max_workers | 3 | | Autovacuum | Sets the maximum number of simultaneously running autovacuum worker processes. | | postmaster | integer | default | 1 | 262143 | | 3 | 3 | | | f | | autovacuum_multixact_freeze_max_age | 400000000 | | Autovacuum | Multixact age at which to autovacuum a table to prevent multixact wraparound. | | postmaster | integer | default | 10000 | 2000000000 | | 400000000 | 400000000 | | | f | | autovacuum_naptime | 60 | s | Autovacuum | Time to sleep between autovacuum runs. | | sighup | integer | default | 1 | 2147483 | | 60 | 60 | | | f | | autovacuum_vacuum_cost_delay | 2 | ms | Autovacuum | Vacuum cost delay in milliseconds, for autovacuum. | | sighup | real | default | -1 | 100 | | 2 | 2 | | | f | | autovacuum_vacuum_cost_limit | -1 | | Autovacuum | Vacuum cost amount available before napping, for autovacuum. | | sighup | integer | default | -1 | 10000 | | -1 | -1 | | | f | | autovacuum_vacuum_insert_scale_factor | 0.2 | | Autovacuum | Number of tuple inserts prior to vacuum as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.2 | 0.2 | | | f | | autovacuum_vacuum_insert_threshold | 1000 | | Autovacuum | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums. | | sighup | integer | default | -1 | 2147483647 | | 1000 | 1000 | | | f | | autovacuum_vacuum_scale_factor | 0.2 | | Autovacuum | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. | | sighup | real | default | 0 | 100 | | 0.2 | 0.2 | | | f | | autovacuum_vacuum_threshold | 50 | | Autovacuum | Minimum number of tuple updates or deletes prior to vacuum. | | sighup | integer | default | 0 | 2147483647 | | 50 | 50 | | | f | | autovacuum_work_mem | -1 | kB | Resource Usage / Memory | Sets the maximum memory to be used by each autovacuum worker process. | | sighup | integer | default | -1 | 2147483647 | | -1 | -1 | | | f | | log_autovacuum_min_duration | 10000 | ms | Reporting and Logging / What to Log | Sets the minimum execution time above which autovacuum actions will be logged. | Zero prints all actions. -1 turns autovacuum logging off. | sighup | integer | configuration file | -1 | 2147483647 | | -1 | 10000 | | | f |

Et il semble que ces réglages ne soient pas overridés par table :

SELECT relname, reloptions FROM pg_class where reloptions is NOT NULL

| relname | reloptions | |--------------------|-------------------------| | pg_stats | {security_barrier=true} | | pg_stats_ext | {security_barrier=true} | | pg_stats_ext_exprs | {security_barrier=true} |

Il faut qu'on voit si l'auto-vacuum a priori en place est suffisant.

thbar commented 1 year ago

La section "Vacuum Statistics" de cet article donne la query suivante:

SELECT
    n.nspname AS schema_name,
    c.relname AS table_name,
    c.reltuples AS row_count,
    c.relpages AS page_count,
    s.n_dead_tup AS dead_row_count,
    s.last_vacuum,
    s.last_autovacuum,
    s.last_analyze,
    s.last_autoanalyze
FROM
    pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid
WHERE
    c.relkind = 'r'
    AND n.nspname = 'public';

J'ai la sensation que l'auto-vacuum fait bien son travail, à regarder les "dead rows" et le timestamp de dernier auto-vacuum:

| schema_name | table_name | row_count | page_count | dead_row_count | last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | |-------------|----------------------------|-----------|------------|----------------|-------------|-------------------------------|--------------|-------------------------------| | public | geo_data_import | 3 | 1 | 5 | | | | | | public | spatial_ref_sys | 8496 | 867 | 0 | | | | | | public | geo_data | 51419 | 21616 | 0 | | 2023-07-16 12:31:17.831398+00 | | 2023-07-16 12:31:18.482709+00 | | public | oban_peers | 1 | 1 | 27 | | 2023-07-17 07:57:42.338644+00 | | 2023-07-17 07:57:42.343514+00 | | public | breaking_news | 0 | 0 | 0 | | | | | | public | dataset | 486 | 155 | 126 | | 2023-07-17 04:11:25.198482+00 | | 2023-07-17 04:15:24.968158+00 | | public | data_import | 425 | 15 | 20 | | 2023-07-17 04:02:24.930602+00 | | 2023-07-17 04:02:24.933695+00 | | public | epci | 1249 | 135 | 0 | | 2023-07-06 11:13:11.337805+00 | | | | public | gtfs_calendar_dates | 0 | 0 | 0 | | | | | | public | multi_validation | 105276 | 11743 | 4355 | | | | 2023-07-17 07:11:43.900467+00 | | public | resource_metadata | 426736 | 16475 | 3 | | 2023-07-08 21:43:50.587606+00 | | 2023-07-16 16:20:16.710405+00 | | public | stats_history | 20450 | 269 | 0 | | | | | | public | metrics | 372560 | 7035 | 55081 | | 2023-07-10 02:30:15.688726+00 | | 2023-07-17 07:40:42.488177+00 | | public | oban_jobs | 51734 | 2280 | 10577 | | 2023-07-17 06:15:28.25187+00 | | 2023-07-17 07:03:42.490274+00 | | public | departement | 102 | 16 | 0 | | | | | | public | dataset_aom_legal_owner | 357 | 16 | 1 | | | | | | public | dataset_region_legal_owner | 0 | 1 | 0 | | | | | | public | resource_related | -1 | 0 | 0 | | | | | | public | aom | 803 | 1006 | 0 | | 2023-07-06 18:18:51.71936+00 | | | | public | data_conversion | 60432 | 4044 | 0 | | | | | | public | region | 20 | 5 | 0 | | | | | | public | resource_unavailability | 35688 | 477 | 1116 | | | | | | public | dataset_score | 19609 | 1317 | 0 | | 2023-07-13 16:02:02.507264+00 | | 2023-07-15 16:02:11.809055+00 | | public | commune | 34988 | 5049 | 0 | | | | | | public | logs_import | 15021 | 229 | 2825 | | 2023-07-12 03:00:50.147039+00 | | 2023-07-17 04:06:24.974069+00 | | public | resource | 1239 | 106 | 227 | | 2023-07-16 04:08:06.070836+00 | | 2023-07-17 04:15:25.21424+00 | | public | resource_history | 61262 | 7593 | 7224 | | 2023-07-03 18:17:20.499303+00 | | 2023-07-15 12:13:25.26427+00 | | public | gtfs_calendar | 20 | 1 | 0 | | | | | | public | gtfs_stop_times | 0 | 0 | 0 | | | | | | public | gtfs_stops | 621631 | 12089 | 15238 | | 2023-07-17 04:02:25.879701+00 | | 2023-07-17 04:02:26.357148+00 | | public | gtfs_trips | 0 | 0 | 0 | | | | | | public | dataset_communes | 582 | 4 | 1 | | | | | | public | schema_migrations | 154 | 1 | 0 | | | | | | public | data_import_batch | 59 | 1 | 0 | | 2023-07-13 09:45:57.873687+00 | | | | public | dataset_history | 101629 | 2886 | 2721 | | | | 2023-07-11 21:03:00.225019+00 | | public | dataset_history_resources | 247308 | 12693 | 14470 | | | | 2023-07-09 21:01:51.738034+00 | | public | notification_subscription | 975 | 32 | 7 | | 2023-06-27 16:03:11.766721+00 | | 2023-06-27 16:03:11.772098+00 | | public | contact | 324 | 16 | 85 | | 2023-07-14 03:41:26.233356+00 | | 2023-07-17 03:41:24.331983+00 | | public | notifications | 1734 | 402 | 168 | | | | 2023-07-11 06:00:46.748165+00 | | public | contacts_organizations | 115 | 1 | 12 | | | | 2023-07-05 11:11:11.771863+00 | | public | organization | 229 | 25 | 5 | | | | 2023-07-12 04:16:50.658039+00 |

thbar commented 1 year ago

@etalab/transport-tech en résumé:

l'auto-vacuum est configuré
il semble bien fonctionner
je n'ai pas l'impression à ce stade qu'il soit nécessaire de faire une opération manuelle

Je clôture.

thbar commented 1 year ago

J'ai l'impression qu'on rentre dans le cas mentionné par la documentation de référence:

https://www.postgresql.org/docs/current/routine-vacuuming.html

PostgreSQL databases require periodic maintenance known as vacuuming. For many installations, it is sufficient to let vacuuming be performed by the autovacuum daemon, which is described in Section 25.1.6.

For those not using autovacuum, a typical approach is to schedule a database-wide VACUUM once a day during a low-usage period, supplemented by more frequent vacuuming of heavily-updated tables as necessary.

Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity.

Mais à suivre :smile:

etalab / transport-site

Étudier le besoin de VACUUM sur la base de production #3318