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:
On Wed, Jul 11, 2012 at 3:51 PM, Daniel Farina <daniel@xxxxxxxxxx> wrote:Nope. I don't. But an exact crossover is a level of precision I don'treally need, because here are where things stand on a completelyunremarkable 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 totalWith TRUNCATE:rake 49.86s user 2.93s system 5% cpu 15:17.88 total15x slower. This is a Macbook Air with full disk encryption and SSDdisk 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, andprobably about a dozen indexes. This application is entirelyunremarkable in its test-database workload: it wants to load a fewrecords, do a few things, and then clear those handful of records.How many rounds of truncation does one rake do? I.e. how manytruncations are occurring over the course of that 1 minute or 15minutes?
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--Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)To make changes to your subscription:
