Обсуждение: do separate databases have any impact each other?

While writing installation instructions for my new PostgreSQL product, I found myself
writing the following sentence:
"For first time users, we recommend building the gnova database,
since it has no impact on other databases."

Is this really true?  Of course, my gnova database will take some disk space,
but other than that can y'all think of other concerns a big production database
admin might have about installing a new (smallish) database?
Is it true that if no one has begun a session involving my database, it simply
sits on disk and cannot possibly interfere with other databases/sessions?
When a session ends that had opened my database, do all traces of it disappear,
except its life on disk?  How about .so's it might have caused to be loaded?

TJ

"TJ O'Donnell" <tjo@acm.org> writes:
> While writing installation instructions for my new PostgreSQL product, I found myself
> writing the following sentence:
> "For first time users, we recommend building the gnova database,
> since it has no impact on other databases."

> Is this really true?

Reasonably.

> When a session ends that had opened my database, do all traces of it disappear,
> except its life on disk?  How about .so's it might have caused to be loaded?

.so's only get loaded into sessions that have used them (unless you go
out of your way to persuade the postmaster to preload them).

AFAIK the only serious reason why someone might not want a playpen
database added to an existing installation is that any instability
at the C-code level propagates to the whole cluster --- that is,
a core dump in your .so takes out backends in other databases too.
The prevention for this is to make a separate cluster with its
own postmaster.

(Playpen code can have other bad side-effects, of course, such as
hogging all your CPU or I/O bandwidth.  But a separate cluster
doesn't help that --- only putting it on a different machine does.)

            regards, tom lane

> While writing installation instructions for my new PostgreSQL
> product, I found myself writing the following sentence: "For first
> time users, we recommend building the gnova database, since it has
> no impact on other databases."
>
> Is this really true?  Of course, my gnova database will take some
> disk space, but other than that can y'all think of other concerns a
> big production database admin might have about installing a new
> (smallish) database?  Is it true that if no one has begun a session
> involving my database, it simply sits on disk and cannot possibly
> interfere with other databases/sessions?  When a session ends that
> had opened my database, do all traces of it disappear, except its
> life on disk?  How about .so's it might have caused to be loaded?

There are various indirect effects that one database may have on
another.

- They share a single set of shared buffers

  I have seen cases where storing application logs in the same
  database as the application used had the detrimental effect that the
  logs would chew up shared buffer cache, which hurt performance

- _Any_ long running transaction on the cluster prevents vacuums from
  taking useful effect.

  That is, if I have a connection open in transaction in Database A,
  as of time t[1000] vacuuming can do no good on any subsequent
  activity after time t[1000] in *any* database.

  We experienced a problem with this where we had a PG backend hosting
  a replicated ticketing database, where replication blows quickly
  through tuples in pg_listener, amongst other thing.  There was also
  an application using some funky Perl database module that would hold
  connections open literally for days at a time.  Those connections
  would prevent vacuums from doing any good :-(.  We had to separate
  those databases as a result.

On the other hand, if a database sits on the cluster mostly unused,
and connections are opened comparatively briefly, there shouldn't be
any big problem.
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.

On Mon, Aug 15, 2005 at 01:15:03PM -0700, TJ O'Donnell wrote:
> While writing installation instructions for my new PostgreSQL product, I
> found myself
> writing the following sentence:
> "For first time users, we recommend building the gnova database,
> since it has no impact on other databases."

A problem not mentioned already is that if your database contains a C
function and it has a bug which results in a crash, your whole
production database will go down.  This may not be desirable.

(plphp may cause that too, for some functions, as we saw in a report on
some list a couple of days ago ...)

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"Use it up, wear it out, make it do, or do without"

Alvaro Herrera wrote:
> On Mon, Aug 15, 2005 at 01:15:03PM -0700, TJ O'Donnell wrote:
>
>>While writing installation instructions for my new PostgreSQL product, I
>>found myself
>>writing the following sentence:
>>"For first time users, we recommend building the gnova database,
>>since it has no impact on other databases."
>
>
> A problem not mentioned already is that if your database contains a C
> function and it has a bug which results in a crash, your whole
> production database will go down.  This may not be desirable.

Well I have the perfect solution for that.... <grin>
I just won't write any buggy code!
Seriously, this could be a problem, especially since there is
3rd party software involved, too.  Guess I've opened myself up for some
serious testing procedures.  That's a good thing.
Since I'm writing in C++ with a thin interface to C for pg,
I catch a lot of nasty situtations which used to crash my C programs.

TJ