Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1egsiP-0006Jj-IE for pgsql-performance@arkaria.postgresql.org; Wed, 31 Jan 2018 13:46:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1egsiN-0003ps-QB for pgsql-performance@arkaria.postgresql.org; Wed, 31 Jan 2018 13:46:15 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1egsiN-0003pj-Ht for pgsql-performance@lists.postgresql.org; Wed, 31 Jan 2018 13:46:15 +0000 Received: from ec2-54-154-62-225.eu-west-1.compute.amazonaws.com ([54.154.62.225] helo=WIN-D84BPDQUF5D) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1egsiJ-0007c1-IS for pgsql-performance@lists.postgresql.org; Wed, 31 Jan 2018 13:46:14 +0000 Received: from gpdPC (212.56.100.243) by WIN-D84BPDQUF5D (Axigen) with (ECDHE-RSA-AES128-SHA256 encrypted) ESMTPSA id 342122; Wed, 31 Jan 2018 13:45:48 +0000 From: "Gary Doades" To: "'Vitaliy Garnashevich'" , "'Rick Otten'" Cc: References: <6c7a45df-f6ab-f2ce-6f84-9555864f6c86@gmail.com> <768e3a56-46ed-3dd1-8722-953a47857a35@gmail.com> In-Reply-To: <768e3a56-46ed-3dd1-8722-953a47857a35@gmail.com> Subject: RE: effective_io_concurrency on EBS/gp2 Date: Wed, 31 Jan 2018 13:46:00 -0000 Message-ID: <014801d39a99$d4c32930$7e497b90$@gpdnet.co.uk> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0149_01D39A99.D4C6D2B0" X-Mailer: Microsoft Outlook 15.0 Thread-Index: AQHM35uz4/gu/xCNKIR+e8svqeCXkAGym6oxApVTlh6jeTNJIA== Content-Language: en-gb X-AxigenSpam-Level: 9 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_0149_01D39A99.D4C6D2B0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =20 =20 > I've tried to re-run the test for some specific values of = effective_io_concurrency. The results were the same.=20 > 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. =20 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 =E2=80=9Cbandwidth=E2=80=9D is available, = especially if the VM is EBS Optimised. =20 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=E2=80=A6. At a price! =20 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.=20 =20 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. =20 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. =20 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.) =20 =20 On Wed, Jan 31, 2018 at 7:03 AM, Vitaliy Garnashevich = > wrote: Hi, I've tried to run a benchmark, similar to this one: https://www.postgresql.org/message-id/flat/CAHyXU0yiVvfQAnR9cyH%3DHWh1WbL= Rsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiVvfQAnR9cyH=3DHWh1W= bLRsioe=3DmzRJTHwtr=3D2azsTdQ@mail.gmail.com CREATE TABLESPACE test OWNER postgres LOCATION '/path/to/ebs'; pgbench -i -s 1000 --tablespace=3Dtest 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=3D$i" >>test.txt psql pgbench -c "set effective_io_concurrency=3D$i; set = enable_indexscan=3Doff; explain (analyze, buffers) select * from = pgbench_accounts where aid between 1000 and 10000000 and abalance !=3D = 0;" >>test.txt done I get the following results: effective_io_concurrency=3D0 Execution time: 40262.781 ms effective_io_concurrency=3D1 Execution time: 98125.987 ms effective_io_concurrency=3D2 Execution time: 55343.776 ms effective_io_concurrency=3D4 Execution time: 52505.638 ms effective_io_concurrency=3D8 Execution time: 54954.024 ms effective_io_concurrency=3D16 Execution time: 54346.455 ms effective_io_concurrency=3D32 Execution time: 55196.626 ms effective_io_concurrency=3D64 Execution time: 55057.956 ms effective_io_concurrency=3D128 Execution time: 54963.510 ms effective_io_concurrency=3D256 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=3D256 (or = more) on EBS. The other one is that effective_io_concurrency=3D1 (the = worst case) is actually the default for PostgreSQL on Linux. Thoughts? Regards, Vitaliy =20 =20 ------=_NextPart_000_0149_01D39A99.D4C6D2B0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

 

 

>=C2=A0 I've tried to re-run the test = for some specific values of effective_io_concurrency. The results were = the same.

=C2=A0> 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 =E2=80=9Cbandwidth=E2=80=9D 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=E2=80=A6. 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> = wrote:

Hi,

I've tried to run a benchmark, = similar to this one:

https://www.postgresql.org/message-id/flat/CAHyXU0yiVvf= QAnR9cyH%3DHWh1WbLRsioe%3DmzRJTHwtr%3D2azsTdQ%40mail.gmail.com#CAHyXU0yiV= vfQAnR9cyH=3DHWh1WbLRsioe=3DmzRJTHwtr=3D2azsTdQ@mail.gmail.com
CREATE TABLESPACE test OWNER postgres LOCATION = '/path/to/ebs';

pgbench -i -s 1000 --tablespace=3Dtest = 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=3D$i" >>test.txt
  = psql pgbench -c "set effective_io_concurrency=3D$i; set = enable_indexscan=3Doff; explain (analyze, buffers)  select * from = pgbench_accounts where aid between 1000 and 10000000 and abalance !=3D = 0;" >>test.txt
done

I get the following = results:

effective_io_concurrency=3D0
 Execution time: = 40262.781 ms
effective_io_concurrency=3D1
 Execution time: = 98125.987 ms
effective_io_concurrency=3D2
 Execution time: = 55343.776 ms
effective_io_concurrency=3D4
 Execution time: = 52505.638 ms
effective_io_concurrency=3D8
 Execution time: = 54954.024 ms
effective_io_concurrency=3D16
 Execution time: = 54346.455 ms
effective_io_concurrency=3D32
 Execution time: = 55196.626 ms
effective_io_concurrency=3D64
 Execution time: = 55057.956 ms
effective_io_concurrency=3D128
 Execution time: = 54963.510 ms
effective_io_concurrency=3D256
 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=3D256 (or more) on EBS. The other one is that = effective_io_concurrency=3D1 (the worst case) is actually the default = for PostgreSQL on = Linux.

Thoughts?

Regards,
Vitaliy

 

 <= /o:p>

------=_NextPart_000_0149_01D39A99.D4C6D2B0--