pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is the database being VACUUMed?
5+ messages / 3 participants
[nested] [flat]

* Is the database being VACUUMed?
@ 2006-02-10 15:57  C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 5+ messages in thread

From: C. Bensend @ 2006-02-10 15:57 UTC (permalink / raw)
  To: pgsql-admin


Hey folks,

   I'm running 8.0.4 on OpenBSD, and I'm running into issues where
a large batch job will fire up and occasionally coincide with a
VACUUM.  Which then makes this batch take an hour and a half,
rather than the 30 minutes it usually takes.  :(

   I am going to upgrade to 8.1.2 this weekend, before you ask.  :)

   Here's the question - is there a query I can make in my scripts
(using perl/DBD::Pg) to see if the database is being VACUUMed at
the current time?  I could add a sleep, so the script would
patiently wait for the VACUUM to finish before kicking off several
intense queries.

   Thanks much!

Benny


-- 
"'And you've got 10 gig of files to put through our mail system?' I
ask, squeezing my mouse in a non-approved manner." -- BOFH, 2006-01




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Is the database being VACUUMed?
@ 2006-02-10 20:17  Chris Browne <cbbrowne@acm.org>
  parent: C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Chris Browne @ 2006-02-10 20:17 UTC (permalink / raw)
  To: pgsql-admin

benny@bennyvision.com ("C. Bensend") writes:
> Hey folks,
>
>    I'm running 8.0.4 on OpenBSD, and I'm running into issues where
> a large batch job will fire up and occasionally coincide with a
> VACUUM.  Which then makes this batch take an hour and a half,
> rather than the 30 minutes it usually takes.  :(
>
>    I am going to upgrade to 8.1.2 this weekend, before you ask.  :)
>
>    Here's the question - is there a query I can make in my scripts
> (using perl/DBD::Pg) to see if the database is being VACUUMed at
> the current time?  I could add a sleep, so the script would
> patiently wait for the VACUUM to finish before kicking off several
> intense queries.
>
>    Thanks much!

If you have command string monitoring turned on, via
stats_command_string in the postgresql.conf file, then you could get
this information from the system view pg_stat_activity.

Generally, you could look to see if a current_query is a vacuum, perhaps via...

select * from pg_stat_activity where lower(current_query) like 'vacuum%' ;

If that parameter is not turned on, then ps auxww | egrep [something
finding your PG processes] | grep VACUUM could perhaps do the trick,
albeit not from a straightforward database query...
-- 
output = ("cbbrowne" "@" "acm.org")
http://www.ntlug.org/~cbbrowne/advocacy.html
"Is your pencil Y2K certified?  Do you know the possible effects if it
isn't?"



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Is the database being VACUUMed?
@ 2006-02-10 21:13  C. Bensend <benny@bennyvision.com>
  parent: Chris Browne <cbbrowne@acm.org>
  0 siblings, 1 reply; 5+ messages in thread

From: C. Bensend @ 2006-02-10 21:13 UTC (permalink / raw)
  To: pgsql-admin


> If you have command string monitoring turned on, via
> stats_command_string in the postgresql.conf file, then you could get
> this information from the system view pg_stat_activity.
>
> Generally, you could look to see if a current_query is a vacuum, perhaps
> via...
>
> select * from pg_stat_activity where lower(current_query) like 'vacuum%' ;

Are there any drawbacks to turning this on (mine is not)?  If not,
I can certainly do that.

> If that parameter is not turned on, then ps auxww | egrep [something
> finding your PG processes] | grep VACUUM could perhaps do the trick,
> albeit not from a straightforward database query...

Yes, I could do that, but I'd rather avoid it if I can.  :)

Thanks much!

Benny


-- 
"'And you've got 10 gig of files to put through our mail system?' I
ask, squeezing my mouse in a non-approved manner." -- BOFH, 2006-01




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Is the database being VACUUMed?
@ 2006-02-10 21:19  Brad Nicholson <bnichols@ca.afilias.info>
  parent: C. Bensend <benny@bennyvision.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Brad Nicholson @ 2006-02-10 21:19 UTC (permalink / raw)
  To: C. Bensend <benny@bennyvision.com>; +Cc: pgsql-admin

C. Bensend wrote:

>>If you have command string monitoring turned on, via
>>stats_command_string in the postgresql.conf file, then you could get
>>this information from the system view pg_stat_activity.
>>
>>Generally, you could look to see if a current_query is a vacuum, perhaps
>>via...
>>
>>select * from pg_stat_activity where lower(current_query) like 'vacuum%' ;
>>    
>>
>
>Are there any drawbacks to turning this on (mine is not)?  If not,
>I can certainly do that.
>
>  
>

Well, you have to turn on the stats collector, so you will pay a little 
bit of a performance penalty for that.  I doubt you'll notice it unless 
you're really close to the edge.  You do have to restart the database to 
enable the stats collector if it's off.  The pg_stat_activity stuff is 
pretty essential for debugging problems in the database.

-- 
Brad Nicholson  416-673-4106 
Database Administrator, Afilias Canada Corp.





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Is the database being VACUUMed?
@ 2006-02-10 21:32  C. Bensend <benny@bennyvision.com>
  parent: Brad Nicholson <bnichols@ca.afilias.info>
  0 siblings, 0 replies; 5+ messages in thread

From: C. Bensend @ 2006-02-10 21:32 UTC (permalink / raw)
  To: pgsql-admin


> Well, you have to turn on the stats collector, so you will pay a little
> bit of a performance penalty for that.  I doubt you'll notice it unless
> you're really close to the edge.  You do have to restart the database to
> enable the stats collector if it's off.  The pg_stat_activity stuff is
> pretty essential for debugging problems in the database.

I already have the stats collector on, so that's OK.

And my database is very simple, very much my own research project,
so bouncing it is no problem.

This looks like it will solve my problem nicely.  Thanks to all
of you!

Benny


-- 
"'And you've got 10 gig of files to put through our mail system?' I
ask, squeezing my mouse in a non-approved manner." -- BOFH, 2006-01




^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2006-02-10 21:32 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-10 15:57 Is the database being VACUUMed? C. Bensend <benny@bennyvision.com>
2006-02-10 20:17 ` Chris Browne <cbbrowne@acm.org>
2006-02-10 21:13   ` C. Bensend <benny@bennyvision.com>
2006-02-10 21:19     ` Brad Nicholson <bnichols@ca.afilias.info>
2006-02-10 21:32       ` C. Bensend <benny@bennyvision.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