Search Postgresql Archives

vacuumdb --jobs deadlock: how to avoid pg_catalog ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux