Bad performance for a 3000 rows table updated permanently - Mailing list pgsql-novice

Hi,

Hardware: Biprocessor PIII 1.3GHz and SCSI RAID

database=> SELECT VERSION();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4

I have a table with 3000 rows (this number is almost constant, and
never decrease), where columns are declared as follow:

table "data"
---------------------------------------------------------------------
field1  SERIAL
field2  TEXT             (average of 17 char.) (500 distincts values)
field3  TEXT             (average of 4 char.) (25 distincts values)
field4  TEXT             (average of 5 char.) (5 distincts values)
field5  TEXT             (average of 600 char., up to 8K, may be NULL)
field6  TIMESTAMP W/O TZ
field7  TIMESTAMP W/O TZ
field8  TIMESTAMP W/O TZ
field9  TEXT             (15 char.)
field10 TIMESTAMP W/O TZ

And some indexes are defined:

idx_1 ON data(field2,field3)
idx_2 ON data(field4)
idx_3 ON data(field7)
idx_4 ON data(field10)

Note: PostgreSQL is the only process actively used, and table "data"
      is the only "dynamic" table (only one other table exists and is
      used for INSERT a few times per days.) Very few query (SELECT)
      are performed for now, until I discover why the performance are
      bad.

A stored procedure (PL/pgSQL) is called with an average of 14 times
per seconds and, 99% of the time, this result on one SELECT followed
by an UPDATE on table "data".

The SELECT check if the pair (field2,field3) exists, then UPDATE the
(unique) matching row from procedure parameters.

While using RRD to graph average time taken for 3 consecutives request

    SELECT COUNT(*) FROM data;

I obtain the following duration:

- immediatly after a

    VACUUM VERBOSE FULL data;

  the request take 50ms

- after 6 hours, 1s

- after 8 hours, 2s

- after 14 hours, 4s

- after 18 hours, 20s

while the number of row doesn't grow.

My need are to obtain very quick response to allow 10-20 users to
access simultaneously the database for various read-only queries. And
50ms for a SELECT is really great ! But 20s is not acceptable (in fact
more than 2-3s is already too high..) All of this need to be available
24/7.

In fact, I don't understand why the count "unused" grows permanently
without the place being reused. Here is an example of VACUUM FULL on
the table data (after 18 hours without running any VACUUM):

database=> VACUUM FULL VERBOSE data;
INFO:  --Relation public.data--
INFO: Pages 40857: Changed 67, reaped 40723, Empty 0, New 0; Tup 3058:
  Vac 522857, Keep/VTL 3/3, UnUsed 323596, MinLen 148, MaxLen 2024;
  Re-using: Free/Avail. Space 329059648/329056556; EndEmpty/Avail. Pages
  0/40811.
        CPU 2.86s/0.08u sec elapsed 8.04 sec.
INFO:  Index idx_2: Pages 141310; Tuples 3058: Deleted 522857.
        CPU 10.05s/2.95u sec elapsed 152.18 sec.
INFO:  Index idx_1: Pages 19395; Tuples 3058: Deleted 522857.
        CPU 2.31s/2.70u sec elapsed 96.05 sec.
INFO:  Index idx_4: Pages 126919; Tuples 3058: Deleted 522857.
        CPU 8.67s/3.21u sec elapsed 50.46 sec.
INFO:  Index idx_3: Pages 8127; Tuples 3058: Deleted 522857.
        CPU 1.01s/2.86u sec elapsed 32.62 sec.
INFO:  Rel data: Pages: 40857 --> 180; Tuple(s) moved: 2948.
        CPU 8.84s/5.10u sec elapsed 184.16 sec.
INFO:  Index idx_2: Pages 141321; Tuples 3058: Deleted 2948.
        CPU 9.78s/0.30u sec elapsed 137.80 sec.
INFO:  Index idx_1: Pages 19395; Tuples 3058: Deleted 2948.
        CPU 1.91s/0.39u sec elapsed 78.71 sec.
INFO:  Index idx_4: Pages 126927; Tuples 3058: Deleted 2948.
        CPU 8.41s/0.09u sec elapsed 41.62 sec.
INFO:  Index idx_3: Pages 8127; Tuples 3058: Deleted 2948.
        CPU 0.81s/0.13u sec elapsed 20.91 sec.
INFO:  --Relation pg_toast.pg_toast_301706--
INFO: Pages 76: Changed 38, reaped 75, Empty 0, New 0; Tup 4: Vac 178,
  Keep/VTL 0/0, UnUsed 189, MinLen 78, MaxLen 2034; Re-using:
  Free/Avail. Space 615164/615164; EndEmpty/Avail. Pages 0/76.
        CPU 0.01s/0.00u sec elapsed 0.14 sec.
INFO:  Index pg_toast_301706_index: Pages 4; Tuples 4: Deleted 178.
        CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Rel pg_toast_301706: Pages: 76 --> 1; Tuple(s) moved: 4.
        CPU 0.01s/0.00u sec elapsed 0.08 sec.
INFO:  Index pg_toast_301706_index: Pages 4; Tuples 4: Deleted 4.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 803886.56 ms
database=>

Almost 14 minutes to perform the task !

Running VACUUM data; or VACUUM ANALYSE data; doesn't help for
performance.

I don't know what to do:

- perform VACUUM FULL more frequently (every hour for example) (to
  lower the time the table is LOCKed),

- perform VACUUM FULL only every 24 or 48h, or even more, and queue
  request in my application until completed,

- optimize field type perharps..,

- or sometimes else !

Any idea ?

--
Frédéric Jolliton