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
    <vgarnashev...@gmail.com <mailto:vgarnashev...@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=2azs...@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


Reply via email to