Re: scale up (postgresql vs mssql)

On 4/18/2012 2:32 AM, Eyal Wilde wrote:
> hi all,
>
> i ran vmstat during the test :
>
> [yb@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