From aris@quickschools.com Sun Nov  8 03:22:43 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 82354633F43
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat,  7 Nov 2009 23:22:43 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 44946-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 03:22:23 +0000 (UTC)
X-Greylist: delayed 00:06:40.762633 by SQLgrey-1.7.6
Received: from smtpauth22.prod.mesa1.secureserver.net
	(smtpauth22.prod.mesa1.secureserver.net [64.202.165.44])
	by mail.postgresql.org (Postfix) with SMTP id 4430E63268E
	for <pgsql-performance@postgresql.org>;
	Sat,  7 Nov 2009 23:22:31 -0400 (AST)
Received: (qmail 23737 invoked from network); 8 Nov 2009 03:15:48 -0000
Received: from unknown (12.110.105.2)
	by smtpauth22.prod.mesa1.secureserver.net (64.202.165.44) with ESMTP;
	08 Nov 2009 03:15:47 -0000
From: "Aris Samad-Yahaya" <aris@quickschools.com>
To: <pgsql-performance@postgresql.org>
Subject: CREATE TABLE slowing down significantly over time
Date: Sat, 7 Nov 2009 22:15:40 -0500
Message-ID: <008801ca6021$c1cc7630$45656290$@com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0089_01CA5FF7.D8F66E30"
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcpgIb4ht6AD3IngQVO7bgIIC+qQFw==
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200911/79
X-Sequence-Number: 36247

This is a multi-part message in MIME format.

------=_NextPart_000_0089_01CA5FF7.D8F66E30
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

I'm facing a problem where running a CREATE TABLE has slowed down
significantly over time.

 

This is problematic because my application needs to routinely create a new
schema and create 300 tables in each new schema. In total it takes about 3
minutes, which may not seem like a big deal, but this is time sensitive
because users of our SaaS application are waiting in real-time for the
schema and 300 the tables to be created.

 

It used to take about 15 seconds to create those 300 tables in a new schema
(when there were only a few schemas, say about 50). It now takes about 3
minutes (and now we have about 200 schemas, with more data but not hugely
so).

 

To debug this problem, I've created a new database in a separate (and dinky)
laptop, and running a single test CREATE TABLE command takes about 19 ms.

 

But on the server with 200+ schemas, this single command takes between 200
and 300 ms.

 

My test command on psql is:

CREATE TABLE <TheSchemaName>.academicsemesters (

    id text NOT NULL,

    creationdate timestamp with time zone,

    academicsemestername text,

    academicyearandsemestername text,

    startdate timestamp with time zone,

    enddate timestamp with time zone,

    isplanningmode boolean NOT NULL,

    isclosed boolean NOT NULL,

    isactive boolean NOT NULL,

    status text,

    workflowstubid text,

    deleted boolean NOT NULL,

    academicyearid text

);

 

* Any tips anyone can give on what might be the underlying cause of the
slowing down of the CREATE TABLE command over time?

* Is the problem caused by the increasing number of schemas?

 

Thanks in advance,

Aris


------=_NextPart_000_0089_01CA5FF7.D8F66E30
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
	{mso-style-priority:34;
	margin-top:0in;
	margin-right:0in;
	margin-bottom:0in;
	margin-left:.5in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
	{page:Section1;}
 /* List Definitions */
 @list l0
	{mso-list-id:1195968935;
	mso-list-type:hybrid;
	mso-list-template-ids:1152803452 694983140 67698691 67698693 67698689 =
67698691 67698693 67698689 67698691 67698693;}
@list l0:level1
	{mso-level-start-at:0;
	mso-level-number-format:bullet;
	mso-level-text:\F0B7;
	mso-level-tab-stop:none;
	mso-level-number-position:left;
	text-indent:-.25in;
	font-family:Symbol;
	mso-fareast-font-family:Calibri;
	mso-bidi-font-family:"Times New Roman";}
ol
	{margin-bottom:0in;}
ul
	{margin-bottom:0in;}
-->
</style>
<!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext=3D"edit">
  <o:idmap v:ext=3D"edit" data=3D"1" />
 </o:shapelayout></xml><![endif]-->
</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal>I&#8217;m facing a problem where running a CREATE =
TABLE has
slowed down significantly over time.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>This is problematic because my application needs to =
routinely
create a new schema and create 300 tables in each new schema. In total =
it takes
about 3 minutes, which may not seem like a big deal, but this is time =
sensitive
because users of our SaaS application are waiting in real-time for the =
schema and
300 the tables to be created.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>It used to take about 15 seconds to create those =
300 tables
in a new schema (when there were only a few schemas, say about 50). It =
now
takes about 3 minutes (and now we have about 200 schemas, with more data =
but
not hugely so).<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>To debug this problem, I&#8217;ve created a new =
database in
a separate (and dinky) laptop, and running a single test CREATE TABLE =
command
takes about 19 ms.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>But on the server with 200+ schemas, this single =
command
takes between 200 and 300 ms.<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>My test command on psql is:<o:p></o:p></p>

<p class=3DMsoNormal>CREATE TABLE =
&lt;TheSchemaName&gt;.academicsemesters (<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; id text NOT NULL,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; creationdate timestamp with time =
zone,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; academicsemestername =
text,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; academicyearandsemestername =
text,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; startdate timestamp with time =
zone,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; enddate timestamp with time =
zone,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; isplanningmode boolean NOT =
NULL,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; isclosed boolean NOT =
NULL,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; isactive boolean NOT =
NULL,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; status text,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; workflowstubid =
text,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; deleted boolean NOT =
NULL,<o:p></o:p></p>

<p class=3DMsoNormal>&nbsp;&nbsp;&nbsp; academicyearid =
text<o:p></o:p></p>

<p class=3DMsoNormal>);<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>* Any tips anyone can give on what might be the =
underlying
cause of the slowing down of the CREATE TABLE command over =
time?<o:p></o:p></p>

<p class=3DMsoNormal>* Is the problem caused by the increasing number of =
schemas?<o:p></o:p></p>

<p class=3DMsoNormal><o:p>&nbsp;</o:p></p>

<p class=3DMsoNormal>Thanks in advance,<o:p></o:p></p>

<p class=3DMsoNormal>Aris<o:p></o:p></p>

</div>

</body>

</html>

------=_NextPart_000_0089_01CA5FF7.D8F66E30--




From tgl@sss.pgh.pa.us Sun Nov  8 03:29:33 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 0389F633F43
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat,  7 Nov 2009 23:29:33 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 46114-06
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 03:29:13 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by mail.postgresql.org (Postfix) with ESMTP id 4B8E263268E
	for <pgsql-performance@postgresql.org>;
	Sat,  7 Nov 2009 23:29:22 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id nA83TKbA018870;
	Sat, 7 Nov 2009 22:29:20 -0500 (EST)
To: "Aris Samad-Yahaya" <aris@quickschools.com>
cc: pgsql-performance@postgresql.org
Subject: Re: CREATE TABLE slowing down significantly over time 
In-reply-to: <008801ca6021$c1cc7630$45656290$@com> 
References: <008801ca6021$c1cc7630$45656290$@com>
Comments: In-reply-to "Aris Samad-Yahaya" <aris@quickschools.com>
	message dated "Sat, 07 Nov 2009 22:15:40 -0500"
Date: Sat, 07 Nov 2009 22:29:20 -0500
Message-ID: <18869.1257650960@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.242 tagged_above=-10 required=5 tests=AWL=-1.243,
	BAYES_50=0.001
X-Spam-Level: 
X-Archive-Number: 200911/80
X-Sequence-Number: 36248

"Aris Samad-Yahaya" <aris@quickschools.com> writes:
> I'm facing a problem where running a CREATE TABLE has slowed down
> significantly over time.

System catalog bloat maybe?  What are your vacuuming practices?

			regards, tom lane



From craig@postnewspapers.com.au Sun Nov  8 03:47:58 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 7AD486340D8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sat,  7 Nov 2009 23:47:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 84488-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 03:47:50 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.postnewspapers.com.au
	(202-89-185-120.static.dsl.amnet.net.au [202.89.185.120])
	by mail.postgresql.org (Postfix) with ESMTP id 70DE6633317
	for <pgsql-performance@postgresql.org>;
	Sat,  7 Nov 2009 23:47:50 -0400 (AST)
Received: from localhost (access [127.0.0.1])
	by mail.postnewspapers.com.au (Postfix) with ESMTP id 868461E043F;
	Sun,  8 Nov 2009 11:47:45 +0800 (WST)
X-Virus-Scanned: Debian amavisd-new at postnewspapers.com.au
Received: from mail.postnewspapers.com.au ([127.0.0.1])
	by localhost (access.postnewspapers.com.au [127.0.0.1]) (amavisd-new,
	port 10024)
	with LMTP id X9RiQHcgMSqW; Sun,  8 Nov 2009 11:47:43 +0800 (WST)
Received: from [10.1.1.6] (124-169-36-119.dyn.iinet.net.au [124.169.36.119])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(Client CN "Craig Ringer",
	Issuer "POST Certificate Authority" (verified OK))
	by mail.postnewspapers.com.au (Postfix) with ESMTPSA id 4516B1E043D;
	Sun,  8 Nov 2009 11:47:43 +0800 (WST)
Message-ID: <4AF63F5B.2060300@postnewspapers.com.au>
Date: Sun, 08 Nov 2009 11:47:39 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-GB;
	rv:1.9.1.1) Gecko/20090715 Thunderbird/3.0b3
MIME-Version: 1.0
To: Aris Samad-Yahaya <aris@quickschools.com>
CC: pgsql-performance@postgresql.org
Subject: Re: CREATE TABLE slowing down significantly over time
References: <008801ca6021$c1cc7630$45656290$@com>
In-Reply-To: <008801ca6021$c1cc7630$45656290$@com>
X-Enigmail-Version: 0.97a
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.11 tagged_above=-10 required=5 tests=BAYES_05=-1.11
X-Spam-Level: 
X-Archive-Number: 200911/81
X-Sequence-Number: 36249

On 8/11/2009 11:15 AM, Aris Samad-Yahaya wrote:

> It used to take about 15 seconds to create those 300 tables in a new
> schema (when there were only a few schemas, say about 50). It now takes
> about 3 minutes (and now we have about 200 schemas, with more data but
> not hugely so).

200 schemas, 300 tables per schema. That's sixty THOUSAND tables.

> * Is the problem caused by the increasing number of schemas?

and increasing table count, I expect.

You do batch the table and schema creation into a single transaction,
right? If not, do that first, rather than creating each table in a
separate transaction (ie: relying on autocommit).

It may also be worth thinking about the app's design. Is a new schema
and 300 new tables for each user really the best way to tackle what
you're doing? (It might be, but it's a question worth asking yourself).

--
Craig Ringer



From aris@quickschools.com Sun Nov  8 04:55:18 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id A6ABA6327A8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 00:55:18 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 23836-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 04:55:07 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtpauth23.prod.mesa1.secureserver.net
	(smtpauth23.prod.mesa1.secureserver.net [64.202.165.47])
	by mail.postgresql.org (Postfix) with SMTP id D6DA763268E
	for <pgsql-performance@postgresql.org>;
	Sun,  8 Nov 2009 00:55:07 -0400 (AST)
Received: (qmail 7204 invoked from network); 8 Nov 2009 04:55:05 -0000
Received: from unknown (12.110.105.2)
	by smtpauth23.prod.mesa1.secureserver.net (64.202.165.47) with ESMTP;
	08 Nov 2009 04:55:05 -0000
From: "Aris Samad-Yahaya" <aris@quickschools.com>
To: "'Craig Ringer'" <craig@postnewspapers.com.au>
Cc: <pgsql-performance@postgresql.org>
References: <008801ca6021$c1cc7630$45656290$@com>
	<4AF63F5B.2060300@postnewspapers.com.au>
In-Reply-To: <4AF63F5B.2060300@postnewspapers.com.au>
Subject: Re: CREATE TABLE slowing down significantly over time
Date: Sat, 7 Nov 2009 23:55:00 -0500
Message-ID: <009501ca602f$a13169e0$e3943da0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcpgJj17xVyV6oFgRwKILOMjVnUUvwACOMDg
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=AWL=0.001,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/82
X-Sequence-Number: 36250

Hi Craig,

Yes we do put the creation of the 300 tables into a single transaction. The
difference between putting them in a single transaction and individual
transactions is about 30 seconds over the 3 minutes.

As for the creation of 300 individual tables for an account... yes we were
trying to think through that issue very hard. It's the SaaS maturity levels
discussion: How much do you separate the databases for each account, vs
sharing customer information into large tables. I hear SalesForce puts most
everything in giant tables, whereas we've decided to separate customer
accounts into separate schemas.

-----Original Message-----
From: Craig Ringer [mailto:craig@postnewspapers.com.au] 
Sent: Saturday, November 07, 2009 10:48 PM
To: Aris Samad-Yahaya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] CREATE TABLE slowing down significantly over time

On 8/11/2009 11:15 AM, Aris Samad-Yahaya wrote:

> It used to take about 15 seconds to create those 300 tables in a new
> schema (when there were only a few schemas, say about 50). It now takes
> about 3 minutes (and now we have about 200 schemas, with more data but
> not hugely so).

200 schemas, 300 tables per schema. That's sixty THOUSAND tables.

> * Is the problem caused by the increasing number of schemas?

and increasing table count, I expect.

You do batch the table and schema creation into a single transaction,
right? If not, do that first, rather than creating each table in a
separate transaction (ie: relying on autocommit).

It may also be worth thinking about the app's design. Is a new schema
and 300 new tables for each user really the best way to tackle what
you're doing? (It might be, but it's a question worth asking yourself).

--
Craig Ringer




From aris@quickschools.com Sun Nov  8 04:58:19 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 183966327A8
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 00:58:19 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 92675-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 04:58:11 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtpauth19.prod.mesa1.secureserver.net
	(smtpauth19.prod.mesa1.secureserver.net [64.202.165.30])
	by mail.postgresql.org (Postfix) with SMTP id 2D6C163268E
	for <pgsql-performance@postgresql.org>;
	Sun,  8 Nov 2009 00:58:11 -0400 (AST)
Received: (qmail 29217 invoked from network); 8 Nov 2009 04:58:09 -0000
Received: from unknown (12.110.105.2)
	by smtpauth19.prod.mesa1.secureserver.net (64.202.165.30) with ESMTP;
	08 Nov 2009 04:58:09 -0000
From: "Aris Samad-Yahaya" <aris@quickschools.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us>
In-Reply-To: <18869.1257650960@sss.pgh.pa.us>
Subject: Re: CREATE TABLE slowing down significantly over time 
Date: Sat, 7 Nov 2009 23:58:03 -0500
Message-ID: <009601ca6030$0e731440$2b593cc0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook 12.0
Thread-Index: AcpgI6pBBk9VV3d3QvifadOaCZk6wQAC/jiw
Content-Language: en-us
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/83
X-Sequence-Number: 36251

We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
schedule this weekly moving forward).

Interesting pointer about system catalog bloat. I tried to vacuum full the
system catalog tables (pg_*), and the performance for creating a single
table manually improved dramatically (back to what it used to be), but as
soon as I created the next schema, the performance went back down to the
same level.

So there's a clue there somewhere. Next I will try to vacuum full the entire
database.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Saturday, November 07, 2009 10:29 PM
To: Aris Samad-Yahaya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] CREATE TABLE slowing down significantly over time 

"Aris Samad-Yahaya" <aris@quickschools.com> writes:
> I'm facing a problem where running a CREATE TABLE has slowed down
> significantly over time.

System catalog bloat maybe?  What are your vacuuming practices?

			regards, tom lane




From scott.marlowe@gmail.com Sun Nov  8 05:31:05 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 8D68963418E
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 01:31:05 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 96741-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 05:30:57 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f186.google.com (mail-iw0-f186.google.com
	[209.85.223.186])
	by mail.postgresql.org (Postfix) with ESMTP id B54EE6340D8
	for <pgsql-performance@postgresql.org>;
	Sun,  8 Nov 2009 01:30:57 -0400 (AST)
Received: by iwn16 with SMTP id 16so1621742iwn.29
	for <pgsql-performance@postgresql.org>;
	Sat, 07 Nov 2009 21:30:56 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=VZkzaMQjE1Xc4RbxG/nx5MwKFTjrGtGEEw/KzsjtT7o=;
	b=ta6L6hBv+n4tXGgG8ipasbhhZJHuWv/by3Os5FFGINXrZqDDlHwb0pYFN3TV/uTdIB
	93nG3YDHSdQ9h35KDwxot9lfJ37kKxL8BF4N4+zUsO4Yqxy4gKH/y3ozwKlRVITXd+W2
	eRo2AL+e//Vo5P91ftxy8URflMyIEL3ZvrRxE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=c01fexxwP9aceyId3mtcqMDgHhHG0TwKfLPtawrQSjzh3TNQHtGkB2W9sf4B7tytdF
	62AdTHggGjiLnwc+gC3IU6A70L+wjgIjEspE+TJfPXnCGjbnogRY45TKofCisz3ma/EA
	zRvjEMkVeDvnYtYHKYsQQCiUC+KV0/pOmN+uM=
MIME-Version: 1.0
Received: by 10.231.25.29 with SMTP id x29mr5322570ibb.31.1257658256573; Sat,
	07 Nov 2009 21:30:56 -0800 (PST)
In-Reply-To: <009601ca6030$0e731440$2b593cc0$@com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
Date: Sat, 7 Nov 2009 22:30:56 -0700
Message-ID: <dcc563d10911072130i6e029e5dg815d4f7909e19383@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Aris Samad-Yahaya <aris@quickschools.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/84
X-Sequence-Number: 36252

On Sat, Nov 7, 2009 at 9:58 PM, Aris Samad-Yahaya <aris@quickschools.com> wrote:
> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
> schedule this weekly moving forward).
>
> Interesting pointer about system catalog bloat. I tried to vacuum full the
> system catalog tables (pg_*), and the performance for creating a single
> table manually improved dramatically (back to what it used to be), but as
> soon as I created the next schema, the performance went back down to the
> same level.
>
> So there's a clue there somewhere. Next I will try to vacuum full the entire
> database.

If you don't run autovac, it's possible you've managed to bloat your
pg_catalog tables...  Note that we run similar numbers of tables, as
we have 30 tables and about 10 indexes in over 2000 schemas.  We did
the trick Tom posted:
alter function pg_table_is_visible(oid) cost 10;
to get faster tab completion and / or \d performance.



From robertmhaas@gmail.com Mon Nov  9 03:59:03 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 693F2632256
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Sun,  8 Nov 2009 23:59:03 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 66475-08
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 03:58:51 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com
	[209.85.218.213])
	by mail.postgresql.org (Postfix) with ESMTP id 156B3630EAD
	for <pgsql-performance@postgresql.org>;
	Sun,  8 Nov 2009 23:58:51 -0400 (AST)
Received: by bwz5 with SMTP id 5so3163531bwz.3
	for <pgsql-performance@postgresql.org>;
	Sun, 08 Nov 2009 19:58:49 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=sO1TiGLO3AVyk4Cd62YXJXBti+IDqhF2A2phoXjSsAY=;
	b=izaSWMUm2Tbm+ZpF1dtE25ZbZEAHyYpo9B3mPFsgfW1tTP3Be0jPT8EnA0qnBFsAqb
	VVwc+obK6x1NA4C3pwNDQM28TsLY01ne7Jl2rG2aZwAdkdbas0f0Nvzc3pFyD6I4FSYi
	/RBbQ8EdsDhHQzjs0yS1T73MTeu5eruoZwGhE=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=X8ItrRAEuxFJFNbxwYuBV2QleqaoAYQGpH/CLRJ0DK1yBxzweWaziBl1fNDjHs/8iG
	eJAz14lbexxcp5V2LmnFre4pCex4iG7PKg46GVuhkEh+kBpXpv1hvkA6JppdrlTXmvpZ
	aDkpL+zDPw0jQLpWRD1n5w4ZR/Fu2e2ked44w=
MIME-Version: 1.0
Received: by 10.239.169.18 with SMTP id m18mr582391hbe.200.1257739129538;
 Sun,
	08 Nov 2009 19:58:49 -0800 (PST)
In-Reply-To: <009601ca6030$0e731440$2b593cc0$@com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
Date: Sun, 8 Nov 2009 22:58:49 -0500
Message-ID: <603c8f070911081958sbac9712k15f50b80e5419d3c@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Robert Haas <robertmhaas@gmail.com>
To: Aris Samad-Yahaya <aris@quickschools.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/85
X-Sequence-Number: 36253

On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya
<aris@quickschools.com> wrote:
> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
> schedule this weekly moving forward).
>
> Interesting pointer about system catalog bloat. I tried to vacuum full the
> system catalog tables (pg_*), and the performance for creating a single
> table manually improved dramatically (back to what it used to be), but as
> soon as I created the next schema, the performance went back down to the
> same level.
>
> So there's a clue there somewhere. Next I will try to vacuum full the entire
> database.

And maybe REINDEX, too.

...Robert



From gryzman@gmail.com Mon Nov  9 08:40:41 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 1BDA46340AE
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 04:40:41 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 05409-05
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 08:40:33 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com
	[209.85.218.213])
	by mail.postgresql.org (Postfix) with ESMTP id B0C8A632539
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Nov 2009 04:40:32 -0400 (AST)
Received: by bwz5 with SMTP id 5so3300532bwz.3
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Nov 2009 00:40:30 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=XlLXUiFwZGhwmvAuc9tiTi2MarbLxNJ9wdm9NlmpueA=;
	b=NJxH/I65B0prLHUSDNW2E1jmclxftDtm+tCayRDNxJrwMuAKnbTEmzqOU43fTA0yQK
	rX/wiCzjCr6IhqF63I5LHQSlLbJour4rjtKaSvsPpk7IpJeWxVEUQLBVuFOZVINs0KMY
	TxArmX9s7ngGboViZSRu0KYzQESt9oHkNkfvI=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=yHunMP9BTGRWqJU0G05JaLNzvL0yG481eKTJ6ltDgEVdloP609M8SPeZSGaLTRheua
	++I+Cgpo8Vbk/e7biShw+sD3ZwTgACx3JBcJvmrm1amsLY9SroJBgn4xUJCgwf9hqLQo
	OiPyT2O2J846UFRlp5JD2eIJg9xPngW5FhPhk=
MIME-Version: 1.0
Received: by 10.204.10.135 with SMTP id p7mr8178490bkp.69.1257756030308; Mon,
	09 Nov 2009 00:40:30 -0800 (PST)
In-Reply-To: <603c8f070911081958sbac9712k15f50b80e5419d3c@mail.gmail.com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
	<603c8f070911081958sbac9712k15f50b80e5419d3c@mail.gmail.com>
Date: Mon, 9 Nov 2009 08:40:30 +0000
Message-ID: <2f4958ff0911090040v11389c9dud0787b0de4836a2a@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= <gryzman@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Aris Samad-Yahaya <aris@quickschools.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=000325556ce666d1930477ec2507
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5
	tests=BAYES_00=-2.599, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200911/86
X-Sequence-Number: 36254

--000325556ce666d1930477ec2507
Content-Type: text/plain; charset=UTF-8

On Mon, Nov 9, 2009 at 3:58 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
>
> And maybe REINDEX, too.


yup, nevermind the mess in table, indices are getting fscked much quicker
than table it self, because of its structure.




-- 
GJ

--000325556ce666d1930477ec2507
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<br><br><div class=3D"gmail_quote">On Mon, Nov 9, 2009 at 3:58 AM, Robert H=
aas <span dir=3D"ltr">&lt;<a href=3D"mailto:robertmhaas@gmail.com">robertmh=
aas@gmail.com</a>&gt;</span> wrote:<blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class=3D"im">
<br>
</div>And maybe REINDEX, too.</blockquote><div><br></div><div>yup, nevermin=
d the mess in table, indices are getting fscked much quicker than table it =
self, because of its structure.=C2=A0</div><div>=C2=A0</div></div><br><br c=
lear=3D"all">
<br>-- <br>GJ<br>

--000325556ce666d1930477ec2507--



From robertmhaas@gmail.com Mon Nov  9 12:22:13 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id B232B633DAA
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 08:22:13 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 37246-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 12:22:05 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-bw0-f213.google.com (mail-bw0-f213.google.com
	[209.85.218.213])
	by mail.postgresql.org (Postfix) with ESMTP id C5546633286
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Nov 2009 08:22:05 -0400 (AST)
Received: by bwz5 with SMTP id 5so3502131bwz.3
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Nov 2009 04:22:03 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=o7SovH+Cmsi66yQhkFswQLsQzBRtXvgbB4veJZB54f0=;
	b=HEwkAgNZlaXivIGj7nE/F6oYHgSv0ZDojW6lUExU22IUa2RsGUrKDsibty9vswZuwG
	QnOxODdZh4R7HvhN1gmzMCkkWXpvnONnVIsQtoPSIhcwHSWG34GyY7G++9pzIUeyKpkm
	d7xMzFRnJFLmgM65m7rDe+IzkcmDjdEuiPckA=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=UKZsLyRJDvFCi1fcHGOvTsoFVO5RTueu5LbvNsXbKkgVPnjdqgOChQWtg+8Evn6GYR
	al9xJa2WmdLUdkuOLdhZxcCs2Ic4XwKwPvyyj8OgOZWit3u0FnsnKpb8W6i5vz0D21CP
	IMpBrPvJPCoJYuWAuheoIMlGviUOaLg9o1GZs=
MIME-Version: 1.0
Received: by 10.239.190.227 with SMTP id y35mr755445hbh.212.1257769323072;
	Mon, 09 Nov 2009 04:22:03 -0800 (PST)
In-Reply-To: <009601ca6030$0e731440$2b593cc0$@com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
Date: Mon, 9 Nov 2009 07:22:02 -0500
Message-ID: <603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Robert Haas <robertmhaas@gmail.com>
To: Aris Samad-Yahaya <aris@quickschools.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/87
X-Sequence-Number: 36255

On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya
<aris@quickschools.com> wrote:
> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
> schedule this weekly moving forward).
>
> Interesting pointer about system catalog bloat. I tried to vacuum full the
> system catalog tables (pg_*), and the performance for creating a single
> table manually improved dramatically (back to what it used to be), but as
> soon as I created the next schema, the performance went back down to the
> same level.
>
> So there's a clue there somewhere. Next I will try to vacuum full the entire
> database.

You should really enable autovacuum.  You'll probably have to VACUUM
FULL and REINDEX to clean everything up, but after that autovacuum
should be MUCH more effective than a nightly vacuum run.  If you're
running some ancient Pg version where autovacuum is not enabled by
default, you should also consider upgrading.  There are a lot of
goodies (including performance enhancements) in newer versions.

...Robert



From fotographs@gmail.com Mon Nov  9 14:46:40 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 21CB6632BE4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 10:46:40 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 58955-01-4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 14:46:31 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-px0-f186.google.com (mail-px0-f186.google.com
	[209.85.216.186])
	by mail.postgresql.org (Postfix) with ESMTP id EDA5D63551C
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Nov 2009 10:46:31 -0400 (AST)
Received: by pxi16 with SMTP id 16so1005967pxi.29
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Nov 2009 06:46:29 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=Gh4Rf5YwHRe91hzlEXp9TCuAqXPr4ZExQrhXwtKpsuw=;
	b=FL6rOj1jwtXXGRLv9cb4QHY2edAwvi8nQrPogXiKHmuYl05DEF7qpSIsNzOAZQFEKQ
	veMEp3FFUl4zh2iWimdqupLq2rq64sp+UYY7jz9QuWIRWgzSlyYEo/ZaqW7wjOI+leKu
	RqQvFerMD9f2WvxlCWFgkr9ttBe7WknL0ely0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=SXg2pB38Asd4ZUh7bXRWbJYHtGFXn+39TJOyKs8TBKDjwTmwjgUnUiNVE5MuRsmO3l
	cukeOntKThv3UTx/ttPgAaM++Q1/pFRFupS4Kiag19InXxezcgY/loiaQ4R/0L5cYPd9
	b8RMK7B6GcKxBuoSs41ZVZIy1lehjvMjkd+Sg=
MIME-Version: 1.0
Received: by 10.140.251.12 with SMTP id y12mr442438rvh.236.1257777989717;
 Mon,
	09 Nov 2009 06:46:29 -0800 (PST)
In-Reply-To: <603c8f070911081958sbac9712k15f50b80e5419d3c@mail.gmail.com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
	<603c8f070911081958sbac9712k15f50b80e5419d3c@mail.gmail.com>
Date: Mon, 9 Nov 2009 06:46:29 -0800
Message-ID: <f2fd819a0911090646w159e6e4bu309742528554068f@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Anj Adu <fotographs@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Aris Samad-Yahaya <aris@quickschools.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/91
X-Sequence-Number: 36259

Why is reindex needed ?    Unless most of the key values get deleted
frequently..this is not needed. (I am assuming postgres 8.x and above)

On Sun, Nov 8, 2009 at 7:58 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya
> <aris@quickschools.com> wrote:
>> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going to
>> schedule this weekly moving forward).
>>
>> Interesting pointer about system catalog bloat. I tried to vacuum full the
>> system catalog tables (pg_*), and the performance for creating a single
>> table manually improved dramatically (back to what it used to be), but as
>> soon as I created the next schema, the performance went back down to the
>> same level.
>>
>> So there's a clue there somewhere. Next I will try to vacuum full the entire
>> database.
>
> And maybe REINDEX, too.
>
> ...Robert
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



From scott.marlowe@gmail.com Mon Nov  9 15:33:22 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id E00A3634CA4
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 11:33:22 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 67125-02-6
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 15:33:14 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-iw0-f186.google.com (mail-iw0-f186.google.com
	[209.85.223.186])
	by mail.postgresql.org (Postfix) with ESMTP id 01C786349FD
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Nov 2009 11:33:13 -0400 (AST)
Received: by mail-iw0-f186.google.com with SMTP id 16so2285290iwn.29
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Nov 2009 07:33:12 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type
	:content-transfer-encoding;
	bh=ojHExHwUwV188KHUhCbxE21mFjtndEexqKgO/vdm238=;
	b=OOisW9yTa+T0M0fmdNDMBCgsdr8Gvjx0xzOfJFmQFLCdS/H3yGbWqzYoe9DjCS0w/c
	ewZl/tBJhnSctCn7FdZKYEqmLcxWuJWsXwc8extO0ZvfmyoOH6lBNQeYZT4opUBHVZ8l
	81WAksqpdavHN8yPUc0tA5eo9+Y6cBUayqLOM=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=XeF9pK9Itsn+i4qK6eho3B3rB7Ouea7lenjukDRTZTQwQdx4Wgme1ibZlzbx/p07VN
	U5usiTazPjP1PjiSFyN3GRmF+yddZ7RAwtlw3ZpdM8czek/Lv4BdsunXDM2OEd95LJq7
	7V3iQbDw0r2msNHulzuIHhkY+aFB+L4CUGauk=
MIME-Version: 1.0
Received: by 10.231.10.16 with SMTP id n16mr8177332ibn.24.1257780792630; Mon,
	09 Nov 2009 07:33:12 -0800 (PST)
In-Reply-To: <603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
	<603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com>
Date: Mon, 9 Nov 2009 08:33:12 -0700
Message-ID: <dcc563d10911090733u416e5c06mf1f960ae4268b9de@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Aris Samad-Yahaya <aris@quickschools.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/92
X-Sequence-Number: 36260

On Mon, Nov 9, 2009 at 5:22 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya
> <aris@quickschools.com> wrote:
>> We vacuum analyze nightly, and vacuum normally ad-hoc (but we're going t=
o
>> schedule this weekly moving forward).
>>
>> Interesting pointer about system catalog bloat. I tried to vacuum full t=
he
>> system catalog tables (pg_*), and the performance for creating a single
>> table manually improved dramatically (back to what it used to be), but a=
s
>> soon as I created the next schema, the performance went back down to the
>> same level.
>>
>> So there's a clue there somewhere. Next I will try to vacuum full the en=
tire
>> database.
>
> You should really enable autovacuum. =A0You'll probably have to VACUUM
> FULL and REINDEX to clean everything up, but after that autovacuum
> should be MUCH more effective than a nightly vacuum run. =A0If you're
> running some ancient Pg version where autovacuum is not enabled by
> default, you should also consider upgrading. =A0There are a lot of
> goodies (including performance enhancements) in newer versions.

Also note that the argument that autovacuum chews up too much IO is
moot now that you can set cost delay to 10 to 20 milliseconds.  Unless
you're running on the hairy edge of maximum IO at all times, autovac
should be pretty much unnoticed.



From robertmhaas@gmail.com Mon Nov  9 15:49:46 2009
Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 473FC632D51
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 11:49:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 69474-01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 15:49:37 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-yx0-f181.google.com (mail-yx0-f181.google.com
	[209.85.210.181])
	by mail.postgresql.org (Postfix) with ESMTP id 4CC1F632CEC
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Nov 2009 11:49:38 -0400 (AST)
Received: by yxe11 with SMTP id 11so2551318yxe.15
	for <pgsql-performance@postgresql.org>;
	Mon, 09 Nov 2009 07:49:36 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=0KOHTsApXS2matOjdUiD6Ls5FAqn+DSWPfJas3JUkxA=;
	b=dQ9xEgQMST9zFktQUyi8mo6caIaRv1fLWUfRPq9f8HMNyGDhI3sY8jmCVhmM7l7shb
	hQI5sv3GzyOQWyxQ3okICahamrscp/peAmrbLBWIv7uyRhdyQZeNcOXgjaD2N0igBnPv
	fRiHhGW+yDcIxYIklw7qIphU1EouexSjJNnU8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=eSDudiK3RgCkYuG/xyEd/UyAFKH+ttynDKjQmbzyfJ0XkG2ZtQcb6+dbi/OgAmw29O
	bjZ6I4l6H0+wpPwkKr1H9fCOTgHTFbm4yr2CEb7LINuhsfc2XinJml/TVHs0jZueqLkS
	bkuxx7xnhtLvfqTUUg+wl0CLc3/3C532DyOWI=
MIME-Version: 1.0
Received: by 10.239.138.12 with SMTP id n12mr787100hbn.69.1257781775591; Mon,
	09 Nov 2009 07:49:35 -0800 (PST)
In-Reply-To: <f2fd819a0911090646w159e6e4bu309742528554068f@mail.gmail.com>
References: <008801ca6021$c1cc7630$45656290$@com>
	<18869.1257650960@sss.pgh.pa.us> <009601ca6030$0e731440$2b593cc0$@com>
	<603c8f070911081958sbac9712k15f50b80e5419d3c@mail.gmail.com>
	<f2fd819a0911090646w159e6e4bu309742528554068f@mail.gmail.com>
Date: Mon, 9 Nov 2009 10:49:35 -0500
Message-ID: <603c8f070911090749k541b4c3nc4888f54b28f93fe@mail.gmail.com>
Subject: Re: CREATE TABLE slowing down significantly over time
From: Robert Haas <robertmhaas@gmail.com>
To: Anj Adu <fotographs@gmail.com>
Cc: Aris Samad-Yahaya <aris@quickschools.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/93
X-Sequence-Number: 36261

On Mon, Nov 9, 2009 at 9:46 AM, Anj Adu <fotographs@gmail.com> wrote:
> Why is reindex needed ?

VACUUM FULL does not fix index bloat, only table boat.

...Robert



From greg@2ndquadrant.com Mon Nov  9 17:04:44 2009
Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 22BD5633D01
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 13:04:44 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 97953-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  9 Nov 2009 17:04:32 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from moutng.kundenserver.de (moutng.kundenserver.de
	[212.227.126.187])
	by mail.postgresql.org (Postfix) with ESMTP id F12116336E6
	for <pgsql-performance@postgresql.org>;
	Mon,  9 Nov 2009 13:04:32 -0400 (AST)
Received: from [192.168.1.3] (pool-96-244-14-10.bltmmd.fios.verizon.net
	[96.244.14.10])
	by mrelayeu.kundenserver.de (node=mreu0) with ESMTP (Nemesis)
	id 0M0MwL-1MFz2U2Xcb-00uHtF; Mon, 09 Nov 2009 18:04:30 +0100
Message-ID: <4AF84B95.7060005@2ndquadrant.com>
Date: Mon, 09 Nov 2009 12:04:21 -0500
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Scott Marlowe <scott.marlowe@gmail.com>
CC: Robert Haas <robertmhaas@gmail.com>,
	Aris Samad-Yahaya <aris@quickschools.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
Subject: Re: CREATE TABLE slowing down significantly over time
References: <008801ca6021$c1cc7630$45656290$@com>	
	<18869.1257650960@sss.pgh.pa.us>
	<009601ca6030$0e731440$2b593cc0$@com>	
	<603c8f070911090422t52412697hf19b222a033f729@mail.gmail.com>
	<dcc563d10911090733u416e5c06mf1f960ae4268b9de@mail.gmail.com>
In-Reply-To: <dcc563d10911090733u416e5c06mf1f960ae4268b9de@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Provags-ID: V01U2FsdGVkX1+nteVasvCtzuwhuiOHjBK5CulqV3s7a9RpWSG
	+YZyW7JKuXkCaOgsOLBw0uXtaegYyqxgoiZoFXaYGn5w/kSbMT
	qb8eyzpR68Oeg5JHPp9b/uBnC3gxv/G
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200911/95
X-Sequence-Number: 36263

Scott Marlowe wrote:
> Also note that the argument that autovacuum chews up too much IO is
> moot now that you can set cost delay to 10 to 20 milliseconds.  Unless
> you're running on the hairy edge of maximum IO at all times, autovac
> should be pretty much unnoticed
And if you're running on the hairy edge like that, you really need 
autovacuum whether you think you can afford it or not.  Badly maintained 
tables are also I/O intensive, and it's easy for someone who thinks "I'm 
too busy to allocate VACUUM time" to end up wasting more resources than 
it would have taken to just do things right in the first place.  I see 
way too many people who suffer from false economy when it comes to 
autovacuum planning.

Some comments on this whole discussion:

1) You don't end up with dead rows [auto]vacuum needs to clean up just 
when you delete things.  They show up when you UPDATE things, too--the 
original row isn't removed until after the new one is written.  The 
overhead isn't as bad on UPDATEs in 8.3 or later, but just because you 
don't delete doesn't mean you don't need VACUUM to clean up dead stuff.

2) Any time you find yourself considering VACUUM FULL to clean things 
up, you're probably making a mistake, because the sort of situations 
it's the only tool to recover from tend to be broader disasters.  The 
guidelines in 
http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html 
spell out my feeling here as a tip:  "the best way is to use CLUSTER or 
one of the table-rewriting variants of ALTER TABLE".  If you're fighting 
performance issues because of some amount of background mismanagement 
with an unknown amount of table garbage in the past, it's quite possible 
you'll find the reinvigorated performance you get from CLUSTER worth the 
maintenance cost of needing an exclusive lock for it to run for a 
while.  See 
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959 
for more information.  I run CLUSTER all the time, and every time I 
think I'm saving time by doing VACUUM FULL/REINDEX instead I regret it 
as another false economy.  Turn on autovacuum, make it run all the time 
but at a slower average speed if you're concerned about its overhead, 
and use CLUSTER once to blow away the accumulated bloat from before you 
were doing the right things.

3) There is a lot of speculation here and no measurements.  What I'd be 
doing in this case is running something like the query at 
http://wiki.postgresql.org/wiki/Disk_Usage (but without the lines that 
filter out pg_catalog because the catalogs are a strong suspect here) 
regularly while debugging the problem here.  Measure how big all the 
catalog tables and indexes are, do your operations that make things 
better or worse, then measure again.  Turn autovacuum on, repeat the 
test, see if things are different.  This type of problem tends to be 
really easy to quantify.

--
Greg Smith    greg@2ndquadrant.com    Baltimore, MD