2

I come from a SQL Server, Oracle, Sybase DBA background but I am now looking into an AWS Aurora cluster running PostgreSQL 9.6.12 and have noticed something which I think is odd, but maybe it's not, which is why I am here to ask the question. I have looked everywhere but can not find an answer. The default autovacuum and autoanalyze values are still set. Autovacuum does seem to get around to doing what it needs to do on application tables, eventually, but what I have noticed is that it seems to spend most of its time frequently vacuuming and analysing a small set of system tables. They are:

  1. pg_type
  2. pg_shdepend
  3. pg_attribute
  4. pg_class
  5. pg_depend

I am seeing this both through AWS Performance Insights data and also through direct queries to the database instance using this code:

    WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_anal_threshold,        
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_analyze_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_anal_scale_factor,        
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_vac_scale_factor
    FROM pg_class
) 
SELECT
    PSUT.relname,
--    to_char(PSUT.last_analyze, 'YYYY-MM-DD HH24:MI')     AS last_analyze,
    to_char(PSUT.last_autoanalyze, 'YYYY-MM-DD HH24:MI') AS last_autoanalyze,    
--    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
    to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
    to_char(C.reltuples, '9G999G999G999')               AS n_tup,
    to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
    to_char(coalesce(RS.rel_av_anal_threshold, current_setting('autovacuum_analyze_threshold')::BIGINT) + coalesce(RS.rel_av_anal_scale_factor, current_setting('autovacuum_analyze_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_analyze_threshold,
    to_char(coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_vacuum_threshold,
    CASE
        WHEN (coalesce(RS.rel_av_anal_threshold, current_setting('autovacuum_analyze_threshold')::BIGINT) + coalesce(RS.rel_av_anal_scale_factor, current_setting('autovacuum_analyze_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    end
    AS expect_av_analyze,    
    CASE
        WHEN (coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    end
    AS expect_av_vacuum,
    PSUT.autoanalyze_count,
    PSUT.autovacuum_count
FROM
    pg_stat_all_tables PSUT
    JOIN pg_class C
        ON PSUT.relid = C.oid
    JOIN rel_set RS
        ON PSUT.relid = RS.oid
ORDER BY PSUT.autoanalyze_count DESC; --C.reltuples

AWS RDS Performance Insights graph showing that half the CPU activity is consumed by autovacuum processes: RDS Performance Insights screen grab

At first I thought that it may be due to a lot of temporary tables being created and then destroyed or something similar as I would periodically see the numbers of tuples go from, for example, roughly 8,000 to 8,000,000 and then back again in several of the previously mentioned tables. But I haven't been able to find any evidence of temp table creation and the offshore developers say they don't use them.

Is this sort of behaviour normal in normal PostgreSQL or Aurora (PostgreSQL)? Is there anything anyone could suggest to look at to ascertain what may be happening here if this is not normal? This database is about a terabyte in size on an instance with 122GB of RAM (75% allocated to shared_buffers - the default for Aurora.)

I am looking to change the autovaccum settings from the defaults to handle this databases much larger tables but just wanted to make sure that wouldn't be a waste of time if the tables in question just monopolise autovacuum/autoanalyse's time.

Current settings (from pg_settings):

autovacuum  on
autovacuum_analyze_scale_factor 0.05
autovacuum_analyze_threshold    50
autovacuum_freeze_max_age   200000000
autovacuum_max_workers  3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime  5
autovacuum_vacuum_cost_delay    5
autovacuum_vacuum_cost_limit    -1
autovacuum_vacuum_scale_factor  0.1
autovacuum_vacuum_threshold 50

Here is the relevant output of the query. Note the columns for autoanalyze_count and autovacuum_count. This instance has only been running for 6 days so those numbers look incredibly high. All the other tables only show 0-10 for those columns (I didn't put the rest of the tables in for efficiency).

relname                                             |last_autoanalyze|last_autovacuum |n_tup         |dead_tup      |av_analyze_threshold|av_vacuum_threshold|expect_av_analyze|expect_av_vacuum|autoanalyze_count|autovacuum_count|
----------------------------------------------------|----------------|----------------|--------------|--------------|--------------------|-------------------|-----------------|----------------|-----------------|----------------|
pg_type                                             |2020-03-06 18:20|2020-03-06 18:20|         1,352|           192|           118      |           185     |*                |*               |            22781|           34428|
pg_shdepend                                         |2020-03-06 18:20|2020-03-06 18:20|       694,312|           164|        34,766      |        69,481     |                 |                |            20945|           73784|
pg_class                                            |2020-03-06 18:20|2020-03-06 18:20|         1,172|           264|           109      |           167     |*                |*               |            13758|           21198|
pg_attribute                                        |2020-03-06 18:20|2020-03-06 18:20|         9,205|         1,976|           510      |           970     |*                |*               |            12692|           17710|
pg_depend                                           |2020-03-06 18:20|2020-03-06 18:20|        10,981|         1,143|           599      |         1,148     |*                |                |            11255|           16883|

In summary, what I am asking is: Is it normal for a small set of system tables to be constantly and consistently autovacuumed? Any insights would be greatly appreciated.

2 Answers 2

1

The catalog tables that you mention as being vacuumed all the time indicate that indeed tables (or, less likely, composite data types) are created and destroyed all the time:

  • pg_class is the table of tables (and other relations)
  • pg_attributes is the table of columns
  • pg_type contains types, and for every table a composite type with the same name is created
  • pg_depend contains dependencies between the table and — for example — the associated type.
  • pg_shdepend contains the dependency between a table and the owning role.

The table most at risk of bloat is pg_attribute.

Since you are using a hosted database, you probably neither have superuser access, nor can you use the pgstattuple extension that would allow you to determine exactly how bloated the table is.

But you can find out its size:

SELECT pg_total_relation_size('pg_attribute');

Also, you can find out how many dead (deleted) tuples there are:

SELECT n_live_tup, n_dead_tup
FROM pg_stat_sys_tables
WHERE relname = 'pg_attribute';

Any dead tuple you see there is evidence that columns are deleted. So if this number keeps increasing, you have a proof that you developers are mistaken (perhaps it is not a temporary table, but a regular table).

To keep the problem at bay, make autovacuum as aggressive as you can:

autovacuum_vacuum_cost_delay = 0
5
  • That's the best, succinct explanation of those tables that I've seen. Thanks heaps @Laurenz Albe! pg_attributes is 1458 MB currently which seems really bloated, even though its frequently being vacuumed. Is it possible to run vacuum full on system tables? relname |n_live_tup|n_dead_tup| ------------|----------|----------| pg_type | 1170| 192| pg_attribute| 9205| 1976| pg_class | 1172| 264|
    – dodgybugga
    Mar 6, 2020 at 9:41
  • Interestingly, the n_tup column in my query returned a value of 6,016,721 right after I ran your second query for pg_attribute. 2 secs later it was back down to 6,387. There's definitely a lot of something being created and destroyed but I can't work out what. Any ideas on where to look to find out? It doesn't appear to be tables as the number of tables in pg_stat_all_tables it doesn't seem to grow at all.
    – dodgybugga
    Mar 6, 2020 at 9:49
  • You won't see temporary tables (from other sessions) in pg_stat_all_tables. Yes, you can run VACUUM (FULL) on a system catalog as superuser. I would set log_statement = 'ddl', then you get all CREATE and DROP statements in the log file. Mar 6, 2020 at 10:00
  • I've set log_statement = 'ddl' yesterday and reviewed the logs today. No presence of any ddl commands unfortunately. I even created and removed a table myself to make sure it was working and the ddl for those statements did show up in the logs. Is it possible background/system stuff (possibly even for auto vacuum itself) is being created and destroyed but it simply doesn't show up in any logging?
    – dodgybugga
    Mar 10, 2020 at 3:31
  • Dead tuples only occur when rows are deleted or updated. For these catalog tables, this only happens when objects are altered or dropped. Autovacuum will modify pg_class, but not the other catalogs. So I have no explanation why you don't see anything in the logs. Are the catalog tables still accumulating dead tuples? My last straw is that the logging parameters are overridden somewhere. Mar 10, 2020 at 6:45
1

Can you post the output of your SQL query and precise the frequency of autovacuum ? What is the number of rows of these 5 system catalog tables ? Do you have a lot of DDL statements running frequently ?

Autovacuum runs if number of tuples obsoleted since the last VACUUM exceeds the vacuum threshold defined as:

vacuum threshold =  autovacuum_vacuum_threshold +  autovacuum_vacuum_scale_factor *pg_class.reltuples;

In your case: 50 + 0.1 * pg_class.reltuples

2
  • Apologies for the delay getting back, I was off yesterday. I've edited the question with the results of the query for those tables. The n_tuples column holds the number of rows. Unfortunately I'm not all that familiar with this app so not sure about the rates of DDL.I'll ask the devs again if they are creating and removing much stuff frequently. When I check the number of tables in pg_stat_all_tables it doesn't seem to grow at all.
    – dodgybugga
    Mar 6, 2020 at 7:39
  • I've turned on log_statement = 'ddl' as suggested by @laurenz-albe to see if there is much ddl going on but it doesn't appear to be the case or at least its not reporting it at all.
    – dodgybugga
    Mar 10, 2020 at 4:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.