Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6DF122E013A for ; Tue, 15 Jan 2008 12:26:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 41562-03 for ; Tue, 15 Jan 2008 12:26:04 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0D8092E00EA for ; Tue, 15 Jan 2008 12:26: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 m0FGQ1lD024677; Tue, 15 Jan 2008 11:26:01 -0500 (EST) To: Jakub Ouhrabka cc: pgsql-performance@postgresql.org Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores In-reply-to: <478CD535.6030004@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> <23470.1200410459@sss.pgh.pa.us> <478CD535.6030004@comgate.cz> Comments: In-reply-to Jakub Ouhrabka message dated "Tue, 15 Jan 2008 16:45:57 +0100" Date: Tue, 15 Jan 2008 11:26:00 -0500 Message-ID: <24676.1200414360@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/128 X-Sequence-Number: 28367 Jakub Ouhrabka writes: >>> Huh. One transaction truncating a dozen tables? That would match the >>> sinval trace all right ... > It should be 4 tables - the shown log looks like there were more truncates? Actually, counting up the entries, there are close to 2 dozen relations apparently being truncated in the trace you showed. But that might be only four tables at the user level, since each index on these tables would appear separately, and you might have a toast table plus index for each one too. If you want to dig down, the table OIDs are visible in the trace, in the messages with type -1: >> LOG: sending inval msg -1 0 30036 0 30700 3218341912 ^^^^^ ^^^^^ DBOID RELOID so you could look into pg_class to confirm what's what. > Yes, performance was the initial reason to use truncate instead of > delete many years ago. But today the truncated tables usualy contain > exactly one row - quick measurements now show that it's faster to issue > delete instead of truncate in this case. Okay, for a table of just a few entries I agree that DELETE is probably better. But don't forget you're going to need to have those tables vacuumed fairly regularly now, else they'll start to bloat. regards, tom lane