Regarding the CLOG reads happening in my test with PostgreSQL 8.3beta1, the community advised that it could be thrashing the CLOG Buffers resulting on those mutiple reads. Interesting thing that I learned from the Community that the most current CLOG buffer page is actually pinned (shared) but the remaining CLOG Buffers are used for caching old pages. So the recommendation was to change NUM_CLOG_BUFFERS from default 8 to 16 in clog.h in the source code and recompile PostgreSQL 8.3beta1.
I tried the change and yes it certainly seems to almost take out all the reads that I was seeing on the CLOG files. The downside is that it requires re-compilation of PostgreSQL. If one does not want to recompile postgresql, then my earlier workaround still works in reducing the pain on the disk.
I also tried out the new wal_writer_delay flag. The default is 200ms for now. I tried 100ms and found that I got similar gains as the default. But from what I understand, I am cutting the risk of data loss (not data corruption like fsync=off) from 600ms to 300ms. This does warrant more tests at still lower values but the community is right in asking for more tests for the new flag.
Now to hit the other bottlenecks.. Hopefully PostgreSQL 8.3 becomes the tide of change in the expensive world of databases.
No comments:
Post a Comment