Re: ANALYZE sampling is too good

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).

Cheers

Mark