Jon,
I have tried a little bench with pgbench on my 2 proc 2.4 Gb with 4 GB RAM and Linux
RH 9.0.
The database size is 700 MB, so it can be loaded in memory.
Postgres 7.4 is on disk sda (Root disk)
Meta Data are on disk sdb
bench data are on disk sdc
When pgbench is running, i can see with top tool that the CPU are 53% in I/O wait. And
mainling because postgres is writting block on sdb disk. And the Transaction Per
Second (tps) are 222.
By setting "fsync=false", the CPU I/O wait decrease to 0.6%. And the result tps is :
466.
So, should i conclude that even if the whole database is in memory, the TPS result is
slow down by the WAL mecanism which wait for writting the log on disk ?
And the main thing to increase the TPS and preserve the consistency of data in case of
crash is to increase the I/O throughput of the Postgres WAL disk by creating RAID0 on
fiber channel subsystem (I will test that as soon asap).
Regards,
Thierry
Jonathan Bartlett wrote:
> > Could you tell me what is the real impact of "fsync=false" on the WAL and on the
> > database in the same catastrophic scenario ?
>
> I am not certain on this point, but I believe fsync=false messes up the
> whole thing. The nice thing about WAL is that fsync is no longer as much
> of a slowdown, because PG rarely has to do random-access writes to the
> disk.
>
> Jon
>
