Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore - Mailing list pgsql-hackers
From | Peter Geoghegan |
---|---|
Subject | Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore |
Date | |
Msg-id | CAM3SWZRMY=hZWRvCGqpGkiHyOYLSp6g3jJqgC9ccYW5VJ=L_vw@mail.gmail.com Whole thread Raw |
In response to | Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore (David Rowley <david.rowley@2ndquadrant.com>) |
Responses |
Re: Memory prefetching while sequentially fetching from SortTuple
array, tuplestore
(Amir Rohan <amir.rohan@zoho.com>)
Re: Memory prefetching while sequentially fetching from SortTuple array, tuplestore (David Rowley <david.rowley@2ndquadrant.com>) |
List | pgsql-hackers |
On Thu, Sep 3, 2015 at 5:35 PM, David Rowley <david.rowley@2ndquadrant.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
Attachment
pgsql-hackers by date: