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

Reply via email to