pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance Problem with Vacuum of bytea table (PG 8.0.13)
8+ messages / 4 participants
[nested] [flat]
* Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 08:29 Bastian Voigt <post@bastian-voigt.de>
0 siblings, 3 replies; 8+ messages in thread
From: Bastian Voigt @ 2007-05-25 08:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 10:33 Richard Huxton <dev@archonet.com>
parent: Bastian Voigt <post@bastian-voigt.de>
2 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2007-05-25 10:33 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 10:48 Bastian Voigt <post@bastian-voigt.de>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bastian Voigt @ 2007-05-25 10:48 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 11:00 Richard Huxton <dev@archonet.com>
parent: Bastian Voigt <post@bastian-voigt.de>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2007-05-25 11:00 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 11:10 Bastian Voigt <post@bastian-voigt.de>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bastian Voigt @ 2007-05-25 11:10 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 11:51 Richard Huxton <dev@archonet.com>
parent: Bastian Voigt <post@bastian-voigt.de>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2007-05-25 11:51 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-25 14:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bastian Voigt <post@bastian-voigt.de>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2007-05-25 14:18 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
Bastian Voigt <post@bastian-voigt.de> 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
@ 2007-05-27 16:08 Jim C. Nasby <decibel@decibel.org>
parent: Bastian Voigt <post@bastian-voigt.de>
2 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2007-05-27 16:08 UTC (permalink / raw)
To: Bastian Voigt <post@bastian-voigt.de>; +Cc: pgsql-performance
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 decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-05-27 16:08 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-25 08:29 Performance Problem with Vacuum of bytea table (PG 8.0.13) Bastian Voigt <post@bastian-voigt.de>
2007-05-25 10:33 ` Richard Huxton <dev@archonet.com>
2007-05-25 10:48 ` Bastian Voigt <post@bastian-voigt.de>
2007-05-25 11:00 ` Richard Huxton <dev@archonet.com>
2007-05-25 11:10 ` Bastian Voigt <post@bastian-voigt.de>
2007-05-25 11:51 ` Richard Huxton <dev@archonet.com>
2007-05-25 14:18 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-05-27 16:08 ` Jim C. Nasby <decibel@decibel.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