Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eF3I9-0003nI-Dl for pgsql-admin@arkaria.postgresql.org; Wed, 15 Nov 2017 19:24:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eF3I7-0007bH-0S for pgsql-admin@arkaria.postgresql.org; Wed, 15 Nov 2017 19:24:07 +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 1eF3G8-0004J7-7l for pgsql-admin@postgresql.org; Wed, 15 Nov 2017 19:22:04 +0000 Received: from mail-wm0-x229.google.com ([2a00:1450:400c:c09::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eF3Fz-0004yE-0k for pgsql-admin@postgresql.org; Wed, 15 Nov 2017 19:22:03 +0000 Received: by mail-wm0-x229.google.com with SMTP id l8so5047387wmg.4 for ; Wed, 15 Nov 2017 11:21:54 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:references:in-reply-to:subject:date:message-id:mime-version :thread-index:content-language; bh=SbD2U8B4j8bHh2lZCKghKjhuRPbhjPDielSAzBu4FJ0=; b=Gx7N8MgWZKBur9wC9P3L5zjqkj1GxR3vWl97gXWstsriq+UEHPhXHFj/trrVWyF2Wa 8aB3afriEwkQ40GLa5+KHfaMzPnmQTCNO/eAGJBrW8wHFgDitYzvzY+wylM+l8zfjR+x jQXLoHKEt7xLuJt/r/d3WkRt5lrf7FBEmj/OMI1R+N756Fo5Tr/HGj/u4gbDLvwHDWY4 g6kokQJAPiIGK+XSaceQ5lpCawjyOhj0vhACuTsTdy299f7rFQO39fnpAII7xErn0xPz w2CDtmHSh5UrRVVtOkgR+E6+4gbmNo2i3HMw9HG6fyEMJU7vvcIvp4oR1ZLMBXWaHWGj WcTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:references:in-reply-to:subject:date :message-id:mime-version:thread-index:content-language; bh=SbD2U8B4j8bHh2lZCKghKjhuRPbhjPDielSAzBu4FJ0=; b=diDKC4zAWOCqvdKR9iElaI9c5/2yt8/xez9585W4+n8liGA0N7OjvOjk7fvmywSz2M o8fDJWZskDNc4Ijxtg3g1RIGqkJtu9Yh9Imng/o8BfAucFyNZUat4PfE+NOIF57FXYpq GMxRgRNFREOi3Xfc1CBC2n17ic0YLKER1bYBN5NpJogkQ68vGWRd9s+W80cPtCCouI6i x3wSaNgiCH2ETzwbkm+Sk4/3hgsW5EVb47kFecIsqYt2CPUiXvFhSaAXKeEvjDZZur98 gl7hME/XW0lWcNqfPIFi7niWjyE53X+h/dtPBxYl6lrJ9Cl6j4bBI6/B6NqACcYA74JK pakQ== X-Gm-Message-State: AJaThX5GL4oM/qqO1BBGp8vIwOlBIzuOTjYKDgGck3wbFSp4OXvjH5w0 mkQaC2iyQWsO0G3CirEr10I7lg== X-Google-Smtp-Source: AGs4zMaKR9WRFg5/X25hPdjl2cLChHh9KSqytH1g9Kk9kPteU2IME03+jfAyFviXB910G/Evgj0IZw== X-Received: by 10.28.122.11 with SMTP id v11mr12379473wmc.104.1510773710646; Wed, 15 Nov 2017 11:21:50 -0800 (PST) Received: from PC018 (dslb-178-009-057-066.178.009.pools.vodafone-ip.de. [178.9.57.66]) by smtp.gmail.com with ESMTPSA id 34sm17892688wra.39.2017.11.15.11.21.48 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 15 Nov 2017 11:21:49 -0800 (PST) From: "Klaus P. Pieper" To: References: <007c01d35e34$10a7f770$31f7e650$@gmail.com> <008301d35e35$637891c0$2a69b540$@gmail.com> <005301d35e39$42131510$c6393f30$@gmail.com> , In-Reply-To: Subject: Re: Performance difference between servers Date: Wed, 15 Nov 2017 20:21:44 +0100 Message-ID: <009801d35e46$f9eb5eb0$edc21c10$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0099_01D35E4F.5BB34920" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQNaLifc2PYzL3c8wJpjGYbbDmQX5QHVAdAjAqWc9rkCCWy5qAJCkn8NAfgvaHIBvgRrAAJJVrgpn5H+zNA= Content-Language: de X-Antivirus: Avast (VPS 171115-2, 15.11.2017), Outbound message X-Antivirus-Status: Clean List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0099_01D35E4F.5BB34920 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable The BIOS RAID was one of my first thoughts, too. But both CrystalDiskMark a= nd pgbench show better values fort he servers running on the RAID systems. = One other thing that came into mind is that server #1 might use fixed VHDs = whereas #2 and #3 use dynamic VHDs. I=E2=80=98 not sure about #1 but will i= nvestigate this tomorrow. I compared the postgresql.conf files already and they are very similar, not= hing that should explain such a big difference. Klaus Von: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.= org] Im Auftrag von Scott Whitney Gesendet: Mittwoch, 15. November 2017 19:25 An: Evan Rempel ; pgsql-admin@postgresql.org Betreff: Re: [ADMIN] Performance difference between servers That's a very valid point, Evan, which slipped my mind entirely. Klaus said= that they're BIOS RAID. I would expect a performance hit there, yes. _____ From: pgsql-admin-owner@postgresql.org > on behalf of Evan Rempel > Sent: Wednesday, November 15, 2017 12:21 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Performance difference between servers I would expect any heavy write task to take 100% longer on a RAID 1 system.= Unless you have a higher end RAID controller, the RAID 1 will write data t= o one drive, and then write the same data to the second drive. If you can t= est this, break the RAID 1 mirror and retry your test. Evan. On 11/15/2017 09:59 AM, Scott Whitney wrote: Well, you've got me stumped a bit on this. I prefer the easy ones. =F0=9F= =98=89 The only thing I see here that is suspect is the 2 1TB spindle drives, but = if that were the situation, you would just see server #2 as the outlier whi= ch is not what you're seeing. A few things I would check: 1) postgresql.conf on all 3 servers. What are the differences if any? 2) Logging on all systems from the host OS to the guest OS to pg (in the co= nf file). 3) Are these commands running local to the server or remote from another ho= st? 4) Check the free RAM on the host OSes. Make sure you're not paging in the = host. Whether the guests are idle or not, it won't matter if the host OS is= swapping. I doubt it based on the specs you sent. Comparing #1 and #3 (leaving 2 out for the moment) gives you pretty much a = 1:1 hardware comparison. If I read this correctly, running the same create db statement on #3 as #1 = take minimum twice the amount of time? That ain't right. _____ From: pgsql-admin-owner@postgresql.org on behalf of kpi6288@gmail.com Sent: Wednesday, November 15, 2017 11:43 AM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Performance difference between servers Trying as best as a can: #1 HP 8200 Elite microtower, i7-2600, 16GB RAM, 1 SSD 480 GB, 1 SSD 900 GB= , Hyper-V 2012 R2 (not 2008 R2 as written in my original question) #2 Lenovo TS 150, E3-1245 V5, 40GB RAM, 2 SSD 850GB as RAID 1 (BIOS level)= , 2 HDDs 1TB as RAID 1 (BIOS level) #3 model unknown, E3-1270 V3, 32GB RAM, 2 SSD 480GB as RAID 1 (BIOS level)= Server #1 is more a PC than a server. Server #1 runs in total 4 virtual guests. 8GB and 6 =E2=80=9Ecores=E2=80=9C= dedicated to the server running PostgreSQL and 3GB to other machines which= are almost 100% idle. Server #2 has currently on the one guets running PostgreSQL with 16GB RAM a= nd 6 =E2=80=9Ecores=E2=80=9C dedicated. Server #3 runs 2 virtual guests. 12GB and 4 cores dedicated tot he guest ru= nnning PostgreSQL. 4 GB and 3 cores decicated to another guest with is prac= tically idle. Servers #2 and #3 have still plenty of free disk space while server #1 is u= sing approx. 70% oft he disks. Server #1 has much older SSDs than #2. Not sure about #3. Von: Scott Whitney [mailto:scott@journyx.com] Gesendet: Mittwoch, 15. November 2017 18:19 An: kpi6288@gmail.com Betreff: Re: [ADMIN] Performance difference between servers Can you post the hardware host specs for #1, #2 and #3? If those ARE the ha= rdware specs (for example, you list SSDs), can you list the resources assig= ned to the guests? _____ From: kpi6288@gmail.com > Sent: Wednesday, November 15, 2017 11:15 AM To: Scott Whitney Subject: AW: [ADMIN] Performance difference between servers Yes, exactly: Three different (hardware) machines, each having a hyper-V host and the Win= dows Servers as Hyper-V guests. Von: Scott Whitney [mailto:scott@journyx.com] Gesendet: Mittwoch, 15. November 2017 18:11 An: kpi6288@gmail.com ; pgsql-admin@postgresql.o= rg Betreff: Re: [ADMIN] Performance difference between servers Are you saying that each of these servers is a virtual HyperV machine runni= ng under a hardware host? _____ From: pgsql-admin-owner@postgresql.org > on behalf of kpi6288@gmail.com > Sent: Wednesday, November 15, 2017 11:06 AM To: pgsql-admin@postgresql.org Subject: [ADMIN] Performance difference between servers We have 3 different servers, one (the oldest) ist significantly faster at c= ertain operations and I can=E2=80=99t find out the reason: (table created in monospace font) Server Processor RAM CrystalDiskMark pgbench CREATE DATABASE OS #1 i7-2600 8GB 495 / 473 26.000 4.5 =E2=80=A6 6 s = W/ Server 2008 R2 on Hyper V Server 2008 #2 E3-1245 16GB 797 / 514 50.000 13 =E2=80=A6 19 s = W/ Server 2016 Std. on Hyper V Server 2016 #3 E3-1270 12GB 933 / 334 40.000 13 =E2=80=A6 20 s = W/ Server 2008 Essentials on Hyper V Server 2016 CrystalDiskMark: =E2=80=9ESeq Q32T1=E2=80=9C Pgbench: pgbench -i -s 200 -U postgres pgbench pgbench -c 40 -j 10 -T 150 -U postgres -S -P 5 pgbench CREATE DATABASE: create database test with template myTemplate All running PostgreSQL 9.6 x64 with similar configuration. All running as v= irtual machines, no other virtual machines creating any load. Using the sam= e template database on all machines. Although the indicative numbers of pgbench and CrystalDiskMark show better = values for server #2 and #3, server #1 creates a new database considerably = faster. All servers have SSDs. Server #1 has two single SSDs (a system disk and a d= ata disk). Servers #2 and #3 have only one logical disk for system and data= , and this is two SSDs running as RAID 1. This may be slower but if it is, = I would excpect this also to show up in the pgbench results. The reason behind this exercise is that a test application was also reporte= d to be slower on server #2 and 3. Any idea how to track down the performance difference? Thank you Klaus Journyx, Inc. 7600 Burnet Road #300 Austin, TX 78757 www.journyx.com p 512.834.8888 f 512-834-8858 Do you receive our promotional emails? Click here or visit http://journyx.com/communication-preference= s to unsubscribe. --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren gepr=C3=BCft. https://www.avast.com/antivirus ------=_NextPart_000_0099_01D35E4F.5BB34920 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

The BIOS RAID was one of my first thoughts, too. B= ut both CrystalDiskMark and pgbench show better values fort he servers runn= ing on the RAID systems.

 

One other thing that c= ame into mind is that server #1 might use fixed VHDs whereas #2 and #3 use = dynamic VHDs. I=E2=80=98 not sure about #1 but will investigate this tomorr= ow.

 

I compared the postgresql.conf files alread= y and they are very similar, nothing that should explain such a big differe= nce.

 

Klaus

 

Von: pgsql-admin-owner@p= ostgresql.org [mailto:pgsql-admin-owner@postgresql.org] Im Auftrag von <= /b>Scott Whitney
Gesendet: Mittwoch, 15. November 2017 19:25
<= b>An: Evan Rempel <erempel@uvic.ca>; pgsql-admin@postgresql.orgBetreff: Re: [ADMIN] Performance difference between servers

 

That's a = very valid point, Evan, which slipped my mind entirely. Klaus said that the= y're BIOS RAID. I would expect a performance hit there, yes.

 

From: pgsql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.o= rg> on behalf of Evan Rempel <= erempel@uvic.ca>
Sent: Wednesday, November 15, 2017 12:21 = PM
To: pgsql-admin@= postgresql.org
Subject: Re: [ADMIN] Performance difference be= tween servers

 


I would expect any heavy write task to take 100% longer on a RAID 1 s= ystem. Unless you have a higher end RAID controller, the RAID 1 will write = data to one drive, and then write the same data to the second drive. If you= can test this, break the RAID 1 mirror and retry your test.

Evan.
On 11/15/2017 09:59 AM, Scott Whitney wrote:

Well, you've got me stumped a bit on this. I prefer t= he easy ones. 😉

 

The only= thing I see here that is suspect is the 2 1TB spindle drives, but if that = were the situation, you would just see server #2 as the outlier which is no= t what you're seeing.

 

A fe= w things I would check:

=  

1)= postgresql.conf on all 3 servers. What are the differences if any?

2) Logging on all systems from the host OS to the guest OS to= pg (in the conf file).

= 3) Are these commands running = local to the server or remote from another host?

4) Ch= eck the free RAM on the host OSes. Make sure you're not paging in the host.= Whether the guests are idle or not, it won't matter if the host OS is swap= ping. I doubt it based on the specs you sent.

&nb= sp;

Comparing #1 and #3 (leaving 2 out for the moment) give= s you pretty much a 1:1 hardware comparison.

&nb= sp;

If I read this correctly, running the same create db st= atement on #3 as #1 take minimum twice the amount of time? That ain't right= =2E

 

 

From: pgsql-admin-o= wner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of kpi6288@gmail.com <kpi6288@gmail.com>
Sent: Wednesday, November 15, = 2017 11:43 AM
To: p= gsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance di= fference between servers

 

=

Trying as best as a can:

 

#1  HP 8200 Elite microtower, i7-2600, 16G= B RAM, 1 SSD 480 GB, 1 SSD 900 GB, Hyper-V 2012 R2 (not 2008 R2 as written = in my original question)

<= span style=3D'font-size:12.0pt;font-family:Consolas;color:black'>#2  L= enovo TS 150, E3-1245 V5, 40GB RAM, 2 SSD 850GB as RAID 1 (BIOS level), 2 H= DDs 1TB as RAID 1 (BIOS level)

#3&nb= sp; model unknown, E3-1270 V3, 32GB RAM, 2 SSD 480GB as RAID 1 (BIOS level)=

<= p class=3Dxxmsonormal style=3D'background:white'> 

Server #= 1 is more a PC than a server.

&nb= sp;

= Server #1 runs in total 4 virt= ual guests. 8GB and 6 =E2=80=9Ecores=E2=80=9C dedicated to the server runni= ng PostgreSQL and 3GB to other machines which are almost 100% idle.

Server #2 has currently on the one guets = running PostgreSQL with 16GB RAM and 6 =E2=80=9Ecores=E2=80=9C dedicated. <= o:p>

Server #3 runs 2 virtual guests. 1= 2GB and 4 cores dedicated tot he guest runnning PostgreSQL. 4 GB and 3 core= s decicated to another guest with is practically idle.

 

Serve= rs #2 and #3 have still plenty of free disk space while server #1 is using = approx. 70% oft he disks.

 <= o:p>

Server #1 has much older SSDs than= #2. Not sure about #3.

 

Von: Scott Whitney [mailto:scott@journyx.com]
Gesendet: Mitt= woch, 15. November 2017 18:19
An: kpi6288@gmail.com
Betreff: Re: [ADMIN] Performance di= fference between servers

 

Can you= post the hardware host specs for #1, #2 and #3? If those ARE the hardware = specs (for example, you list SSDs), can you list the resources assigned to = the guests?

&= nbsp;

Yes, exactly:=

 

Three different (hardware) machines, each having a hyper-V host and= the Windows Servers as Hyper-V guests.

 

 <= /span>

= Von: Scott Whitney [mailto:scott@journyx.com]
Gesendet: Mittwoch, 15= =2E November 2017 18:11
An: = kpi6288@gmail.com; pgsql-= admin@postgresql.org
Betreff: Re: [ADMIN] Performance differe= nce between servers

&= nbsp;

Are you = saying that each of these servers is a virtual HyperV machine running under= a hardware host?

 

 

 

From: pg= sql-admin-owner@postgresql.org <pgsql-admin-owner@postgresql.org> on behalf of kpi6288@gmail.com <kpi6288@gmail.com>
Sent: Wednesday, No= vember 15, 2017 11:06 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Perform= ance difference between servers

 

We have 3 different servers, one (the oldest) ist significantly faster at= certain operations and I can=E2=80=99t find out the reason:

 

(table created in monospace font)

 

Server  Processor  RAM   CrystalDiskMark   = ;pgbench   CREATE DATABASE   OS

  #1    i7-2600   8GB =     495 / 473        = ;26.000      4.5 =E2=80=A6 6 s   &n= bsp;  W/ Server 2008 R2 on Hyper V Server 2008

   #2    E3-1245  &nb= sp;16GB    797 / 514       &nb= sp;50.000      13 =E2=80=A6 19 s   =    W/ Server 2016 Std. on Hyper V Server 2016

   #3    E3-1270  &nb= sp;12GB    933 / 334      &nbs= p; 40.000      13 =E2=80=A6 20 s   =    W/ Server 2008 Essentials on Hyper V Server 2016

 

CrystalDiskMark: = =E2=80=9ESeq Q32T1=E2=80=9C

Pgbe= nch:

   pgbench = -i -s 200 -U postgres pgbench

&n= bsp;  pgbench -c 40 -j 10 -T 150 -U postgres -S -P 5 pgbench

CREATE DATABASE:

   create database test with template my= Template

 

All running PostgreSQL 9.6 x64 with similar conf= iguration. All running as virtual machines, no other virtual machines creat= ing any load. Using the same template database on all machines. =

 

Although the indicative numbers of pgbench and CrystalDiskMark sho= w better values for server #2 and #3, server #1 creates a new database cons= iderably faster.

All servers ha= ve SSDs. Server #1 has two single SSDs (a system disk and a data disk). Ser= vers #2 and #3 have only one logical disk for system and data, and this is = two SSDs running as RAID 1. This may be slower but if it is, I would excpec= t this also to show up in the pgbench results.

 

The reas= on behind this exercise is that a test application was also reported to be = slower on server #2 and 3.

&nbs= p;

Any idea how to track down th= e performance difference?

 = ;

= Thank you

Klaus

&n= bsp;

 

 

= Journyx, Inc.

7600 Burnet Road #300
Austin, TX 7= 8757
www.journyx.com =

 

p 512.834.8888 

f 512-834-8858 

 

<= div>

Do you receive our promotional emails? Click here or visit http://journyx.com/comm= unication-preferences to unsubscribe.

=

&nb= sp;


3D"" Virenf= rei. www.avast.com
=
------=_NextPart_000_0099_01D35E4F.5BB34920--