Thread: can't explain commit performance win7 vs linux : 8000/s vs 419/s

can't explain commit performance win7 vs linux : 8000/s vs 419/s

From
"t.dalpozzo@gmail.com"
Date:
Hi,
I've postgres 9.5.3 installed on win7 64 bit, and ubuntu 16.04tls 64
bit,  same SSD (evo 850 pro) , two different partitions. Laptop is 3.8Ghz.
I've in each partition a simple database with one table called data256
with one column of 256 char.
I wrote a program using libpq which:
1 connects to 127.0.0.1 to the server
2 drops and recreates the table;
3 executes 2000 times the exec() function with the command  "INSERT INTO
data256 VALUES ('AAAAAA...... 250 times')"
I want to commit after every insert of course.
The program is the same both in win and linux; in ansi c, so it's portable.

Performance:
Win7: 8000 write/sec
Linux: 419 write/sec

I don't figure out why such a difference. Also what should I expect?
Which one is reasonable?

I compared the two postgresql.conf, they're identical (except obvious
things), they're the default ones, I didn't touch them. I just tried to
disable ssl in one because it was set but nothing changes.
I didn't go into deeper analysis because the source C file used for test
is the same and the two postgresql.conf are identical.

Then, in order to test write / flush without postgres, I made another C
program, to open a file in writing, and for 1000 times : write 256 bytes
and flush them (using fsync in linux and FlushFileBuffers in win).
Win7: 200 write/sec
Linux: 100 write/sec




Thanks
Pupillo












Re: can't explain commit performance win7 vs linux : 8000/s vs 419/s

From
Tom Lane
Date:
"t.dalpozzo@gmail.com" <t.dalpozzo@gmail.com> writes:
> Performance:
> Win7: 8000 write/sec
> Linux: 419 write/sec

My immediate reaction to that is that Windows isn't actually writing
the data to disk when it should in order to guarantee that commits
are persistent.  There are multiple layers that might be trying to
optimize away the writes, and I don't know enough about Windows to
help you debug it.  But see

https://www.postgresql.org/docs/9.5/static/wal-reliability.html

for some discussion.

> I don't figure out why such a difference. Also what should I expect?
> Which one is reasonable?

The lower number sounds a lot more plausible for laptop-grade hardware.
If you weren't using an SSD I wouldn't believe that one was doing
persistent commits either.

            regards, tom lane


Re: can't explain commit performance win7 vs linux : 8000/s vs 419/s

From
Jeff Janes
Date:
On Sat, Jun 25, 2016 at 9:19 AM, t.dalpozzo@gmail.com
<t.dalpozzo@gmail.com> wrote:
> Hi,
> I've postgres 9.5.3 installed on win7 64 bit, and ubuntu 16.04tls 64 bit,
> same SSD (evo 850 pro) , two different partitions. Laptop is 3.8Ghz.
> I've in each partition a simple database with one table called data256 with
> one column of 256 char.
> I wrote a program using libpq which:
> 1 connects to 127.0.0.1 to the server
> 2 drops and recreates the table;
> 3 executes 2000 times the exec() function with the command  "INSERT INTO
> data256 VALUES ('AAAAAA...... 250 times')"
> I want to commit after every insert of course.
> The program is the same both in win and linux; in ansi c, so it's portable.
>
> Performance:
> Win7: 8000 write/sec
> Linux: 419 write/sec
>
> I don't figure out why such a difference. Also what should I expect? Which
> one is reasonable?

The Win7 numbers seem suspiciously high to me, even for SSD.  Have you
tried holding the power button until it hard-resets the computer in
the middle of a run (preferably several runs going in parallel), and
see if comes back up without corruption and contains consistent data?
And then repeat that a several times?


> I compared the two postgresql.conf, they're identical (except obvious
> things), they're the default ones, I didn't touch them.

We don't know which things are obvious to you.

>
> Then, in order to test write / flush without postgres, I made another C
> program, to open a file in writing, and for 1000 times : write 256 bytes and
> flush them (using fsync in linux and FlushFileBuffers in win).
> Win7: 200 write/sec
> Linux: 100 write/sec

Rather than rolling your own program, can you run pg_test_fsync on each?

Cheers,

Jeff


Re: can't explain commit performance win7 vs linux : 8000/s vs 419/s

From
Imre Samu
Date:
my guess:
- maybe the NTFS compression is enabled?  [  "Compress this drive to save disk space” ? ]  [  your test data is ideal for compression:  VALUES ('AAAAAA...... 250 times') ]
- or Windows Samsung Magician extreme settings?  or RAPID mode cache enabled? 
"RAPID mode is a RAM caching feature. Samsung’s RAPID white paper states that RAPID works by analyzing “system traffic and leverages spare system resources (DRAM and CPU) to deliver read acceleration through intelligent caching of hot data and write optimization through tight coordination with the SSD.”


on Ubuntu16.04  (+ Samsung SSD 840 PRO )    I use "Samsung SSD Magician DC "  trim optimization   [ "sudo ./magician -d 0 -T " ]
http://jcutrer.com/howto/linux/samsung-magician-command-line-linux

$ sudo ./magician
================================================================================================
Samsung(R) SSD Magician DC Version 1.0
Copyright (c) 2014 Samsung Corporation
================================================================================================
Usage:  ./magician  [operation] ..

Allowed Operations:
-L[ --list]              Shows a disk(s) attached to the system.           
-F[ --firmware-update]   Updates firmware to specified disk.               
-E[ --erase]             Securely Erases all data from specified disk.     
-O[ --over-provision]    Performs one of the Over-Provisioning related     
                         operations on specified disk.                     
-T[ --trim]              Optimizes specified disk.                         
-S[ --smart]             Shows S.M.A.R.T values of specified disk.         
-M[ --setmax]            Performs SetMax related operations on specified disk.
-W[ --writecache]        Enables/Disables Write Cache on specified disk.   
-X[ --sctcachestate]     Gets the SCT write cache state for specified disk.
-C[ --command-history]   Shows history of the previously executed commands.
-I[ --info]              Displays the disk details to the user.            
-license                 Shows the End User License Agreement.             
-H[ --help]              Shows detailed Help.                              


regards,
 Imre


2016-06-25 18:19 GMT+02:00 t.dalpozzo@gmail.com <t.dalpozzo@gmail.com>:
Hi,
I've postgres 9.5.3 installed on win7 64 bit, and ubuntu 16.04tls 64 bit,  same SSD (evo 850 pro) , two different partitions. Laptop is 3.8Ghz.
I've in each partition a simple database with one table called data256 with one column of 256 char.
I wrote a program using libpq which:
1 connects to 127.0.0.1 to the server
2 drops and recreates the table;
3 executes 2000 times the exec() function with the command  "INSERT INTO data256 VALUES ('AAAAAA...... 250 times')"
I want to commit after every insert of course.
The program is the same both in win and linux; in ansi c, so it's portable.

Performance:
Win7: 8000 write/sec
Linux: 419 write/sec

I don't figure out why such a difference. Also what should I expect? Which one is reasonable?

I compared the two postgresql.conf, they're identical (except obvious things), they're the default ones, I didn't touch them. I just tried to disable ssl in one because it was set but nothing changes.
I didn't go into deeper analysis because the source C file used for test is the same and the two postgresql.conf are identical.

Then, in order to test write / flush without postgres, I made another C program, to open a file in writing, and for 1000 times : write 256 bytes and flush them (using fsync in linux and FlushFileBuffers in win).
Win7: 200 write/sec
Linux: 100 write/sec




Thanks
Pupillo












--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance