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
