performance tuning

Поиск
Список
Период
Сортировка
От Pecsenyanszky Istvan
Тема performance tuning
Дата
Msg-id Pine.SGI.3.96.980606161959.20903H-100000@visio.c3.hu
обсуждение исходный текст
Список 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


В списке pgsql-admin по дате отправления:

Предыдущее
От: Marin D
Дата:
Сообщение: [GENERAL] European/American dates (fwd)
Следующее
От: Gabor
Дата:
Сообщение: database security