Re: Create tables 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