Thread: Migrating to 8.3 - checkpoints and background writer
For those of you considering a move to the upcoming 8.3 release, now in beta, I've written some documentation on the changes made in checkpoint and background writer configuration in the new version: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm Since the first half of that covers the current behavior in 8.1 and 8.2, those sections may be helpful if you'd like to know more about checkpoint slowdowns and ways to resolve them even if you have no plans to evaluate 8.3 yet. I'd certainly encourage anyone who can run the 8.3 beta to consider adding some tests in this area while there's still time to correct any issues encountered before the official release. On the topic of performance improvements in 8.3, I don't think this list has been getting information about the concurrent sequential scans improvements. Check out these documents for more about that: http://j-davis.com/postgresql/83v82_scans.html http://j-davis.com/postgresql/syncscan/syncscan.pdf http://j-davis.com/postgresql/syncscan/syncscan.odp I particularly liked that first one because it gives a nice sample of how to generate large amounts of data easily and then run benchmarks against it, which is a handy thing to know for many types of performance comparisons. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Sun, Nov 04, 2007 at 07:33:46PM -0500, Greg Smith wrote: > On the topic of performance improvements in 8.3, I don't think this list > has been getting information about the concurrent sequential scans > improvements. Check out these documents for more about that: > > http://j-davis.com/postgresql/83v82_scans.html That's a nice writeup. I'm a bit puzzled by this part, though: "All tests are on linux with the anticipatory I/O scheduler. The default I/O scheduler for Linux is CFQ (Completely Fair Queue), which does not work well for PostgreSQL in my tests." All earlier benchmarks I've seen (even specifically for Postgres) have said that cfq > deadline > anticipatory for database work. How large was the difference for you? /* Steinar */ -- Homepage: http://www.sesse.net/
On Mon, 5 Nov 2007, Steinar H. Gunderson wrote: > I'm a bit puzzled by this part, though: "All tests are on linux with the > anticipatory I/O scheduler. The default I/O scheduler for Linux is CFQ > (Completely Fair Queue), which does not work well for PostgreSQL in my > tests." The syncronized scan articles were from Jeff Davis and I can't answer for him. I will say I don't actually agree with that part of the document myself and almost put a disclaimer to that effect in my message; here it is now that you bring it up. I suspect the strong preference for avoiding CFQ in his tests comes from the limitations of how simple (S)ATA drives are handled in Linux, and that tests with a more robust disk subsystem may very well give different results. Certainly the adaptive scheduler appears stronger compared to CFQ as disk seek times go up, and that's the area where regular hard drives are weakest relative to what's normally in a server-class system. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
On Nov 4, 2007, at 6:33 PM, Greg Smith wrote: > For those of you considering a move to the upcoming 8.3 release, > now in beta, I've written some documentation on the changes made in > checkpoint and background writer configuration in the new version: > > http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm > > Since the first half of that covers the current behavior in 8.1 and > 8.2, those sections may be helpful if you'd like to know more about > checkpoint slowdowns and ways to resolve them even if you have no > plans to evaluate 8.3 yet. I'd certainly encourage anyone who can > run the 8.3 beta to consider adding some tests in this area while > there's still time to correct any issues encountered before the > official release. Greg, thanks a lot of this. I'd say this should definitely be linked to from the main site's techdocs section. Erik Jones Software Developer | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com