Re: Create tables performance

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: Sylvain CAILLET <scaillet(at)alaloop(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Create tables performance
Date: 2012-07-06 17:22:16
Message-ID: CAMkU=1wDFxKom_VJuUcq7jKezcB0JLUWWmQNMOo_xL56_0LPdw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Fri, Jul 6, 2012 at 8:15 AM, Sylvain CAILLET <scaillet(at)alaloop(dot)com> 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 !

What if you create those 170 tables in a database without 100,000
pre-existing tables?

What else does your script do?

I can create 170 tables each with 10 rows in a database containing
100,000 other tables in less than a second on 8.3.9, either all in one
transaction or in ~340 separate transactions.

So whatever problem you are having is probably specific to your
details, not a generic issue. It is hard to say if an upgrade would
help if the root cause is not known.

What do the standard monitoring tools show? Are you IO bound, or CPU
bound? If CPU, is it in postgres or in java?

> 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 ?

I doubt that that is a problem on any reasonably modern Linux.

Cheers,

Jeff

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2012-07-06 18:12:24 Re: Create tables performance
Previous Message Steve Crawford 2012-07-06 16:06:47 Re: PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each non-empty table and reset unique identifier column of empty ones.