Thread: PgSQL 12 on WinSrv ~3x faster than on Linux
Dear All,
I have demo stand on Hyper-V (2xIntel E5-2650v2, 256GB RAM, Intel SSDs in RAID):
* One VM with 1C application Server
* 2 database VMs with same database imported into each PostgreSQL (~56Gb, "1C accounting 3.0" config):
1. CentOS 7 with postgresql_12.6_6.1C_x86_64 (distribution with special patches for running 1C), VM is updated with LIS for Hyper-V
2. Windows Server 2019 with same postgresql_12.6_6.1C_x86_64
My real life test is to "register" 10 _same_ documents (провести документы) in each of 1C/PostgreSQL DBs. Both PostgreSQL DBs are identical and just before test imported to PostgreSQL via application server (DT import).
On Windows Server test procedure takes 20-30 seconds, on Linux it takes 1m-1m10seconds. PostgreSQL VMs are running on same Hypervisor with same resources assigned to each of them.
Tuning PostgreSQL config and/or CentOS don't make any difference. Contrary on Windows VM we have almost 3x better performance with stock PostgreSQL config.
Any ideas what's wrong? For me such a big difference on identical databases/queries looks strange.
--
Taras Savchuk
On Fri, 4 Jun 2021 at 23:53, Taras Savchuk <taras@1adm.ru> wrote: > My real life test is to "register" 10 _same_ documents (провести документы) in each of 1C/PostgreSQL DBs. Both PostgreSQLDBs are identical and just before test imported to PostgreSQL via application server (DT import). > On Windows Server test procedure takes 20-30 seconds, on Linux it takes 1m-1m10seconds. PostgreSQL VMs are running on sameHypervisor with same resources assigned to each of them. > Tuning PostgreSQL config and/or CentOS don't make any difference. Contrary on Windows VM we have almost 3x better performancewith stock PostgreSQL config. > > Any ideas what's wrong? For me such a big difference on identical databases/queries looks strange. It's pretty difficult to say. You've not provided any useful details about the workload you're running. If this "register 10 _same_ documents" thing requires running some query, then you might want to look at EXPLAIN (ANALYZE, BUFFERS) for that query. You might want to consider doing SET track_io_timing = on; Perhaps Linux is having to read more buffers from disk than Windows. David.
also if you can setup an external timer \timing , along with explain analyse to get total time, it would help if everything else is same.
I have seen some threads thar mention added startup cost for parallel workers on windows but not on Linux.
But I do not want to mix those threads here, but just FYI.
On Fri, Jun 4, 2021, 6:12 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 4 Jun 2021 at 23:53, Taras Savchuk <taras@1adm.ru> wrote:
> My real life test is to "register" 10 _same_ documents (провести документы) in each of 1C/PostgreSQL DBs. Both PostgreSQL DBs are identical and just before test imported to PostgreSQL via application server (DT import).
> On Windows Server test procedure takes 20-30 seconds, on Linux it takes 1m-1m10seconds. PostgreSQL VMs are running on same Hypervisor with same resources assigned to each of them.
> Tuning PostgreSQL config and/or CentOS don't make any difference. Contrary on Windows VM we have almost 3x better performance with stock PostgreSQL config.
>
> Any ideas what's wrong? For me such a big difference on identical databases/queries looks strange.
It's pretty difficult to say. You've not provided any useful details
about the workload you're running.
If this "register 10 _same_ documents" thing requires running some
query, then you might want to look at EXPLAIN (ANALYZE, BUFFERS) for
that query. You might want to consider doing SET track_io_timing =
on; Perhaps Linux is having to read more buffers from disk than
Windows.
David.
David Rowley <dgrowleyml@gmail.com> writes:
> On Fri, 4 Jun 2021 at 23:53, Taras Savchuk <taras@1adm.ru> wrote:
>> Any ideas what's wrong? For me such a big difference on identical databases/queries looks strange.
> It's pretty difficult to say. You've not provided any useful details
> about the workload you're running.
> If this "register 10 _same_ documents" thing requires running some
> query, then you might want to look at EXPLAIN (ANALYZE, BUFFERS) for
> that query. You might want to consider doing SET track_io_timing =
> on; Perhaps Linux is having to read more buffers from disk than
> Windows.
The first thing that comes to mind for me is fsync working correctly
(i.e. actually waiting for the disk write) in Linux but not in Windows.
On a weird VM stack like you've got, it's not hard for that sort of
thing to go wrong. Needless to say, if that's the issue then the
apparent performance win is coming at the cost of crash safety.
pg_test_fsync might help detect such a problem.
regards, tom lane
> The first thing that comes to mind for me is fsync working correctly (i.e.
> actually waiting for the disk write) in Linux but not in Windows.
> On a weird VM stack like you've got, it's not hard for that sort of thing to go
> wrong. Needless to say, if that's the issue then the apparent performance
> win is coming at the cost of crash safety.
>
> pg_test_fsync might help detect such a problem.
>
> regards, tom lane
>
fsync performance on win is much better (results are below). Also network performance for VMs on same HV for win-win is
40%better than for win-linux (5,97Gbps vs 3,6Gbps).
Regarding weird VM stack - we're running both win and linux VMs and Hyper-V works reasonable well except... this issue
)
Win:
C:\Program Files\PostgreSQL\12.6-6.1C\bin>pg_test_fsync.exe
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 7423,645 ops/sec 135 usecs/op
fdatasync n/a
fsync 1910,611 ops/sec 523 usecs/op
fsync_writethrough 1987,900 ops/sec 503 usecs/op
open_sync n/a
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 3827,254 ops/sec 261 usecs/op
fdatasync n/a
fsync 1920,720 ops/sec 521 usecs/op
fsync_writethrough 1863,852 ops/sec 537 usecs/op
open_sync n/a
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write n/a
2 * 8kB open_sync writes n/a
4 * 4kB open_sync writes n/a
8 * 2kB open_sync writes n/a
16 * 1kB open_sync writes n/a
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 144,065 ops/sec 6941 usecs/op
write, close, fsync 148,751 ops/sec 6723 usecs/op
Non-sync'ed 8kB writes:
write 165,484 ops/sec 6043 usecs/op
Linux:
[root@pgsql12 ~]# /usr/pgsql-12/bin/pg_test_fsync
5 seconds per test
O_DIRECT supported on this platform for open_datasync and open_sync.
Compare file sync methods using one 8kB write:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 2947.296 ops/sec 339 usecs/op
fdatasync 2824.271 ops/sec 354 usecs/op
fsync 1885.924 ops/sec 530 usecs/op
fsync_writethrough n/a
open_sync 1816.312 ops/sec 551 usecs/op
Compare file sync methods using two 8kB writes:
(in wal_sync_method preference order, except fdatasync is Linux's default)
open_datasync 1458.849 ops/sec 685 usecs/op
fdatasync 2712.756 ops/sec 369 usecs/op
fsync 1769.353 ops/sec 565 usecs/op
fsync_writethrough n/a
open_sync 902.626 ops/sec 1108 usecs/op
Compare open_sync with different write sizes:
(This is designed to compare the cost of writing 16kB in different write
open_sync sizes.)
1 * 16kB open_sync write 1798.811 ops/sec 556 usecs/op
2 * 8kB open_sync writes 887.727 ops/sec 1126 usecs/op
4 * 4kB open_sync writes 494.843 ops/sec 2021 usecs/op
8 * 2kB open_sync writes 233.659 ops/sec 4280 usecs/op
16 * 1kB open_sync writes 117.417 ops/sec 8517 usecs/op
Test if fsync on non-write file descriptor is honored:
(If the times are similar, fsync() can sync data written on a different
descriptor.)
write, fsync, close 1673.781 ops/sec 597 usecs/op
write, close, fsync 1727.787 ops/sec 579 usecs/op
Non-sync'ed 8kB writes:
write 200638.271 ops/sec 5 usecs/op
--
Taras