Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 21 January 2013 13:10, Noah Misch n...@leadboat.com wrote: What filesystem did you use for testing? Would you also provide /proc/cpuinfo or a rough description of the system's CPUs? Unfortunately, I don't have access to that server at the moment. It's under Greg Smith's control. I believe you yourself had an account on this server at one point. I do know that the CPU is an Intel Core i7-870: http://ark.intel.com/products/41315/Intel-Core-i7-870-Processor-8M-Cache-2_93-GHz I am pretty sure that the filesystem that the various block devices were mounted with was ext4 (without LVM), but it might have been XFS. I don't recall. The operating system was Debian Lenny. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 19 January 2013 20:38, Noah Misch n...@leadboat.com wrote: staticloud.com seems to be gone. Would you repost these? I've pushed these to a git repo, hosted on github. https://github.com/petergeoghegan/commit_delay_benchmarks I'm sorry that I didn't take the time to make the html benchmarks easily viewable within a browser on this occasion. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
I've added this to the open commitfest. I think that a formal review is probably required here. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
Peter Geoghegan wrote: Some of you will be aware that I've tried to formulate a good general recommendation for setting the value of commit_delay, in light of the improvements made in 9.3. I previously asked for help finding a methodology for optimising commit_delay [1]. The documentation related to commit_delay still says that we don't know what might work well, though I don't think that's true any more. I found the time to do some benchmarks of my own - Greg Smith made available a server that he frequently uses for benchmarks. It was previously my observation that half of raw single-page sync time as reported by pg_test_fsync for you wal_sync_method worked best for commit_delay. I went so far as to modify pg_test_fsync to output average time per op in microseconds for each operation with commit_delay in mind, which is a patch that has already been committed [2]. [...] Attached is a doc-patch that makes recommendations that are consistent with my observations about what works best here. I'd like to see us making *some* recommendation - for sympathetic cases, setting commit_delay appropriately can make a very large difference to transaction throughput. Such sympathetic cases - many small write transactions - are something that tends to be seen relatively frequently with web applications, that disproportionately use cloud hosting. It isn't at all uncommon for these cases to be highly bound by their commit rate, and so it is compelling to try to amortize the cost of a flush as effectively as possible there. It would be unfortunate if no one was even aware that commit_delay is now useful for these cases, since the setting allows cloud hosting providers to help these cases quite a bit, without having to do something like compromise durability, which in general isn't acceptable. The point of all these benchmarks isn't to show how effective commit_delay now is, or can be - we already had that discussion months ago, before the alteration to its behaviour was committed. The point is to put my proposed doc changes in the appropriate context, so that I can build confidence that they're balanced and helpful, by showing cases that are not so sympathetic. Thoughts? If there is an agreement that half the sync time as reported by pg_test_fsync is a good value, would it make sense to have initdb test sync time and preset commit_delay? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 11/15/12 12:19 AM, Albe Laurenz wrote: If there is an agreement that half the sync time as reported by pg_test_fsync is a good value, would it make sense to have initdb test sync time and preset commit_delay? Peter has validated this against a good range of systems, but it would be optimistic to say it's a universal idea. My main concern with this would be the relatively common practice of moving the pg_xlog directory after initdb time. Sometimes people don't know about the option to have initdb move it. Sometimes the drive to hold pg_xlog isn't available when the database is originally created yet. And the camp I fall into (which admittedly is the group who can take care of this on their own) will move pg_xlog manually and symlink it on their own, because that's what we're used to. I would rather see this just turn into one of the things a more general tuning tool knew how to do, executing against a fully setup system. Having a useful implementation of commit_delay and useful docs on it seems like enough of a jump forward for one release. Moving fully into auto-tuning before getting more field feedback on how that works out is pretty aggressive. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 11/15/2012 04:56 PM, Greg Smith wrote: I would rather see this just turn into one of the things a more general tuning tool knew how to do, executing against a fully setup system. Having a useful implementation of commit_delay and useful docs on it seems like enough of a jump forward for one release. Moving fully into auto-tuning before getting more field feedback on how that works out is pretty aggressive. It'll also potentially make it harder to get reproducible results in benchmarking and testing across repeated runs, cause confusion when someone relocates a DB or changes hardware, and slow down initdb (and thus testing). I'd be all for making it part of a test my hardware and tune my DB tool, but not such a fan of doing it at initdb time. Making initdb less predictable and more complicated sounds like asking for trouble. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On Thu, Nov 15, 2012 at 9:56 AM, Greg Smith g...@2ndquadrant.com wrote: On 11/15/12 12:19 AM, Albe Laurenz wrote: If there is an agreement that half the sync time as reported by pg_test_fsync is a good value, would it make sense to have initdb test sync time and preset commit_delay? Peter has validated this against a good range of systems, but it would be optimistic to say it's a universal idea. My main concern with this would be the relatively common practice of moving the pg_xlog directory after initdb time. Sometimes people don't know about the option to have initdb move it. Sometimes the drive to hold pg_xlog isn't available when the database is originally created yet. And the camp I fall into (which admittedly is the group who can take care of this on their own) will move pg_xlog manually and symlink it on their own, because that's what we're used to. An even more common usecase for this, I think, is I installed from a package that ran initdb for me.. I still think manual moving of pg_xlog is a lot more common than using the initdb option in general. I would rather see this just turn into one of the things a more general tuning tool knew how to do, executing against a fully setup system. Having a useful implementation of commit_delay and useful docs on it seems like enough of a jump forward for one release. Moving fully into auto-tuning before getting more field feedback on how that works out is pretty aggressive. +1. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 15 November 2012 10:04, Magnus Hagander mag...@hagander.net wrote: I would rather see this just turn into one of the things a more general tuning tool knew how to do, executing against a fully setup system. Having a useful implementation of commit_delay and useful docs on it seems like enough of a jump forward for one release. Moving fully into auto-tuning before getting more field feedback on how that works out is pretty aggressive. +1. I am inclined to agree. I did attempt to use the instrumentation macros to have commit_delay set adaptively at runtime, which would have at least addressed this concern, but that just didn't work as well as this. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 15 November 2012 08:56, Greg Smith g...@2ndquadrant.com wrote: My main concern with this would be the relatively common practice of moving the pg_xlog directory after initdb time. I probably should have increased the default number of seconds that pg_test_fsync runs for, in light of the fact that that can make an appreciable difference when following this method. I'd suggest that a value of 5 be used there. I think we should just change that, since everyone will just use the default anyway (or, the more cautious ones will use a higher value than the default, if anything). -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
* Peter Geoghegan (pe...@2ndquadrant.com) wrote: I am inclined to agree. I did attempt to use the instrumentation macros to have commit_delay set adaptively at runtime, which would have at least addressed this concern, but that just didn't work as well as this. Setting it at run-time was actually my first thought on this. I'm disappointed to hear that it didn't work out well. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
On 16 November 2012 01:20, Stephen Frost sfr...@snowman.net wrote: Setting it at run-time was actually my first thought on this. I'm disappointed to hear that it didn't work out well. I actually did quite a lot of research on it, that was interesting, but ultimately didn't lead to any breakthroughs. There was a couple of research papers written on this subject in the late 80s and early 90s that are cited in the transaction processing literature relatively frequently, but the models involved aren't terribly applicable to modern hardware. This is the main paper: http://www.hpl.hp.com/techreports/tandem/TR-88.1.pdf This later 1991 paper reads kind of like a critique of the 1988 paper: http://www.linux-mips.org/pub/linux/mips/people/macro/DEC/DTJ/DTJ107/DTJ107PF.PDF There doesn't appear to be much of anything in the public domain after this. I suppose that I could have spent more time pursuing this, but I very much had the sense of pursuing diminishing returns. If the whole idea of making the thing adaptive is to make it more accessible to users, well, they'll still have to turn it on themselves (presumably it will never be on by default), which is only slightly less effort than following my advice here. I suspect my tools - in particular, pgbench - just isn't sophisticated enough to evaluate the effectiveness of any particular model. I might have used something like Tsung, which modes workloads stochastically, instead. However, I just didn't have the time. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers- ow...@postgresql.org] On Behalf Of Peter Geoghegan Sent: 15 November 2012 09:44 To: PG Hackers Subject: [HACKERS] Doc patch making firm recommendation for setting the value of commit_delay Some of you will be aware that I've tried to formulate a good general recommendation for setting the value of commit_delay, in light of the improvements made in 9.3. I previously asked for help finding a methodology for optimising commit_delay [1]. The documentation related to commit_delay still says that we don't know what might work well, though I don't think that's true any more. I found the time to do some benchmarks of my own - Greg Smith made available a server that he frequently uses for benchmarks. It was previously my observation that half of raw single-page sync time as reported by pg_test_fsync for you wal_sync_method worked best for commit_delay. I went so far as to modify pg_test_fsync to output average time per op in microseconds for each operation with commit_delay in mind, which is a patch that has already been committed [2]. This general approach worked really well on my laptop, which has a slowish fsync of about 8 milliseconds (8,000 microseconds), for which a commit_delay setting of 4,000 (microseconds) seemed to clearly work best, on both insert and tpc-b benchmarks [3]. snip Thoughts? I think for sure, since the GUC maintained its original name, that the docs need to be updated to let people know the background behaviour has changed and may now be far more useful. I've read through the patch. Only thing I see out of place is a small typo: !values of varnamecommit_siblings/varname should be used is such cases, Should probably read !values of varnamecommit_siblings/varname should be used *in* such cases, Thanks for doing all the benchmarks too. Good to see such a range of different hardware tested. Regards David Rowley -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
