Message-ID: <46569E6A.2060509@bastian-voigt.de>
Date: Fri, 25 May 2007 10:29:30 +0200
From: Bastian Voigt <post@bastian-voigt.de>
User-Agent: Thunderbird 2.0.0.0 (X11/20070502)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Performance Problem with Vacuum of bytea table (PG 8.0.13)
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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