pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests

From: "Klaus P(dot) Pieper" <kpi6288(at)gmail(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
Date: 2017-05-29 07:42:53
Message-ID: 028501d2d84f$2f1ea840$8d5bf8c0$@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2017-05-29 08:19:01 Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
Previous Message phb07 2017-05-27 15:42:54 Re: Monitoring tool for Postgres Database