Re: performance of insert/delete/update

Josh Berkus <josh@agliodbs.com> writes:
>> On my machine I see a sizable difference (more than 2x) in the rate at
>> which simple INSERT statements are processed as separate transactions
>> and as large batches --- if I have fsync on.  With fsync off, nearly no
>> difference.

> I'm using fdatasych, which *does* perform faster than fsych on my system.
> Could this make the difference?

No; you still have to write the data and wait for the disk to spin.
(FWIW, PG defaults to wal_sync_method = open_datasync on my system,
and that's what I used in checking the speed just now.  So I wasn't
actually executing any fsync() calls either.)

On lots of PC hardware, the disks are configured to lie and report write
complete as soon as they've accepted the data into their internal
buffers.  If you see very little difference between fsync on and fsync
off, or if you are able to benchmark transaction rates in excess of your
disk's RPM, you should suspect that your disk drive is lying to you.

As an example: in testing INSERT speed on my old HP box just now,
I got measured rates of about 16000 inserts/minute with fsync off, and
5700/min with fsync on (for 1 INSERT per transaction).  Knowing that my
disk drive is 6000 RPM, the latter number is credible.  On my PC I get
numbers way higher than the disk rotation rate :-(

            regards, tom lane