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

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

"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

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