From: Bastian Voigt <post@bastian-voigt.de>
To: pgsql-performance@postgresql.org
Subject: Performance Problem with Vacuum of bytea table (PG 8.0.13)
Date: Fri, 25 May 2007 10:29:30 +0200
Message-ID: <46569E6A.2060509@bastian-voigt.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: post@bastian-voigt.de
Subject: Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)
In-Reply-To: <46569E6A.2060509@bastian-voigt.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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