Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
On Fri, May 25, 2007 at 10:29:30AM +0200, Bastian Voigt wrote: Hi *, for caching large autogenerated XML files, I have created a bytea table in my database so that the cached files can be used by multiple servers. There are about 500 rows and 10-20 Updates per minute on the table. The files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL version is 8.0.13 on Gentoo Linux (x86) with PostGIS 1.2.0. For vacuum I use the pg_autovacuum daemon. It decided to vacuum my cache table about every 3 hours, the vacuum process takes 20-30 minutes (oops!) every time. You'll want to decrease autovacum_vacuum_scale_factor to 0.2 if you're on anything less than 8.2. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpmVh9hXWqRK.pgp Description: PGP signature
[PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
Hi *, for caching large autogenerated XML files, I have created a bytea table in my database so that the cached files can be used by multiple servers. There are about 500 rows and 10-20 Updates per minute on the table. The files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL version is 8.0.13 on Gentoo Linux (x86) with PostGIS 1.2.0. For vacuum I use the pg_autovacuum daemon. It decided to vacuum my cache table about every 3 hours, the vacuum process takes 20-30 minutes (oops!) every time. Now my big big problem is that the database gets really really slow during these 20 minutes and after the vacuum process is running for a short time, many transactions show state UPDATE waiting in the process list. In my Java application server I sometimes get tons of deadlock Exceptions (waiting on ShareLock blahblah). The web frontend gets nearly unusable, logging in takes more than 60 seconds, etc. etc. Under normal circumstances my application is really fast, vacuuming other tables is no problem, only the bytea table is really awkward I hope some of you performance cracks can help me... this is my table definition: Table »public.binary_cache« Column | Type| Attributes --+-+--- cache_id | bigint | not null date | timestamp without time zone | data | bytea | Indexe: »binary_cache_pkey« PRIMARY KEY, btree (cache_id) Thanks in advance for any hints! -- 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
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
Bastian Voigt wrote: OK, I'll give that a try. What about pg_autovacuum then? Is it a problem when two processes try to vacuum the same table in parallel? Or do I need to deactivate autovacuum altogether? I was about to say that you can tune pg_autovacuum, but I just checked your original post and you're running 8.0.x - not sure about that one. You'll have to check the documentation for that version to see if you can either: 1. exclude that table from pg_autovacuum 2. increase pg_autovacuum's sensitivity If not, and this table is the most active, it might be simpler just to run your own vacuum-ing from a cron job. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
Richard Huxton wrote: I was about to say that you can tune pg_autovacuum, but I just checked your original post and you're running 8.0.x - not sure about that one. The system catalog pg_autovacuum which allows finetuning autovacuum at table level was introduced in 8.1 :-( You'll have to check the documentation for that version to see if you can either: 1. exclude that table from pg_autovacuum 2. increase pg_autovacuum's sensitivity (1) seems to be impossible (correct me if I'm wrong..), so maybe I'll go for (2) ... If not, and this table is the most active, it might be simpler just to run your own vacuum-ing from a cron job. Well, it is one of the most active, but there are others. pg_autovacuum seems to do a very good job, apart from this one table... -- Bastian Voigt Neumünstersche Straße 4 20251 Hamburg telefon +49 - 40 - 67957171 mobil +49 - 179 - 4826359 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
Bastian Voigt wrote: Hi *, for caching large autogenerated XML files, I have created a bytea table in my database so that the cached files can be used by multiple servers. There are about 500 rows and 10-20 Updates per minute on the table. The files stored in the bytea are anything from 10kB to 10MB. My PostgreSQL version is 8.0.13 on Gentoo Linux (x86) with PostGIS 1.2.0. For vacuum I use the pg_autovacuum daemon. It decided to vacuum my cache table about every 3 hours, the vacuum process takes 20-30 minutes (oops!) every time. Try vacuuming every 3 minutes and see what happens. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
Bastian Voigt wrote: Richard Huxton wrote: I was about to say that you can tune pg_autovacuum, but I just checked your original post and you're running 8.0.x - not sure about that one. The system catalog pg_autovacuum which allows finetuning autovacuum at table level was introduced in 8.1 :-( Hmm - thought it might have been :-( You'll have to check the documentation for that version to see if you can either: 1. exclude that table from pg_autovacuum 2. increase pg_autovacuum's sensitivity (1) seems to be impossible (correct me if I'm wrong..), so maybe I'll go for (2) ... No, the per-table stuff was via the system table. If not, and this table is the most active, it might be simpler just to run your own vacuum-ing from a cron job. Well, it is one of the most active, but there are others. pg_autovacuum seems to do a very good job, apart from this one table... Do you have any settings in your postgresql.conf? Failing that, you would have to poke around the source. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
My quick and dirty solution (Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13))
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
Re: My quick and dirty solution (Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13))
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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: My quick and dirty solution (Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13))
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
Bastian Voigt [EMAIL PROTECTED] writes: Now my big big problem is that the database gets really really slow during these 20 minutes and after the vacuum process is running for a short time, many transactions show state UPDATE waiting in the process list. In my Java application server I sometimes get tons of deadlock Exceptions (waiting on ShareLock blahblah). The web frontend gets nearly unusable, logging in takes more than 60 seconds, etc. etc. Hmm. That's a bit weird --- what are they waiting on exactly? Look in pg_locks to see what the situation is. A vacuum per se ought not be blocking any updates. Aside from the recommendation to make the vacuums happen more frequently instead of less so, you should experiment with vacuum_cost_delay and related parameters. The idea is to reduce vacuum's I/O load so that it doesn't hurt foreground response time. This means any individual vacuum will take longer, but you won't need to care. regards, tom lane ---(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
Re: My quick and dirty solution (Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.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
Re: My quick and dirty solution (Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13))
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Performance Problem with Vacuum of bytea table (PG 8.0.13)
OK, I'll give that a try. What about pg_autovacuum then? Is it a problem when two processes try to vacuum the same table in parallel? Or do I need to deactivate autovacuum altogether? Try vacuuming every 3 minutes and see what happens. (Sorry Richard, forgot to reply to the list!) -- Bastian Voigt Neumünstersche Straße 4 20251 Hamburg telefon +49 - 40 - 67957171 mobil +49 - 179 - 4826359 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
