Two databases running under the same engine instance

[Former Member]

Hi

I have 2 databases, one is ~30gb and contains all the relational data and one is ~300gb and contains only one table with blobs.

Currently I am running these DBs both in the same engine instance, is this a good idea? What advantages or disadvantages to this setup are there?

Thanks! Ivan

[VolkerBarth]

We all seem to assume you are about to choose between two databases on one database server vs. two databases on two database servers (still on the same machine). Is this correct?

Or are asking whether it would be helpful to merge the table from the second database into the first database (possibly as a separate dbspace)?

[Former Member]

Thanks for all the replies. The server machine has ~24gb of RAM and is Win 2008 R2 64 bit.

Both DBs are running in the same engine on the same server, I am considering using different server instances on the same machine, one for each database.

Similar to what Alexey says below, I am wondering if the "blob db" is pushing out much more relevant data that is in the "main db" from the cache or other resource areas. Also wondering if some actions such as perhaps table scans on the blob DB might cause problems for the main db.

In terms of what Dmitri says below, two server instances competing for the one machines resources, this I think I am happy to control at least to the extent I can via setting limited cache for the blob db for example.

Regarding Justin et al comments, please clarify "an assertion"? Regarding same front end, it does use both DBs but not all the time, probably not a fair bit of the time, so if this is likely or might reduce "stopping" issues that would be of interest (I am having this very type of issue I believe, see my comments on http://sqlanywhere-forum.sap.com/questions/1482/a-sequential-scan-blocks-all-new-connections?page=1#...)

What might help as part of the decision is to see what the cache is actually holding over a period, but I dont know if this is possible.

[VolkerBarth]

Justin's remark about an assertion refers to a somewhat unexpected state inside the database engine. In such (IMHO very) rare cases the database engine will notify about the reason and then stop immediately. This could be due to possible bugs in the database software or to hardware issues or the like.

Some more info is available here.

Obviously, an assertion will stop all databases on that engine, so if you use a separate engine for each database, an assertion in one engine wouldn't stop the databases on the other engines.

---

However, I would not claim that this is a better/simpler situation from an application programming point of view in case your app does need both databases - you still will need to fix (or at least check) the issue before a re-connect is possible...

[VolkerBarth]

Well, if you primarily want to prevent the engine from sharing resources (like cache) between both databases, than I guess using two engines might be helpful. I guess you should then limit the second engine to use a rather small (or even fixed) cache size.

[VolkerBarth]

Just to clarify: You tagged "runtime-engine" - are you really using the rteng12.exe (which is a limited database engine with a particular deployment licence)? Or is it the standard (personal or network) database engine (deng12.exe or dbsrv12.exe)?

[Former Member]

I am using dbsrv12.exe, couldnt find a suitable tag and am not yet allowed to create my own.

[Former Member · Accepted solution]

When running two DBs on one engine you must consider cache concurrency factor. Two DB share same memory and blobs data can push out OLTP DB datapages from DB cache. Of course, it depends from your DBs usage scenario.

[justin_willey]

It does depend very much on the circumstances; as Dmitri says, running on the same engine allows resources to be shared intelligently However one downside is that an assertion on one database (hopefully a very rare event) will stop both databases. On 32bit systems one can sometimes improve performance with two engines because more memory overall can be allocated to two separate processes than to a single process.

[VolkerBarth]

Really? I've been not aware of that rule, always thought that all user processes share a 2 GB address space (unless booted with /3G and the like...).

[justin_willey]

Yes - but the amount available to any single process is (I believe) limited by the amount of contiguous memory available when the process starts, and I have seen this down around the 1.2G mark - even when there is plenty more available.

I could easily be wrong on this - so I welcome comments!

[justin_willey]

In your case it sounds like the two database are used by the same front end - so it may not matter that both would be stopped by a problem with one of them.

[former_SQLA_member1694880]

32-bit processes are limited to 2G RAM per process, not per computer :).

[VolkerBarth]

Thanks for the correction - per process is what I really meant:

AFAIK, all Win32 processes do have 4 GB of address space, but by default only 2 GB are available for user space. - IMHO, that would mean one process would not limitate another one's address space...

[former_SQLA_member1694880]

I won't recommend running databases on separate engines as they'll be competing for resources (primarily RAM). Single engine hosting 2 databases can manage resources in a more intelligent fashion :).

[VolkerBarth]

I guess there's not much too tell without you giving more information on your goals and your sysrem requirements.

For a general discussion on the different architectures (putting tables into separate machines vs. database servers vs. separate databases on the same database server vs. same database...) there's a blog article series "Multi-Tenant Database Architecture" from Chris Kleisath that might help: It deals with splitting application data for different tenants and shows the pros and cons of each alternative. Though I'm aware that "multi-tenant" is not your particular concern I guess you might still make use of the information. I'd consider parts 3 and 4 primarily.