Michael Riess <mlriess@gmx.de> writes:
>> On 12/1/05, Michael Riess <mlriess@gmx.de> wrote:
>>> 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.
>>>
>>> (We NEED that many tables, please don't recommend to reduce them)
>>>
>> Have you ANALYZEd your database? VACUUMing?
>
> Of course ... before 8.1 we routinely did a vacuum full analyze each
> night. As of 8.1 we use autovacuum.
VACUUM FULL was probably always overkill, unless "always" includes
versions prior to 7.3...
>> BTW, are you using some kind of weird ERP? I have one that treat
>> informix as a fool and don't let me get all of informix potential...
>> maybe the same is in your case...
>
> No. Our database contains tables for we content management
> systems. The server hosts approx. 500 cms applications, and each of
> them has approx. 30 tables.
>
> That's why I'm asking if it was better to have 500 databases with 30
> tables each. In previous Postgres versions this led to even worse
> performance ...
This has the feeling of fitting with Alan Perlis' dictum below...
Supposing you have 500 databases, each with 30 tables, each with 4
indices, then you'll find you have, on disk...
# of files = 500 x 30 x 5 = 75000 files
If each is regularly being accessed, that's bits of 75000 files
getting shoved through OS and shared memory caches. Oh, yes, and
you'll also have regular participation of some of the pg_catalog
files, with ~500 instances of THOSE, multiplied some number of ways...
An application with 15000 frequently accessed tables doesn't strike me
as being something that can possibly turn out well. You have, in
effect, more tables than (arguably) bloated ERP systems like SAP R/3;
it only has a few thousand tables, and since many are module-specific,
and nobody ever implements *all* the modules, it is likely only a few
hundred that are "hot spots." No 15000 there...
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/languages.html
It is better to have 100 functions operate on one data structure than
10 functions on 10 data structures. -- Alan J. Perlis