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.
