MySQL Databases. How Many for a Web App?

Question

I'm building a web app. This app will use MySQL to store all the information associated with each user. However, it will also use MySQL to store sys admin type stuff like error logs, event logs, various temporary tokens, etc. This second set of information will probably be larger than the first set, and it's not as important. If I lost all my error logs, the site would go on without a hiccup.

I am torn on whether to have multiple databases for these different types of information, or stuff it all into a single database, in multiple tables.

The reason to keep it all in one, is that I only have to open up one connection. I've noticed a measurable time penalty for connection opening, particularly using remote mysql servers.

What do you guys do?

Answer 1

I would only use one databse - mostly for the reason you supply: You only need one connection to reach both logging and user stored data.

Depending on your programming language, some frameworks (J2EE as an example) provide connection pooling. With two databases you would need two pools. In PHP on the other hand, the performance come in to perspective when setting up a connection (or two).

Answer 2

Fisrt,i must say, i think storing all your event logs, error logs in db is a very bad idea, instead you may want to store them on the filesystem.

You will only need error logs or event logs if something in your web app goes unexpected. Then you download the file, and examine it, thats all. No need to store it on the db. It will slow down your db and your web app.

As an answer to your question, if you really want to do that, you should seperate them, and you should find a way to keep your page running even your event og and error log databases are loaded and responding slowly.

Answer 3

Going with two distinct database (one for your application's "core" data, and another one for "technical" data) might not be a bad idea, at least if you expect your application to have a lot of users :

• it'll allow you to put one DB on one server, and the other DB on a second server
  • and you can think about scaling a bit more, later : more servers for the "core" data, and still only one for the "technical" data -- or the opposite
• if the "technical" data is not as important, you can (more easily) have two distinct backup processes / policies
• having two distinct databases, and two distinct servers, also means you can have heavy calculations on the technical data, without impacting the DB server that hosts the "core" data -- and those calculations can be useful, on logs, or stuff like that.
  • as a sidenote : if you don't need that kind of "reporting" calculations, maybe storing those data to a DB is not useful, and files would do perfectly ?

Maybe opening two connections means a bit more time -- but that difference is probably rather negligible, is it not ?

I've worked a couple of times on applications that would use two database :

• One "master" / "write" database, that would be used only for writes
• and one "slave" database (a replication of the first one, to several slave servers), that would be used for reads

This way, yes, we sometimes open two connections -- bu one server alone would not have been able to handle the load...

Answer 4

Use connection pooling anyway. So the time to get a connection is not a problem. But if you have 2 connections, transaction handling become more complicated. On the other hand, sometimes it's handy to have 2 connections: if something goes wrong on the business transaction, you can rollback transaction and still log the failure on the admin transaction. But I would still stick to one database.

Answer 5

I see no reason for two databases. It'd be perfectly acceptable to have tables that are devoted to "technical" and "business"data, but the logical separation should be sufficient.

Physical separation doesn't seem necessary to me, unless you mean an application and data warehouse star schema. In that case, it's either real-time updates or, more typically, a nightly batch ETL.

Answer 6

It makes no difference to mysql in any way whether you use separate "datbases", they are simply catalogues.

It may make setting permissions easier, this is a legitimate reason to do it. Other than that, it is exactly the same as keeping the tables in the same db (except you can have several tables with the same name ... but please don't)

Putting them on separate servers might be a good idea however, as you probably don't want your core critical (user info, for example) data mixed in with your high-volume, unimportant data. This is particularly true for old audit data, debug logs etc.

Also short-lived data, such as search results, sessions etc, could be placed on a different server - it presumably has no high availability[1] requirement.

Having said that, if you don't need to do this, dump it all on one server where it's easier to manage (backup, provide high availibilty, manage security etc).

It is not generally possible to take a consistent snapshot of data on >1 server. This is a good reason to only have one (or one that you care about for backup purposes)

[1] Of the data, not the database.

Answer 7

In MySQL, InnoDB has an option of storing all tables of a certain database in one file, or having one file per table.

Having one file per table is somewhat recommended anyway, and if you do that, it makes difference on the database storage level if you have one database or several.

With connection pooling, one database or several is probably not going to matter either.

So, in my opinion, the question is if you'd ever consider separating the "other half" of the database to a separate server - with the separate server having perhaps a very different hardware configuration, such as no RAID. If so, consider using separate databases. If not, use a single database.