pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
8+ messages / 3 participants
[nested] [flat]
* pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
@ 2017-05-29 07:42 Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 08:19 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 18:14 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Andres Freund <andres@anarazel.de>
0 siblings, 2 replies; 8+ messages in thread
From: Klaus P. Pieper @ 2017-05-29 07:42 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
@ 2017-05-29 08:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 12:58 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2017-05-29 08:19 UTC (permalink / raw)
To: Klaus P. Pieper <kpi6288@gmail.com>; +Cc: pgsql-admin
"Klaus P. Pieper" <kpi6288@gmail.com> writes:
> How can Windows outperform Debian by a factor of 2?
Are you sure you have Windows configured to fsync properly?
Not waiting for writes to hit disk would be a really easy
explanation for a discrepancy of this kind.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 08:19 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-05-29 12:58 ` Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 16:16 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Klaus P. Pieper @ 2017-05-29 12:58 UTC (permalink / raw)
To: pgsql-admin
> -----Ursprüngliche Nachricht-----
> Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Gesendet: Montag, 29. Mai 2017 10:19
> An: Klaus P. Pieper
> Cc: pgsql-admin@postgresql.org
> Betreff: Re: [ADMIN] pgbench Windows outperforms Debian in Write tests
> while Debian is faster in SELECT only tests
>
> "Klaus P. Pieper" < <mailto:kpi6288@gmail.com> kpi6288@gmail.com> writes:
> > How can Windows outperform Debian by a factor of 2?
>
> Are you sure you have Windows configured to fsync properly?
> Not waiting for writes to hit disk would be a really easy explanation for
a
> discrepancy of this kind.
It seems that it is indeed related to the wal_sync_method settings.
Results for 1 client read / write:
Option Windows Debian
fdatasync 340 150
fsync 125 90
open_datasync 360 160
fsync_writethrough 115 n/a
open_sync n/a 90
fsync = off 360 400
The result setting fsync=off shows that the maximum performance of the
Debian syste is above Windows.
However, with fsync=on, Windows still outperforms the Debian system with all
options.
In addition, the results on the Windows system with “fsync = off” are the
same as “wal_sync_method = open_datasync”
What does this mean?
- Is the Windows system “better” than the Debian system?
- Or is the underlying filesystem on the Windows system less safe?
- Or is the Windows system better integrated with the Hyper-V host?
The Windows systems reports that the write cache is switched on for the
virtual disk. It is however not possible to switch is off.
Thanks
Klaus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 08:19 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 12:58 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
@ 2017-05-29 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 19:59 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2017-05-29 16:16 UTC (permalink / raw)
To: Klaus P. Pieper <kpi6288@gmail.com>; +Cc: pgsql-admin
"Klaus P. Pieper" <kpi6288@gmail.com> writes:
>> Are you sure you have Windows configured to fsync properly?
> It seems that it is indeed related to the wal_sync_method settings.
> Results for 1 client read / write:
> Option Windows Debian
> fdatasync 340 150
> fsync 125 90
> open_datasync 360 160
> fsync_writethrough 115 n/a
> open_sync n/a 90
> fsync = off 360 400
OK, so open_datasync and fdatasync are nonfunctional on Windows, which
is unsurprising. The other cases are evidently doing *something*, but
the amount of overhead is variable from one sync method to another,
which is likewise unsurprising. The whole reason why we have these
options at all is that some systems handle some of them better than
others. In this case, I think the relevant conclusion is that correct
configuration for Debian is to use open_datasync or fdatasync, while
correct configuration for Windows is to use fsync or fsync_writethrough,
and when you compare those two configurations, yes Windows is slower.
If you're a paranoid sort you would want to do plug-pull testing
to ensure that your selected setting actually does prevent data
corruption during a system crash.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 08:19 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 12:58 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 16:16 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-05-29 19:59 ` Klaus P. Pieper <kpi6288@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Klaus P. Pieper @ 2017-05-29 19:59 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
> Von: Tom Lane
> Gesendet: Montag, 29. Mai 2017 18:17
>
> OK, so open_datasync and fdatasync are nonfunctional on Windows, which is
> unsurprising. The other cases are evidently doing *something*, but the
amount
> of overhead is variable from one sync method to another, which is likewise
> unsurprising. The whole reason why we have these options at all is that
some
> systems handle some of them better than others. In this case, I think the
> relevant conclusion is that correct configuration for Debian is to use
> open_datasync or fdatasync, while correct configuration for Windows is to
use
> fsync or fsync_writethrough, and when you compare those two
configurations,
> yes Windows is slower.
Excellent answer, thanks.
Correct me if I'm wrong: I understand that synchronous_commit = off in
combination with fdatasync (Debian) or fsync_writethrough (Windows) is
"safe" (for regular interactive desktop applications).
These options gave the following results:
Windows Debian
40 clients READ/WRITE 5400 7400
1 client READ/WRITE 400 390
Single client READ/WRITE tests are on now the same level. In the multi
client test, Debian is performing significantly better than Windows.
>
> If you're a paranoid sort you would want to do plug-pull testing to ensure
that
> your selected setting actually does prevent data corruption during a
system
> crash.
That's certainly an option before we take this into production.
Regards Klaus
---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
https://www.avast.com/antivirus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
@ 2017-05-29 18:14 ` Andres Freund <andres@anarazel.de>
2017-05-29 20:03 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Andres Freund @ 2017-05-29 18:14 UTC (permalink / raw)
To: Klaus P. Pieper <kpi6288@gmail.com>; +Cc: pgsql-admin
On 2017-05-29 09:42:53 +0200, Klaus P. Pieper wrote:
> 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?
Windows IIRC defaults to something close to barriers=0.
- Andres
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 18:14 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Andres Freund <andres@anarazel.de>
@ 2017-05-29 20:03 ` Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 20:11 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Klaus P. Pieper @ 2017-05-29 20:03 UTC (permalink / raw)
To: pgsql-admin
> -----Ursprüngliche Nachricht-----
> Von: Andres Freund
> Gesendet: Montag, 29. Mai 2017 20:14
>
> Windows IIRC defaults to something close to barriers=0.
I think this explains in simple words what the manual mentions - I just
didn't understand it before.
However, there may be more people like me just expecting the default
settings in postgresql.conf being "safe". As far as I understand, they are
not for the Windows installation. A comment in postgresql.conf might help
those people.
Regards klaus
---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
https://www.avast.com/antivirus
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 18:14 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Andres Freund <andres@anarazel.de>
2017-05-29 20:03 ` Re: pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
@ 2017-05-29 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2017-05-29 20:11 UTC (permalink / raw)
To: Klaus P. Pieper <kpi6288@gmail.com>; +Cc: pgsql-admin
"Klaus P. Pieper" <kpi6288@gmail.com> writes:
> However, there may be more people like me just expecting the default
> settings in postgresql.conf being "safe". As far as I understand, they are
> not for the Windows installation. A comment in postgresql.conf might help
> those people.
Yeah, we've just recently realized that they're not safe for Windows
unless you set some system-level options that are not on by default :-(.
I think probably what needs to happen is that we change the default
on Windows. It's not really my field though, and not much discussion
has happened about it yet.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2017-05-29 20:11 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-29 07:42 pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 08:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 12:58 ` Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 16:16 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-29 19:59 ` Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 18:14 ` Andres Freund <andres@anarazel.de>
2017-05-29 20:03 ` Klaus P. Pieper <kpi6288@gmail.com>
2017-05-29 20:11 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox