pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum goes worse
20+ messages / 8 participants
[nested] [flat]
* Vacuum goes worse
@ 2007-10-16 08:48 Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
0 siblings, 2 replies; 20+ messages in thread
From: Stéphane Schildknecht @ 2007-10-16 08:48 UTC (permalink / raw)
To: pgsql-performance
Hi,
For some times, we have a vacuuming process on a specific table that
goes slower and slower. In fact, it took some 3 minutes a month ago, and
now it take almost 20 minutes. But, if one day it take so many time, it
is possible that on the day after it will only take 4 minutes...
I know the table in concern had 450000 tuples two months ago and now has
more than 700000 tuples in it.
I wonder vacuum verbose would tell me if fsm parameters were not too
badly configured, but I can't get the 4 last lines of the output...
Is there another way to get these info ? Or is it a parameter badly
configured ?
For information, it's on AIX, PG8.1.9.
Some configuration parameters :
client_min_messages : notice
log_error_verbosity : default
log_min_error_statement : panic
log_min_messages : notice.
Whats's more, I wonder what we could monitor to get some explanation of
the recent time increase, and then have a quite-sure way of configuring
the server.
I have to say the database is hosted, accessed in production on a 24/7
basis and then every change in configuration has to be scheduled.
Some more information you may ask:
chackpoint_segments : 32
checkpoint_timeout : 180
checkpoint_warning : 30
wal_buffers : 64
maintenance_work_mem : 65536
max_fsm_pages : 400000
max_fsm_relations : 1000
shared_buffers : 50000
temp_bufers : 1000
We also have 4Gb RAM.
Isn't checkpoint_segments too low as all files in pg_xlogs seem to be
recycled within a few minutes. (In fact among the 60 files, at least 30
have been modified during the few minutes of that particular vacuum).
Thanks for any advice you could give me.
Best regards,
--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 09:08 Heikki Linnakangas <heikki@enterprisedb.com>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
1 sibling, 1 reply; 20+ messages in thread
From: Heikki Linnakangas @ 2007-10-16 09:08 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: pgsql-performance
Stéphane Schildknecht wrote:
> I wonder vacuum verbose would tell me if fsm parameters were not too
> badly configured, but I can't get the 4 last lines of the output...
Why not?
> Whats's more, I wonder what we could monitor to get some explanation of
> the recent time increase, and then have a quite-sure way of configuring
> the server.
sar or iostat output would be a good start, to determine if it's waiting
for I/O or what.
> I have to say the database is hosted, accessed in production on a 24/7
> basis and then every change in configuration has to be scheduled.
>
> Some more information you may ask:
> chackpoint_segments : 32
> checkpoint_timeout : 180
> checkpoint_warning : 30
> wal_buffers : 64
> maintenance_work_mem : 65536
> max_fsm_pages : 400000
> max_fsm_relations : 1000
> shared_buffers : 50000
> temp_bufers : 1000
>
> We also have 4Gb RAM.
>
> Isn't checkpoint_segments too low as all files in pg_xlogs seem to be
> recycled within a few minutes. (In fact among the 60 files, at least 30
> have been modified during the few minutes of that particular vacuum).
Increasing checkpoint_segments seems like a good idea then. You should
increase checkpoint_timeout as well, 180 is just 3 minutes. How much
concurrent activity is there in the database? 30 pg_xlog files equals
512 MB of WAL; that's quite a lot.
Have you changed the vacuum cost delay settings from the defaults?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 09:50 Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
parent: Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 20+ messages in thread
From: Stéphane Schildknecht @ 2007-10-16 09:50 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: pgsql-performance
Heikki Linnakangas a écrit :
> Stéphane Schildknecht wrote:
>
>> I wonder vacuum verbose would tell me if fsm parameters were not too
>> badly configured, but I can't get the 4 last lines of the output...
>>
>
> Why not?
>
I would like to know... Seems like vacuum does not want me to see these
precious line. I really don't know why.
>
>> Whats's more, I wonder what we could monitor to get some explanation of
>> the recent time increase, and then have a quite-sure way of configuring
>> the server.
>>
>
> sar or iostat output would be a good start, to determine if it's waiting
> for I/O or what.
>
Ok, I'll try that.
>
> Increasing checkpoint_segments seems like a good idea then. You should
> increase checkpoint_timeout as well, 180 is just 3 minutes. How much
> concurrent activity is there in the database? 30 pg_xlog files equals
> 512 MB of WAL; that's quite a lot.
>
I don't know exactly how far, but yes, activity is high.
> Have you changed the vacuum cost delay settings from the defaults?
>
Not yet.
--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 14:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
1 sibling, 1 reply; 20+ messages in thread
From: Tom Lane @ 2007-10-16 14:26 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: pgsql-performance
=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
> For some times, we have a vacuuming process on a specific table that
> goes slower and slower. In fact, it took some 3 minutes a month ago, and
> now it take almost 20 minutes. But, if one day it take so many time, it
> is possible that on the day after it will only take 4 minutes...
> I know the table in concern had 450000 tuples two months ago and now has
> more than 700000 tuples in it.
The real question is how often do rows get updated? I suspect you
probably need to vacuum this table more than once a day.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 15:26 Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 20+ messages in thread
From: Stéphane Schildknecht @ 2007-10-16 15:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane a écrit :
> =?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
>
>> For some times, we have a vacuuming process on a specific table that
>> goes slower and slower. In fact, it took some 3 minutes a month ago, and
>> now it take almost 20 minutes. But, if one day it take so many time, it
>> is possible that on the day after it will only take 4 minutes...
>>
>
>
>> I know the table in concern had 450000 tuples two months ago and now has
>> more than 700000 tuples in it.
>>
>
> The real question is how often do rows get updated? I suspect you
> probably need to vacuum this table more than once a day.
>
>
To be honest, I suspect it too. But, I have been told by people using
that database they can't do vacuum more frequently than once in a day as
it increases the time to achieve concurrent operations.
That's also why they don't want to hear about autovacuum.
And finally that's why I'm looking for everything I can monitor to
obtain information to convince them they're wrong and I'm right ;-)
That's also why I am so disappointed vacuum doesn't give me these 4
hints lines.
Regards,
--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
1 sibling, 0 replies; 20+ messages in thread
From: Tom Lane @ 2007-10-16 15:30 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: pgsql-performance
=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
> Tom Lane a écrit :
>> The real question is how often do rows get updated? I suspect you
>> probably need to vacuum this table more than once a day.
> To be honest, I suspect it too. But, I have been told by people using
> that database they can't do vacuum more frequently than once in a day as
> it increases the time to achieve concurrent operations.
vacuum_cost_delay can help here.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 15:43 Scott Marlowe <scott.marlowe@gmail.com>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
1 sibling, 1 reply; 20+ messages in thread
From: Scott Marlowe @ 2007-10-16 15:43 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On 10/16/07, Stéphane Schildknecht
<stephane.schildknecht@postgresqlfr.org> wrote:
> Tom Lane a écrit :
> > =?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
> >
> >> For some times, we have a vacuuming process on a specific table that
> >> goes slower and slower. In fact, it took some 3 minutes a month ago, and
> >> now it take almost 20 minutes. But, if one day it take so many time, it
> >> is possible that on the day after it will only take 4 minutes...
> >>
> >
> >
> >> I know the table in concern had 450000 tuples two months ago and now has
> >> more than 700000 tuples in it.
> >>
> >
> > The real question is how often do rows get updated? I suspect you
> > probably need to vacuum this table more than once a day.
> >
> >
>
> To be honest, I suspect it too. But, I have been told by people using
> that database they can't do vacuum more frequently than once in a day as
> it increases the time to achieve concurrent operations.
> That's also why they don't want to hear about autovacuum.
Sounds like somebody there is operating on the belief that vacuums
always cost the same amount i/o wise. With the vacuum_cost_delay
setting Tim mentioned this is not true. Their concern shouldn't be
with how you accomplish your job, but with you meeting certain
performance criteria, and with vacuum cost delay, it is quite possible
to vacuum midday with affecting the db too much.
> And finally that's why I'm looking for everything I can monitor to
> obtain information to convince them they're wrong and I'm right ;-)
Good luck with that. I still have a boss who thinks "vacuum's not
fast enough". His last experience with pgsql was in the 7.2 days.
Generally he's a pretty smart guy, but he's convinced himself that
PostgreSQL 8.3 and 7.2 are pretty much the same beasts.
> That's also why I am so disappointed vacuum doesn't give me these 4
> hints lines.
What kind of error, or output, does it give you at the end? Any hint
as to why they're missing?
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-16 16:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2007-10-16 16:03 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; pgsql-performance
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On 10/16/07, St=E9phane Schildknecht
> <stephane.schildknecht@postgresqlfr.org> wrote:
>> That's also why I am so disappointed vacuum doesn't give me these 4
>> hints lines.
> What kind of error, or output, does it give you at the end? Any hint
> as to why they're missing?
If you're talking about the FSM statistics display, that only gets
printed by a database-wide VACUUM (one that doesn't name a specific
table).
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 00:03 Brian Herlihy <btherl@yahoo.com.au>
0 siblings, 2 replies; 20+ messages in thread
From: Brian Herlihy @ 2007-10-17 00:03 UTC (permalink / raw)
To: pgsql-performance
Would it make sense to show the FSM stats for individual table vaccums as well? I'm wondering if the reason they aren't shown is because it wouldn't be useful or isn't practical, or just that it hasn't been done.
Brian
----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
If you're talking about the FSM statistics display, that only gets
printed by a database-wide VACUUM (one that doesn't name a specific
table).
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 01:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brian Herlihy <btherl@yahoo.com.au>
1 sibling, 0 replies; 20+ messages in thread
From: Tom Lane @ 2007-10-17 01:16 UTC (permalink / raw)
To: Brian Herlihy <btherl@yahoo.com.au>; +Cc: pgsql-performance
Brian Herlihy <btherl@yahoo.com.au> writes:
> Would it make sense to show the FSM stats for individual table vaccums as w=
> ell? I'm wondering if the reason they aren't shown is because it wouldn't =
> be useful or isn't practical, or just that it hasn't been done.
It was intentionally omitted in the original design, on the grounds that
after a single-table VACUUM there's no very good reason to think that
the global FSM stats are sufficiently complete to be accurate. Of
course, in a multi-database installation the same charge could be
leveled against the situation after a single-database VACUUM, so maybe
there's not a lot of strength in the argument.
IIRC the code change would be trivial, it's just a matter of judgment
whether the extra output is useful/trustworthy.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 03:19 Joshua D. Drake <jd@commandprompt.com>
parent: Brian Herlihy <btherl@yahoo.com.au>
1 sibling, 1 reply; 20+ messages in thread
From: Joshua D. Drake @ 2007-10-17 03:19 UTC (permalink / raw)
To: Brian Herlihy <btherl@yahoo.com.au>; +Cc: pgsql-performance
On Tue, 16 Oct 2007 17:03:39 -0700 (PDT)
Brian Herlihy <btherl@yahoo.com.au> wrote:
> Would it make sense to show the FSM stats for individual table
> vaccums as well? I'm wondering if the reason they aren't shown is
> because it wouldn't be useful or isn't practical, or just that it
> hasn't been done.
I am not sure how useful it would be as the FSM is global. However what
would be useful is something like VACUUM SUMMARY, where I could get
"just" the stats instead of all the other output that comes along with
VERBOSE.
Joshua D. Drake
>
> Brian
>
> ----- Original Message ----
> From: Tom Lane <tgl@sss.pgh.pa.us>
>
> If you're talking about the FSM statistics display, that only gets
> printed by a database-wide VACUUM (one that doesn't name a specific
> table).
>
> regards, tom lane
>
>
>
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 5: don't forget to increase
> your free space map settings
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 11:33 Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 20+ messages in thread
From: Stéphane Schildknecht @ 2007-10-17 11:33 UTC (permalink / raw)
To: pgsql-performance
Tom Lane a écrit :
> "Scott Marlowe" <scott.marlowe@gmail.com> writes:
>
>> On 10/16/07, St=E9phane Schildknecht
>> <stephane.schildknecht@postgresqlfr.org> wrote:
>>
>>> That's also why I am so disappointed vacuum doesn't give me these 4
>>> hints lines.
>>>
>
>
>> What kind of error, or output, does it give you at the end? Any hint
>> as to why they're missing?
>>
>
> If you're talking about the FSM statistics display, that only gets
> printed by a database-wide VACUUM (one that doesn't name a specific
> table).
>
Yes, I am. The command line is (in a shell script whom ouput is
redirected in a global file) :
vacuumdb -d $DBNAME -p $DBPORT -U $DBUSR -z -v
That does not explain why we don't get FSM statitics display. The output
ends with:
INFO: vacuuming "public.sometable"
INFO: "sometable": removed 62 row versions in 3 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "sometable": found 62 removable, 5 nonremovable row versions in 5
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 534 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.sometable"
INFO: "sometable": scanned 5 of 5 pages, containing 5 live rows and 0
dead rows; 5 rows in sample, 5 estimated total rows
VACUUM
Best regards,
Stéphane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 13:43 Alvaro Herrera <alvherre@commandprompt.com>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 20+ messages in thread
From: Alvaro Herrera @ 2007-10-17 13:43 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Brian Herlihy <btherl@yahoo.com.au>; pgsql-performance
Joshua D. Drake wrote:
> On Tue, 16 Oct 2007 17:03:39 -0700 (PDT)
> Brian Herlihy <btherl@yahoo.com.au> wrote:
>
> > Would it make sense to show the FSM stats for individual table
> > vaccums as well? I'm wondering if the reason they aren't shown is
> > because it wouldn't be useful or isn't practical, or just that it
> > hasn't been done.
>
> I am not sure how useful it would be as the FSM is global. However what
> would be useful is something like VACUUM SUMMARY, where I could get
> "just" the stats instead of all the other output that comes along with
> VERBOSE.
What would be really useful is to remove all that noise from vacuum and
make it appear on a view. 8.4 material all of this, of course.
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"Estoy de acuerdo contigo en que la verdad absoluta no existe...
El problema es que la mentira sí existe y tu estás mintiendo" (G. Lama)
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 14:35 Stefano Dal Pra <s.dalpra@gmail.com>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2 siblings, 1 reply; 20+ messages in thread
From: Stefano Dal Pra @ 2007-10-17 14:35 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: pgsql-performance
Your first post says vacuum goes worse (slower).
I see that you do not issue the -f option (FULL VACUUM).
I had a similar situation with a server (with frequent update)
performing nightly vacuumdb. After a few many days it went
slower and slower.
The first solution was to add the -f switch.
Note that it leads to table lock (see docs :-)
the FULL option completely rewrite the table on disk making it much
more compact
(i think of it similar to a "defrag" on windows). I had a dramatic
speed improvement
after the first vacuum full.
latest solution (psql 8.0.1) was a perl script which selectively
chooses tables to
full vacuum basing on results from this select:
SELECT a.relname, a.relpages FROM pg_class a ,pg_stat_user_tables b
WHERE a.relname = b.relname order by relpages desc;
this was to see how much a table's size grows through time.
With psql 8.2.x we adopted pg_autovacuum which seems to perform good,
even thought
i do not clearly understand whether it occasionally performs a full
vacuum (i think he does'nt).
Stefano
On 10/17/07, Stéphane Schildknecht
<stephane.schildknecht@postgresqlfr.org> wrote:
> Tom Lane a écrit :
> > "Scott Marlowe" <scott.marlowe@gmail.com> writes:
> >
> >> On 10/16/07, St=E9phane Schildknecht
> >> <stephane.schildknecht@postgresqlfr.org> wrote:
> >>
> >>> That's also why I am so disappointed vacuum doesn't give me these 4
> >>> hints lines.
> >>>
> >
> >
> >> What kind of error, or output, does it give you at the end? Any hint
> >> as to why they're missing?
> >>
> >
> > If you're talking about the FSM statistics display, that only gets
> > printed by a database-wide VACUUM (one that doesn't name a specific
> > table).
> >
>
> Yes, I am. The command line is (in a shell script whom ouput is
> redirected in a global file) :
>
> vacuumdb -d $DBNAME -p $DBPORT -U $DBUSR -z -v
>
>
> That does not explain why we don't get FSM statitics display. The output
> ends with:
> INFO: vacuuming "public.sometable"
> INFO: "sometable": removed 62 row versions in 3 pages
> DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: "sometable": found 62 removable, 5 nonremovable row versions in 5
> pages
> DETAIL: 0 dead row versions cannot be removed yet.
> There were 534 unused item pointers.
> 0 pages are entirely empty.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO: analyzing "public.sometable"
> INFO: "sometable": scanned 5 of 5 pages, containing 5 live rows and 0
> dead rows; 5 rows in sample, 5 estimated total rows
> VACUUM
>
> Best regards,
>
> Stéphane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 15:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2 siblings, 1 reply; 20+ messages in thread
From: Tom Lane @ 2007-10-17 15:53 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: pgsql-performance
=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
> Yes, I am. The command line is (in a shell script whom ouput is
> redirected in a global file) :
> vacuumdb -d $DBNAME -p $DBPORT -U $DBUSR -z -v
> That does not explain why we don't get FSM statitics display.
Is $DBUSR a superuser? If not, some tables are likely getting skipped.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 15:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 20+ messages in thread
From: Tom Lane @ 2007-10-17 15:56 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; +Cc: Joshua D. Drake <jd@commandprompt.com>; Brian Herlihy <btherl@yahoo.com.au>; pgsql-performance
Alvaro Herrera <alvherre@commandprompt.com> writes:
> What would be really useful is to remove all that noise from vacuum and
> make it appear on a view.
Well, if you want something decoupled from VACUUM there's already
contrib/pg_freespacemap.
> 8.4 material all of this, of course.
I am hoping that we rewrite FSM into the distributed DSM structure
that's been talked about, so that the whole problem goes away in 8.4.
regards, tom lane
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 16:07 Alvaro Herrera <alvherre@commandprompt.com>
parent: Stefano Dal Pra <s.dalpra@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Alvaro Herrera @ 2007-10-17 16:07 UTC (permalink / raw)
To: Stefano Dal Pra <s.dalpra@gmail.com>; +Cc: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; pgsql-performance
Stefano Dal Pra escribió:
> Your first post says vacuum goes worse (slower).
> I see that you do not issue the -f option (FULL VACUUM).
>
> I had a similar situation with a server (with frequent update)
> performing nightly vacuumdb. After a few many days it went
> slower and slower.
When you have that problem, the solution is to issue more plain vacuum
(not full) more frequently. If it's a highly updated table, then maybe
once per hour or more. It depends on the update rate.
> With psql 8.2.x we adopted pg_autovacuum which seems to perform good,
> even thought
> i do not clearly understand whether it occasionally performs a full
> vacuum (i think he does'nt).
It doesn't because it's normally not necessary. Also, we don't want to
be acquiring exclusive locks in a background automatic process, so if
you really need vacuum full (and I question your need to) then you must
issue it yourself.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-17 18:43 Scott Marlowe <scott.marlowe@gmail.com>
parent: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2 siblings, 1 reply; 20+ messages in thread
From: Scott Marlowe @ 2007-10-17 18:43 UTC (permalink / raw)
To: Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>; +Cc: pgsql-performance
On 10/17/07, Stéphane Schildknecht
<stephane.schildknecht@postgresqlfr.org> wrote:
> Tom Lane a écrit :
>
> Yes, I am. The command line is (in a shell script whom ouput is
> redirected in a global file) :
>
> vacuumdb -d $DBNAME -p $DBPORT -U $DBUSR -z -v
>
>
> That does not explain why we don't get FSM statitics display.
Hmmm. Have you tried running that command interactively? I'm just
wondering if your redirect is somehow dropping bits of the output.
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-18 06:17 Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 20+ messages in thread
From: Stéphane Schildknecht @ 2007-10-18 06:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Tom Lane a écrit :
> =?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane.schildknecht@postgresqlfr.org> writes:
>
>> Yes, I am. The command line is (in a shell script whom ouput is
>> redirected in a global file) :
>>
>
>
>> vacuumdb -d $DBNAME -p $DBPORT -U $DBUSR -z -v
>>
>
>
>> That does not explain why we don't get FSM statitics display.
>>
>
> Is $DBUSR a superuser? If not, some tables are likely getting skipped.
>
> regards, tom lane
>
No it's not a superuser as some pg_tables are skipped, according to logs.
So that's why these information are not diplayed in 8.19. Shame on me!
Thanks for all the advice. In fact, I did not take care of that as on
8.2.x, these information are displayed whenever you run it with
superuser or not...
What's more it seems (here on my linux box) it does not depends on the
client but on the server version.
Best regards,
--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org
^ permalink raw reply [nested|flat] 20+ messages in thread
* Re: Vacuum goes worse
@ 2007-10-18 06:19 Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 20+ messages in thread
From: Stéphane Schildknecht @ 2007-10-18 06:19 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-performance
Scott Marlowe a écrit :
> On 10/17/07, Stéphane Schildknecht
> <stephane.schildknecht@postgresqlfr.org> wrote:
>
>> Tom Lane a écrit :
>>
>> Yes, I am. The command line is (in a shell script whom ouput is
>> redirected in a global file) :
>>
>> vacuumdb -d $DBNAME -p $DBPORT -U $DBUSR -z -v
>>
>>
>> That does not explain why we don't get FSM statitics display.
>>
>
> Hmmm. Have you tried running that command interactively? I'm just
> wondering if your redirect is somehow dropping bits of the output.
>
I tried a few combinations of redirection tests to verify that, and
always get all of the output or none of it.
According to Tom and tests I made on another box, superuser is the key.
Thanks anyway.
Best regards,
--
Stéphane SCHILDKNECHT
Président de PostgreSQLFr
http://www.postgresqlfr.org
^ permalink raw reply [nested|flat] 20+ messages in thread
end of thread, other threads:[~2007-10-18 06:19 UTC | newest]
Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-16 08:48 Vacuum goes worse Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2007-10-16 09:08 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-16 09:50 ` Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2007-10-16 14:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-16 15:26 ` Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2007-10-16 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-16 15:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-16 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-17 11:33 ` Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2007-10-17 14:35 ` Stefano Dal Pra <s.dalpra@gmail.com>
2007-10-17 16:07 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-10-17 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-18 06:17 ` Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2007-10-17 18:43 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-18 06:19 ` Stéphane Schildknecht <stephane.schildknecht@postgresqlfr.org>
2007-10-17 00:03 Re: Vacuum goes worse Brian Herlihy <btherl@yahoo.com.au>
2007-10-17 01:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-10-17 03:19 ` Joshua D. Drake <jd@commandprompt.com>
2007-10-17 13:43 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-10-17 15:56 ` Tom Lane <tgl@sss.pgh.pa.us>
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