Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dFFMK-0008Pv-4A for pgsql-admin@arkaria.postgresql.org; Mon, 29 May 2017 07:45:00 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dFFMJ-0003Gx-F8 for pgsql-admin@arkaria.postgresql.org; Mon, 29 May 2017 07:44:59 +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 1dFFKT-0008W6-RE for pgsql-admin@postgresql.org; Mon, 29 May 2017 07:43:05 +0000 Received: from mail-wm0-x244.google.com ([2a00:1450:400c:c09::244]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dFFKL-00009b-Sa for pgsql-admin@postgresql.org; Mon, 29 May 2017 07:43:05 +0000 Received: by mail-wm0-x244.google.com with SMTP id d127so15532679wmf.1 for ; Mon, 29 May 2017 00:42:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:to:subject:date:message-id:mime-version:thread-index :content-language; bh=LCGz2DqWeyz65oVrf6nVW7SImKTRlKHkFPkqmeMpuz8=; b=ijoDhJP2bvC4hM29tzth3Ko75ygvxvYYctsBQ4xFx3vXzfRTZxFLOHDRdkoyrsFpYb /GPMPRhj3eRAUekDycAp/ejsVOLkCLYZBLQ/4Sg0lPxC2jrPT4TJveB+mI/XYMeBYM+m O6FOKVEUXxM80vZiWdRcL8oS2O4NSmgwRrQCNTrFEpwe2ffMrWW3oVEc9H6U3X5QpLJv o+JTrB7O9i47mUeaFMhvNP+WVZgnkNL9v6AMCVMnp7UenM4nW2N6R16V+RoZWBOKAHGc 0ouSxtnXQvWo95RIPWpghoJ3A8vP8+l37HPAN4SvL2UYmK3KPJWg4ft+rFlBwUbYSf8p yZeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:to:subject:date:message-id:mime-version :thread-index:content-language; bh=LCGz2DqWeyz65oVrf6nVW7SImKTRlKHkFPkqmeMpuz8=; b=svJnRBd0feKRJmWM+gNwzWNhjl4O83oHSpff5fhs8x4Uh4qbCE7UQP+0i2jpADP7/x Se/FC0KUV0lzP+i6s2X7m86rsATPdJoOAyQt2+AsNqG2jhcvfHKzmBubComuYNqzLXzv 816Fc2S7wcEzkJw6lzx6h16xLe7NIe/Kn+141xcv03f7HeNXxxrFJL8tkVjlMf76wECm j8/IFT7K+W8c0wGjG9RRg7BQ9dIMqisiO+NirxxBt12dIeucjYjcDlhZZQQ/mtvWwlnF C6nAxRdgDvZMJSzjYGlvdiM8f8GgCUmTR2UkW5UNP6EQSpn8S6AjKJz+SpPjT+hKEQeq x6bg== X-Gm-Message-State: AODbwcBaFbffHlzZpQiZpoctHiG1EFm/v4sOibSJnSVsY0a++aU92C6B D6uTsNLH2ZUzVun5 X-Received: by 10.223.173.74 with SMTP id p68mr9302408wrc.163.1496043776012; Mon, 29 May 2017 00:42:56 -0700 (PDT) Received: from PC14 (mail.ibeq.com. [212.100.42.241]) by smtp.gmail.com with ESMTPSA id s5sm5965958wra.60.2017.05.29.00.42.54 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 29 May 2017 00:42:55 -0700 (PDT) From: "Klaus P. Pieper" To: Subject: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Date: Mon, 29 May 2017 09:42:53 +0200 Message-ID: <028501d2d84f$2f1ea840$8d5bf8c0$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0286_01D2D85F.F2A889B0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AdLYSx9ceBZduI4WROqdEvKqkIZu3A== Content-Language: de X-Pg-Spam-Score: -2.4 (--) 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_0286_01D2D85F.F2A889B0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit I did a quick comparison between Windows and Linux (Debian) in the following environment: Server E3-1245 with 40GB RAM running Hyper-V 2016 Hyper-V uses 2 SSDs as RAID 1 For the tests both freshly installed: 1. Windows 10 test image from modern.ie 2. Debian 8.8 with Xfce desktop 3. Current PostgreSQL 9.6.3 (x64) Setup was pretty much standard on both machines. Only shared_buffers was increased to 4000MB. Test preparation with a scale factor of 200: pgbench -i -s 200 -U postgres pgbench All tests were carried out repeatedly with different -T settings to eliminate "warming up" effects. pgbench running on the same machine as the database server. These are TPS numbers (the table was written in monospace font e.g. Consolas): Windows Debian Test statement 40 clients SELECT only 47000 76000 pgbench -c 40 -j 10 -T 150 -U postgres -S -P 5 pgbench 1 client SELECT only 3300 18000 pgbench -c 40 -j 10 -T 150 -U postgres -P 5 pgbench 40 clients READ/WRITE 2000 2000 pgbench -U postgres -S -P 5 pgbench 1 client READ WRITE 370 150 pgbench -U postgres -P 5 pgbench Mounting the file system with barriers=0 gives much better results on the Debian system (better than the Windows system). But since this machine does not have a battery buffered disk adapter, that wouldn't be an option for a production system. I'm in particular concerned about the disadvantage in the single client WRITE scenario running Debian. How can Windows outperform Debian by a factor of 2? Is there anything I could do to improve the Debian write performance? Thank you Klaus BTW: My original intention was to analyze the difference between standard installation and initdb --data-checksums. Results were similar on both systems. ------=_NextPart_000_0286_01D2D85F.F2A889B0 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

I did a quick comparison between Windows and Linux (Debian) = in the following environment:

 

Server E3-1245 with 40GB RAM = running Hyper-V 2016

Hyper-V uses 2 SSDs as RAID 1

 

For the tests both freshly = installed:

1.       = Windows 10 test image = from modern.ie

2.       = Debian 8.8 with Xfce = desktop

3.       = Current PostgreSQL = 9.6.3 (x64)

 

Setup was pretty much standard on both machines. Only = shared_buffers was increased to 4000MB.

 

Test preparation with a scale = factor of 200:

pgbench -i -s 200 -U postgres pgbench =

 

All tests were carried out repeatedly with different = –T settings to eliminate “warming up” effects. pgbench = running on the same machine as the database server. =

 

These are TPS numbers (the table was written in monospace = font e.g. Consolas):

 

       =             &= nbsp;     Windows     =    Debian   Test = statement

40 clients SELECT only =     47000        =  76000   pgbench -c 40 -j 10 -T 150 -U postgres -S -P 5 = pgbench

1 client SELECT = only        3300 =         18000   = pgbench -c 40 -j 10 -T 150 -U postgres -P 5 = pgbench

40 clients = READ/WRITE       2000 =          2000   = pgbench -U postgres -S -P 5 pgbench

1 = client READ WRITE          = 370           = 150   pgbench -U postgres -P 5 pgbench

 

Mounting the file system with = barriers=3D0 gives much better results on the Debian system (better than = the Windows system). But since this machine does not have a battery = buffered disk adapter, that wouldn’t be an option for a production = system.

 

I’m in particular concerned about the disadvantage in = the single client WRITE scenario running Debian. How can Windows = outperform Debian by a factor of 2?

 

Is there anything I could do to = improve the Debian write performance?

 

Thank you

Klaus

 

BTW: My original intention was to = analyze the difference between standard installation and initdb = --data-checksums. Results were similar on both systems. =

 

 

------=_NextPart_000_0286_01D2D85F.F2A889B0--