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

> Heck, you're already pushing the performance envelope with 3,000 users,
> might as well go for the faster of the two and you'll have one less
> scheduled upgrade ahead of you.
>
> When do you need to go live?  If it's >1 month, then I'd definitely
> recommend 7.4.


heh... ;)

PostgreSQL 7.4 is so fu*#$%^ fast!!!

Unbelievable...

I've made test as usual ( some post earlier ) 3.000 schemas and 300
simultaneously connected. I've tuned postgresql.conf with my friend who
is our sql guru ( but he runs oracle usualy )
7.4 is so fast, that sometimes clients ( laptop with full X11
workstation and celeron 700 ) could not keep up forking perl script to
test new "database" ;)

my conf:
-----------------------
max_connections = 512
shared_buffers = 32768

sort_mem = 2048
vacuum_mem = 20480

max_fsm_pages = 589824
max_fsm_relations = 32768

fsync = false
wal_sync_method = fsync
wal_buffers = 1024

checkpoint_segments = 4
checkpoint_timeout = 1800
checkpoint_warning = 30
commit_delay = 10000
commit_siblings = 2

effective_cache_size = 131072
random_page_cost = 4

log_connections = true
log_duration = true
log_pid = true
log_statement = true
log_timestamp = true

search_path = '$user'
max_locks_per_transaction = 512
--------------------------------

from this test 4 tables(int,text,int) 1000 rows in each, no indexes

------------------------------------------------------------------
[test] times in sec.
(dbname) (conn. time)                   (q = queries)
              (1row)(250rows)(tripleJoin)(update250rows)(update1000rows)
test2291: connect:1 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:9
test2260: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:10
test2274: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:8
test2296: connect:0 q_fast:1 q_med:0 q_slow:6 q_upd:0 q_upd_all:6
test2283: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:8
test2302: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:8
test2290: connect:0 q_fast:1 q_med:0 q_slow:3 q_upd:0 q_upd_all:8
test2287: connect:0 q_fast:1 q_med:0 q_slow:6 q_upd:0 q_upd_all:6
test2267: connect:0 q_fast:1 q_med:0 q_slow:1 q_upd:0 q_upd_all:11
-----------------------------------------------------------------

the "\d" queries works under this load just fine!

Now, I just have to modify phpPgAdmin (it's for users to modify their
own "database" ), I don't know why when I select to database it's try to
fetch all tablenames from all schemas. From log:

---------------------------------------------------------------------
2003-11-06 22:53:06 [8880] LOG:  statement: SET SEARCH_PATH TO "test998"
2003-11-06 22:53:06 [8880] LOG:  duration: 1.207 ms
2003-11-06 22:53:06 [8880] LOG:  statement: SELECT tablename, tableowner
FROM pg_catalog.pg_tables
                                WHERE schemaname='test998' ORDER BY
tablename
2003-11-06 22:53:06 [8880] LOG:  duration: 31.005 ms
2003-11-06 22:53:06 [8880] LOG:  statement: SET SEARCH_PATH TO "test999"
2003-11-06 22:53:06 [8880] LOG:  duration: 1.202 ms
2003-11-06 22:53:06 [8880] LOG:  statement: SELECT tablename, tableowner
FROM pg_catalog.pg_tables
                                WHERE schemaname='test999' ORDER BY
tablename
2003-11-06 22:53:06 [8880] LOG:  duration: 30.604 ms
----------------------------------------------------------------

I should go alive with this hosting at the end of the month, but at the
beginning we shouldn't have many customer, so we decide to try v7.4 in
beta now, and wait for official release.


... And my management says, that there is no good support for Open
Source, heh... ;)))


thanks all
Marek