pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum ignore tables
17+ messages / 3 participants
[nested] [flat]
* autovacuum ignore tables
@ 2006-09-18 18:33 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 2 replies; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-18 18:33 UTC (permalink / raw)
To: pgsql-admin
Hi
Is it possible to tell autovacuum to ignore certain tables (These tables
are high traffic daily partition tables that get dropped every other
day).
I am trying to get autovacuum to complete quicker and prevent Xactn
wraparound (currently, it takes very long and due to the fact that I
have to restart the database for maintenance purposes), autovacuum never
finishes.)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-18 20:32 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-09-18 20:32 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> I am trying to get autovacuum to complete quicker and prevent Xactn
> wraparound (currently, it takes very long and due to the fact that I
> have to restart the database for maintenance purposes), autovacuum never
> finishes.)
Why are you restarting the database? There is no "maintenance purpose"
I'm aware of that requires routine postmaster restarts.
As for suppressing autovac on specific tables, see the pg_autovacuum
system catalog.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-19 00:15 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-19 00:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
I have a high traffic table which is populated by a trigger on another
table. This high traffic table is partitioned by day. When I issue a
drop table on a child table..it goes into a wait state AND puts all jdbc
inserts in a wait state.(in short..a deadlock). I can repeat this use
case any time.
The only way to guarantee all drops to work is to briefly shutdown ,
modify pg_hba.conf to disallow client access, restart and issue the drop
commands (followed by restoration of original pg_hba.conf)
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 18, 2006 1:32 PM
To: Sriram Dandapani
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> I am trying to get autovacuum to complete quicker and prevent Xactn
> wraparound (currently, it takes very long and due to the fact that I
> have to restart the database for maintenance purposes), autovacuum
never
> finishes.)
Why are you restarting the database? There is no "maintenance purpose"
I'm aware of that requires routine postmaster restarts.
As for suppressing autovac on specific tables, see the pg_autovacuum
system catalog.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-19 04:10 Matthew T. O'Connor <matthew@zeut.net>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-09-19 04:10 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
Sriram Dandapani wrote:
>
> Hi
>
>
>
> Is it possible to tell autovacuum to ignore certain tables (These
> tables are high traffic daily partition tables that get dropped every
> other day).
>
>
>
> I am trying to get autovacuum to complete quicker and prevent Xactn
> wraparound (currently, it takes very long and due to the fact that I
> have to restart the database for maintenance purposes), autovacuum
> never finishes.)
>
If you are running PostgreSQL 8.1 with integrated autovacuum, then yes,
you can disable autovacuum on a per table basis, if you are using an
older version with the contrib autovacuum, then no.
Matt
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-26 23:58 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-26 23:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
If I tell autovacuum to ignore the high traffic daily partition tables
that get dropped regularly, will it reset the transaction id wraparound
counter when it is done?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, September 18, 2006 1:32 PM
To: Sriram Dandapani
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> I am trying to get autovacuum to complete quicker and prevent Xactn
> wraparound (currently, it takes very long and due to the fact that I
> have to restart the database for maintenance purposes), autovacuum
never
> finishes.)
Why are you restarting the database? There is no "maintenance purpose"
I'm aware of that requires routine postmaster restarts.
As for suppressing autovac on specific tables, see the pg_autovacuum
system catalog.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* autovacuum ignore tables
@ 2006-09-28 22:07 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-28 22:07 UTC (permalink / raw)
To: pgsql-admin
If I were to specify in the pg_autovacuum catalog that certain high
volume partitioned tables(that get dropped daily) be ignored, then when
autovacuum finishes, will it update the transaction id wraparound
counter (this way, I can get autovacuum to finish quickly )
OR
Will I still need to periodically do vacuumdb -a to take care of the
wraparound problem.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 04:22 Matthew T. O'Connor <matthew@zeut.net>
parent: Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-09-29 04:22 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
Sriram Dandapani wrote:
>
> If I were to specify in the pg_autovacuum catalog that certain high
> volume partitioned tables(that get dropped daily) be ignored, then
> when autovacuum finishes, will it update the transaction id wraparound
> counter (this way, I can get autovacuum to finish quickly )
>
> OR
>
> Will I still need to periodically do vacuumdb ¡Va to take care of the
> wraparound problem.
>
You don't need to do a manual vacuumdb -a since autovacuum will do this
once it decides you are getting too close to the wraparound point. I
believe this has been improved in the upcoming 8.2 release where
autovacuum no longer need to vacuum the whole database at once, rather
XID wraparound is now tracked on a per table basis.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 06:40 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 2 replies; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-29 06:40 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@zeut.net>; +Cc: pgsql-admin
The only issue I have with autovacuum is the fact that I have to briefly
stop/restart postgres every couple of days, which kills autovacuum and
it has no memory of previous work done. I work with several databases
with partitioned tables having high daily volume. Dropping partitioned
tables locks out jdbc inserts and the drop command itself goes into a
WAIT state. Hence, I have to stop postgres,update pg_hba.conf to prevent
access,restart postgres,drop tables and update pg_hba to allow
access(Crazy, but I have no choice because Postgres deadlocks on drop
child tables while inserts happen on the parent)
-----Original Message-----
From: Matthew T. O'Connor [mailto:matthew@zeut.net]
Sent: Thursday, September 28, 2006 9:22 PM
To: Sriram Dandapani
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
Sriram Dandapani wrote:
>
> If I were to specify in the pg_autovacuum catalog that certain high
> volume partitioned tables(that get dropped daily) be ignored, then
> when autovacuum finishes, will it update the transaction id wraparound
> counter (this way, I can get autovacuum to finish quickly )
>
> OR
>
> Will I still need to periodically do vacuumdb -a to take care of the
> wraparound problem.
>
You don't need to do a manual vacuumdb -a since autovacuum will do this
once it decides you are getting too close to the wraparound point. I
believe this has been improved in the upcoming 8.2 release where
autovacuum no longer need to vacuum the whole database at once, rather
XID wraparound is now tracked on a per table basis.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 13:31 Matthew T. O'Connor <matthew@zeut.net>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 17+ messages in thread
From: Matthew T. O'Connor @ 2006-09-29 13:31 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: pgsql-admin
Sriram Dandapani wrote:
> The only issue I have with autovacuum is the fact that I have to briefly
> stop/restart postgres every couple of days, which kills autovacuum and
> it has no memory of previous work done. I work with several databases
> with partitioned tables having high daily volume. Dropping partitioned
> tables locks out jdbc inserts and the drop command itself goes into a
> WAIT state. Hence, I have to stop postgres,update pg_hba.conf to prevent
> access,restart postgres,drop tables and update pg_hba to allow
> access(Crazy, but I have no choice because Postgres deadlocks on drop
> child tables while inserts happen on the parent)
Sounds like a fairly serious problem, but I'll let someone else talk to
that. As for the autovac issue, as of 8.1 and autovac being integrated
into core, this isn't true. It does remember where it was, at least as
long as you don't have the GUC var reset_stats_on_restart (something
like that) set.
Matt
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 14:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
1 sibling, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-09-29 14:45 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> The only issue I have with autovacuum is the fact that I have to briefly
> stop/restart postgres every couple of days, which kills autovacuum and
> it has no memory of previous work done.
As already stated, this isn't true as of 8.1.
> I work with several databases
> with partitioned tables having high daily volume. Dropping partitioned
> tables locks out jdbc inserts and the drop command itself goes into a
> WAIT state. Hence, I have to stop postgres,update pg_hba.conf to prevent
> access,restart postgres,drop tables and update pg_hba to allow
> access(Crazy, but I have no choice because Postgres deadlocks on drop
> child tables while inserts happen on the parent)
It sounds to me like you have a problem with failing to commit
transactions promptly. The DROP will wait for existing transactions to
release their locks on the doomed table, but its lock request will block
any incoming transactions that try to acquire new locks on the table.
So basically you should see a hiccup of length equal to the longest
normal transaction using that table. In a well-designed concurrent
system that should not be a problem.
It might be worth doing the DROP as
begin;
lock table parent_table;
drop table child_table;
commit;
so that SELECTs on the parent do simply block and don't risk getting
errors from trying to access a just-dropped child table.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 17:59 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-29 17:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
I tried your code with the lock table followed by drop.
The lock table goes into a wait state AND all INSERTS (even new ones
coming in) go into a wait state.
Ps -ef | grep waiting shows the function call (that does the lock and
drop) in wait state as well as all INSERTS.
All jdbc inserts are committed cleanly. When the commit is done..the
autocommit for the connection is set to true (to flush out any open
transactions)
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, September 29, 2006 7:45 AM
To: Sriram Dandapani
Cc: Matthew T. O'Connor; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> The only issue I have with autovacuum is the fact that I have to
briefly
> stop/restart postgres every couple of days, which kills autovacuum and
> it has no memory of previous work done.
As already stated, this isn't true as of 8.1.
> I work with several databases
> with partitioned tables having high daily volume. Dropping partitioned
> tables locks out jdbc inserts and the drop command itself goes into a
> WAIT state. Hence, I have to stop postgres,update pg_hba.conf to
prevent
> access,restart postgres,drop tables and update pg_hba to allow
> access(Crazy, but I have no choice because Postgres deadlocks on drop
> child tables while inserts happen on the parent)
It sounds to me like you have a problem with failing to commit
transactions promptly. The DROP will wait for existing transactions to
release their locks on the doomed table, but its lock request will block
any incoming transactions that try to acquire new locks on the table.
So basically you should see a hiccup of length equal to the longest
normal transaction using that table. In a well-designed concurrent
system that should not be a problem.
It might be worth doing the DROP as
begin;
lock table parent_table;
drop table child_table;
commit;
so that SELECTs on the parent do simply block and don't risk getting
errors from trying to access a just-dropped child table.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 18:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-09-29 18:10 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> I tried your code with the lock table followed by drop.
> The lock table goes into a wait state AND all INSERTS (even new ones
> coming in) go into a wait state.
Well, it's waiting for something. Try looking in pg_locks to see which
transaction is blocking it.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 18:21 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-29 18:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
I see RowExclusiveLocks for all the jdbc inserts
Real question is: Why do the INSERTS go into wait state as soon as the
lock table statement is issued on the parent?
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, September 29, 2006 11:10 AM
To: Sriram Dandapani
Cc: Matthew T. O'Connor; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> I tried your code with the lock table followed by drop.
> The lock table goes into a wait state AND all INSERTS (even new ones
> coming in) go into a wait state.
Well, it's waiting for something. Try looking in pg_locks to see which
transaction is blocking it.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 21:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-09-29 21:38 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> Real question is: Why do the INSERTS go into wait state as soon as the
> lock table statement is issued on the parent?
If you were just inserting directly into other child tables, a lock on
either the parent or the target child table shouldn't affect them.
I wonder if you are using conditional rules to redirect the inserts,
and the rules include a reference to the target table? If that's the
case, you really need to drop the relevant rule before you remove the
child table, anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 21:42 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 1 reply; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-29 21:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
The jdbc inserts go into the main parent table and check constraints
redirect them to child tables.
If I were to drop the rule, that would immediately affect the inserts
and they would go the the parent table. And I cannot lock the parent due
to the problem I mentioned earlier.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, September 29, 2006 2:38 PM
To: Sriram Dandapani
Cc: Matthew T. O'Connor; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> Real question is: Why do the INSERTS go into wait state as soon as the
> lock table statement is issued on the parent?
If you were just inserting directly into other child tables, a lock on
either the parent or the target child table shouldn't affect them.
I wonder if you are using conditional rules to redirect the inserts,
and the rules include a reference to the target table? If that's the
case, you really need to drop the relevant rule before you remove the
child table, anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 21:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Tom Lane @ 2006-09-29 21:48 UTC (permalink / raw)
To: Sriram Dandapani <sdandapani@counterpane.com>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> The jdbc inserts go into the main parent table and check constraints
> redirect them to child tables.
A check constraint can't redirect anything. Are you saying that you use
a trigger to try to insert the row into *each* child table, relying on
the constraints to make all except one insert fail? Ugh. You'd do a
lot better to duplicate the partitioning-rule knowledge in the trigger,
and do only one insert that should succeed --- no wasted cycles, and no
need for a subtransaction.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: autovacuum ignore tables
@ 2006-09-29 21:54 Sriram Dandapani <sdandapani@counterpane.com>
0 siblings, 0 replies; 17+ messages in thread
From: Sriram Dandapani @ 2006-09-29 21:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@zeut.net>; pgsql-admin
I meant to say...rules are used on the parent table to redirect to
child. Just like the way the postgres 8.1 documentation has it.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, September 29, 2006 2:48 PM
To: Sriram Dandapani
Cc: Matthew T. O'Connor; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] autovacuum ignore tables
"Sriram Dandapani" <sdandapani@counterpane.com> writes:
> The jdbc inserts go into the main parent table and check constraints
> redirect them to child tables.
A check constraint can't redirect anything. Are you saying that you use
a trigger to try to insert the row into *each* child table, relying on
the constraints to make all except one insert fail? Ugh. You'd do a
lot better to duplicate the partitioning-rule knowledge in the trigger,
and do only one insert that should succeed --- no wasted cycles, and no
need for a subtransaction.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2006-09-29 21:54 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-18 18:33 autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-18 20:32 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-19 04:10 ` Matthew T. O'Connor <matthew@zeut.net>
2006-09-19 00:15 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-26 23:58 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-28 22:07 autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 04:22 ` Matthew T. O'Connor <matthew@zeut.net>
2006-09-29 06:40 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 13:31 ` Matthew T. O'Connor <matthew@zeut.net>
2006-09-29 14:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 17:59 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 18:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 18:21 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 21:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 21:42 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
2006-09-29 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 21:54 Re: autovacuum ignore tables Sriram Dandapani <sdandapani@counterpane.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox