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
