Hi all
We are building hosting with apache + php ( our own mod_virtual module )
with about 10.000 wirtul domains + PostgreSQL.
PostgreSQL is on a different machine ( 2 x intel xeon 2.4GHz 1GB RAM
scsi raid 1+0 )
I've made some test's - 3000 databases and 400 clients connected at same
time. These clients was doing on each database set of query 10 times,
and then connect to next database. These queries:
select * from table1 where number='$i'
update table2 set some_text='int(rand(5))'
select * from table1 where position in (select position from table2
where number in (select number from table3))
Each database has four tables (int,text,int) with 1000 records.
Postgres is taking all memory and all processor ( 4CPU with Hyper
Threading )
The first two queries has time duration 0 to 10 sec
Third query has 15-70 sec.
But my problem is that when I hit command:
psql -h 127.0.0.1 dbname dbuser
I'm waiting about 3-5 sec to enter psql monitor, so every new connection
from apache will wait about 3-5 sec to put query to server. Thats a very
long time...
4 sec. to connect to server and 4 sec. to process a query.
Why this time to connect to server is so long ???
I could made persistent connection, but with 10.000 clients it will kill
the server.
Has any one idea how to tune postgres, to accept connection faster?
Maybe some others settings to speed up server ?
My settings:
PostgreSQL:
max_connections = 512
shared_buffers = 8192
max_fsm_relations = 10000
max_fsm_pages = 100000
max_locks_per_transaction = 512
wal_buffers = 32
sort_mem = 327681
vacuum_mem = 8192
fsync = true
effective_cache_size = 1000000
log_connections = true
log_pid = true
log_statement = true
log_duration = true
log_timestamp = true
Kernel:
kernel.shmmni = 8192
kernel.shmall = 134217728
kernel.shmmax = 536870912
RLIMIT_NPROC=1000
greetings
Marek