Re: WAL Performance Improvements

Tom Lane wrote:

> Janardhana Reddy <jana-reddy(at)mediaring(dot)com(dot)sg> writes:
> > I've attached a patch which should improve the performance of WAL by
> > reducing the fsync time
> > and write time by 50%(if OS page size is 4k) , if the transaction
> > generate the WAL data less then 4k. Instead of
> > writing every time 8k data in to the WAL file it will write only the
> > portion of the data which
> > as changed from the last time(Example : if transaction generates 150
> > bytes of WAL data ,then it writes
> > only 150 bytes instead of 8k).
>
> As near as I can tell, this breaks WAL by failing to ensure that the
> rest of the current page is zeroed. After crash and recovery, you might
> read obsolete WAL records (written during the previous cycle of life
> of the WAL segment file) and think they are valid.
>
> I'd also be interested to see the measurements backing up the claim of 50%
> performance improvement. That'd depend very largely on the filesystem block
> size, no?
>
> regards, tom lane

Hi,
I have done some changes to patch to overcome the "WAL failing to ensure
that the
rest of the current page is zeroed". for every write to the WAL empty
record(32 bytes with zero value ) is appended so that
at the time of REDO it can see the record with zero length in all cases.

Test Results with Latest patch :
environment: Intel PC ,IDE (harddisk),Linux Kernel 2.4.0 (OS
Version). Single
connection is connected to the database and pumping
continously insert statements. each insert
generates 160 bytes to WAL Log.

With out applying the Patch :
Transaction Per Second : 332 TPS
Time Taken by fdatasync : 2160
usec/call
Time taken by write : 61
usec/call
After applying the patch :
Transaction Per Second : 435 TPS
Time Taken by fdatasync : 512
usec/sec
Time Taken by write : 42 usec/sec

I've attached latest patch and xlog.c .

Regards
jana