Обсуждение: Is the database being VACUUMed?

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

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?"

> 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

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.

> 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