Re: ANALYZE sampling is too good

On 10/12/13 15:17, Mark Kirkwood wrote:
> On 10/12/13 15:11, Mark Kirkwood wrote:
>> On 10/12/13 15:04, Mark Kirkwood wrote:
>>> On 10/12/13 13:53, Mark Kirkwood wrote:
>>>> On 10/12/13 13:20, Mark Kirkwood wrote:
>>>>> On 10/12/13 13:14, Mark Kirkwood wrote:
>>>>>> On 10/12/13 12:14, Heikki Linnakangas wrote:
>>>>>>>
>>>>>>>
>>>>>>> I took a stab at using posix_fadvise() in ANALYZE. It turned out
>>>>>>> to be very easy, patch attached. Your mileage may vary, but I'm
>>>>>>> seeing a nice gain from this on my laptop. Taking a 30000 page
>>>>>>> sample of a table with 717717 pages (ie. slightly larger than
>>>>>>> RAM), ANALYZE takes about 6 seconds without the patch, and less
>>>>>>> than a second with the patch, with effective_io_concurrency=10.
>>>>>>> If anyone with a good test data set loaded would like to test
>>>>>>> this and post some numbers, that would be great.
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> I did a test run:
>>>>>>
>>>>>> pgbench scale 2000 (pgbench_accounts approx 25GB).
>>>>>> postgres 9.4
>>>>>>
>>>>>> i7 3.5Ghz Cpu
>>>>>> 16GB Ram
>>>>>> 500 GB Velociraptor 10K
>>>>>>
>>>>>> (cold os and pg cache both runs)
>>>>>> Without patch: ANALYZE pgbench_accounts 90s
>>>>>> With patch: ANALYZE pgbench_accounts 91s
>>>>>>
>>>>>> So I'm essentially seeing no difference :-(
>>>>>
>>>>>
>>>>> Arrg - sorry forgot the important bits:
>>>>>
>>>>> Ubuntu 13.10 (kernel 3.11.0-14)
>>>>> filesystem is ext4
>>>>>
>>>>>
>>>>>
>>>>
>>>> Doing the same test as above, but on a 80GB Intel 520 (ext4
>>>> filesystem mounted with discard):
>>>>
>>>> (cold os and pg cache both runs)
>>>> Without patch: ANALYZE pgbench_accounts 5s
>>>> With patch: ANALYZE pgbench_accounts 5s
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>> Redoing the filesystem on the 520 as btrfs didn't seem to make any
>>> difference either:
>>>
>>> (cold os and pg cache both runs)
>>> Without patch: ANALYZE pgbench_accounts 6.4s
>>> With patch: ANALYZE pgbench_accounts 6.4s
>>>
>>>
>>>
>>
>> Ah - I have just realized I was not setting effective_io_concurrency
>> - so I'll redo the test. - Apologies.
>>
>>
>
> Redoing the test on the velociraptor gives me exactly the same numbers
> as before (effective_io_concurrency = 10 instead of 1).
>
>

Good grief - repeating the test gave:

Without patch: ANALYZE pgbench_accounts 90s
With patch: ANALYZE pgbench_accounts 42s

pretty consistent *now*. No idea what was going on in the 1st run (maybe
I happened to have it running at the same time as a checkpoint)? Anyway
will stop now before creating more confusion.

Regards

Mark