Postgres Pro
Downloads
Home   >   mailing lists

Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ? - Mailing list pgsql-general

From Eduard Rozenberg
Subject Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?
Date
Msg-id 3E4B3E2A-CBBE-472A-8FDE-C1A9538F2DF0@gmail.com
Whole thread Raw
In response to Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?  (Adrian Klaver <adrian.klaver@aklaver.com>)
List pgsql-general
Tree view
@Adrian thanks.

I did a test vacuum full the "normal" non-parallel way (VACUUM FULL sql) previously on a test db copy and saw the DB size (postgres 9.6) shrink from 6.8 TB to 4.4 TB of actual, real disk space usage ($ df -d 1).

I don't know the reason so much space was "locked up" (other than there is a lot of data "churn" from daily updates). But I definitely do need to do the vac full on the production db to get down to the smaller size - cannot afford the 2.4 TB of "wasted" space on an ongoing basis.

Based on your email it sounds like the vacuuming of those pg_catalog.* tables is done regardless, as a normal part of doing vac full on my own database.

Unfortunately I still don't see an ideal way to run vacuum full in parallel via vacuumdb without running into the expected and documented deadlock. Only method I'm aware of is to list each table individually with "-t table1 -t table2..." to "vacuum db --jobs" which is not pleasant and not exceedingly beautiful.

Thanks.

On May 14, 2020, at 10:35, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

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.klaver@aklaver.com

pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: vacuumdb --jobs deadlock: how to avoid pg_catalog ?
Next
From: Thomas Markus
Date:
Subject: Re: Practical usage of large objects.