On Wed, Jul 11, 2012 at 6:41 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote: > On 07/12/2012 06:51 AM, Daniel Farina wrote: >> >> 15x slower. This is a Macbook Air with full disk encryption and SSD >> disk with fsync off, e.g. a very typical developer configuration. > > Don't use full disk encryption for throwaway test data if you care about how > long those tests take. It's a lot like tuning the engine in your car while > ignoring the fact that the handbrake is jammed on and you're dragging a > parachute. Use a ramdisk or un-encrypted partition, something that doesn't > take three weeks to fsync(). No. Full disk encryption is not that slow. And as we see, there is a workaround that works "just fine" (maybe it could be faster, who knows?) in this exact configuration. The greater problem is more likely to be HFS+, the file system. If someone produces and gets adoption of a wonderfully packaged test-configurations of Postgres using a ram-based block device that somehow have a good user experience living alongside the persistent version, this problem can go away completely. In fact, that would be *phenomenal*, because so many things could be so much faster. But that's surprisingly challenging: for example, last I checked, Postgres.app, principally written by one of my colleagues, does *not* disable fsync because we don't know of a great way to communicate the relaxed expectations of durability, even though Postgres.app is targeted towards developers: for example, it does not run until you log in, so it's more like a foreground application. Maybe if the connection had an option that said "x-test=true", or something...deposit your idea here. Until then, this is an at the level of an is-ought problem: there is no immediate to even moderately distant future where people are not going to click the full disk encryption button their OS vendor gives them (nor should they *not* click that: people love to download bits of data from production to their local machine to figure out problems, and I think the world is a better place for it), and people are going to use HFS+ in large numbers, so talking about how many people "just" ought to reconfigure is tantamount to blaming the victim, especially when we have a sound and workable workaround in hand to at least prove definitively that the problem is not intractable. > That said, this performance gap makes me wonder if TRUNCATE is forcing > metadata synchronisation even with fsync=off, causing the incredibly > glacially awesomely slow disk access of your average FDE system to kick in, > possibly even once per table or even once per file (index, table, toast, > etc). Lousy file system is my guess. HFS is not that great. I bet ext3 would be a reasonable model of this amount of pain as well. > You could help progress this issue constructively by doing some profiling on > your system, tracing Pg's system calls, and determining what exactly it's > doing with DELETE vs TRUNCATE and where the time goes. On Linux you'd use > OProfile for this and on Solaris you'd use DTrace. Dunno what facilities Mac > OS X has but there must be something similar. I'm sure I could, but first I want to put to complete rest the notion that this is an "edge case." It's only an edge case if the only database you have runs in production. An understanding by more people that this is a problem of at least moderate impact is a good first step. I'll ask some of my more Macintosh-adept colleagues for advice. >> I've seen way more than one complaint, and I'm quite sure there are >> thousands of man hours (or more) spent on people who don't even know >> to complain about such atrocious performance (or maybe it's so bad >> that most people run a web search and find out, probably being left >> really annoyed from having to yak shave as a result). > > I suspect you're right - as DB based unit testing becomes more commonplace > this is turning up a lot more. As DB unit tests were first really popular in > the ruby/rails crowd they've probably seen the most pain, but as someone who > doesn't move in those circles I wouldn't have known. They certainly don't > seem to have been making noise about it here, and I've only recently seen > some SO questions about it. Well, here's another anecdotal data point to show how this can sneak under the radar: because this was a topic of discussion in the office today, a colleague in the Department of Data discovered his 1.5 minute testing cycle could be cut to thirty seconds. We conservatively estimate he runs the tests 30 times a day when working on his project, and probably more. Multiply that over a few weeks (not even counting the cost of more broken concentration) and we're talking a real loss of productivity and satisfaction. Here's an example of a person that works on a Postgres-oriented project at his day job, has multi-year experience with it, and can write detailed articles like these: https://devcenter.heroku.com/articles/postgresql-concurrency . If he didn't know to get this right without having it called out as a caveat, what number of people have but the most slim chance? Our best asset is probably the relative obscurity of TRUNCATE vs. DELETE for those who are less familiar with the system. I'm sure he would have found it eventually when starting to profile his tests when they hit the 3-4 minute mark, although he might just as easily said "well, TRUNCATE, that's the fast one...nothing to do there...". > That said, the group of people who care about this most are not well > represented as active contributors to PostgreSQL. I'd love it if you could > help start to change that by stepping in and taking a little time to profile > exactly what's going on with your system so we can learn what, exactly, is > slow. It's not my platform of choice, per se, but on my Ubuntu Precise on ext4 with fsync off and no disk encryption: $ rake 55.37user 2.36system 1:15.33elapsed 76%CPU (0avgtext+0avgdata 543120maxresident)k 0inputs+2728outputs (0major+85691minor)pagefaults 0swaps $ rake 53.85user 1.97system 2:04.38elapsed 44%CPU (0avgtext+0avgdata 547904maxresident)k 0inputs+2640outputs (0major+100226minor)pagefaults 0swaps Which is a not-as-pathetic slowdown, but still pretty substantial, being somewhat shy of 2x. I'll ask around for someone who is Macintosh-OS-inclined (not as a user, but as a developer) about a good way to get a profile. -- fdr -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
