Обсуждение: [ADMIN] pgbench Windows outperforms Debian in Write tests while Debian is faster in SELECT only tests

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.

> -----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" <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

> 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