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.