pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed15,000 tables
32+ messages / 20 participants
[nested] [flat]
* 15,000 tables
@ 2005-12-01 13:42 Michael Riess <mlriess@gmx.de>
0 siblings, 4 replies; 32+ messages in thread
From: Michael Riess @ 2005-12-01 13:42 UTC (permalink / raw)
To: pgsql-performance
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.
(We NEED that many tables, please don't recommend to reduce them)
Logically these tables could be grouped into 500 databases. My question is:
Would performance be better if I had 500 databases (on one postgres
server instance) which each contain 30 tables, or is it better to have
one large database with 15,000 tables? In the old days of postgres 6.5
we tried that, but performance was horrible with many databases ...
BTW: I searched the mailing list, but found nothing on the subject - and
there also isn't any information in the documentation about the effects
of the number of databases, tables or attributes on the performance.
Now, what do you say? Thanks in advance for any comment!
Mike
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 14:16 David Lang <dlang@invendra.net>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 2 replies; 32+ messages in thread
From: David Lang @ 2005-12-01 14:16 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: 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
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 14:51 Michael Riess <mlriess@gmx.de>
parent: David Lang <dlang@invendra.net>
1 sibling, 1 reply; 32+ messages in thread
From: Michael Riess @ 2005-12-01 14:51 UTC (permalink / raw)
To: pgsql-performance
Hi David,
>
> 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.
We use ReiserFS, and I don't think that this is causing the problem ...
although it would probably help to split the directory up using tablespaces.
But thanks for the suggestion!
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 14:56 Michael Riess <mlriess@gmx.de>
parent: David Lang <dlang@invendra.net>
1 sibling, 0 replies; 32+ messages in thread
From: Michael Riess @ 2005-12-01 14:56 UTC (permalink / raw)
To: pgsql-performance
Hi David,
incidentally: The directory which holds our datbase currently contains
73883 files ... do I get a prize or something? ;-)
Regards,
Mike
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: [PERFORM] 15,000 tables
@ 2005-12-01 15:45 Ing. Jhon Carrillo // Caracas, Venezuela <jhon.carrillo.foros@gmail.com>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 0 replies; 32+ messages in thread
From: Ing. Jhon Carrillo // Caracas, Venezuela @ 2005-12-01 15:45 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; Postgresql <pgsql-es-ayuda@postgresql.org>
2005/12/1, Michael Riess <mlriess@gmx.de>:
> 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.
>
> (We NEED that many tables, please don't recommend to reduce them)
it's amazing!!!!! 15000 tables!!!! WOW
what kind of information you managment in your db?
Imagine some querys :(
only for curiosity!!!!!
Jhon Carrillo
DBA / Software Engineer
Caracas-Venezuela
>
> Logically these tables could be grouped into 500 databases. My question is:
>
> Would performance be better if I had 500 databases (on one postgres
> server instance) which each contain 30 tables, or is it better to have
> one large database with 15,000 tables? In the old days of postgres 6.5
> we tried that, but performance was horrible with many databases ...
>
> BTW: I searched the mailing list, but found nothing on the subject - and
> there also isn't any information in the documentation about the effects
> of the number of databases, tables or attributes on the performance.
>
> Now, what do you say? Thanks in advance for any comment!
>
> Mike
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 16:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 3 replies; 32+ messages in thread
From: Tom Lane @ 2005-12-01 16:03 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Michael Riess <mlriess@gmx.de> writes:
> (We NEED that many tables, please don't recommend to reduce them)
No, you don't. Add an additional key column to fold together different
tables of the same structure. This will be much more efficient than
managing that key at the filesystem level, which is what you're
effectively doing now.
(If you really have 15000 distinct rowtypes, I'd like to know what
your database design is...)
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 16:04 Michael Riess <mlriess@gmx.de>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 3 replies; 32+ messages in thread
From: Michael Riess @ 2005-12-01 16:04 UTC (permalink / raw)
To: pgsql-performance
Hi,
> On 12/1/05, Michael Riess <mlriess@gmx.de> 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.
>>
>> (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.
>
> 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 ...
Mike
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 16:15 Jaime Casanova <systemguards@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 32+ messages in thread
From: Jaime Casanova @ 2005-12-01 16:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
On 12/1/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Riess <mlriess@gmx.de> writes:
> > (We NEED that many tables, please don't recommend to reduce them)
>
> No, you don't. Add an additional key column to fold together different
> tables of the same structure. This will be much more efficient than
> managing that key at the filesystem level, which is what you're
> effectively doing now.
>
> (If you really have 15000 distinct rowtypes, I'd like to know what
> your database design is...)
>
> regards, tom lane
>
Maybe he is using some kind of weird ERP... take the case of BaaN
(sadly i use it in my work): BaaN creates about 1200 tables per
company and i have no control of it... we have about 12000 tables
right now...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 16:28 Michael Riess <mlriess@gmx.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 32+ messages in thread
From: Michael Riess @ 2005-12-01 16:28 UTC (permalink / raw)
To: pgsql-performance
Hi Tom,
> Michael Riess <mlriess@gmx.de> writes:
>> (We NEED that many tables, please don't recommend to reduce them)
>
> No, you don't. Add an additional key column to fold together different
> tables of the same structure. This will be much more efficient than
> managing that key at the filesystem level, which is what you're
> effectively doing now.
Been there, done that. (see below)
>
> (If you really have 15000 distinct rowtypes, I'd like to know what
> your database design is...)
Sorry, I should have included that info in the initial post. You're
right in that most of these tables have a similar structure. But they
are independent and can be customized by the users.
Think of it this way: On the server there are 500 applications, and each
has 30 tables. One of these might be a table which contains the products
of a webshop, another contains news items which are displayed on the
website etc. etc..
The problem is that the customers can freely change the tables ... add
columns, remove columns, change column types etc.. So I cannot use
system wide tables with a key column.
Mike
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 16:30 Guido Neitzer <guido.neitzer@pharmaline.de>
parent: Michael Riess <mlriess@gmx.de>
2 siblings, 0 replies; 32+ messages in thread
From: Guido Neitzer @ 2005-12-01 16:30 UTC (permalink / raw)
To: pgsql-performance
On 01.12.2005, at 17:04 Uhr, Michael Riess wrote:
> 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.
Just for my curiosity: Are the "about 30 tables" with similar schemas
or do they differ much?
We have a small CMS system running here, where I have all information
for all clients in tables with relationships to a client table.
But I assume you are running a pre-build CMS which is not designed
for "multi-client ability", right?
cug
--
PharmaLine, Essen, GERMANY
Software and Database Development
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 17:07 me@alternize.com
parent: Michael Riess <mlriess@gmx.de>
2 siblings, 1 reply; 32+ messages in thread
From: me@alternize.com @ 2005-12-01 17:07 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; pgsql-performance
hi michael
>> 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.
what i noticed is autovacuum not working properly as it should. i had 8.1
running with autovacuum for just 2 days or so and got warnings in pgadmin
that my tables would need an vacuum. i've posted this behaviour some weeks
ago to the novice list requesting more infos on how to "tweak" autovacuum
properly - unfortunately without any respones. thats when i switched the
nightly analyze job back on - everything runs smooth since then.
maybe it helps in your case as well?
cheers,
thomas
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 17:09 Chris Browne <cbbrowne@acm.org>
parent: Michael Riess <mlriess@gmx.de>
2 siblings, 1 reply; 32+ messages in thread
From: Chris Browne @ 2005-12-01 17:09 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 18:07 Gavin M. Roy <gmr@ehpg.net>
parent: Michael Riess <mlriess@gmx.de>
0 siblings, 1 reply; 32+ messages in thread
From: Gavin M. Roy @ 2005-12-01 18:07 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Hi Michael,
I'm a fan of ReiserFS, and I can be wrong, but I believe using a
journaling filesystem for the PgSQL database could be slowing things
down.
Gavin
On Dec 1, 2005, at 6:51 AM, Michael Riess wrote:
> Hi David,
>
>> 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.
>
> We use ReiserFS, and I don't think that this is causing the
> problem ... although it would probably help to split the directory
> up using tablespaces.
>
> But thanks for the suggestion!
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
Gavin M. Roy
800 Pound Gorilla
gmr@ehpg.net
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 18:40 Tino Wildenhain <tino@wildenhain.de>
parent: Gavin M. Roy <gmr@ehpg.net>
0 siblings, 2 replies; 32+ messages in thread
From: Tino Wildenhain @ 2005-12-01 18:40 UTC (permalink / raw)
To: Gavin M. Roy <gmr@ehpg.net>; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
Am Donnerstag, den 01.12.2005, 10:07 -0800 schrieb Gavin M. Roy:
> Hi Michael,
>
> I'm a fan of ReiserFS, and I can be wrong, but I believe using a
> journaling filesystem for the PgSQL database could be slowing things
> down.
Have a 200G+ database, someone pulling the power plug
or a regular reboot after a year or so.
Wait for the fsck to finish.
Now think again :-)
++Tino
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 18:48 Ron <rjpeace@earthlink.net>
parent: Tino Wildenhain <tino@wildenhain.de>
1 sibling, 1 reply; 32+ messages in thread
From: Ron @ 2005-12-01 18:48 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
Agreed. Also the odds of fs corruption or data loss are higher in a
non journaling fs. Best practice seems to be to use a journaling fs
but to put the fs log on dedicated spindles separate from the actual
fs or pg_xlog.
Ron
At 01:40 PM 12/1/2005, Tino Wildenhain wrote:
>Am Donnerstag, den 01.12.2005, 10:07 -0800 schrieb Gavin M. Roy:
> > Hi Michael,
> >
> > I'm a fan of ReiserFS, and I can be wrong, but I believe using a
> > journaling filesystem for the PgSQL database could be slowing things
> > down.
>
>Have a 200G+ database, someone pulling the power plug
>or a regular reboot after a year or so.
>
>Wait for the fsck to finish.
>
>Now think again :-)
>
>++Tino
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 18:49 Gavin M. Roy <gmr@ehpg.net>
parent: Tino Wildenhain <tino@wildenhain.de>
1 sibling, 1 reply; 32+ messages in thread
From: Gavin M. Roy @ 2005-12-01 18:49 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
Here's a fairly recent post on reiserfs (and performance):
http://archives.postgresql.org/pgsql-novice/2005-09/msg00007.php
I'm still digging on performance of ext2 vrs journaled filesystems,
as I know I've seen it before.
Gavin
My point was not in doing an fsck, but rather in
On Dec 1, 2005, at 10:40 AM, Tino Wildenhain wrote:
> Am Donnerstag, den 01.12.2005, 10:07 -0800 schrieb Gavin M. Roy:
>> Hi Michael,
>>
>> I'm a fan of ReiserFS, and I can be wrong, but I believe using a
>> journaling filesystem for the PgSQL database could be slowing things
>> down.
>
> Have a 200G+ database, someone pulling the power plug
> or a regular reboot after a year or so.
>
> Wait for the fsck to finish.
>
> Now think again :-)
>
> ++Tino
>
Gavin M. Roy
800 Pound Gorilla
gmr@ehpg.net
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 18:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 32+ messages in thread
From: Tom Lane @ 2005-12-01 18:57 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: Tino Wildenhain <tino@wildenhain.de>; pgsql-performance
Ron <rjpeace@earthlink.net> writes:
> Agreed. Also the odds of fs corruption or data loss are higher in a
> non journaling fs. Best practice seems to be to use a journaling fs
> but to put the fs log on dedicated spindles separate from the actual
> fs or pg_xlog.
I think we've determined that best practice is to journal metadata only
(not file contents) on PG data filesystems. PG does expect the filesystem
to remember where the files are, so you need metadata protection, but
journalling file content updates is redundant with PG's own WAL logging.
On a filesystem dedicated to WAL, you probably do not need any
filesystem journalling at all --- we manage the WAL files in a way
that avoids changing metadata for a WAL file that's in active use.
A conservative approach would be to journal metadata here too, though.
regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 19:08 Gavin M. Roy <gmr@ehpg.net>
parent: Gavin M. Roy <gmr@ehpg.net>
0 siblings, 0 replies; 32+ messages in thread
From: Gavin M. Roy @ 2005-12-01 19:08 UTC (permalink / raw)
To: Gavin M. Roy <gmr@ehpg.net>; +Cc: Tino Wildenhain <tino@wildenhain.de>; Michael Riess <mlriess@gmx.de>; pgsql-performance
Heh looks like I left a trailing thought...
My post wasn't saying don't use journaled filesystems, but rather
that it can be slower than non-journaled filesystems, and I don't
consider recovery time from a crash to be a factor in determining the
speed of reads and writes on the data. That being said, I think
Tom's reply on what to journal and not to journal should really put
an end to this side of the conversation.
Gavin
On Dec 1, 2005, at 10:49 AM, Gavin M. Roy wrote:
> Here's a fairly recent post on reiserfs (and performance):
>
> http://archives.postgresql.org/pgsql-novice/2005-09/msg00007.php
>
> I'm still digging on performance of ext2 vrs journaled filesystems,
> as I know I've seen it before.
>
> Gavin
>
>
> My point was not in doing an fsck, but rather in
> On Dec 1, 2005, at 10:40 AM, Tino Wildenhain wrote:
>
>> Am Donnerstag, den 01.12.2005, 10:07 -0800 schrieb Gavin M. Roy:
>>> Hi Michael,
>>>
>>> I'm a fan of ReiserFS, and I can be wrong, but I believe using a
>>> journaling filesystem for the PgSQL database could be slowing things
>>> down.
>>
>> Have a 200G+ database, someone pulling the power plug
>> or a regular reboot after a year or so.
>>
>> Wait for the fsck to finish.
>>
>> Now think again :-)
>>
>> ++Tino
>>
>
> Gavin M. Roy
> 800 Pound Gorilla
> gmr@ehpg.net
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
Gavin M. Roy
800 Pound Gorilla
gmr@ehpg.net
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 19:34 Michael Riess <mlriess@gmx.de>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 4 replies; 32+ messages in thread
From: Michael Riess @ 2005-12-01 19:34 UTC (permalink / raw)
To: pgsql-performance
> 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...
Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL,
but the database got considerably slower near the end of the week.
>
>>> 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...
>
Not all of the tables are frequently accessed. In fact I would estimate
that only 20% are actually used ... but there is no way to determine if
or when a table will be used. I thought about a way to "swap out" tables
which have not been used for a couple of days ... maybe I'll do just
that. But it would be cumbersome ... I had hoped that an unused table
does not hurt performance. But of course the internal tables which
contain the meta info get too large.
> 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..
I think that my systems confirms with the 80/20 rule ...
.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 19:40 Jaime Casanova <systemguards@gmail.com>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 0 replies; 32+ messages in thread
From: Jaime Casanova @ 2005-12-01 19:40 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On 12/1/05, Michael Riess <mlriess@gmx.de> wrote:
> > 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...
>
> Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL,
> but the database got considerably slower near the end of the week.
>
> >
> >>> 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...
> >
>
> Not all of the tables are frequently accessed. In fact I would estimate
> that only 20% are actually used ... but there is no way to determine if
> or when a table will be used. I thought about a way to "swap out" tables
> which have not been used for a couple of days ... maybe I'll do just
> that. But it would be cumbersome ... I had hoped that an unused table
> does not hurt performance. But of course the internal tables which
> contain the meta info get too large.
>
> > 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..
>
> I think that my systems confirms with the 80/20 rule ...
> .
>
How many disks do you have i imagine you can put tables forming one
logical database in a tablespace and have tables spread on various
disks...
--
Atentamente,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 20:28 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 32+ messages in thread
From: Merlin Moncure @ 2005-12-01 20:28 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
> 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)
>
> Logically these tables could be grouped into 500 databases. My
question
> is:
>
> Would performance be better if I had 500 databases (on one postgres
> server instance) which each contain 30 tables, or is it better to have
> one large database with 15,000 tables? In the old days of postgres 6.5
> we tried that, but performance was horrible with many databases ...
>
> BTW: I searched the mailing list, but found nothing on the subject -
and
> there also isn't any information in the documentation about the
effects
> of the number of databases, tables or attributes on the performance.
>
> Now, what do you say? Thanks in advance for any comment!
I've never run near that many databases on one box so I can't comment on
the performance. But let's assume for the moment pg runs fine with 500
databases. The most important advantage of multi-schema approach is
cross schema querying. I think as you are defining your problem this is
a better way to do things.
Merlin
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 21:15 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 0 replies; 32+ messages in thread
From: Scott Marlowe @ 2005-12-01 21:15 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On Thu, 2005-12-01 at 13:34, Michael Riess wrote:
> > 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...
>
> Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL,
> but the database got considerably slower near the end of the week.
Generally, this means either your vacuums are too infrequent, or your
fsm settings are too small.
Note that vacuum and analyze aren't "married" any more, like in the old
days. You can issue either separately, depending on your usage
conditions.
Note that with the newest versions of PostgreSQL you can change the
settings for vacuum priority so that while it takes longer to vacuum, it
doesn't stomp on the other processes toes so much anymore, so more
frequent plain vacuums may be the answer.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-01 21:16 Alvaro Herrera <alvherre@commandprompt.com>
parent: me@alternize.com
0 siblings, 0 replies; 32+ messages in thread
From: Alvaro Herrera @ 2005-12-01 21:16 UTC (permalink / raw)
To: me@alternize.com; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
me@alternize.com wrote:
> what i noticed is autovacuum not working properly as it should. i had 8.1
> running with autovacuum for just 2 days or so and got warnings in pgadmin
> that my tables would need an vacuum.
Hum, so how is autovacuum's documentation lacking? Please read it
critically and let us know so we can improve it.
http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM
Maybe what you need is to lower the "vacuum base threshold" for tables
that are small.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 03:47 Craig A. James <cjames@modgraph-usa.com>
0 siblings, 1 reply; 32+ messages in thread
From: Craig A. James @ 2005-12-02 03:47 UTC (permalink / raw)
To: pgsql-performance
So say I need 10,000 tables, but I can create tablespaces. Wouldn't that solve the performance problem caused by Linux's (or ext2/3's) problems with large directories?
For example, if each user creates (say) 10 tables, and I have 1000 users, I could create 100 tablespaces, and assign groups of 10 users to each tablespace. This would limit each tablespace to 100 tables, and keep the ext2/3 file-system directories manageable.
Would this work? Would there be other problems?
Thanks,
Craig
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 07:46 David Lang <dlang@invendra.net>
parent: Craig A. James <cjames@modgraph-usa.com>
0 siblings, 0 replies; 32+ messages in thread
From: David Lang @ 2005-12-02 07:46 UTC (permalink / raw)
To: Craig A. James <cjames@modgraph-usa.com>; +Cc: pgsql-performance
On Thu, 1 Dec 2005, Craig A. James wrote:
> So say I need 10,000 tables, but I can create tablespaces. Wouldn't that
> solve the performance problem caused by Linux's (or ext2/3's) problems with
> large directories?
>
> For example, if each user creates (say) 10 tables, and I have 1000 users, I
> could create 100 tablespaces, and assign groups of 10 users to each
> tablespace. This would limit each tablespace to 100 tables, and keep the
> ext2/3 file-system directories manageable.
>
> Would this work? Would there be other problems?
This would definantly help, however there's still the question of how
large the tables get, and how many total files are needed to hold the 100
tables.
you still have the problem of having to seek around to deal with all these
different files (and tablespaces just spread them further apart), you
can't solve this, but a large write-back journal (as opposed to
metadata-only) would mask the problem.
it would be a trade-off, you would end up writing all your data twice, so
the throughput would be lower, but since the data is safe as soon as it
hits the journal the latency for any one request would be lower, which
would allow the system to use the CPU more and overlap it with your
seeking.
David Lang
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 08:15 Ron <rjpeace@earthlink.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 32+ messages in thread
From: Ron @ 2005-12-02 08:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Agreed, and I apologize for the imprecision of my post below.
I should have written:
"Best practice seems to be to use a journaling fs and log metadata
only and put it on separate dedicated spindles."
I've seen enough HD failures that I tend to be paranoid and log the
metadata of fs dedicated to WAL as well, but that may very well be overkill.
Ron
At 01:57 PM 12/1/2005, Tom Lane wrote:
>Ron <rjpeace@earthlink.net> writes:
> > Agreed. Also the odds of fs corruption or data loss are higher in a
> > non journaling fs. Best practice seems to be to use a journaling fs
> > but to put the fs log on dedicated spindles separate from the actual
> > fs or pg_xlog.
>
>I think we've determined that best practice is to journal metadata only
>(not file contents) on PG data filesystems. PG does expect the filesystem
>to remember where the files are, so you need metadata protection, but
>journalling file content updates is redundant with PG's own WAL logging.
>
>On a filesystem dedicated to WAL, you probably do not need any
>filesystem journalling at all --- we manage the WAL files in a way
>that avoids changing metadata for a WAL file that's in active use.
>A conservative approach would be to journal metadata here too, though.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 13:02 Michael Stone <mstone+postgres@mathom.us>
parent: Ron <rjpeace@earthlink.net>
0 siblings, 0 replies; 32+ messages in thread
From: Michael Stone @ 2005-12-02 13:02 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Fri, Dec 02, 2005 at 03:15:00AM -0500, Ron wrote:
>I've seen enough HD failures that I tend to be paranoid and log the
>metadata of fs dedicated to WAL as well, but that may very well be overkill.
Especially since it wouldn't gain anything. Journalling doesn't give you
any advantage whatsoever in the face of a HD failure.
Mike Stone
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 14:16 Alex Stapleton <alexs@advfn.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 32+ messages in thread
From: Alex Stapleton @ 2005-12-02 14:16 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Riess <mlriess@gmx.de>; pgsql-performance
On 1 Dec 2005, at 16:03, Tom Lane wrote:
> Michael Riess <mlriess@gmx.de> writes:
>> (We NEED that many tables, please don't recommend to reduce them)
>
> No, you don't. Add an additional key column to fold together
> different
> tables of the same structure. This will be much more efficient than
> managing that key at the filesystem level, which is what you're
> effectively doing now.
>
> (If you really have 15000 distinct rowtypes, I'd like to know what
> your database design is...)
>
Won't you end up with awful seek times if you just want data which
previously been stored in a single table? E.g. whilst before you
wanted 1000 contiguous rows from the table, now you want 1000 rows
which now have 1000 rows you don't care about in between each one you
do want.
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 14:20 Alex Stapleton <alexs@advfn.com>
parent: Alex Stapleton <alexs@advfn.com>
0 siblings, 0 replies; 32+ messages in thread
From: Alex Stapleton @ 2005-12-02 14:20 UTC (permalink / raw)
To: Alex Stapleton <alexs@advfn.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Michael Riess <mlriess@gmx.de>; pgsql-performance
On 2 Dec 2005, at 14:16, Alex Stapleton wrote:
>
> On 1 Dec 2005, at 16:03, Tom Lane wrote:
>
>> Michael Riess <mlriess@gmx.de> writes:
>>> (We NEED that many tables, please don't recommend to reduce them)
>>
>> No, you don't. Add an additional key column to fold together
>> different
>> tables of the same structure. This will be much more efficient than
>> managing that key at the filesystem level, which is what you're
>> effectively doing now.
>>
>> (If you really have 15000 distinct rowtypes, I'd like to know what
>> your database design is...)
>>
>
> Won't you end up with awful seek times if you just want data which
> previously been stored in a single table? E.g. whilst before you
> wanted 1000 contiguous rows from the table, now you want 1000 rows
> which now have 1000 rows you don't care about in between each one
> you do want.
>
I must of had a total and utter failure of intellect for a moment
there. Please ignore that :P
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 15:16 Jan Wieck <JanWieck@Yahoo.com>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 0 replies; 32+ messages in thread
From: Jan Wieck @ 2005-12-02 15:16 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
On 12/1/2005 2:34 PM, Michael Riess wrote:
>> VACUUM FULL was probably always overkill, unless "always" includes
>> versions prior to 7.3...
>
> Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL,
> but the database got considerably slower near the end of the week.
This indicates that you have FSM settings that are inadequate for that
many tables and eventually the overall size of your database. Try
setting those to
max_fsm_relations = 80000
max_fsm_pages = (select sum(relpages) / 2 from pg_class)
Another thing you might be suffering from (depending on the rest of your
architecture) is file descriptor limits. Especially if you use some sort
of connection pooling or persistent connections like PHP, you will have
all the backends serving multiple of your logical applications (sets of
30 tables). If on average one backend is called for 50 different apps,
then we are talking 50*30*4=6000 files accessed by that backend. 80/20
rule leaves 1200 files in access per backend, thus 100 active backends
lead to 120,000 open (virtual) file descriptors. Now add to that any
files that a backend would have to open in order to evict an arbitrary
dirty block.
With a large shared buffer pool and little more aggressive background
writer settings, you can avoid mostly that regular backends would have
to evict dirty blocks.
If the kernel settings allow Postgres to keep that many file descriptors
open, you avoid directory lookups.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 21:08 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Michael Riess <mlriess@gmx.de>
3 siblings, 0 replies; 32+ messages in thread
From: Andrew Sullivan @ 2005-12-02 21:08 UTC (permalink / raw)
To: pgsql-performance
On Thu, Dec 01, 2005 at 08:34:43PM +0100, Michael Riess wrote:
> Well, we tried switching to daily VACUUM ANALYZE and weekly VACUUM FULL,
> but the database got considerably slower near the end of the week.
If you have your FSM configured correctly and you are vacuuming
tables often enough for your turnover, than in regular operation you
should _never_ need VACUUM FULL. So it sounds like your first
problem is that. With the 15000 tables you were talking about,
though, that doesn't surprise me.
Are you sure more back ends wouldn't be a better answer, if you're
really wedded to this design? (I have a feeling that something along
the lines of what Tom Lane said would be a better answer -- I think
you need to be more clever, because I don't think this will ever work
well, on any system.)
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
^ permalink raw reply [nested|flat] 32+ messages in thread
* Re: 15,000 tables
@ 2005-12-02 23:00 Francisco Reyes <lists@stringsutils.com>
parent: Michael Riess <mlriess@gmx.de>
0 siblings, 0 replies; 32+ messages in thread
From: Francisco Reyes @ 2005-12-02 23:00 UTC (permalink / raw)
To: Michael Riess <mlriess@gmx.de>; +Cc: pgsql-performance
Michael Riess writes:
> Sorry, I should have included that info in the initial post. You're
> right in that most of these tables have a similar structure. But they
> are independent and can be customized by the users.
>
How about creating 50 databases and give each it's own tablespace?
It's not only whether PostgreSQL can be optimized, but also how well your
filesystem is handling the directory with large number of files. by
splitting the directories you will likely help the OS and will be able to
perhaps better determine if the OS or the DB is at fault for the slowness.
^ permalink raw reply [nested|flat] 32+ messages in thread
end of thread, other threads:[~2005-12-02 23:00 UTC | newest]
Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-01 13:42 15,000 tables Michael Riess <mlriess@gmx.de>
2005-12-01 14:16 ` David Lang <dlang@invendra.net>
2005-12-01 14:51 ` Michael Riess <mlriess@gmx.de>
2005-12-01 18:07 ` Gavin M. Roy <gmr@ehpg.net>
2005-12-01 18:40 ` Tino Wildenhain <tino@wildenhain.de>
2005-12-01 18:48 ` Ron <rjpeace@earthlink.net>
2005-12-01 18:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-02 08:15 ` Ron <rjpeace@earthlink.net>
2005-12-02 13:02 ` Michael Stone <mstone+postgres@mathom.us>
2005-12-01 18:49 ` Gavin M. Roy <gmr@ehpg.net>
2005-12-01 19:08 ` Gavin M. Roy <gmr@ehpg.net>
2005-12-01 14:56 ` Michael Riess <mlriess@gmx.de>
2005-12-01 15:45 ` Ing. Jhon Carrillo // Caracas, Venezuela <jhon.carrillo.foros@gmail.com>
2005-12-01 16:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-01 16:15 ` Jaime Casanova <systemguards@gmail.com>
2005-12-01 16:28 ` Michael Riess <mlriess@gmx.de>
2005-12-02 23:00 ` Francisco Reyes <lists@stringsutils.com>
2005-12-02 14:16 ` Alex Stapleton <alexs@advfn.com>
2005-12-02 14:20 ` Alex Stapleton <alexs@advfn.com>
2005-12-01 16:04 ` Michael Riess <mlriess@gmx.de>
2005-12-01 16:30 ` Guido Neitzer <guido.neitzer@pharmaline.de>
2005-12-01 17:07 ` me@alternize.com
2005-12-01 21:16 ` Alvaro Herrera <alvherre@commandprompt.com>
2005-12-01 17:09 ` Chris Browne <cbbrowne@acm.org>
2005-12-01 19:34 ` Michael Riess <mlriess@gmx.de>
2005-12-01 19:40 ` Jaime Casanova <systemguards@gmail.com>
2005-12-01 21:15 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-12-02 15:16 ` Jan Wieck <JanWieck@Yahoo.com>
2005-12-02 21:08 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-12-01 20:28 Re: 15,000 tables Merlin Moncure <merlin.moncure@rcsonline.com>
2005-12-02 03:47 Re: 15,000 tables Craig A. James <cjames@modgraph-usa.com>
2005-12-02 07:46 ` David Lang <dlang@invendra.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox