sort_mem sizing (Non-linear Performance)

On Thu, 30 May 2002, Tom Lane wrote:

> Curt Sampson <cjs(at)cynic(dot)net> writes:
> > I'm noticing that performance in creating a particular index, and
> > also a little bit in a simple query, seems somewhat non-linear,
>
> Btree index build is primarily a sort, so cannot have better than
> O(n*log(n)) performance for random data. Not sure why you'd expect
> linearity.

Not exactly "expect." More "wish," really.

> Increasing SORT_MEM would help the constant factor, however...

Thanks for the tip. I tried that, though not on the monster 500m
row table yet. On the 100m row table, I get some interesting results.

First I confirmed that with the default sortmem = 512, I get the
results I previously got, about an hour:

! system usage stats:
! 3715.756060 elapsed 1975.705112 user 218.771892 system sec
! [1975.717191 user 218.771892 sys total]
! 29/535 [29/538] filesystem blocks in/out
! 124/1949 [124/2281] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/5] messages rcvd/sent
! 98271/335134 [98271/335139] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 636965 read, 9 written, buffer hit rate = 0.05%
! Local blocks: 218962 read, 218962 written, buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written

Next I tried to bump the sortmem up to 256 MB, half the physical RAM
of the machine. I found out the hard way that the back-end doing the
indexing will grow to something over three times the size of sortmem,
and proceeded (slowly) to extricate myself from swap hell.

Next try, 128 MB. This time the process was about 400 MB in size
(350 MB resident), and it did indeed shave more than 20% off my
sort time, clocking in at about 48 1/2 minutes.

! system usage stats:
! 2897.031560 elapsed 2396.215835 user 168.270137 system sec
! [2396.215835 user 168.285445 sys total]
! 2/678 [2/680] filesystem blocks in/out
! 165926/91843 [165926/92159] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/5] messages rcvd/sent
! 166718/225004 [166750/225010] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 636979 read, 9 written, buffer hit rate = 0.05%
! Local blocks: 218962 read, 218962 written, buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written

But I decided I couldn't spare that much memory for a database doing
real work, too, and backed down to 32 MB. That got the process down to
115 MB (100 MB resident), but oddly enough sped it up even more, shaving
the sort time by more than 35% from the original. (The new time was
about 40 minutes.)

DEBUG: QUERY STATISTICS
! system usage stats:
! 2386.940418 elapsed 2155.223379 user 178.008478 system sec
! [2155.233638 user 178.008478 sys total]
! 0/592 [0/594] filesystem blocks in/out
! 100/25113 [100/25399] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/5] messages rcvd/sent
! 2376/274787 [2376/274793] voluntary/involuntary context switches
! postgres usage stats:
! Shared blocks: 636969 read, 10 written, buffer hit rate = 0.05%
! Local blocks: 218962 read, 218962 written, buffer hit rate = 0.00%
! Direct blocks: 0 read, 0 written

So what's up with that, do you think?

Anyway, over the weekend I might have time to try rebuilding an
index on the 500m row table a couple of times with different sortmem
sizes to see how well that works.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC