Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bhp5Y-0002f5-Jf for pgsql-performance@arkaria.postgresql.org; Thu, 08 Sep 2016 02:29:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bhp5X-00018j-WD for pgsql-performance@arkaria.postgresql.org; Thu, 08 Sep 2016 02:29:16 +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 1bhp5X-00018A-0X for pgsql-performance@postgresql.org; Thu, 08 Sep 2016 02:29:15 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bhp5T-00074T-Jk for pgsql-performance@postgresql.org; Thu, 08 Sep 2016 02:29:14 +0000 Received: from [10.9.9.210] (helo=mailfront10.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1bhp5S-0006Te-At for pgsql-performance@postgresql.org; Thu, 08 Sep 2016 04:29:10 +0200 Received: from cpe-66-27-125-35.san.res.rr.com ([66.27.125.35] helo=seasyslap4) by mailfront10.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1bhp5I-0004aU-8b for pgsql-performance@postgresql.org; Thu, 08 Sep 2016 04:29:00 +0200 From: "Mike Sofen" To: "'postgres performance list'" References: <04b701d204c2$0bd7e330$2387a990$@runbox.com> <001401d206a8$9eda1350$dc8e39f0$@runbox.com> In-Reply-To: Subject: Re: Postgres bulk insert/ETL performance on high speed servers - test results Date: Wed, 7 Sep 2016 19:28:46 -0700 Message-ID: <032b01d20978$bb0f18e0$312d4aa0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_032C_01D2093E.0EB10430" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQGpF9i869D43eNvo08BeUkSb4OtBwJxyO9fAcliwO4C0iIqXQGols/0oHsP6qA= Content-Language: en-us X-Pg-Spam-Score: -3.7 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_032C_01D2093E.0EB10430 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable From: Jim Nasby [mailto:Jim.Nasby@BlueTreble.com] Sent: Wednesday, = September 07, 2016 12:22 PM On 9/4/16 7:34 AM, Mike Sofen wrote: > You raise a good point. However, other disk activities involving=20 > large data (like backup/restore and pure large table copying), on both = > platforms, do not seem to support that notion. I did have both our IT = > department and Cisco turn on instrumentation for my last test,=20 > capturing all aspects of both tests on both platforms, and I=E2=80=99m = hoping=20 > to see the results early next week and will reply again. =20 Something important to remember about Postgres is that it makes = virtually no efforts to optimize IO; it throws the entire problem in the = OSes lap. So differences in OS config or in IO *latency* can have a = massive impact on performance. Because of the sensitivity to IO latency, = you can also end up with a workload that only reports say 60% IO = utilization but is essentially IO bound (would be 100% IO utilization if = enough read-ahead was happening). -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in = Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in = Treble! http://BlueTreble.com 855-TREBLE2 (855-873-2532) mobile: 512-569-9461 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D Hi Jim, =20 Thanks for that info regarding the sensitivity to IO latency. As it = turns out, our network guys have determined while the AWS Direct Connect = pipe is running at =E2=80=9Cnormal=E2=80=9D speed, the end to latency is = quite high and are working with AWS support to see if there are any = optimizations to be done. To me, the performance differences have to be = tied to networking, especially since it does appear that for these EC2 = instances, all data =E2=80=93 both SSD and network =E2=80=93 is = consuming bandwidth in their network =E2=80=9Cconnection=E2=80=9D, = possibly adding to PG IO pressure. I=E2=80=99ll keep your note in mind = as we evaluate next steps. =20 Mike =20 =20 ------=_NextPart_000_032C_01D2093E.0EB10430 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

From: Jim Nasby = [mailto:Jim.Nasby@BlueTreble.com] =C2=A0Sent: Wednesday, September 07, = 2016 12:22 PM
On 9/4/16 7:34 AM, Mike Sofen wrote:

> You raise a good = point.=C2=A0 However, other disk activities involving

> large data (like = backup/restore and pure large table copying), on both

> platforms, do not = seem to support that notion.=C2=A0 I did have both our IT =

> = department and Cisco turn on instrumentation for my last test, =

> = capturing all aspects of both tests on both platforms, and I=E2=80=99m = hoping

> to see the results early next week and = will reply again.

 

Something important to remember about = Postgres is that it makes virtually no efforts to optimize IO; it throws = the entire problem in the OSes lap. So differences in OS config or in IO = *latency* can have a massive impact on performance. Because of the = sensitivity to IO latency, you can also end up with a workload that only = reports say 60% IO utilization but is essentially IO bound (would be = 100% IO utilization if enough read-ahead was = happening).

--

Jim Nasby, Data Architect, Blue Treble = Consulting, Austin TX Experts in Analytics, Data Architecture and = PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com

855-TREBLE2 (855-873-2532)=C2=A0=C2=A0 = mobile: 512-569-9461

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<= /span>

Hi Jim,

 

Thanks = for that info regarding the sensitivity to IO latency.=C2=A0 As it turns = out, our network guys have determined while the AWS Direct Connect pipe = is running at =E2=80=9Cnormal=E2=80=9D speed, the end to latency is = quite high and are working with AWS support to see if there are any = optimizations to be done. =C2=A0To me, the performance differences have = to be tied to networking, especially since it does appear that for these = EC2 instances, all data =E2=80=93 both SSD and network =E2=80=93 is = consuming bandwidth in their network =E2=80=9Cconnection=E2=80=9D, = possibly adding to PG IO pressure.=C2=A0 I=E2=80=99ll keep your note in = mind as we evaluate next steps.

 

Mike

 

 

------=_NextPart_000_032C_01D2093E.0EB10430--