Should I be concerned about performance with connections to multiple databases?

Question

I have a PHP app that is running on an Apache server with MySQL databases. Based on the subdomain that users access, I am connecting them to a database (sub1.domain.com connects to database_sub1 and sub2.domain.com connects to database_sub2). Right now there are 10 subdomain-database combos, but that number could potentially grow to well over 100.

So, is this a bad thing?

Considering my situation, is mysql_pconnect the way to go?

Thanks, and please let me know if more info would be helpful.

Josh

Answer 1

It partly depends on the rest of your configuration, but as long as each transaction only involves one connection then the database client code should perform as you would expect - about the same as with a single database but with more possibilities to improve the performance of the database servers, up to the limit of the network bandwidth.

If more than one connection participates in a transaction, then you probably need an XA compliant transaction manager, and these usually carry a significant performance overhead.

Answer 2

No, it's not a bad thing.

It's rather question of number of parallel connections in total. This can be defined by "max_connections" in mysql settings (default it's 151 since MySQL 5.1.15), and is limited by capability of your platform (i.e. 2048< on Windows, more on Linux), hardware (RAM) and system settings (mainly by limit of open files). It can be a bottleneck if you have many parallel users, number of databases is not important.

I made a script which connects 400+ databases in one execution (one after one, not parallel) and i found mysql + php handling it very well (no significant memory leaks, no big overhead). So i assume there will be no problem with your configuration.

And, finnaly - mysql_pconnect is generally not good think in web developement if there is no significant overhead in connecting database per se. You have to manage it really carefully to avoid problems with max_connections, locks, pending scripts etc. I think that pconnect has limited use (ie. cron job runned every second or something like that)

Answer 3

Is this an app you have written?

If so, from a maintenance standpoint this may turn into a nightmare.

What happens when you alter the program and need to change the database?

Unless you have a sweet migration tool to help you make changes to all your databases to the new schema you may find yourself in a world of hurt.

I realize you may be too far into this project now, but if a little additional relation was added to the schema to differentiate between the domains (companies/users), you could run them all off one database with little additional overhead.

If performance really becomes a problem (Read this) you can implement Clustering or another elegant solution, but at least you won't have 100+ databases to maintain.