X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.192]) by svr1.postgresql.org (Postfix) with ESMTP id 1C7A8CBFF1 for ; Sat, 19 Jul 2003 21:22:13 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr4.postgresql.org [64.117.224.192]) (amavisd-new, port 10024) with ESMTP id 89292-06 for ; Sat, 19 Jul 2003 21:22:46 +0000 (GMT) Received: from ctg-msnex01.staff.berbee.com (msn-office1.binc.net [64.73.12.254]) by svr1.postgresql.org (Postfix) with ESMTP id 79482CB516 for ; Sat, 19 Jul 2003 18:22:11 -0300 (ADT) Received: from localhost ([172.30.254.220] RDNS failed) by ctg-msnex01.staff.berbee.com with Microsoft SMTPSVC(5.0.2195.5329); Sat, 19 Jul 2003 16:22:47 -0500 From: "Jeremy M. Guthrie" Reply-To: jeremy.guthrie@berbee.com Organization: Berbee Information Networks To: pgsql-performance@postgresql.org Subject: Poor delete performance AFTER vacuum analyze Date: Sat, 19 Jul 2003 16:22:40 -0500 User-Agent: KMail/1.5.1 MIME-Version: 1.0 Content-Type: Text/Plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Content-Description: clearsigned data Content-Disposition: inline Message-Id: <200307191622.46709.jeremy.guthrie@berbee.com> X-OriginalArrivalTime: 19 Jul 2003 21:22:47.0847 (UTC) FILETIME=[E6C8EB70:01C34E3B] X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/308 X-Sequence-Number: 2601 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, I have an application I wrote that uses two tables heavily. The first tab= le=20 is a queue table, the second table is an archive table. The queue table is= =20 constantly taking on new records at the rate of 10/second. My software pul= ls=20 the logs from the queue table, processes them, moves them to the archive=20 table, and then deletes the processed entries from the queue table. Symptoms: My system will run great after a full vacuum(as I would expect). It will = run=20 all day long taking only 3-5 seconds to run and deal with approximately=20 100megs of new data each day. However, the instant the system finishes onl= y=20 a 'vacuum analyze', the whole thing slows WAY down to where each run can ta= ke=20 10-15 minutes. ie. After a full analyze vacuum, my software will load, process, move, an= d=20 delete in under 3-4 seconds. After a analyze vacuum(notice: not full), it= =20 can load, process and move data in 3-5 seconds but the delete takes 10-15= =20 minutes! I submit the delete as one transaction to clear out the records= =20 processed. Trunactae won't work because other records are coming in while = I=20 process data. Mind you the archive table is 15 million records while the= =20 temporary table is maybe 10-20,000 records.=20 Now I just rewrote a portion of my application to change its behavior. Wh= at=20 it did before was that it would pile through a 10 gig archive table,=20 processed logs, etc... in about 3 minutes but I did not delete in the same= =20 way because everything is already in one table. My software has to run eve= ry=20 five minutes so the three minute runtime is getting close for process=20 overlap(yuck).=20 Recap The old system didn't delete records but plowed through the 10 gig db and= =20 takes 3 1/2 minutes to do its job. The new system flies through the smalle= r=20 queue table(100-200k) but it dies after conducting a non-full vacuum. Is the planner just that much better at analyzing a full then an regular= =20 analyze or is there something else I'm missing? The Box: The DB is a dual P4 2.4ghz Xeon w/ 1.5 gig of RAM. IBM 335 w/ 36gig mirror= ed. kernel.shmmax =3D 1342177280 shared_buffers =3D 115200 # 2*max_connections, min 16 sort_mem =3D 65536 # min 32 vacuum_mem =3D 196608 # min 1024 fsync =3D false - --=20 Jeremy M. Guthrie Systems Engineer Berbee 5520 Research Park Dr. Madison, WI 53711 Phone: 608-298-1061 Berbee...Decade 1. 1993-2003 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/GbajqtjaBHGZBeURAkKiAJ9zaqQISD47XycRcSgDKbNeuqqaKQCfcgim yCdaycBg4+99Epd7EuAAxsE=3D =3D9xlS -----END PGP SIGNATURE-----