pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple 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