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

Re: scale up (postgresql vs mssql)

From: Andy Colson <andy(at)squeakycode(dot)net>
To: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: scale up (postgresql vs mssql)
Date: 2012-04-16 15:43:20
Message-ID: 4F8C3E18.2090205@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
On 4/15/2012 7:43 AM, Eyal Wilde wrote:
> hi,
>
> thanks a lot to all of you for your help.
>
> (i'm sorry i did not know how to reply to a certain message)
>
> i found that the best number of active connections is indeed 8-10. with
> 8-10 active connections postgresql did ~170 "account-id"s. this is still
> only half of what mssql did, but it now makes sence, considering that
> mssql works close to twice faster.
>
> i "played" with work_mem, shared_buffers, temp_buffers. i ran the tests
> with both of the following configurations, but no significant difference
> was found.
>
> thanks again for any more help.
>

We'd need to know if you are CPU bound or IO bound before we can help. 
Watch "vmstat 2" while the server is busy (and maybe post a few rows).


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

I think that may be a problem.  Treating PG like its mssql wont work 
well I'd bet.  things that work well in one database may not work well 
in another.

Instead of temp tables, have you tried derived tables?  Instead of:

insert into temptable select * from stuff;
select * from temptable;

try something like:

select * from (
   select * from stuff
) as subq

Another thing you might try to remove temp tables is to use views.

I dont know if it'll be faster, I'm just guessing.  Pulling out 
individual parts and running "explain analyze" on them will help you 
find the slow ones.  Finding which is faster (temp tables, derived 
tables, or views) might help you deiced what needs to be re-written.

Also, I'm not sure how well PG does "return multiple refcursors". there 
may be a lot of round trips from client to server to fetch next.  How 
hard would it be to re-do your single procedure that returns a bunch of 
refcursors into multiple procedures each returning one resultset?

Or maybe it would be something you can speed test to see if it would 
even make a difference.

-Andy

In response to

pgsql-performance by date

Next:From: Scott MarloweDate: 2012-04-16 15:45:57
Subject: Re: H800 + md1200 Performance problem
Previous:From: Cesar MartinDate: 2012-04-16 14:13:42
Subject: Re: H800 + md1200 Performance problem

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