Thread

1. Re: scale up (postgresql vs mssql)

   Eyal Wilde <eyal@impactsoft.co.il> — 2012-04-15T12:43:27Z

   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.
   
   
   "version";"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc
   (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"
   "bytea_output";"escape"
   "client_encoding";"UNICODE"
   "lc_collate";"en_US.UTF-8"
   "lc_ctype";"en_US.UTF-8"
   "listen_addresses";"*"
   "log_destination";"stderr"
   "log_line_prefix";"%t "
   "logging_collector";"on"
   "max_connections";"100"
    "max_stack_depth";"2MB"
   "server_encoding";"UTF8"
   "shared_buffers";"32MB"
   "TimeZone";"Israel"
   "wal_buffers";"1MB"
   
   
   
   "version";"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc
   (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"
   "bytea_output";"escape"
   "client_encoding";"UNICODE"
   "lc_collate";"en_US.UTF-8"
   "lc_ctype";"en_US.UTF-8"
   "listen_addresses";"*"
   "log_destination";"stderr"
   "log_line_prefix";"%t "
   "logging_collector";"on"
   "max_connections";"100"
    "max_stack_depth";"2MB"
   "port";"5432"
   "server_encoding";"UTF8"
   "shared_buffers";"3GB"
   "temp_buffers";"64MB"
   "TimeZone";"Israel"
   "wal_buffers";"16MB"
   "work_mem";"20MB"
   

2. Re: scale up (postgresql vs mssql)

   Andy Colson <andy@squeakycode.net> — 2012-04-16T15:43:20Z

   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
   
   

3. Re: scale up (postgresql vs mssql)

   Richard Huxton <dev@archonet.com> — 2012-04-16T15:55:46Z

   On 15/04/12 13:43, Eyal Wilde wrote:
   >
   > "version";"PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc
   > (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit"
   
   You've probably checked this, but if not it's worth making sure your 
   disk I/O is roughly equivalent for the two operating-systems. It might 
   be poor drivers on the CentOs system.
   
   Do you have two equivalent machines, or are you dual-booting?
   
   -- 
      Richard Huxton
      Archonet Ltd