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: CAMiEbcj+6ViLiAX0C3+VzhVGt5ZedMh8KCE1JTgXgEVUq+GeNw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
hi,

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
execution.

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
scale-up?
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!

Responses

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-2014 The PostgreSQL Global Development Group