Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore

On Thu, Sep 3, 2015 at 5:35 PM, David Rowley
<david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> My test cases are:

Note that my text caching and unsigned integer comparison patches have
moved the baseline down quite noticeably. I think that my mobile
processor out-performs the Xeon you used for this, which seems a
little odd even taken the change in baseline performance into account.

> set work_mem ='1GB';
> create table t1 as select md5(random()::text) from
> generate_series(1,10000000);
>
> Times are in milliseconds. Median and average over 10 runs.
>
> Test 1
> select count(distinct md5) from t1;
>
> Master Patched Median 10,965.77 10,986.30 (99.81%) Average
> 10,983.63 11,013.55 (99.73%)

> Are you seeing any speedup from any of these on your hardware?

I gather that 10,965.77 here means 10,965 milliseconds, since that
roughly matches what I get.

For the sake of simplicity, I will focus on your test 1 as a baseline.
Note that I ran VACUUM FREEZE before any testing was performed, just
in case.

On my laptop:

postgres=# \dt+ t1
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+-------+--------+-------------
public | t1 | table | pg | 651 MB |
(1 row)

Times for "Test 1", "select count(distinct md5) from t1":

Patch:

Time: 10076.870 ms
Time: 10094.873 ms
Time: 10125.253 ms <-- median
Time: 10222.042 ms
Time: 10269.247 ms

Master:

Time: 10641.142 ms
Time: 10706.181 ms
Time: 10708.860 ms < -- median
Time: 10725.426 ms
Time: 10781.398 ms

So, to answer your question: Yes, I can see a benefit for this query
on my test hardware (laptop), although it is not spectacular. It may
still be quite worthwhile.

I attach a revised version of the patch tested here, following
feedback from Andres. This should not make any difference to the
performance.

It's worth considering that for some (possibly legitimate) reason, the
built-in function call is ignored by your compiler, since GCC has
license to do that. You might try this on both master and patched
builds:

~/postgresql/src/backend/utils/sort$ gdb -batch -ex 'file tuplesort.o'
-ex 'disassemble tuplesort_gettuple_common' > prefetch_disassembly.txt

Then diff the file prefetch_disassembly.txt for each build to see what
the differences are in practice. Consider an extract of the output on
my system:

...
0x00000000000028ee <+926>: callq 0x28f3 <tuplesort_gettuple_common+931>
0x00000000000028f3 <+931>: nopl 0x0(%rax,%rax,1)
0x00000000000028f8 <+936>: sub $0x1,%eax
0x00000000000028fb <+939>: test %eax,%eax
0x00000000000028fd <+941>: mov %eax,0xd0(%rdi)
0x0000000000002903 <+947>: jne 0x25ce <tuplesort_gettuple_common+126>
0x0000000000002909 <+953>: jmpq 0x2710 <tuplesort_gettuple_common+448>
0x000000000000290e <+958>: xchg %ax,%ax
0x0000000000002910 <+960>: mov 0x58(%rdi),%rsi
0x0000000000002914 <+964>: lea (%rax,%rax,2),%rax
0x0000000000002918 <+968>: lea (%rsi,%rax,8),%rax
0x000000000000291c <+972>: mov 0x30(%rax),%rax
0x0000000000002920 <+976>: prefetchnta (%rax)
0x0000000000002923 <+979>: mov $0x1,%eax
0x0000000000002928 <+984>: jmpq 0x2712 <tuplesort_gettuple_common+450>
0x000000000000292d <+989>: nopl (%rax)
...

Notably, there is a prefetchnta instruction here.

Note that I'm going away on vacation in about a week. I wanted to give
people feedback on various things before then, since it was overdue.
FYI, after Thursday I will be very unlikely to answer e-mail for a
couple of weeks.

--
Peter Geoghegan