Eyal Wilde <eyal@impactsoft.co.il> wrote:
> now, i run a test that simulates 20 simultaneous clients, asking
> for "account-id" randomly. once a client get a result, it
> immediately asks for another one. the test last 5 seconds. i use
> a connection pool (with Tomcat web-server). the pool is
> automatically increased to ~20 connections (as expected). the
> result is postgresql dose ~60 "account-id"s, whereas ms-sql dose
> ~330 "account-id"s. postgresql shows that each "account-id" took
> about 400-1000 msec ,which is so much slower than the ~60 msec of
> a single execution.
> the hardware is one 4-core xeon. 8GB of ram. the database size is
> just a few GB's. centos-6.2.
>
> do you think the fact that postgresql use a process per connection
> (instead of multi-threading) is inherently a weakness of
> postgrsql, regarding scale-up?
I doubt that has much to do with anything.
> would it be better to limit the number of connections to something
> like 4, so that executions don't interrupt each other?
The point where a lot of workloads hit optimal performance is with
the number of active connections limited to ((core count * 2) +
effective spindle count). Determining "active spindle count can be
tricky (for example it is zero in a fully-cached read-only
workload), so it takes more information than you've given us to know
exactly where the optimal point might be, but if it's a single
drive, then if you have 4 cores (not 2 cores with hyperthreading)
you might want to limit your connection pool to somewhere in the 8
to 10 range. You generally should configure a connection pool to be
transaction based, with a request to start a transaction while all
connections are busy causing the request queue, with completion of a
transaction causing it to pull a request for the queue, if
available. I'm pretty sure that Tomcat's pool supports this.
Could you describe your disk system and show us the result of
running the query?:
http://wiki.postgresql.org/wiki/Server_Configuration
-Kevin