Re: PostgreSQL Tuning Results

On Wed, 12 Feb 2003, [ISO-8859-1] Hans-J\xFCrgen Sch\xF6nig wrote:

> Be careful with sort_mem - this might lead to VERY unexpected results. I
> did some testing on my good old Athlon 500 with a brand new IBM 120 Gigs
> HDD. Reducing the sort_mem gave me significantly faster results when
> sorting/indexing 20.000.000 randon rows.

Actually, the results are completely expected once you know what's
exactly is going on. I found it weird that my sorts were also slowing
down with more sort memory until Tom or Bruce or someone pointed out to
me that my stats said my sorts were swapping.

If I'm understanding this correctly, this basically meant that my sort
results would start hitting disk becuase they were being paged out to
swap space, but then once the block was sorted, it would be read in
again from disk, and then written out to disk again (in a different
place), creating a lot more I/O than was really necessary.

This strikes me, too, as another area where mmap might allow the system
to do a better job with less tuning. Basically, the sort is getting
split into a bunch of smaller chunks, each of which is individually
sorted, and then you merge at the end, right? So if all those individual
chunks were mmaped, the system could deal with paging them out if and
when necessary, and for the sorts you do before the merge, you could
mlock() the area that you're currently sorting to make sure that it
doesn't thrash.

If the VM system accepts hints, you might also get some further
optimizations because you can tell it (using madvise()) when you're
doing random versus sequential access on a chunk of memory.

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