From: Andy Colson <andy@squeakycode.net>
To: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-performance@postgresql.org
Subject: Re: scale up (postgresql vs mssql)
Date: Wed, 18 Apr 2012 14:47:49 -0500
Message-ID: <4F8F1A65.20206@squeakycode.net> (raw)
In-Reply-To: <CAMiEbchK65f7AmtZLSO0SuR2JZr3+Q9GaxyRr+NEV+VdW71p5w@mail.gmail.com>
References: <CAMiEbchK65f7AmtZLSO0SuR2JZr3+Q9GaxyRr+NEV+VdW71p5w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, eyal@impactsoft.co.il
Subject: Re: scale up (postgresql vs mssql)
In-Reply-To: <4F8F1A65.20206@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox