Re: effective_io_concurrency on EBS/gp2

2018-01-31 14:15 GMT+01:00 Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com>:

> I've tried to re-run the test for some specific values of
> effective_io_concurrency. The results were the same.
>
> That's why I don't think the order of tests or variability in "hardware"
> performance affected the results.
>

AWS uses some intelligent throttling, so it can be related to hardware.

> Regards,
> Vitaliy
>
>
> On 31/01/2018 15:01, Rick Otten wrote:
>
> We moved our stuff out of AWS a little over a year ago because the
> performance was crazy inconsistent and unpredictable. I think they do a
> lot of oversubscribing so you get strange sawtooth performance patterns
> depending on who else is sharing your infrastructure and what they are
> doing at the time.
>
> The same unit of work would take 20 minutes each for several hours, and
> then take 2 1/2 hours each for a day, and then back to 20 minutes, and
> sometimes anywhere in between for hours or days at a stretch. I could
> never tell the business when the processing would be done, which made it
> hard for them to set expectations with customers, promise deliverables, or
> manage the business. Smaller nodes seemed to be worse than larger nodes, I
> only have theories as to why. I never got good support from AWS to help me
> figure out what was happening.
>
> My first thought is to run the same test on different days of the week and
> different times of day to see if the numbers change radically. Maybe spin
> up a node in another data center and availability zone and try the test
> there too.
>
> My real suggestion is to move to Google Cloud or Rackspace or Digital
> Ocean or somewhere other than AWS. (We moved to Google Cloud and have
> been very happy there. The performance is much more consistent, the
> management UI is more intuitive, AND the cost for equivalent infrastructure
> is lower too.)
>
>
> On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich <
> vgarnashevich(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> I've tried to run a benchmark, similar to this one:
>>
>> https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9
>> cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#
>> CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azsTdQ(at)mail(dot)gmail(dot)com
>>
>> CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs';
>>
>> pgbench -i -s 1000 --tablespace=test pgbench
>>
>> echo "" >test.txt
>> for i in 0 1 2 4 8 16 32 64 128 256 ; do
>> sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql restart
>> echo "effective_io_concurrency=$i" >>test.txt
>> psql pgbench -c "set effective_io_concurrency=$i; set
>> enable_indexscan=off; explain (analyze, buffers) select * from
>> pgbench_accounts where aid between 1000 and 10000000 and abalance != 0;"
>> >>test.txt
>> done
>>
>> I get the following results:
>>
>> effective_io_concurrency=0
>> Execution time: 40262.781 ms
>> effective_io_concurrency=1
>> Execution time: 98125.987 ms
>> effective_io_concurrency=2
>> Execution time: 55343.776 ms
>> effective_io_concurrency=4
>> Execution time: 52505.638 ms
>> effective_io_concurrency=8
>> Execution time: 54954.024 ms
>> effective_io_concurrency=16
>> Execution time: 54346.455 ms
>> effective_io_concurrency=32
>> Execution time: 55196.626 ms
>> effective_io_concurrency=64
>> Execution time: 55057.956 ms
>> effective_io_concurrency=128
>> Execution time: 54963.510 ms
>> effective_io_concurrency=256
>> Execution time: 54339.258 ms
>>
>> The test was using 100 GB gp2 SSD EBS. More detailed query plans are
>> attached.
>>
>> PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
>> 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
>>
>> The results look really confusing to me in two ways. The first one is
>> that I've seen recommendations to set effective_io_concurrency=256 (or
>> more) on EBS. The other one is that effective_io_concurrency=1 (the worst
>> case) is actually the default for PostgreSQL on Linux.
>>
>> Thoughts?
>>
>> Regards,
>> Vitaliy
>>
>>
>
>