vacuumdb --jobs deadlock: how to avoid pg_catalog ?

From: Eduard Rozenberg <edrozenberg(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: vacuumdb --jobs deadlock: how to avoid pg_catalog ?
Date: 2020-05-14 15:35:56
Message-ID: 4C3B3D43-F3BE-4BE7-A812-E3FB4E43236A@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 <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 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.

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2020-05-14 17:35:15 Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?
Previous Message Jeff Janes 2020-05-14 15:26:11 Re: what is the best way to access cold data on another server?