Thoughts on multi database connectivity (varying)

[potatoe]

Hello there,

As described in my thread's title.. Here's the situation:

On this CRM I am currently developing to migrate a desktop CRM. I have planned to separate the database for my core and still use the current desktop CRM database to prevent any more file size increase the CRM database. Also, the project requires me to have a failover database for the old DB

The first database (MySQL) being my primary core DB (this has server settings, style, users etc.. essentially what i need to process full system initialisation) and is the failover database.

Second has the data that I need to load in some of my forms (MSSQL/MySQL).

I may have a third database (type would vary)

Now, in terms of performance and server load on a full production web server (apache, php, mysql)--would it be fast or stable enough to handle on/off connections?

tl;dr;
I have two databases i need to switch on and off connections ex. because I may need to grab data from database a and another one from database b or c--would this be heavy server side?

Thanks
Dev

[james.newell]

Hi potatoe,

My guess is that using multiple databases will be slightly slower than using a single database. Why? A PHP request is handled on a single thread. Consequently the script blocks while each database connection is made and while each query is performed. And because you're using an extra database, your script will have to wait for the extra database connection to be made.

There probably won't be any extra overhead with the queries (unless you're doing something that could have been done with one query i.e. a join (the database is faster at performing a join then coding it in PHP)) as they will still have to wait for each other regardless of whether you're using one, two or more databases.

If you were using Java or another language that allowed you to use threads, using two databases may actually improve the response of the request as the two queries could be run at the same time on different databases (or the same database with different connections). Although this would depend on the type of data, how its being used for and the bandwidth between the server and database.

Whether this slight loss in performance (due to multiple databases) is an issue for you will depend on whether you're website is on a shared server and how many people you expect to access the server at any one time. If you're worried, you should test/time both options to see if its significant.

Hope thats helps.

James