performance tuning

From: Pecsenyanszky Istvan <pisti(at)c3(dot)hu>
To: pgsql-admin(at)postgresql(dot)org
Subject: performance tuning
Date: 1998-06-06 14:22:53
Message-ID: Pine.SGI.3.96.980606161959.20903H-100000@visio.c3.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

We have a rather simple database with 2 tables and 2 indices. The tables
contain char, int, and bool type fields, and both has ~60000 records now.

-rw------- 1 postgres postgres 3727360 Jun 5 11:45 mail
-rw------- 1 postgres postgres 1843200 Jun 4 02:45 mail_name_key
-rw------- 1 postgres postgres 9977856 Jun 5 11:45 pers
-rw------- 1 postgres postgres 1835008 Jun 4 02:45 pers_name_key

We would like to reach at least 15-20 query per second, 95 percent
SELECT id FROM mail WHERE name='name' queries. The rest is SELECT pers or
UPDATE in one of the two tables.

When the number of concurrent queries is 30 or higher, the postgres
answers very slowly, and it writes

NOTICE: SIAssignBackendId: discarding tag 2147339305
FATAL 1: Backend cache invalidation initialization failed

messages to the log.

If the number of concurrencies are 10, then everything goes fine, but the
number of queries/sec are 8. Is this the maximum loadability of postgres?

Is the any fine tuning possibilities for higher performance?

Some other questions:

1. How often the database has to be vacuumed? (Our database is vacuumed 3
times a day now.)
2. Why select * much more fast than select id? (before vacuum)
(`id' is a field in the table)

Postmaster runs with options: postmaster -B 468 -i -o -F.

Backend system: FreeBSD-2.2.6R, PII-400MHz, 64MB, UW SCSI RAID
Postgres version: 6.3.2

Thanks,
Pisti

Browse pgsql-admin by date

  From Date Subject
Next Message Gabor 1998-06-06 16:07:50 database security
Previous Message Marin D 1998-06-06 09:54:24 [GENERAL] European/American dates (fwd)