Re: performance problem - 10.000 databases - Mailing list pgsql-admin

Hi all

I was asking for help, a week ago. Performace tests took mi some more
time because of other things that I had to do.
The problem was: tune PostgreSQL to work with 10.000 databases.
Tom Lane (thanks) suggested solution: one database and 10.000 schemas.
From now I will write switch "database" - thats mean one database, but
different schema.
I don't know how much traffic there will be on production site, so I
tested 3.000 schemas and 400 clients simultaneously connected.
Each "database" has 4 tables (int,text,int) with 1.000 records and no
idexes.
Each client was doing:

10 x connect,"select * from table[rand(1-4)] where
number=[rand(1-1000)]",disconnect--(fetch one row)

5 x connect,"select * from table[rand(1-4)] where
position=[rand(1-5)]","update table[rand(1-4)] set text='some text',
position='integer[rand(1-5)] where number=[rand(1-1000)]",disconnect(
fetch 250 row/update one row)

1 x connect,"update table[rand(1-4)] set text='some text',
position='[rand(1-5)]'",disconnect(update 250 rows)

1 x connect,"select * from table1 where position in (select position
from table2 where number in (select number from table3))",disconnect

after that client switch to another "database", and start testing from
the beginning.

During this test I was changing some parameters in postgres, and send
kill -HUP ( pg_ctl reload ). I still don't know what settings will be
best for me, except "shared buffers", and some kernel and shell
settings.

I noticed that queries like: "\d table1" "\di" "\dp" are extremly slow,
when 400 clients is connected not even postgres user can't do that query
- why, how to improve that? Will it be a problem ?

Below, there are some results of this test, and postgresql.conf settings
I didn't change random_page_cost because on this test machine I've got
only one scsi disk for /data directory.

Postgres use 90% of the 4 cpus and takes 2GB RAM but load average
doesn't jump over 10-20, so it works better with lot of schemas, than
with a lot of db's

Maybe some suggestion of my postgresql.conf settings? And why queries
"\d" are so extremly slow?

Thank You
Marek

[PostgreSQL]
max_connections = 512
shared_buffers = 65536
max_fsm_relations = 10000
max_fsm_pages = 100000
max_locks_per_transaction = 512
wal_buffers = 32
sort_mem = 16384
vacuum_mem = 8192
effective_cache_size = 1000000
random_page_cost = 4

[kernel]
kernel/shmmni = 8192
kernel/shmall = 134217728
kernel/shmmax = 536870912

[test] times in sec.
(dbname) (conn. time)            (q = queries)
          (1 row)(250 rows)(triple join)(update 250 rows)(update 1000 rows)
test2374: connect:1 q_fast:4 q_med:0 q_slow:46 q_upd:0 q_upd_all:33
test2347: connect:1 q_fast:4 q_med:1 q_slow:48 q_upd:1 q_upd_all:32
test2351: connect:0 q_fast:4 q_med:2 q_slow:49 q_upd:0 q_upd_all:31
test2373: connect:0 q_fast:5 q_med:0 q_slow:46 q_upd:0 q_upd_all:25

[PostgreSQL]
max_connections = 512
shared_buffers = 4096
max_fsm_relations = 1000
max_fsm_pages = 10000
max_locks_per_transaction = 512
wal_buffers = 32
sort_mem = 16384
vacuum_mem = 8192
effective_cache_size = 1000000
random_page_cost = 4

[test]
test2430: connect:0 q_fast:2 q_med:1 q_slow:40 q_upd:0 q_upd_all:17
test2425: connect:0 q_fast:2 q_med:0 q_slow:45 q_upd:0 q_upd_all:20
test2434: connect:0 q_fast:2 q_med:0 q_slow:44 q_upd:0 q_upd_all:23
test2435: connect:1 q_fast:2 q_med:0 q_slow:50 q_upd:0 q_upd_all:18

[PostgreSQL]
max_fsm_relations = 2000
max_fsm_pages = 20000

[test]
test2171: connect:0 q_fast:3 q_med:1 q_slow:42 q_upd:1 q_upd_all:20
test2177: connect:1 q_fast:3 q_med:0 q_slow:43 q_upd:0 q_upd_all:21
test2166: connect:1 q_fast:3 q_med:1 q_slow:39 q_upd:0 q_upd_all:20
test2165: connect:1 q_fast:3 q_med:1 q_slow:42 q_upd:0 q_upd_all:24
test2162: connect:1 q_fast:3 q_med:1 q_slow:39 q_upd:1 q_upd_all:23