Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id CBCF11E3525D for ; Thu, 12 Jul 2012 20:21:30 -0300 (ADT) Received: from mail-pb0-f46.google.com ([209.85.160.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SpShN-0005pf-J7 for pgsql-performance@postgresql.org; Thu, 12 Jul 2012 23:21:30 +0000 Received: by pbbrp8 with SMTP id rp8so4453769pbb.19 for ; Thu, 12 Jul 2012 16:21:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=date:from:to:cc:message-id:in-reply-to:references:subject:x-mailer :mime-version:content-type; bh=m2lRJTGU5VIN3DCBDg7YvLkRicFTEQtiW7NYkPjPYOg=; b=VUKvv+3qTzdkGQP01BiZWdNyWLF3QrFxBdyU77i/FXHY1HqWoJ1SrfwbxebXc+X9X3 R2Vv6+IDsgVA8hZo7KQW42//WQnAgUFg5fwEzmhigE5w8C52MI+gqeprHWPloE9FNJ0i Dji9ILLNjp5+L4Jik2ZFCy1WRjWJh1PYf8znnE/vFxTVEFsL+LtHi5xksg6hIsbAwYKS mib5nai6POvlYqEpkYn67Qsccltffi/kb5nF86/5fdhOktnO5xW3QqEWDONQN9syzfXS 24dRqhxR7kCAFR1JbETafF1Jx5IFXJxT0VGoIL6keiksk6a5x8n77Ai4oqwhGtGyHwH1 IBbg== Received: by 10.68.220.193 with SMTP id py1mr9680390pbc.4.1342135276618; Thu, 12 Jul 2012 16:21:16 -0700 (PDT) Received: from [10.10.10.215] ([204.14.152.118]) by mx.google.com with ESMTPS id wi6sm4710228pbc.35.2012.07.12.16.21.14 (version=TLSv1/SSLv3 cipher=OTHER); Thu, 12 Jul 2012 16:21:15 -0700 (PDT) Date: Thu, 12 Jul 2012 16:21:13 -0700 From: "=?utf-8?Q?Harold_A._Gim=C3=A9nez?=" To: Jeff Janes Cc: Daniel Farina , Tom Lane , Craig Ringer , "=?utf-8?Q?pgsql-performance=40postgresql.org?=" Message-ID: <32078D4B40DD40ECB739D0ECD7D95996@gmail.com> In-Reply-To: References: <4FFCCAC4.4030503@ringerc.id.au> <23853.1342015548@sss.pgh.pa.us> Subject: Re: DELETE vs TRUNCATE explanation X-Mailer: sparrow 1.6.2 (build 1144) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="4fff5be9_4b683d0d_f9ea" X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201207/159 X-Sequence-Number: 47340 --4fff5be9_4b683d0d_f9ea Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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 > > --4fff5be9_4b683d0d_f9ea Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi,

I work with Daniel =46arina and= was the other engineer who =22discovered=22 this, once again. That is, I= got bit by it and have been running TRUNCATE on my test suites for years= .

=20

On Thursday, July 12, = 2012 at 12:15 PM, Jeff Janes wrote:

On Wed, Jul 11, 2012 at 3:51 PM,= Daniel =46arina <daniel=40= heroku.com> wrote:
Nope. I don't. But an exact crossover is a level of precisi= on 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
=22regular web-app=22 profile case:

With en-masse DELETE:
rake 41.89s user 3.08s s= ystem 76% cpu 58.629 total

With TRUNCATE:
<= div>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 configu= ration.

What is shared=5Fbuff= ers=3F

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. Thi= s application is entirely
unremarkable in its test-database wor= kload: it wants to load a few
records, do a few things, and the= n clear those handful of records.

=
How many rounds of truncation does one rake do=3F I.e. how many
truncations are occurring over the course of that 1 minute or 15
minutes=3F

<= div>All tables are cleared out after every test. On this particular proje= ct, I'm running 200+ tests in 1.5 minutes (or 30 seconds with DELETE inst= ead of TRUNCATE). =46or another, bigger project it's running 17= 00+ 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 teac= hing TRUNCATE about small tables and optimizing for that, as well as a se= ction in the docs about postgres tweaks for test suites. I'm sure many pe= ople have done independent research in this area, and it'd be great to ha= ve it documented in one place.

-Harold 

Cheers,

Jeff

--
Sent via pgsql-performance mailing list (<= a href=3D=22mailto:pgsql-performance=40postgresql.org=22>pgsql-performanc= e=40postgresql.org)
To make changes to your subscription:
=
=20 =20 =20 =20
=20

--4fff5be9_4b683d0d_f9ea--