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-29 13:21:23
Message-ID: CAMiEbcj7LF56GiC8BTJbfku-TikaAqoAEY9kZtSfYKoj4Ntvew@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

hi, all.

well, i wondered why there is high rate of bo (blocks out). the procedure
is practically read-only during the whole test. although it's not strictly
read-only, because in a certain condition, there might be writing to a
certain table. but that condition can not be met during this test.

so, i created a ramdisk:
mkfs -q /dev/ram2 100000
mkdir -p /ram4
mount /dev/ram2 /ram4
df -H | grep /ram4

and then:
CREATE TABLESPACE pgram4 OWNER postgres LOCATION '/ram4';

and in postgresql.conf i configured:
temp_tablespaces = 'pgram4'

now, i believe, all the temp-table were in RAM.
vmstat showed:
r b swpd free buff cache si so bi bo in cs us sy id wa st
6 0 0 5916720 69488 1202668 0 0 0 3386 1902 1765 25 3 72 1 0
9 1 0 5907728 69532 1204212 0 0 0 1392 5375 4510 88 8 3 1 0
7 0 0 5886584 69672 1205096 0 0 0 1472 5278 4520 88 10 2 0 0
8 0 0 5877384 69688 1206384 0 0 0 1364 5312 4522 89 8 2 1 0
8 0 0 5869332 69748 1207188 0 0 0 1296 5285 4437 88 8 3 1 0
6 1 0 5854404 69852 1208776 0 0 0 2955 5333 4518 88 9 2 0 0

10 times less bo (blocks out)
5 times less wa (percentage of time spent by cpu waiting to IO)
2 times less b (wait Queue – Process which are waiting for I/O)

the overall test result was (just?) ~15% better...

when i created the ramdisk with mkfs.ext4 (instead of the default ext2),
the performance was the same (~15% better), but vmstat output looked much
the same as before (without the ramdisk) !?? why is that?

as i mentioned, the procedure is practically read-only. shouldn't i expect
bo (blocks out) to be ~0? after forcing temp-tables to be in the RAM, what
other reasons may be the cause for bo (blocks out)?

i see no point pasting the whole procedure here, since it's very long. the
general course of the procedure is:
create temp-tables if they are not exist (practically, they do exist)
do a lot of: insert into temp-table select from table
and : insert into temp-table select from table join temp-table....
after finished insert into temp-table: analyze temp-table (this was the
only way the optimizer behaved properly)
finally, open refcursors of select from temp-tables

Thanks again.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Rich 2012-04-29 15:10:22 Re: NOT EXISTS or LEFT JOIN which one is better?
Previous Message AI Rumman 2012-04-29 09:27:19 NOT EXISTS or LEFT JOIN which one is better?