vacuumdb parallel has a deadlock detected in 9.5.4

On Wed, Sep 28, 2016 at 09:46:22PM +0800, huang wrote:

Hi friends,

There is a error deadlock detected in vacuumdb parallel .
but if I do not use parallel , it has not deadlock detected .

man vacuumdb says about -j option:

Note that using this mode together with the -f (FULL) option might cause
deadlock failures if certain system catalogs are processed in parallel.

so, while I understand it's not pretty, it's well documented.

Best regards,

depesz

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

hubert depesz lubaczewski wrote:

On Wed, Sep 28, 2016 at 09:46:22PM +0800, huang wrote:

Hi friends,

There is a error deadlock detected in vacuumdb parallel .
but if I do not use parallel , it has not deadlock detected .

man vacuumdb says about -j option:

Note that using this mode together with the -f (FULL) option might cause
deadlock failures if certain system catalogs are processed in parallel.

so, while I understand it's not pretty, it's well documented.

Yeah. However I think this behavior is rather unhelpful. Maybe we
should try to fix it somehow, but I'm not sure how. We could say that
pg_catalog tables can only be processed one at a time, instead of trying
to paralelize them? For example: have vacuumdb fill two lists of
tables, one for pg_catalog and one for tables in other schemas. Each
worker chooses a table from the pg_catalog list first if it's not empty
and there's no other worker doing one of these, otherwise one from the
other table.

I think that'd fix it, while not destroying paralelizability too badly.

I'm not volunteering, though. Patches welcome.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi:

On Wed, Sep 28, 2016 at 10:39 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

There is a error deadlock detected in vacuumdb parallel .
but if I do not use parallel , it has not deadlock detected .

man vacuumdb says about -j option:

Note that using this mode together with the -f (FULL) option might cause
deadlock failures if certain system catalogs are processed in parallel.

so, while I understand it's not pretty, it's well documented.

Yeah. However I think this behavior is rather unhelpful. Maybe we
should try to fix it somehow, but I'm not sure how. We could say that
pg_catalog tables can only be processed one at a time, instead of trying
to paralelize them? For example: have vacuumdb fill two lists of
tables, one for pg_catalog and one for tables in other schemas. Each
worker chooses a table from the pg_catalog list first if it's not empty
and there's no other worker doing one of these, otherwise one from the
other table.

I think that'd fix it, while not destroying paralelizability too badly.

I would propose another behaviour, which I think can solve the problem
without introducing more complex code. Put a couple of flags to vacuum
only catalog tables / non catalog tables ( I believe this can be
served by include/exclude schemas too, which will be even more useful
for other things ). This way one can do a full paralell vacuum of
non-catalog objects followed by a serial one on catalog objects (
which should not be too slow on 'normal' databases ) ( I propose this
order because IIRC normal vacuum updates catalog tables so they get
vacuumed after to tidy them )

Francisco Olarte

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

At 2016-09-29 15:57:58, "Francisco Olarte" <folarte@peoplecall.com> wrote:

Hi:

On Wed, Sep 28, 2016 at 10:39 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

There is a error deadlock detected in vacuumdb parallel .
but if I do not use parallel , it has not deadlock detected .

man vacuumdb says about -j option:

Note that using this mode together with the -f (FULL) option might cause
deadlock failures if certain system catalogs are processed in parallel.

so, while I understand it's not pretty, it's well documented.

Yeah. However I think this behavior is rather unhelpful. Maybe we
should try to fix it somehow, but I'm not sure how. We could say that
pg_catalog tables can only be processed one at a time, instead of trying
to paralelize them? For example: have vacuumdb fill two lists of
tables, one for pg_catalog and one for tables in other schemas. Each
worker chooses a table from the pg_catalog list first if it's not empty
and there's no other worker doing one of these, otherwise one from the
other table.

I think that'd fix it, while not destroying paralelizability too badly.

I would propose another behaviour, which I think can solve the problem
without introducing more complex code. Put a couple of flags to vacuum
only catalog tables / non catalog tables ( I believe this can be
served by include/exclude schemas too, which will be even more useful
for other things ). This way one can do a full paralell vacuum of
non-catalog objects followed by a serial one on catalog objects (
which should not be too slow on 'normal' databases ) ( I propose this
order because IIRC normal vacuum updates catalog tables so they get
vacuumed after to tidy them )

Francisco Olarte

hi ,
I just hope the vacuumdb parallel can quickly finishing work .
our database has a lot of tables .
they need vacuumdb . but the time is too long .
so , I used the vacuumdb parallel .

thanks to everyone . : )

Best regards,
TT
28-09-2016

Francisco Olarte wrote:

I would propose another behaviour, which I think can solve the problem
without introducing more complex code. Put a couple of flags to vacuum
only catalog tables / non catalog tables ( I believe this can be
served by include/exclude schemas too, which will be even more useful
for other things ). This way one can do a full paralell vacuum of
non-catalog objects followed by a serial one on catalog objects (
which should not be too slow on 'normal' databases )

OK, that sounds easier to implement. Are you going to submit a patch?

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hello all:

On Thu, Sep 29, 2016 at 7:10 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Francisco Olarte wrote:

I would propose another behaviour, which I think can solve the problem
without introducing more complex code. Put a couple of flags to vacuum
only catalog tables / non catalog tables ( I believe this can be
served by include/exclude schemas too, which will be even more useful
for other things ). This way one can do a full paralell vacuum of
non-catalog objects followed by a serial one on catalog objects (
which should not be too slow on 'normal' databases )

OK, that sounds easier to implement. Are you going to submit a patch?

After looking at vacuumdb source I'm trying to develop and submit a
patch implementing the following:

Two new flags --include-schema=NAME, --exclude-schema=NAME

Two new flags --system-schemas --non-system-schemas, as alias to
--include-schema=pg_catalog and --exclude-schema=pg_catalog

The intended behaviour will be:

- if a table list (--table) is specified, include / exclude schema
will be ignored ( potentially with a warning or may be an error if
more knowledgeable people steer me that way ).

- if both include and exclude are specified, include will take
preference (with warning or error ? ).

- if not the already existing code used to enumerate tables for
paralell vacuum will be augmented to either use only tables in the
include schemas or not use tables in the exclude schemas. If, after
enumeration, the table list becomes empty an error will be produced (
think on --include-schema=oops-I-mistyped-it ). The filtering will be
done client side and will be case sensitive against the schema name
name returned by the queries to the catalogs ( no case folding, to
make it simple ).

- After that vacuumdb will proceed as it would if the table list where
given in the command line, either in serial or paralell mode.

This does not intend to solve the paralell vacuum deadlocks, but it
can let people avoid them by just doing

vacuumdb -f --exclude-schema=pg_catalog -j 20
vacuumdb -f --include-schema=pg_catalog

And vaccuuming catalogs is not going to benefit that much from
paralellism as it is normally fast.

Any comment ?

Francisco Olarte.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On 01-10-2016 12:57, Francisco Olarte wrote:

On Thu, Sep 29, 2016 at 7:10 PM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:

Francisco Olarte wrote:

I would propose another behaviour, which I think can solve the problem
without introducing more complex code. Put a couple of flags to vacuum
only catalog tables / non catalog tables ( I believe this can be
served by include/exclude schemas too, which will be even more useful
for other things ). This way one can do a full paralell vacuum of
non-catalog objects followed by a serial one on catalog objects (
which should not be too slow on 'normal' databases )

OK, that sounds easier to implement. Are you going to submit a patch?

After looking at vacuumdb source I'm trying to develop and submit a
patch implementing the following:

Two new flags --include-schema=NAME, --exclude-schema=NAME

Two new flags --system-schemas --non-system-schemas, as alias to
--include-schema=pg_catalog and --exclude-schema=pg_catalog

This is another feature. Also, it will *sometimes* solve the problem.
Why don't you implement your parallel + serialization idea or Alvaro's
two lists idea? I'm not against adding schema options, I just think it's
not the right way to solve the deadlock problem.

--
Euler Taveira Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Hi Euler:

On Sun, Oct 2, 2016 at 5:33 AM, Euler Taveira <euler@timbira.com.br> wrote:

This is another feature.

Yes, which allows an easier workaround around the problem. Personally
I do not need it because I do not vacuumm paralell and I can easily
write a simple dozen lines in perl which achieves it, but I feel it
can help people like the OP.

Also, it will *sometimes* solve the problem.

No, it does not solve the problem ( paralell vacuum of system catalogs
can still deadlock ). What it does is let you transform a deadlockish
operation ( full paralell database wide vacuum ) into a non
deadlockish one ( full paralell non-system plus linear system )
without too much effort.

Why don't you implement your parallel + serialization idea or Alvaro's
two lists idea?

Well, I want a servant, not a nanny. If I command vacuum db to do full
paralell system catalog vacuum, I want it to do it ( although I WILL
tolerate a warning and MAY tolerate a confirmation request ).

Besides, I proposed the schema solution because I thought that
paralell vacuum would need some code building a table list, and the -t
option will need code to process tables from a list. Code examination
revealed I was correct in both. Adding the options is a simple matter
of adding some simple code ( a couple options, a couple string lists
to hold their data, a couple ifs in the table scanning loop used when
no table list given, a modification to make it happen when schema
optins are given ( presently it plays only when no -t options and
paralell is requested, I need to activate it when schema options are
present too, either in serial or paralell mode ), and the working code
remains untouched). I could refactor a bit to make several runs
easier, have a built in list of system data, and many other things,
but this is a deeper modification which I do not feel presently
prepared to attack. I may do it in the future, but I feel anyone
needing to run full paralell vacuums has a big system and should have
this kind of ops scripted, so it is not going to be that useful.

Bear in mind my work is not specifically in postgres like yours, Its
just a tool. I've been using it since before it had SQL, and have some
experience with C and libpq, but I do not feel confortable adding
complex code even to a simple client program.

I'm not against adding schema options, I just think it's
not the right way to solve the deadlock problem.

As I said, it does not solve the problem, just builds an easier path
than the present ones around it ( you can do exactly the same by
querying the catalog on a script and emitting two vacuumdb commands
with a table list ).

Francisco Olarte.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs