Vacuum goes worse

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

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

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

=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)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

Tom Lane a écrit :
> =?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)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

=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)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

On 10/16/07, Stéphane Schildknecht
<stephane(dot)schildknecht(at)postgresqlfr(dot)org> wrote:
> Tom Lane a écrit :
> > =?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)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?

"Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> On 10/16/07, St=E9phane Schildknecht
> <stephane(dot)schildknecht(at)postgresqlfr(dot)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

Tom Lane a écrit :
> "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
>
>> On 10/16/07, St=E9phane Schildknecht
>> <stephane(dot)schildknecht(at)postgresqlfr(dot)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

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(dot)schildknecht(at)postgresqlfr(dot)org> wrote:
> Tom Lane a écrit :
> > "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> writes:
> >
> >> On 10/16/07, St=E9phane Schildknecht
> >> <stephane(dot)schildknecht(at)postgresqlfr(dot)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
>

=?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)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

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.

On 10/17/07, Stéphane Schildknecht
<stephane(dot)schildknecht(at)postgresqlfr(dot)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.

Tom Lane a écrit :
> =?ISO-8859-1?Q?St=E9phane_Schildknecht?= <stephane(dot)schildknecht(at)postgresqlfr(dot)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

Scott Marlowe a écrit :
> On 10/17/07, Stéphane Schildknecht
> <stephane(dot)schildknecht(at)postgresqlfr(dot)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