Thread: 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