From: Harold A. Giménez <harold.gimenez@gmail.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Daniel Farina <daniel@heroku.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Craig Ringer <ringerc@ringerc.id.au>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: DELETE vs TRUNCATE explanation
Date: Thu, 12 Jul 2012 16:21:13 -0700
Message-ID: <32078D4B40DD40ECB739D0ECD7D95996@gmail.com> (raw)
In-Reply-To: <CAMkU=1ydaypgAeZxyaMc=KhJJw5riGMBiLpvSqOO42=qArRZmQ@mail.gmail.com>
References: <4FFCCAC4.4030503@ringerc.id.au>
<CAAZKuFZ9tYeC1K5ZayZgztm3re_7ytbsEYFa0wwXirB6Hfm+EQ@mail.gmail.com>
<23853.1342015548@sss.pgh.pa.us>
<CAAZKuFYsK+ZQuC=zf0xE=M-LNa02tB7RBy3CHEUyq7LWCkK6jQ@mail.gmail.com>
<CAMkU=1ydaypgAeZxyaMc=KhJJw5riGMBiLpvSqOO42=qArRZmQ@mail.gmail.com>
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@heroku.com (mailto:daniel@heroku.com)> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org (mailto:pgsql-performance@postgresql.org))
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: harold.gimenez@gmail.com, jeff.janes@gmail.com, daniel@heroku.com, tgl@sss.pgh.pa.us, ringerc@ringerc.id.au
Subject: Re: DELETE vs TRUNCATE explanation
In-Reply-To: <32078D4B40DD40ECB739D0ECD7D95996@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox