I believe I have a correction to the usage of posix_fadvise() in xlog.c. Basically posix_fadvise() is being called right before the WAL segment file is closed, which effectively doesn't do anything as opposed to when the file is opened. This proposed correction calls posix_fadvise() in three locations in order to make sure POSIX_FADV_DONTNEED is set correctly since there are three cases for opening a WAL segment file for writing.
I'm hesitant to post any data I have because I only have a little pc with a SATA drive in it. My hardware knowledge on SATA controllers and drives is a little weak, but my testing with dbt-2 is showing the performance dropping. I am guessing that SATA drives have write cache enabled by default so it seems to make sense that using POSIX_FADV_DONTNEED will cause writes to be slower by writing through the disk cache. Again, assuming that is possible with SATA hardware. If memory serves, one of the wins here is suppose to be that in a scenario where we are not expecting to re-read writes to the WAL we also do not want the writes to disk to flush out other data from the operating system disk cache. But I'm not sure how best to test correctness. Anyway, I hope I'm not way off but I'm sure someone will correct me. :) Regards, Mark
pgsql-log-fadvise.patch
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend