pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedForced quiesce
5+ messages / 5 participants
[nested] [flat]
* Forced quiesce
@ 2010-10-28 12:39 Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
0 siblings, 4 replies; 5+ messages in thread
From: Little, Douglas @ 2010-10-28 12:39 UTC (permalink / raw)
To: pgsql-admin
Hi,
We're having to vacuum full the system tables everyday due to the number of temp table drop/creates.
We're finding that if anyone tries to execute a job during the maintenance period, it often hangs the db.
Is there a way that we can force the db to quiesce? Or lock out users.
Luckly, most users are coming in thru a network VIP, so all client IP's are the same.
Thanks in advance.
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
Douglas.Little@orbitz.com<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CB7672.03458C70] orbitz.com<http://www.orbitz.com/; | ebookers.com<http://www.ebookers.com/; | hotelclub.com<http://www.hotelclub.com/; | cheaptickets.com<http://www.cheaptickets.com/; | ratestogo.com<http://www.ratestogo.com/; | asiahotels.com<http://www.asiahotels.com/;
Attachments:
image001.jpg (image/jpeg, 2.3K) download
view image
* Re: Forced quiesce
@ 2010-10-28 12:45 Plugge, Joe R. <JRPlugge@west.com>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
3 siblings, 0 replies; 5+ messages in thread
From: Plugge, Joe R. @ 2010-10-28 12:45 UTC (permalink / raw)
To: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>; pgsql-admin
Could you roll in a temporary pg_hba.conf, issue the pg_ctl reload command to reread that and then identify those ips using ps, and kill -TERM them? After your maintenance, rotate back in your full pg_hba.conf and reload it.
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Little, Douglas
Sent: Thursday, October 28, 2010 7:39 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Forced quiesce
Hi,
We're having to vacuum full the system tables everyday due to the number of temp table drop/creates.
We're finding that if anyone tries to execute a job during the maintenance period, it often hangs the db.
Is there a way that we can force the db to quiesce? Or lock out users.
Luckly, most users are coming in thru a network VIP, so all client IP's are the same.
Thanks in advance.
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
Douglas.Little@orbitz.com<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CB7674.0890EAB0] orbitz.com<http://www.orbitz.com/; | ebookers.com<http://www.ebookers.com/; | hotelclub.com<http://www.hotelclub.com/; | cheaptickets.com<http://www.cheaptickets.com/; | ratestogo.com<http://www.ratestogo.com/; | asiahotels.com<http://www.asiahotels.com/;
Attachments:
image001.jpg (image/jpeg, 2.3K) download
view image
* Re: Forced quiesce
@ 2010-10-28 12:46 Kenneth Marshall <ktm@rice.edu>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
3 siblings, 0 replies; 5+ messages in thread
From: Kenneth Marshall @ 2010-10-28 12:46 UTC (permalink / raw)
To: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>; +Cc: pgsql-admin
On Thu, Oct 28, 2010 at 07:39:19AM -0500, Little, Douglas wrote:
> Hi,
> We're having to vacuum full the system tables everyday due to the number of temp table drop/creates.
> We're finding that if anyone tries to execute a job during the maintenance period, it often hangs the db.
>
> Is there a way that we can force the db to quiesce? Or lock out users.
> Luckly, most users are coming in thru a network VIP, so all client IP's are the same.
>
> Thanks in advance.
>
> Doug Little
>
Hi Doug,
You could use pgbouncer to connect to your database and then issue
a "PAUSE" command before the VACUUM FULL:
PAUSE;
PgBouncer tries to disconnect from all servers, first waiting for all
queries to complete. The command will not return before all queries
are finished. To be used at the time of database restart.
There is also a SUSPEND option, but the PAUSE looks like it will do
what you need.
Regards,
Ken
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Forced quiesce
@ 2010-10-28 13:06 Sergey Konoplev <gray.ru@gmail.com>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
3 siblings, 0 replies; 5+ messages in thread
From: Sergey Konoplev @ 2010-10-28 13:06 UTC (permalink / raw)
To: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>; +Cc: pgsql-admin
Hi,
David Fetter has an article about the issue like your. He describes the
situation with DROP DATABASE but it could easily be adapted for VACUUM. Here
is the link
http://people.planetpostgresql.org/dfetter/index.php?/archives/63-Kick-em-out,-and-keep-em-out!.html
On 28 October 2010 16:39, Little, Douglas <DOUGLAS.LITTLE@orbitz.com> wrote:
>
> Hi,
>
> We’re having to vacuum full the system tables everyday due to the number
of temp table drop/creates.
>
> We’re finding that if anyone tries to execute a job during the maintenance
period, it often hangs the db.
>
>
>
> Is there a way that we can force the db to quiesce? Or lock out users.
>
> Luckly, most users are coming in thru a network VIP, so all client IP’s
are the same.
>
>
>
> Thanks in advance.
>
>
>
> Doug Little
>
>
>
> Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz
Worldwide
>
> 500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax
312.894.5164 | Cell 847-997-5741
>
> Douglas.Little@orbitz.com
>
> orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com |
ratestogo.com | asiahotels.com
>
>
--
Sergey Konoplev
Blog: http://gray-hemp.blogspot.com /
Linkedin: http://ru.linkedin.com/in/grayhemp /
JID/GTalk: gray.ru@gmail.com / Skype: gray-hemp
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Forced quiesce
@ 2010-11-08 15:05 Alvaro Herrera <alvherre@commandprompt.com>
parent: Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
3 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2010-11-08 15:05 UTC (permalink / raw)
To: Little, Douglas <douglas.little@orbitz.com>; +Cc: pgsql-admin
Excerpts from Little, Douglas's message of jue oct 28 09:39:19 -0300 2010:
> Hi,
> We're having to vacuum full the system tables everyday due to the number of temp table drop/creates.
Seems to me that you're solving the wrong problem. I think you should
be doing very frequent lazy vacuum (not full) of certain system catalogs
to prevent bloat. That way you don't need the maintenance to be done
with exclusive locks.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-11-08 15:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-28 12:39 Forced quiesce Little, Douglas <DOUGLAS.LITTLE@orbitz.com>
2010-10-28 12:45 ` Plugge, Joe R. <JRPlugge@west.com>
2010-10-28 12:46 ` Kenneth Marshall <ktm@rice.edu>
2010-10-28 13:06 ` Sergey Konoplev <gray.ru@gmail.com>
2010-11-08 15:05 ` Alvaro Herrera <alvherre@commandprompt.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