Re: effective_io_concurrency on EBS/gp2

More tests:

io1, 100 GB:

effective_io_concurrency=0
  Execution time: 40333.626 ms
effective_io_concurrency=1
  Execution time: 163840.500 ms
effective_io_concurrency=2
  Execution time: 162606.330 ms
effective_io_concurrency=4
  Execution time: 163670.405 ms
effective_io_concurrency=8
  Execution time: 161800.478 ms
effective_io_concurrency=16
  Execution time: 161962.319 ms
effective_io_concurrency=32
  Execution time: 160451.435 ms
effective_io_concurrency=64
  Execution time: 161763.632 ms
effective_io_concurrency=128
  Execution time: 161687.398 ms
effective_io_concurrency=256
  Execution time: 160945.066 ms

effective_io_concurrency=256
  Execution time: 161226.440 ms
effective_io_concurrency=128
  Execution time: 161977.954 ms
effective_io_concurrency=64
  Execution time: 159122.006 ms
effective_io_concurrency=32
  Execution time: 154923.569 ms
effective_io_concurrency=16
  Execution time: 160922.819 ms
effective_io_concurrency=8
  Execution time: 160577.122 ms
effective_io_concurrency=4
  Execution time: 157509.481 ms
effective_io_concurrency=2
  Execution time: 161806.713 ms
effective_io_concurrency=1
  Execution time: 164026.708 ms
effective_io_concurrency=0
  Execution time: 40196.182 ms


st1, 500 GB:

effective_io_concurrency=0
  Execution time: 40542.583 ms
effective_io_concurrency=1
  Execution time: 119996.892 ms
effective_io_concurrency=2
  Execution time: 51137.998 ms
effective_io_concurrency=4
  Execution time: 42301.922 ms
effective_io_concurrency=8
  Execution time: 42081.877 ms
effective_io_concurrency=16
  Execution time: 42253.782 ms
effective_io_concurrency=32
  Execution time: 42087.216 ms
effective_io_concurrency=64
  Execution time: 42112.105 ms
effective_io_concurrency=128
  Execution time: 42271.850 ms
effective_io_concurrency=256
  Execution time: 42213.074 ms

effective_io_concurrency=256
  Execution time: 42255.568 ms
effective_io_concurrency=128
  Execution time: 42030.515 ms
effective_io_concurrency=64
  Execution time: 41713.753 ms
effective_io_concurrency=32
  Execution time: 42035.436 ms
effective_io_concurrency=16
  Execution time: 42221.581 ms
effective_io_concurrency=8
  Execution time: 42203.730 ms
effective_io_concurrency=4
  Execution time: 42236.082 ms
effective_io_concurrency=2
  Execution time: 49531.558 ms
effective_io_concurrency=1
  Execution time: 117160.222 ms
effective_io_concurrency=0
  Execution time: 40059.259 ms

Regards,
Vitaliy

On 31/01/2018 15:46, Gary Doades wrote:
>
> > 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.
>
> We run many MS SQL server VMs in AWS with more than adequate performance.
>
> AWS EBS performance is variable and depends on various factors, mainly 
> the size of the volume and the size of the VM it is attached to. The 
> bigger the VM, the more EBS “bandwidth” is available, especially if 
> the VM is EBS Optimised.
>
> The size of the disk determines the IOPS available, with smaller disks 
> naturally getting less. However, even a small disk with (say) 300 IOPS 
> is allowed to burst up to 3000 IOPS for a while and then gets 
> clobbered. If you want predictable performance then get a bigger disk! 
> If you really want maximum, predictable performance get an EBS 
> Optimised VM and use Provisioned IOPS EBS volumes…. At a price!
>
> Cheers,
>
> Gary.
>
> 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@gmail.com <mailto:vgarnashevich@gmail.com>> wrote:
>
>         Hi,
>
>         I've tried to run a benchmark, similar to this one:
>
>         https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=HWh1WbLRsioe=mzRJTHwtr=2azsTdQ@mail.gmail.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
>