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

Re: scale up (postgresql vs mssql)

From: Eyal Wilde <eyal(at)impactsoft(dot)co(dot)il>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: scale up (postgresql vs mssql)
Date: 2012-04-18 07:32:07
Message-ID: CAMiEbchK65f7AmtZLSO0SuR2JZr3+Q9GaxyRr+NEV+VdW71p5w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
 0  0      0 6131400 160556 1115792    0    0     1    12   22   17  0  0
100  0  0
 0  0      0 6131124 160556 1115800    0    0     0   532  540  360  1  0
99  0  0
 5  1      0 6127852 160556 1116048    0    0     0  3352 1613 1162 18  1
80  1  0
 7  0      0 6122984 160556 1117312    0    0     0 14608 5408 3703 86  7
 6  1  0
 8  0      0 6121372 160556 1117968    0    0     0 13424 5434 3741 86  7
 5  2  0
 7  1      0 6120504 160556 1118952    0    0     0 13616 5296 3546 86  7
 5  2  0
 7  0      0 6119528 160572 1119728    0    0     0 13836 5494 3597 86  7
 4  2  0
 6  1      0 6118744 160572 1120408    0    0     0 15296 5552 3869 89  8
 3  1  0
 2  0      0 6118620 160572 1120288    0    0     0 13792 4548 3054 63  6
25  6  0
 0  0      0 6118620 160572 1120392    0    0     0  3552 1090  716  8  1
88  3  0
 0  0      0 6118736 160572 1120392    0    0     0  1136  787  498  1  0
98  1  0
 0  0      0 6118868 160580 1120400    0    0     0    28  348  324  1  0
99  0  0
 0  0      0 6118992 160580 1120440    0    0     0   380  405  347  1  0
99  1  0
 0  0      0 6118868 160580 1120440    0    0     0  1544  468  320  1  0
100  0  0
 0  0      0 6118720 160580 1120440    0    0     0     0  382  335  0  0
99  0  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?

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.

about having multiple procedures each returning one resultset: it's too
much code rewrite at the web-server's code.

the disk system is a built-in intel fake-raid, configured as raid0. i do a
dual-boot, so both windows and centos are on the same hardware.

Thanks again for any more help.

Responses

pgsql-performance by date

Next:From: Strange, John WDate: 2012-04-18 16:54:22
Subject: Re: Random performance hit, unknown cause.
Previous:From: Віталій ТимчишинDate: 2012-04-18 06:51:19
Subject: Re: bad planning with 75% effective_cache_size

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