Re: 15,000 tables - Mailing list pgsql-performance

From David Lang
Subject Re: 15,000 tables
Date
Msg-id Pine.LNX.4.62.0512010609060.2807@qnivq.ynat.uz
Whole thread Raw
In response to 15,000 tables  (Michael Riess <mlriess@gmx.de>)
Responses Re: 15,000 tables  (Michael Riess <mlriess@gmx.de>)
Re: 15,000 tables  (Michael Riess <mlriess@gmx.de>)
List pgsql-performance
On Thu, 1 Dec 2005, Michael Riess wrote:

> Hi,
>
> we are currently running a postgres server (upgraded to 8.1) which has one
> large database with approx. 15,000 tables. Unfortunately performance suffers
> from that, because the internal tables (especially that which holds the
> attribute info) get too large.

is it becouse the internal tables get large, or is it a problem with disk
I/O?

with 15,000 tables you are talking about a LOT of files to hold these
(30,000 files with one index each and each database being small enough to
not need more then one file to hold it), on linux ext2/3 this many files
in one directory will slow you down horribly. try different filesystems
(from my testing and from other posts it looks like XFS is a leading
contender), and also play around with the tablespaces feature in 8.1 to
move things out of the main data directory into multiple directories. if
you do a ls -l on the parent directory you will see that the size of the
directory is large if it's ever had lots of files in it, the only way to
shrink it is to mv the old directory to a new name, create a new directory
and move the files from the old directory to the new one.

David Lang


pgsql-performance by date:

Previous
From: Andreas Pflug
Date:
Subject: Re: slow insert into very large table
Next
From: David Lang
Date:
Subject: filesystem performance with lots of files