Hi,
I work with Daniel Farina and was the other engineer who "discovered" this, once again. That is, I got bit by it and have been running TRUNCATE on my test suites for years.
On Thursday, July 12, 2012 at 12:15 PM, Jeff Janes wrote:
Nope. I don't. But an exact crossover is a level of precision I don't
really need, because here are where things stand on a completely
unremarkable test suite on the closest project to me that meets the
"regular web-app" profile case:
With en-masse DELETE:
rake 41.89s user 3.08s system 76% cpu 58.629 total
With TRUNCATE:
rake 49.86s user 2.93s system 5% cpu 15:17.88 total
15x slower. This is a Macbook Air with full disk encryption and SSD
disk with fsync off, e.g. a very typical developer configuration.
What is shared_buffers?
1600kB
Not sure this will make much difference with such small data, but of course I could be dead wrong here.
This is a rather small schema -- probably a half a dozen tables, and
probably about a dozen indexes. This application is entirely
unremarkable in its test-database workload: it wants to load a few
records, do a few things, and then clear those handful of records.
How many rounds of truncation does one rake do? I.e. how many
truncations are occurring over the course of that 1 minute or 15
minutes?
All tables are cleared out after every test. On this particular project, I'm running 200+ tests in 1.5 minutes (or 30 seconds with DELETE instead of TRUNCATE). For another, bigger project it's running 1700+ tests in about a minute. You can do the math from there.
I'd say this is not atypical at all, so I too encourage teaching TRUNCATE about small tables and optimizing for that, as well as a section in the docs about postgres tweaks for test suites. I'm sure many people have done independent research in this area, and it'd be great to have it documented in one place.
-Harold
Cheers,
Jeff
--
To make changes to your subscription: