X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A6AAC9DCD9D for ; Thu, 23 Mar 2006 23:11:40 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91170-07 for ; Thu, 23 Mar 2006 23:11:44 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nproxy.gmail.com (nproxy.gmail.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 485469DCD88 for ; Thu, 23 Mar 2006 23:11:36 -0400 (AST) Received: by nproxy.gmail.com with SMTP id c31so381654nfb for ; Thu, 23 Mar 2006 19:11:41 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Za9w9fYROaJqZamLts7gMNGvbVg263sCD+KQKgdySfizlwYaSzH3hrUBPFNsBHdFRtngHhYmm/3G3XW8jLRlho5khPpawgfWk0MssPq/Jfg8wfmm6JSqS2czUPn0TbIP9cjwSqICzyFzsQUhSCnfTPLIOxQGvSocgp91tW/lpno= Received: by 10.48.221.4 with SMTP id t4mr47771nfg; Thu, 23 Mar 2006 19:11:40 -0800 (PST) Received: by 10.48.144.19 with HTTP; Thu, 23 Mar 2006 19:11:40 -0800 (PST) Message-ID: Date: Thu, 23 Mar 2006 22:11:40 -0500 From: "Gregory Maxwell" To: "Gregory Maxwell" , pgsql-admin@postgresql.org Subject: Re: Bloated pg_shdepend_depender_index In-Reply-To: <20060324023105.GB11242@surnet.cl> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20060324023105.GB11242@surnet.cl> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.812 required=5 tests=[AWL=-0.520, RCVD_IN_BL_SPAMCOP_NET=1.332] X-Spam-Score: 0.812 X-Spam-Level: X-Archive-Number: 200603/226 X-Sequence-Number: 20983 On 3/23/06, Alvaro Herrera wrote: > Gregory Maxwell wrote: > > > I recently noticed that this database has grown to a huge size. ... > > Which I found to be somewhat odd because none of the tables have more > > than around 1000 rows. I hadn't been vacuuming because I didn't > > think that anything would ever be deleted.... so I performed a vacuum > > full... but no luck, it was still about 6.4GB. > > > > With some help of the folks on IRC I discovered... > > postgres=3D# select relname, pg_relation_size(oid) FROM pg_class ORDER > > BY 2 DESC LIMIT 2; > > relname | pg_relation_size > > -----------------------------+------------------ > > pg_shdepend_depender_index | 159465472 > > pg_shdepend_reference_index | 97271808 > > (2 rows) > > Huh, that's very strange. > > The pg_shdepend table is a shared catalog, unlike most other catalogs. > Still I don't see why would the indexes not shrink. Do pg_authid, > pg_database, or pg_tablespace show a similar behavior? Is amarok > creating a lot of temp tables or something? When you did that vacuum > full, was there a process connected to the same or another database that > could be holding onto the pg_shdepend table? > > I'll have a look at the code, but I thought I might throw that out, just > in case it rings a bell. Those were the only two objects impacted. I'm not sure what amarok does.. a quick glance at the source shows that it does use temp tables, but I'm not sure how frequently. When I vacuum fulled nothing else was connected.. I just restarted PG=20 and vacuumed again.. no obvious change of disk size (still.. 6.4 gigs).. but this changed: amarokcollection=3D# select relname, pg_relation_size(oid) FROM pg_class ORDER BY 2 DESC LIMIT 20; relname | pg_relation_size ---------------------------------+------------------ pg_attribute_relid_attnam_index | 2881069056 pg_class_relname_nsp_index | 1185890304 pg_attribute_relid_attnum_index | 710565888 pg_depend_depender_index | 451190784 pg_depend_reference_index | 422363136 pg_type_typname_nsp_index | 245293056 pg_class_oid_index | 208420864 pg_shdepend_depender_index | 159465472 pg_shdepend_reference_index | 97271808 pg_constraint_conname_nsp_index | 55107584 pg_index_indrelid_index | 50774016 pg_index_indexrelid_index | 50774016 pg_type_oid_index | 44400640 pg_attrdef_adrelid_adnum_index | 15761408 pg_constraint_conrelid_index | 15728640 pg_constraint_oid_index | 15728640 pg_attrdef_oid_index | 12541952 pg_constraint_contypid_index | 7618560 statistics_url_key | 2113536 url_stats | 2105344 The old output was: postgres=3D# select relname, pg_relation_size(oid) FROM pg_class ORDER BY 2 DESC LIMIT 10; relname | pg_relation_size ---------------------------------+------------------ pg_shdepend_depender_index | 159465472 pg_shdepend_reference_index | 97271808 pg_proc | 352256 pg_proc_proname_args_nsp_index | 344064 pg_depend | 237568 pg_attribute_relid_attnam_index | 204800 pg_attribute | 204800 pg_depend_reference_index | 172032 pg_depend_depender_index | 155648 pg_statistic | 131072 (10 rows) I'm at your disposal on this.. it's weird.