pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Multiple databases one directory
4+ messages / 3 participants
[nested] [flat]

* Multiple databases one directory
@ 2003-01-24 22:39 Noah Silverman <noah@allresearch.com>
  2003-01-24 23:22 ` Re: Multiple databases one directory Josh Berkus <josh@agliodbs.com>
  2003-01-25 00:50 ` Re: Multiple databases one directory Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 4+ messages in thread

From: Noah Silverman @ 2003-01-24 22:39 UTC (permalink / raw)
  To: pgsql-performance

Hi!

Another fun question in our ongoing analysis on whether to switch from 
mysql to postgres. (Just as an update, Postgres has performed 
flawlessly on all of our stress tests so far.)

We have a situation where we will be creating two fairly large and 
complex databases with many tables (thousands) each.  From what I 
understand, postgres keeps everything in one big data directory.

Would there be an advantage to putting each of the two databases into a 
separate directory and starting two instances of postgres?  Is it 
better to just lump everything together.

In a perfect world, we would buy another database server and raid for 
the second database, but being a small company, we just  don't have the 
budget right now. The raid on our current server is much bigger than we 
need.

Thanks,

-N




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Multiple databases one directory
  2003-01-24 22:39 Multiple databases one directory Noah Silverman <noah@allresearch.com>
@ 2003-01-24 23:22 ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Josh Berkus @ 2003-01-24 23:22 UTC (permalink / raw)
  To: Noah Silverman <noah@allresearch.com>; pgsql-performance


Noah,

> Would there be an advantage to putting each of the two databases into a 
> separate directory and starting two instances of postgres?  Is it 
> better to just lump everything together.

You can use the WITH LOCATION option in CREATE DATABASE to put the two 
databases into seperate directories *without* running two instances of 
postgres.

For that matter, the databases each have their own directories, by OID number.

Of course, this only helps you if the seperate directories are on seperate 
disks/arrays/channels.  If everying is on the same disk or array, don't 
bother.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Multiple databases one directory
  2003-01-24 22:39 Multiple databases one directory Noah Silverman <noah@allresearch.com>
@ 2003-01-25 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-01-25 00:57   ` Re: Multiple databases one directory Noah Silverman <noah@allresearch.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Tom Lane @ 2003-01-25 00:50 UTC (permalink / raw)
  To: Noah Silverman <noah@allresearch.com>; +Cc: pgsql-performance

Noah Silverman <noah@allresearch.com> writes:
> We have a situation where we will be creating two fairly large and 
> complex databases with many tables (thousands) each.  From what I 
> understand, postgres keeps everything in one big data directory.

Yeah.  You're kind of at the mercy of the operating system when you do
that: if it copes well with big directories, no problem, but if lookups
in big directories are slow then you'll take a performance hit.

The first thing I'd ask is *why* you think you need thousands of
tables.  How will you keep track of them?  Are there really thousands of
different table schemas?  Maybe you can combine tables by introducing
an extra key column.

Perhaps a little bit of rethinking will yield a small design screaming
to get out of this big one ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Multiple databases one directory
  2003-01-24 22:39 Multiple databases one directory Noah Silverman <noah@allresearch.com>
  2003-01-25 00:50 ` Re: Multiple databases one directory Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-01-25 00:57   ` Noah Silverman <noah@allresearch.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Noah Silverman @ 2003-01-25 00:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Thanks,

We're considering this.

On an unrelated note, it looks like our crash was due to running out of 
file descriptors for the bash shell.

Linux won't let me increase the limit for a user other than root.  Does 
anyone know how to change this (We're running slackware)


Thanks,

-N


On Friday, January 24, 2003, at 07:50 PM, Tom Lane wrote:

> Noah Silverman <noah@allresearch.com> writes:
>> We have a situation where we will be creating two fairly large and
>> complex databases with many tables (thousands) each.  From what I
>> understand, postgres keeps everything in one big data directory.
>
> Yeah.  You're kind of at the mercy of the operating system when you do
> that: if it copes well with big directories, no problem, but if lookups
> in big directories are slow then you'll take a performance hit.
>
> The first thing I'd ask is *why* you think you need thousands of
> tables.  How will you keep track of them?  Are there really thousands 
> of
> different table schemas?  Maybe you can combine tables by introducing
> an extra key column.
>
> Perhaps a little bit of rethinking will yield a small design screaming
> to get out of this big one ...
>
> 			regards, tom lane
>




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-01-25 00:57 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-24 22:39 Multiple databases one directory Noah Silverman <noah@allresearch.com>
2003-01-24 23:22 ` Josh Berkus <josh@agliodbs.com>
2003-01-25 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-01-25 00:57   ` Noah Silverman <noah@allresearch.com>

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