Skip site navigation (1) Skip section navigation (2)

scale up (postgresql vs mssql)

From: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
To: pgsql-performance(at)postgresql(dot)org
Subject: scale up (postgresql vs mssql)
Date: 2012-04-11 22:11:45
Message-ID: (view raw or whole thread)
Lists: pgsql-performance

i had a stored procedure in ms-sql server. this stored procedure gets a
parameter (account-id), dose about 20 queries, fills some temporary tables,
and finally, returns a few result-sets. this stored procedure converted to
stored function in postgresql (9.1). the result-sets are being returned
using refcursors. this stored function is logically, almost identical to
the ms-sql stored procedure.  a LOT of work had been done to make
postgresql getting close to ms-sql speed (preparing temp-tables in advance,
using "analyze" in special places inside the stored function in order to
hint the optimizer that the temp-tables have very few records, thus
eliminating unnecessary and expansive hash-join, and a lot more..). after
all that, the stored function is running in a reasonable speed (normally
~60 milliseconds).

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

in a single execution postgresql may be less the twice slower than ms-sql,
but in 20 simultaneous clients, it's about 6 times worse. why is that?

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
would it be better to limit the number of connections to something like 4,
so that executions don't interrupt each other?

thanks in advance for any help!


pgsql-performance by date

Next:From: Steve CrawfordDate: 2012-04-12 15:47:58
Subject: Re: Linux machine aggressively clearing cache
Previous:From: Merlin MoncureDate: 2012-04-10 13:21:56
Subject: Re: about multiprocessingmassdata

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group