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-18 19:47:49
Message-ID: 4F8F1A65.20206@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance
On 4/18/2012 2:32 AM, Eyal Wilde wrote:
> hi all,
>
> i ran vmstat during the test :
>
> [yb(at)centos08 ~]$ vmstat 1 15
> procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu-----
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
>  2  0      0 6118620 160572 1120288    0    0     0 13792 4548 3054 63 
  6 25  6  0
> the temp-tables normally don't populate more then 10 rows. they are
> being created in advanced. we don't drop them, we use ON COMMIT DELETE
> ROWS. i believe temp-tables are in the RAM, so no disk-i/o, right? and
> also: no writing to the system catalogs, right?

Temp tables are not 100% ram, they might spill to disk. The vmstat shows 
there is disk io.  The BO column (blocks out) shows you are writing to 
disk.  And you have wait time (which means one or more of the cpus is 
stopped waiting for disk).

I don't know if the disk io is because of the temp tables (I've never 
used them myself), or something else (stats, vacuum, logs, other sql, etc).

I'd bet, though, that a derived table would be faster than "create temp 
table...; insert into temp .... ; select .. from temp;"

Of course it may not be that much faster... and it might require a lot 
of code change.  Might be worth a quick benchmark though.

>
> about returning multiple refcursors, we checked this issue in the past,
> and we concluded that returning many small refcursors (all have the same
> structure), is faster than returning 1 big refcursor. dose it sound
> wired (maybe it worth more tests)?  that's why we took that path.
>

No, if you tried it out, I'd stick with what you have.  I've never used 
them myself, so I was just wondering aloud.

-Andy

In response to

pgsql-performance by date

Next:From: Merlin MoncureDate: 2012-04-18 20:13:47
Subject: Re: scale up (postgresql vs mssql)
Previous:From: Strange, John WDate: 2012-04-18 16:54:22
Subject: Re: Random performance hit, unknown cause.

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