pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMy quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
7+ messages / 5 participants
[nested] [flat]
* My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
@ 2007-05-25 12:30 Bastian Voigt <post@bastian-voigt.de>
2007-05-25 12:57 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Kristo Kaiv <kristo.kaiv@skype.net>
2007-05-25 13:16 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Richard Huxton <dev@archonet.com>
2007-05-25 13:56 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 3 replies; 7+ messages in thread
From: Bastian Voigt @ 2007-05-25 12:30 UTC (permalink / raw)
To: pgsql-performance
No, this did not help. The vacuum process is still running far too long
and makes everything slow. It is even worse than before, cause now the
system is slow almost all the time while when vacuuming only every 3
hours it is only slow once every three hours.....
I now did the following. Well, no comment.....
Shellscript A:
while true
do
psql -U $user -d $database -c "vacuum analyze verbose binary_cache"
echo "Going to sleep"
sleep 60
done
Shellscript B:
while true
do
ps aux > $tempfile
numwaiting=`grep UPDATE.waiting $tempfile | grep -c -v grep`
echo "Number of waiting updates: $numwaiting"
vacuumpid=`grep VACUUM $tempfile| grep -v grep | awk '{print $2}'`
echo "PID of vacuum process: $vacuumpid"
if [ $numwaiting -gt 5 ]
then
echo "Too many waiting transactions, killing vacuum
process $vacuumpid..."
kill $vacuumpid
fi
echo "Sleeping 30 Seconds"
sleep 30
done
--
Bastian Voigt
Neumünstersche Straße 4
20251 Hamburg
telefon +49 - 40 - 67957171
mobil +49 - 179 - 4826359
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
2007-05-25 12:30 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
@ 2007-05-25 12:57 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-05-25 14:06 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
2 siblings, 1 reply; 7+ messages in thread
From: Kristo Kaiv @ 2007-05-25 12:57 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
you should first cluster the table on primary key.
The table is probably already bloated from the 3 hr delay it had before.
First
CLUSTER "primary key index name" ON group_fin_account_tst;
Then
vacuum it every 3 minutes.
NB! clustering takes an access exclusive lock on table
Kristo
On 25.05.2007, at 15:30, Bastian Voigt wrote:
> No, this did not help. The vacuum process is still running far too
> long and makes everything slow. It is even worse than before, cause
> now the system is slow almost all the time while when vacuuming
> only every 3 hours it is only slow once every three hours.....
>
>
> I now did the following. Well, no comment.....
>
>
> Shellscript A:
>
> while true
> do
> psql -U $user -d $database -c "vacuum analyze verbose binary_cache"
> echo "Going to sleep"
> sleep 60
> done
>
>
> Shellscript B:
>
> while true
> do
> ps aux > $tempfile
> numwaiting=`grep UPDATE.waiting $tempfile | grep -c -v grep`
> echo "Number of waiting updates: $numwaiting"
>
> vacuumpid=`grep VACUUM $tempfile| grep -v grep | awk '{print
> $2}'`
> echo "PID of vacuum process: $vacuumpid"
>
> if [ $numwaiting -gt 5 ]
> then
> echo "Too many waiting transactions, killing vacuum
> process $vacuumpid..."
> kill $vacuumpid
> fi
> echo "Sleeping 30 Seconds"
> sleep 30
> done
>
> --
> Bastian Voigt
> Neumünstersche Straße 4
> 20251 Hamburg
> telefon +49 - 40 - 67957171
> mobil +49 - 179 - 4826359
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
2007-05-25 12:30 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
2007-05-25 12:57 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Kristo Kaiv <kristo.kaiv@skype.net>
@ 2007-05-25 14:06 ` Bastian Voigt <post@bastian-voigt.de>
0 siblings, 0 replies; 7+ messages in thread
From: Bastian Voigt @ 2007-05-25 14:06 UTC (permalink / raw)
To: pgsql-performance
Kristo Kaiv wrote:
> you should first cluster the table on primary key.
> The table is probably already bloated from the 3 hr delay it had before.
> First
> CLUSTER "primary key index name" ON group_fin_account_tst;
> Then
> vacuum it every 3 minutes.
> NB! clustering takes an access exclusive lock on table
Kristo,
thanks a bunch!!
This was the solution...
The cluster operation took about 60sec, and after it was done the vacuum
finished in only 10sec. or so, with no noticeable performance
bottleneck. Now vacuum is running every 2-3 minutes and makes no problems.
Hhhh, now I can look forward to a laid-back weekend..
Richard, Kristo, Alvaro, thanks 1000 times for responding so quickly
:-)
--
Bastian Voigt
Neumünstersche Straße 4
20251 Hamburg
telefon +49 - 40 - 67957171
mobil +49 - 179 - 4826359
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
2007-05-25 12:30 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
@ 2007-05-25 13:16 ` Richard Huxton <dev@archonet.com>
2007-05-25 14:11 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
2 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2007-05-25 13:16 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
Bastian Voigt wrote:
> No, this did not help. The vacuum process is still running far too long
> and makes everything slow. It is even worse than before, cause now the
> system is slow almost all the time while when vacuuming only every 3
> hours it is only slow once every three hours.....
Could you check the output of vacuum verbose on that table and see how
much work it's doing? I'd have thought the actual bytea data would be
TOASTed away to a separate table for storage, leaving the vacuum with
very little work to do.
It might well be your actual problem is your disk I/O is constantly
saturated and the vacuum just pushes it over the edge. In which case
you'll either need more/better disks or to find a quiet time once a day
to vacuum and just do so then.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
2007-05-25 12:30 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
2007-05-25 13:16 ` Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Richard Huxton <dev@archonet.com>
@ 2007-05-25 14:11 ` Bastian Voigt <post@bastian-voigt.de>
0 siblings, 0 replies; 7+ messages in thread
From: Bastian Voigt @ 2007-05-25 14:11 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-performance
Richard Huxton wrote:
> Could you check the output of vacuum verbose on that table and see how
> much work it's doing? I'd have thought the actual bytea data would be
> TOASTed away to a separate table for storage, leaving the vacuum with
> very little work to do.
I'm quite new to postgres (actually I just ported our running
application from MySQL...), so I don't know what toast means. But I
noticed that vacuum also tried to cleanup some "toast" relations or so.
This was what took so long.
> It might well be your actual problem is your disk I/O is constantly
> saturated and the vacuum just pushes it over the edge. In which case
> you'll either need more/better disks or to find a quiet time once a
> day to vacuum and just do so then.
Yes, that was definitely the case. But now everything runs smoothly
again, so I don't think I need to buy new disks.
Regards
Bastian
--
Bastian Voigt
Neumünstersche Straße 4
20251 Hamburg
telefon +49 - 40 - 67957171
mobil +49 - 179 - 4826359
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13))
2007-05-25 12:30 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
@ 2007-05-25 13:56 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 7+ messages in thread
From: Alvaro Herrera @ 2007-05-25 13:56 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
Bastian Voigt wrote:
> No, this did not help. The vacuum process is still running far too long
> and makes everything slow. It is even worse than before, cause now the
> system is slow almost all the time while when vacuuming only every 3
> hours it is only slow once every three hours.....
>
>
> I now did the following. Well, no comment.....
Killing the vacuum mid-process doesn't help you, because the table will
be in a sorrier state than it was when it started.
I think it would be better if you:
1. Revert pg_autovacuum changes so that it processes every 3 hours or
whatever, like you had at the start of this thread. Or maybe less.
That one will take care of the _other_ tables.
2. Vacuum the bytea table manually more often, say every 10 minutes or
so (vacuum, sleep 10m, goto start). Make sure this is done with an
appropriate vacuum_cost_delay setting (and related settings).
3. Raise max_fsm_pages so that a lot of pages with free space can be
recorded for that table
The point here is that vacuuming the bytea table can take a long time
due to vacuum_cost_delay, but it won't affect the rest of the system;
regular operation will continue to run at (almost) normal speed. Having
a big number of free pages ensures that the free space in the table is
not "lost".
Also, you may want to reindex that table once, because with so many
killing vacuums you have probably screwed up the indexes big time (maybe
cluster it once instead of reindexing, because that will compact the
heap as well as the indexes).
Another recommendation is to upgrade to 8.2.4 which is faster and has
a better autovacuum.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: My quick and dirty "solution" (Re: Performance P roblem with Vacuum of bytea table (PG 8.0.13))
@ 2007-05-25 15:20 Andreas Kostyrka <andreas@kostyrka.org>
0 siblings, 0 replies; 7+ messages in thread
From: Andreas Kostyrka @ 2007-05-25 15:20 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
TOASTed means storage outside of the main table. But AFAIK, only rows bigger 2K are considered for toasting.
Andreas
-- Ursprüngl. Mitteil. --
Betreff: Re: My quick and dirty "solution" (Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13))
Von: Bastian Voigt <post@bastian-voigt.de>
Datum: 25.05.2007 14:13
Richard Huxton wrote:
> Could you check the output of vacuum verbose on that table and see how
> much work it's doing? I'd have thought the actual bytea data would be
> TOASTed away to a separate table for storage, leaving the vacuum with
> very little work to do.
I'm quite new to postgres (actually I just ported our running
application from MySQL...), so I don't know what toast means. But I
noticed that vacuum also tried to cleanup some "toast" relations or so.
This was what took so long.
> It might well be your actual problem is your disk I/O is constantly
> saturated and the vacuum just pushes it over the edge. In which case
> you'll either need more/better disks or to find a quiet time once a
> day to vacuum and just do so then.
Yes, that was definitely the case. But now everything runs smoothly
again, so I don't think I need to buy new disks.
Regards
Bastian
--
Bastian Voigt
Neumünstersche Straße 4
20251 Hamburg
telefon +49 - 40 - 67957171
mobil +49 - 179 - 4826359
---------------------------(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] 7+ messages in thread
end of thread, other threads:[~2007-05-25 15:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-25 12:30 My quick and dirty "solution" (Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)) Bastian Voigt <post@bastian-voigt.de>
2007-05-25 12:57 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-05-25 14:06 ` Bastian Voigt <post@bastian-voigt.de>
2007-05-25 13:16 ` Richard Huxton <dev@archonet.com>
2007-05-25 14:11 ` Bastian Voigt <post@bastian-voigt.de>
2007-05-25 13:56 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-05-25 15:20 Re: My quick and dirty "solution" (Re: Performance P roblem with Vacuum of bytea table (PG 8.0.13)) Andreas Kostyrka <andreas@kostyrka.org>
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