Re: scale up (postgresql vs mssql) - Mailing list pgsql-performance

From Kevin Grittner
Subject Re: scale up (postgresql vs mssql)
Date
Msg-id 4F87F4710200002500046EDE@gw.wicourts.gov
Whole thread Raw
In response to scale up (postgresql vs mssql)  (Eyal Wilde <eyal@impactsoft.co.il>)
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: scale up (postgresql vs mssql)
Next
From: Claudio Freire
Date:
Subject: Re: scale up (postgresql vs mssql)