Message-ID: <4FF79E9B.8040304@ringerc.id.au>
Date: Sat, 07 Jul 2012 10:27:39 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Sylvain CAILLET <scaillet@alaloop.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Create tables performance
References: 
 <2ea3ab10-6768-45f2-a748-7f6c94be30fc@zimbra-ne01.network-studio.com>
In-Reply-To: 
 <2ea3ab10-6768-45f2-a748-7f6c94be30fc@zimbra-ne01.network-studio.com>
Content-Type: multipart/alternative;
	boundary="------------050105060901010505000907"

This is a multi-part message in MIME format.
--------------050105060901010505000907
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 07/06/2012 11:15 PM, Sylvain CAILLET wrote:
> Hi to all,
>
> I run Postgresql 8.3.9 on a dedicated server running with Debian 
> 5.0.4, a strong bi quad-proc with RAM 16Go. My biggest db contains at 
> least 100 000 tables. Last time, I started a Java process I use to 
> make some change on it, it created 170 new tables and it took one full 
> minute. That is a very long time for  such a process on such a server !
If you create and drop a lot of tables, you need to make sure you're 
vacuuming the pg_catalog tables frequently. Newer versions mostly take 
care of this for you, but on 8.3 you'll at minimum have to turn 
autovaccum right up.

See what happens if you run in psql, as a Pg superuser (usually the 
"postgres" account):

   CLUSTER pg_class_oid_index ON pg_catalog.pg_class;
   CLUSTER pg_type_oid_index ON pg_catalog.pg_type;
   CLUSTER pg_attribute_relid_attnam_index ON pg_catalog.pg_attribute;
   CLUSTER pg_index_indexrelid_index ON pg_catalog.pg_index;

I'm guessing you have severe table bloat in your catalogs, in which case 
this may help. I use CLUSTER instead of VACCUUM FULL because on old 
versions like 8.3 it'll run faster and sort the indexes for you too.

> Do you think there could be some configuration tuning to do to improve 
> the performance for create tables ?
> Or do I have to use tablespaces because 100000 files in a single 
> folder is a too many for OS ?

That won't be a problem unless your OS and file system are truly crap.

--
Craig Ringer


--------------050105060901010505000907
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 07/06/2012 11:15 PM, Sylvain CAILLET
      wrote:<br>
    </div>
    <blockquote
cite="mid:2ea3ab10-6768-45f2-a748-7f6c94be30fc@zimbra-ne01.network-studio.com"
      type="cite">
      <meta http-equiv="Context-Type" content="text/html; charset=utf-8">
      <div>
        <div>Hi to all,</div>
        <div><br>
        </div>
        <div>I run Postgresql 8.3.9 on a dedicated server running with
          Debian 5.0.4, a strong bi quad-proc with RAM 16Go. My biggest
          db contains at least 100 000 tables. Last time, I started a
          Java process I use to make some change on it, it created 170
          new tables and it took one full minute. That is a very long
          time for  such a process on such a server ! <br>
        </div>
      </div>
    </blockquote>
    If you create and drop a lot of tables, you need to make sure you're
    vacuuming the pg_catalog tables frequently. Newer versions mostly
    take care of this for you, but on 8.3 you'll at minimum have to turn
    autovaccum right up.<br>
    <br>
    See what happens if you run in psql, as a Pg superuser (usually the
    "postgres" account):<br>
    <br>
      CLUSTER pg_class_oid_index ON pg_catalog.pg_class;<br>
      CLUSTER pg_type_oid_index ON pg_catalog.pg_type;<br>
      CLUSTER pg_attribute_relid_attnam_index ON
    pg_catalog.pg_attribute;<br>
      CLUSTER pg_index_indexrelid_index ON pg_catalog.pg_index;<br>
    <br>
    I'm guessing you have severe table bloat in your catalogs, in which
    case this may help. I use CLUSTER instead of VACCUUM FULL because on
    old versions like 8.3 it'll run faster and sort the indexes for you
    too. <br>
    <br>
    <blockquote
cite="mid:2ea3ab10-6768-45f2-a748-7f6c94be30fc@zimbra-ne01.network-studio.com"
      type="cite">
      <div>
        <div>Do you think there could be some configuration tuning to do
          to improve the performance for create tables ? </div>
        <div>Or do I have to use tablespaces because 100000 files in a
          single folder is a too many for OS ? <br>
        </div>
      </div>
    </blockquote>
    <br>
    That won't be a problem unless your OS and file system are truly
    crap.<br>
    <br>
    --<br>
    Craig Ringer<br>
    <br>
  </body>
</html>

--------------050105060901010505000907--