pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacum Analyze problem
8+ messages / 6 participants
[nested] [flat]
* Vacum Analyze problem
@ 2007-09-04 17:27 rafael@akyasociados.com.ar
0 siblings, 3 replies; 8+ messages in thread
From: rafael@akyasociados.com.ar @ 2007-09-04 17:27 UTC (permalink / raw)
To: pgsql-performance
Hello everyone:
I wanted to ask you about how the VACUUM ANALYZE works. is it possible
that something can happen in order to reset its effects forcing to execute
the VACUUM ANALYZE comand again? i am asking this because i am struggling
with a query which works ok after i run a VACUUM ANALYZE, however, sudennly,
it starts to take forever (the execution of the query) until i make another
VACUUM ANALYZE, and so on ...
I'd like to point that i am a novice when it comes to non basic
postgresql performance related stuff.
Thank you all in advance
Rafael
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 17:33 Mikko Partio <mpartio@gmail.com>
parent: rafael@akyasociados.com.ar
2 siblings, 1 reply; 8+ messages in thread
From: Mikko Partio @ 2007-09-04 17:33 UTC (permalink / raw)
To: rafael@akyasociados.com.ar <rafael@akyasociados.com.ar>; +Cc: pgsql-performance
On 9/4/07, rafael@akyasociados.com.ar <rafael@akyasociados.com.ar> wrote:
>
> Hello everyone:
>
> I wanted to ask you about how the VACUUM ANALYZE works. is it possible
> that something can happen in order to reset its effects forcing to execute
> the VACUUM ANALYZE comand again?
Yes, lots of modifications (INSERT,UPDATE,DELETE) to the table in question.
Regards
MP
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 17:45 Kevin Kempter <kevin@kevinkempterllc.com>
parent: rafael@akyasociados.com.ar
2 siblings, 1 reply; 8+ messages in thread
From: Kevin Kempter @ 2007-09-04 17:45 UTC (permalink / raw)
To: pgsql-performance
On Tuesday 04 September 2007 11:27:07 rafael@akyasociados.com.ar wrote:
> Hello everyone:
>
> I wanted to ask you about how the VACUUM ANALYZE works. is it possible
> that something can happen in order to reset its effects forcing to execute
> the VACUUM ANALYZE comand again? i am asking this because i am struggling
> with a query which works ok after i run a VACUUM ANALYZE, however,
> sudennly, it starts to take forever (the execution of the query) until i
> make another VACUUM ANALYZE, and so on ...
> I'd like to point that i am a novice when it comes to non basic
> postgresql performance related stuff.
>
> Thank you all in advance
>
> Rafael
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
Rafael;
Vacuum Analyze performs 2 tasks at once.
1) Vacuum - this analyzes the table pages and sets appropriate dead row space
(those from old updates or deletes that are not possibly needed by any
existing transactions) as such that the db can re-use (over-write) that
space.
2) Analyze - Like an Oracle compute stats, updates the system catalogs with
current table stat data.
The Vacuum will improve queries since the dead space can be re-used and any
dead space if the table you are having issues with is a high volume table
then the solution is generally to run vacuum more often - I've seen tables
that needed a vacuum every 5 minutes due to significant sustained churn.
The Analyze of course is key for the planner, if the table is growing rapidly
then running analyze more often will help, if however there is lots of churn
but little change in the data (i.e. lots of inserts followed by delete's of
the same rows) then a straight vacuum is probably what you need. If the data
is changing rapidly then bumping up the default_statistics_target value may
help - you can bump the default_statistics_target for a single table in the
pg_autovacuum system catalog table.
Hope this helps...
/Kevin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 17:46 rafael@akyasociados.com.ar
parent: Mikko Partio <mpartio@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: rafael@akyasociados.com.ar @ 2007-09-04 17:46 UTC (permalink / raw)
To: mpartio@gmail.com; +Cc: rafael@akyasociados.com.ar; pgsql-performance
> On 9/4/07, rafael@akyasociados.com.ar <rafael@akyasociados.com.ar>
> wrote:
>>
>> Hello everyone:
>>
>> I wanted to ask you about how the VACUUM ANALYZE works. is it
>> possible
>> that something can happen in order to reset its effects forcing to
>> execute the VACUUM ANALYZE comand again?
>
>
>
> Yes, lots of modifications (INSERT,UPDATE,DELETE) to the table in
> question.
>
> Regards
>
> MP
I knew that in the long run the VACUUM ANALYZE comand has to be executed
again. My question is if something can happen over night and cause the need
of a new VACUUM ANALYZE (regenerating indexes or other thing related with
performance).
Thanks for your reply.
Rafael
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 17:55 Bill Moran <wmoran@collaborativefusion.com>
parent: rafael@akyasociados.com.ar
2 siblings, 0 replies; 8+ messages in thread
From: Bill Moran @ 2007-09-04 17:55 UTC (permalink / raw)
To: rafael@akyasociados.com.ar; +Cc: pgsql-performance
In response to rafael@akyasociados.com.ar:
> Hello everyone:
>
> I wanted to ask you about how the VACUUM ANALYZE works. is it possible
> that something can happen in order to reset its effects forcing to execute
> the VACUUM ANALYZE comand again? i am asking this because i am struggling
> with a query which works ok after i run a VACUUM ANALYZE, however, sudennly,
> it starts to take forever (the execution of the query) until i make another
> VACUUM ANALYZE, and so on ...
> I'd like to point that i am a novice when it comes to non basic
> postgresql performance related stuff.
>
> Thank you all in advance
To add to Mikko's comments:
Periodic vacuuming and analyzing is a mandatory part of running a
PostgreSQL database server. You'll probably be best served to configure
the autovacuum daemon to handle this for you. See the postgresql.conf
config file.
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 18:46 rafael@akyasociados.com.ar
parent: Kevin Kempter <kevin@kevinkempterllc.com>
0 siblings, 1 reply; 8+ messages in thread
From: rafael@akyasociados.com.ar @ 2007-09-04 18:46 UTC (permalink / raw)
To: kevin@kevinkempterllc.com; +Cc: pgsql-performance
> On Tuesday 04 September 2007 11:27:07 rafael@akyasociados.com.ar wrote:
>> Hello everyone:
>>
>> I wanted to ask you about how the VACUUM ANALYZE works. is it
>> possible
>> that something can happen in order to reset its effects forcing to
>> execute the VACUUM ANALYZE comand again? i am asking this because i am
>> struggling with a query which works ok after i run a VACUUM ANALYZE,
>> however, sudennly, it starts to take forever (the execution of the
>> query) until i make another VACUUM ANALYZE, and so on ...
>> I'd like to point that i am a novice when it comes to non basic
>> postgresql performance related stuff.
>>
>> Thank you all in advance
>>
>> Rafael
>>
>>
>>
>> ---------------------------(end of
>> broadcast)--------------------------- TIP 1: if posting/reading
>> through Usenet, please send an appropriate
>> subscribe-nomail command to majordomo@postgresql.org so that
>> your message can get through to the mailing list cleanly
>
> Rafael;
>
> Vacuum Analyze performs 2 tasks at once.
>
> 1) Vacuum - this analyzes the table pages and sets appropriate dead row
> space (those from old updates or deletes that are not possibly needed
> by any existing transactions) as such that the db can re-use
> (over-write) that space.
>
> 2) Analyze - Like an Oracle compute stats, updates the system catalogs
> with current table stat data.
>
> The Vacuum will improve queries since the dead space can be re-used and
> any dead space if the table you are having issues with is a high
> volume table then the solution is generally to run vacuum more often -
> I've seen tables that needed a vacuum every 5 minutes due to
> significant sustained churn.
>
> The Analyze of course is key for the planner, if the table is growing
> rapidly then running analyze more often will help, if however there is
> lots of churn but little change in the data (i.e. lots of inserts
> followed by delete's of the same rows) then a straight vacuum is
> probably what you need. If the data is changing rapidly then bumping
> up the default_statistics_target value may help - you can bump the
> default_statistics_target for a single table in the pg_autovacuum
> system catalog table.
>
> Hope this helps...
>
> /Kevin
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 3: Have you checked our
> extensive FAQ?
>
> http://www.postgresql.org/docs/faq
Thank you all for the information. I'll get to work on it and see what
happends.
Thanks again
Rafael
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 18:52 Richard Broersma Jr <rabroersma@yahoo.com>
parent: rafael@akyasociados.com.ar
0 siblings, 0 replies; 8+ messages in thread
From: Richard Broersma Jr @ 2007-09-04 18:52 UTC (permalink / raw)
To: rafael@akyasociados.com.ar; kevin@kevinkempterllc.com; +Cc: pgsql-performance
--- rafael@akyasociados.com.ar wrote:
> Thank you all for the information. I'll get to work on it and see what
> happends.
> Thanks again
>
> Rafael
I'll chime in with one last thought about excellent resources on Vacuum:
http://www.postgresql.org/docs/8.2/static/sql-vacuum.html
http://www.postgresql.org/docs/8.2/static/sql-analyze.html
http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Vacum Analyze problem
@ 2007-09-04 23:24 Gregory Stark <stark@enterprisedb.com>
parent: rafael@akyasociados.com.ar
0 siblings, 0 replies; 8+ messages in thread
From: Gregory Stark @ 2007-09-04 23:24 UTC (permalink / raw)
To: rafael@akyasociados.com.ar; +Cc: mpartio@gmail.com; pgsql-performance
<rafael@akyasociados.com.ar> writes:
> I knew that in the long run the VACUUM ANALYZE comand has to be executed
> again. My question is if something can happen over night and cause the need
> of a new VACUUM ANALYZE (regenerating indexes or other thing related with
> performance).
The answer to your question is possibly yes for two reasons:
1) If you're running an autovacuum daemon it might decide it's time to vacuum
the table and kick off a vacuum. That sounds most like what you're describing.
2) If the size of the table changes substantially becoming much larger (or
smaller but that wouldn't happen just due to deletes unless you run vacuum)
then recent versions of Postgres will notice even if you don't run analyze and
take that into account.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-09-04 23:24 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-04 17:27 Vacum Analyze problem rafael@akyasociados.com.ar
2007-09-04 17:33 ` Mikko Partio <mpartio@gmail.com>
2007-09-04 17:46 ` rafael@akyasociados.com.ar
2007-09-04 23:24 ` Gregory Stark <stark@enterprisedb.com>
2007-09-04 17:45 ` Kevin Kempter <kevin@kevinkempterllc.com>
2007-09-04 18:46 ` rafael@akyasociados.com.ar
2007-09-04 18:52 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-09-04 17:55 ` Bill Moran <wmoran@collaborativefusion.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