Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id DDE3B2E025A for ; Tue, 15 Jan 2008 11:21:08 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07758-04 for ; Tue, 15 Jan 2008 11:21:04 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 1219D2E096D for ; Tue, 15 Jan 2008 11:21:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id m0FFKxLe023471; Tue, 15 Jan 2008 10:20:59 -0500 (EST) To: Jakub Ouhrabka cc: pgsql-performance@postgresql.org Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores In-reply-to: <478CA26E.9040105@comgate.cz> References: <7083.1199379570@sss.pgh.pa.us> <477D1D59.7050002@comgate.cz> <4782063C.7070105@comgate.cz> <26502.1199745090@sss.pgh.pa.us> <4782AB4D.8010009@comgate.cz> <27655.1199750492@sss.pgh.pa.us> <47836A4C.5080707@comgate.cz> <10713.1199813111@sss.pgh.pa.us> <47874EE8.2050406@comgate.cz> <12997.1200063940@sss.pgh.pa.us> <478B4BB9.4030208@comgate.cz> <13894.1200324134@sss.pgh.pa.us> <478CA26E.9040105@comgate.cz> Comments: In-reply-to Jakub Ouhrabka message dated "Tue, 15 Jan 2008 13:09:18 +0100" Date: Tue, 15 Jan 2008 10:20:59 -0500 Message-ID: <23470.1200410459@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/125 X-Sequence-Number: 28364 Jakub Ouhrabka writes: > we've found it: TRUNCATE Huh. One transaction truncating a dozen tables? That would match the sinval trace all right ... > One more question: is it ok to do mass regexp update of pg_proc.prosrc > changing TRUNCATEs to DELETEs? You might be throwing the baby out with the bathwater, performance-wise. Mass DELETEs will require cleanup by VACUUM, and that'll likely eat more cycles and I/O than you save. I'd think in terms of trying to spread out the TRUNCATEs or check to see if you really need one (maybe the table's already empty), rather than this. I do plan to look at the sinval code once 8.3 is out the door, so another possibility if you can wait a few weeks/months is to leave your app alone and see if the eventual patch looks sane to back-patch. (I don't think the community would consider doing so, but you could run a locally modified Postgres with it.) regards, tom lane