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.
