X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A766632A8ED for ; Wed, 20 Oct 2004 08:51:08 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 31150-03 for ; Wed, 20 Oct 2004 07:51:01 +0000 (GMT) Received: from mail1.catalyst.net.nz (godel.catalyst.net.nz [202.49.159.12]) by svr1.postgresql.org (Postfix) with ESMTP id 4C5A232A892 for ; Wed, 20 Oct 2004 08:51:00 +0100 (BST) Received: from 222-152-142-149.jetstream.xtra.co.nz ([222.152.142.149] helo=lamb.mcmillan.net.nz) by mail1.catalyst.net.nz with asmtp (TLS-1.0:DHE_RSA_3DES_EDE_CBC_SHA:24) (Exim 4.34) id 1CKBFH-00032E-Rv; Wed, 20 Oct 2004 20:50:56 +1300 Received: from lamb.mcmillan.net.nz (lamb.mcmillan.net.nz [127.0.0.1]) by lamb.mcmillan.net.nz (Postfix) with ESMTP id 59E66AD97BBE; Wed, 20 Oct 2004 20:50:45 +1300 (NZDT) Subject: Re: Insert performance, what should I expect? From: Andrew McMillan To: Brock Henry Cc: pgsql-performance@postgresql.org In-Reply-To: <97b3fe2041019185337a8c3d8@mail.gmail.com> References: <97b3fe2041019185337a8c3d8@mail.gmail.com> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-fDpxLEsYDsVSux/w1NKM" Date: Wed, 20 Oct 2004 20:50:44 +1300 Message-Id: <1098258644.22373.161.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 2.0.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/308 X-Sequence-Number: 8784 --=-fDpxLEsYDsVSux/w1NKM Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Wed, 2004-10-20 at 11:53 +1000, Brock Henry wrote: >=20 > Test 1, For each import, I'm dropping all indexes and pkeys/fkeys, > then importing, then adding keys and indexes. Then I've got successive > runs. I figure the reindexing will get more expensive as the database > grows? Sounds like the right approach to me, if the tables are empty before the import. > Successive Imports: 44,49,50,57,55,61,72 (seconds) > =3D average 1051inserts/second (which now that I've written this seems > fairly good) (A) Are you doing the whole thing inside a transaction? This will be significantly quicker. COPY would probably be quicker still, but the biggest difference will be a single transaction. (B) If you are starting with empty files, are you ensuring that the dead records are vacuumed before you start? I would recommend a "vacuum full" on the affected tables prior to the first import run (i.e. when the tables are empty). This is likely to be the reason that the timing on your successive imports increases so much. > sort_mem =3D 4096=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 You probably want to increase this - if you have 1G of RAM then there is probably some spare. But if you actually expect to use 32 connections then 32 * 4M =3D 128M might mean a careful calculation is needed. If you are really only likely to have 1-2 connections running concurrently then increase it to (e.g.) 32768. > max_fsm_relations =3D 300=20=20 If you do a "vacuum full verbose;" the last line will give you some clues as to what to set this (and max_fsm_pages) too. > effective_cache_size =3D 16000=20=20=20=20 16000 * 8k =3D 128M seems low for a 1G machine - probably you could say 64000 without fear of being wrong. What does "free" show as "cached"? Depending on how dedicated the machine is to the database, the effective cache size may be as much as 80-90% of that. > Can I expect it to go faster than this? I'll see where I can make my > script itself go faster, but I don't think I'll be able to do much. > I'll do some pre-prepare type stuff, but I don't expect significant > gains, maybe 5-10%. I'd could happily turn off fsync for this job, but > not for some other databases the server is hosting. You can probably double the speed - maybe more. Cheers, Andrew, ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 How many things I can do without! -- Socrates ------------------------------------------------------------------------- --=-fDpxLEsYDsVSux/w1NKM Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.5 (GNU/Linux) iD8DBQBBdhjUjJA0f48GgBIRAqjyAKC8ABSJoxFHklEVFE5KethNR5j5ZQCdHmMP mSFjmB/Yjs3HNcSYWoJdeeM= =mKj3 -----END PGP SIGNATURE----- --=-fDpxLEsYDsVSux/w1NKM--