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.

[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.