Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?

On 5/14/20 8:35 AM, Eduard Rozenberg wrote:
> Hello there,
>
> I'm a long-time postgres user but vacuumdb newbie :).
>
> Using vacuumdb (v12.2) with '---jobs' to vacuum a v9.6 database on
> localhost with parallel processes:
>
> $ time vacuumdb --full --echo -U postgres --jobs=8 -d mydatabase_test -p
> 5434 --password
>
> As shown below I ran into the (100%?) predictable/expected deadlock with
> sys catalog tables documented in the vacuumdb doc page
> (https://www.postgresql.org/docs/12/app-vacuumdb.html).
>
> Question: I specified "-d mydatabase_test" - why is it also doing a full
> vac on the system catalog (postgres db) which is practically guaranteed

The postgres database and the system catalogs are two separate things.

> to cause a deadlock? I don't need or want it to do a full vac on the
> postgres db as I can do that myself later if I need to, and it takes no
> time compared to full vac of my own 7 TB database that I am "extremely
> eager" to parallelize.

See:

https://www.postgresql.org/docs/12/sql-vacuum.html

"FULL

Selects “full” vacuum, which can reclaim more space, but takes much
longer and exclusively locks the table. This method also requires extra
disk space, since it writes a new copy of the table and doesn't release
the old copy until the operation is complete. Usually this should only
be used when a significant amount of space needs to be reclaimed from
within the table.
"

Pretty sure all that rewriting of tables bloats the system catalogs and
so they get a VACUUM FULL.

The question here is why is it necessary to do a VACUUM FULL in the
first place?

>
> Is my only option list all schemas.tables and feed to vacuumdb as "-t
> schema1.table1 -t schema1.table2 ........" ? That's manageable but
> unfortunate as it creates more work (having to list all tables) and adds
> additional work for automation scripts as well.
>
> I've tried searching forums/goog for past issues related to this. I did
> find an old discussion about possible enhancements to "vacuumdb --jobs"
> to help the user avoid these predictable deadlocks, where the conclusion
> was to let the user figure out a way to deal with it such as via "-t
> table -t table -t table .......".
>
> Maybe "--jobs" should only apply to the user's own databases, and when
> vacuumdb is working on system catalog tables it should apply a strictly
> serial/sequential approach to handle those tables?
>
> Thanks!
>
>
> ------------------------------------------
> output from vacuumdb's --echo
> ------------------------------------------
> ...
> VACUUM (FULL) mycompanytesting.sometable1;
> VACUUM (FULL) index.mydatabasestd_missing;
> VACUUM (FULL) mycompanytesting.sometable2;
> VACUUM (FULL) mycompanytesting.sometable3;
> VACUUM (FULL) pg_catalog.pg_attribute;
> VACUUM (FULL) mycompanydata.sometable4;
> VACUUM (FULL) pg_catalog.pg_statistic;
> VACUUM (FULL) mycompanytesting.sometable5;
> VACUUM (FULL) pg_catalog.pg_proc;
> VACUUM (FULL) pg_catalog.pg_depend;
> VACUUM (FULL) pg_catalog.pg_class;
> VACUUM (FULL) pg_catalog.pg_type;
> vacuumdb: error: vacuuming of database "mydatabase_test" failed: ERROR:
>  deadlock detected
> DETAIL:  Process 28183 waits for AccessShareLock on relation 1259 of
> database 35239378; blocked by process 28182.
> Process 28182 waits for AccessShareLock on relation 1247 of database
> 35239378; blocked by process 28183.
> HINT:  See server log for query details.
>
>
> ------------------------------
> /var/log/postgresql-9.6
> ------------------------------
> ...
> LOG:  received SIGHUP, reloading configuration files
> ERROR:  canceling autovacuum task
> CONTEXT:  automatic vacuum of table "mydatabase_test.someschema.sometable"
> ERROR:  canceling autovacuum task
> CONTEXT:  automatic vacuum of table "mydatabase_test.someschema.sometable"
> ERROR:  canceling autovacuum task
> CONTEXT:  automatic vacuum of table
> "mydatabase_test.pg_toast.pg_toast_35240266"
> LOG:  could not receive data from client: Connection timed out
> LOG:  received SIGHUP, reloading configuration files
> LOG:  received SIGHUP, reloading configuration files
> ERROR:  canceling autovacuum task
> CONTEXT:  automatic vacuum of table "mydatabase_test.someschema.sometable"
> LOG:  received SIGHUP, reloading configuration files
> LOG:  received SIGHUP, reloading configuration files
> ERROR:  canceling autovacuum task
> CONTEXT:  automatic vacuum of table
> "mydatabase_test.pg_toast.pg_toast_35240330"
> ERROR:  canceling autovacuum task
> CONTEXT:  automatic vacuum of table "mydatabase_test.someschema.sometable"
> ERROR:  deadlock detected
> DETAIL:  Process 28183 waits for AccessShareLock on relation 1259 of
> database 35239378; blocked by process 28182.
> Process 28182 waits for AccessShareLock on relation 1247 of database
> 35239378; blocked by process 28183.
> Process 28183: VACUUM (FULL) pg_catalog.pg_type;
> Process 28182: VACUUM (FULL) pg_catalog.pg_class;
> HINT:  See server log for query details.
> STATEMENT:  VACUUM (FULL) pg_catalog.pg_type;
> ERROR:  canceling statement due to user request
> STATEMENT:  VACUUM (FULL) pg_catalog.pg_class;
> ERROR:  canceling statement due to user request
> STATEMENT:  VACUUM (FULL) pg_catalog.pg_depend;
> ERROR:  canceling statement due to user request
> STATEMENT:  VACUUM (FULL) someschema.sometable
> LOG:  could not send data to client: Broken pipe
> FATAL:  connection to client lost
> LOG:  could not send data to client: Broken pipe
> FATAL:  connection to client lost
> ERROR:  canceling statement due to user request
> STATEMENT:  VACUUM (FULL) mycompanydata.wildcard_replacement_bkp;
> LOG:  could not send data to client: Broken pipe
> STATEMENT:  VACUUM (FULL) mycompanydata.wildcard_replacement_bkp;
> FATAL:  connection to client lost
> ERROR:  canceling statement due to user request
> STATEMENT:  VACUUM (FULL) pg_catalog.pg_proc;
> LOG:  could not send data to client: Broken pipe
> STATEMENT:  VACUUM (FULL) pg_catalog.pg_proc;
> FATAL:  connection to client lost
> ERROR:  canceling statement due to user request
> STATEMENT:  VACUUM (FULL) mycompanydata.cpc_tag_score2;
> LOG:  could not send data to client: Broken pipe
> STATEMENT:  VACUUM (FULL) mycompanydata.cpc_tag_score2;
> LOG:  could not send data to client: Broken pipe
> FATAL:  connection to client lost
> FATAL:  connection to client lost
> LOG:  received SIGHUP, reloading configuration files
> LOG:  received SIGHUP, reloading configuration files
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com