Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 349DB2E2EA7 for ; Mon, 14 Jan 2008 11:22:21 -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 88821-04 for ; Mon, 14 Jan 2008 11:22:17 -0400 (AST) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9A8FC2E0114 for ; Mon, 14 Jan 2008 11:22:17 -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 m0EFME79013895; Mon, 14 Jan 2008 10:22:14 -0500 (EST) To: Jakub Ouhrabka cc: pgsql-performance@postgresql.org Subject: Re: Linux/PostgreSQL scalability issue - problem with 8 cores In-reply-to: <478B4BB9.4030208@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> Comments: In-reply-to Jakub Ouhrabka message dated "Mon, 14 Jan 2008 12:47:05 +0100" Date: Mon, 14 Jan 2008 10:22:14 -0500 Message-ID: <13894.1200324134@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/118 X-Sequence-Number: 28357 Jakub Ouhrabka writes: > What does it mean? Look at src/include/storage/sinval.h and src/include/utils/syscache.h. What you seem to have here is a bunch of tuple updates in pg_class (invalidating caches 29 and 30, which in 8.2 correspond to RELNAMENSP and RELOID), followed by a bunch of SharedInvalRelcacheMsg and SharedInvalSmgrMsg. What I find interesting is that the hits are coming against nearly-successive tuple CTIDs in pg_class, eg these are all on pages 25 and 26 of pg_class: > LOG: sending inval msg 30 0 25 45 30036 4294936595 > LOG: sending inval msg 29 0 25 45 30036 2019111801 > LOG: sending inval msg 30 0 26 11 30036 4294936595 > LOG: sending inval msg 29 0 26 11 30036 2019111801 > LOG: sending inval msg 30 0 25 44 30036 4294936597 > LOG: sending inval msg 29 0 25 44 30036 3703878920 > LOG: sending inval msg 30 0 26 10 30036 4294936597 > LOG: sending inval msg 29 0 26 10 30036 3703878920 > LOG: sending inval msg 30 0 26 9 30036 4294936616 > LOG: sending inval msg 29 0 26 9 30036 3527122063 > LOG: sending inval msg 30 0 25 43 30036 4294936616 > LOG: sending inval msg 29 0 25 43 30036 3527122063 The ordering is a little strange --- not sure what's producing that. I can think of three things that might be producing this: 1. DDL operations ... but most sorts of DDL on a table would touch more catalogs than just pg_class, so this is a bit hard to credit. 2. VACUUM. 3. Some sort of direct update of pg_class. The fact that we have a bunch of catcache invals followed by relcache/smgr invals says that this all happened in one transaction, else they'd have been intermixed better. That lets VACUUM off the hook, because it processes each table in a separate transaction. I am wondering if maybe your app does one of those sneaky things like fooling with pg_class.reltriggers. If so, the problem might be soluble by just avoiding unnecessary updates. regards, tom lane