Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a situation were we can (A) deploy instances of an applications in one MySQL database using table prefixing or (B) use different MySQL databases for each instance of the application, for e.g.,

Setup "A":

central_database
  app1_table1
  app1_table2
  app1_tablen
...
  appn_table1
  appn_table2
  appn_tablen

The end result being a large db with many tables.

Setup "B":

app1_db
  table1
  table2
  tablen

...

appn_db
  table1
  table2
  tablen

The end result being many databases with some tables.

All things equal (e.g., amount of data, number of app instances, etc), what are the pros and cons of going with either approach? What would be detrimental to database performance and maintenance? The application is PHP 5 based, run over Apache 2.x, and we're running MySQL 5.x.

Many thanks for your time and thoughts!

share|improve this question
1  
Related: dba.stackexchange.com/questions/4547/… –  Derek Downey Nov 15 '11 at 21:46
    
Related : dba.stackexchange.com/questions/1043/… –  RolandoMySQLDBA Nov 15 '11 at 23:35
add comment

3 Answers 3

up vote 7 down vote accepted

I ran a system with the best part of a thousand databases, spread across multiple servers. They were all an identical structure and were synchronised with a template database which was on each of the machines.

This allowed me the ability to migrate databases from one db to another if one was getting excessively over-loaded, and as the client mix changed, I could create new databases on different servers to load balance across the servers. This was the biggest advantage I got from the system, in that I had multiple large lumps of tin performing multiple complicated queries simultaneously on the separate servers.

The great thing about this, is that you can add servers to the configuration at your own speed, as each server starts to get over-loaded, add another into the mix, migrate some dbs across to the new server and end up with a nicely load balanced set of servers. A really nice and simple way to add scale to the system as and when it is required!

The reason I went with this approach rather than the single huge database approach, was the sheer size of the potential database that would have been created... each of the 1000 databases had 200 tables, and many of the individual tables within each of the databases comprised many hundreds of millions of rows of data!

A single database configuration would have required certain tables (approx 8 of them) to have multi-billions of rows of data, and the total db size would have been over 10Tb. We were able to have multiple servers with 5Tb of RAID 10 storage, with many databases on each.

That's what I would do! Hope it helps your decision making... :)

share|improve this answer
    
Cool answer !!! +1 !!! –  RolandoMySQLDBA Nov 15 '11 at 23:27
add comment

Is the application you are building a SaaS application? If so, I would suggest you consider a third approach - have one DB, with a common structure for all application instances with one difference - add a userid/applicationid column in all columns. This will greatly reduce your application development / maintenance costs. This in my experience is one of the best approaches to storing multi-tenant data.

Also see this great white paper by Microsoft on multi-tenant data architecture

It also highlights the advantages/ disadvantages on the approaches you have mentioned.

share|improve this answer
    
+1 for the Cool White Paper –  RolandoMySQLDBA Nov 15 '11 at 23:27
add comment

Setup B is way easier to manage

Each tablen sits in a different folder. That can be very beneficial if you do no want to test OS limits.

For example, my employer hosts MySQL for a CRM system of car dealerships. Client has 800 dealerships. Each dealership database has 160 tables. That's 128,000 tables.

  • Under Setup A, all 128,000 tables would sit under one database.
  • Under Setup B, each set of 160 tables sits in a subfolder under /var/lib/mysql.

From the perspective of the OS and its ability to handle i-nodes (or FAT tables for Windows), which includes having a maximum number of files per folder:

  • Under Setup A, you would worry about 128,000 files under one folder. Can your OS support that many files under a single folder?
  • Under Setup B, no such worry.

If you had to tweek table structures using ALTER TABLE or some other DDL:

  • Under Setup A, you would have to script the needed DDL using PHP (or specialized MySQL scripts) against the specific table name and corresponding queries before accessing it and making changes
  • Under Setup B, Connect to right database, then access the same named table every time. The access paradigm would always be clean:
    • Specific Database
    • Specific Folder under /var/lib/mysql
    • Specfic TableName.

If you want to put different databases on different disks:

  • Under Setup A, symlinks for every table moved to a separate disk will only exacerbate the "number of inodes in a folder" problem. Disk I/O and overall table access complicates more and increases overall server load since .frm files are repeatedly accessed.
  • Under Setup B, simply move a whole database folder into a separate data mount. Disk I/O can be distributed on demand.
  • CAVEAT : Highly discouraged for InnoDB

Speaking metaphorically, which would you rather have?

  • a gigantic apartment with one bedroom, one bathroom and one kitchen (SetupA)
  • multiple apartments, each with its own bedroom, bathroom and kitchen (SetupB)

When it comes to fixing a radiator in an apartment:

  • With Setup A, every tenant can be inconvenienced and must be involved because you have to talk with affected tenants in front of everyone like it's everybody's business
  • With Setup B, other than hearing some banging on the wall or in the pipes, tenants can go on with their private lives
  • This list and its metaphors can go on and on

IHMO Although budgets may be a driving force to design/infrastructure decisions, I would easily be in favor of separates databases per client.

share|improve this answer
    
Just be careful when you get to too many databases and tables on the same server - we had 1000 x 200, and MySQL seemed to have some serious issues with the table_cache and table_definition_cache settings, and performance suffered. Solution, move half the databases onto another server and load balanace through the software (using a single table containing the db name and which server it was on!) –  Dave Rix Nov 15 '11 at 23:24
    
That's why SetupB allows the flexibility you are suggesting in your comment as well as in your answer. –  RolandoMySQLDBA Nov 15 '11 at 23:26
    
What's funny about this answer is that I addressed it a long time ago : dba.stackexchange.com/questions/1043/… –  RolandoMySQLDBA Nov 15 '11 at 23:33
    
Interesting concept, it relates to filegroups on SQLServer and tablesspaces on Oracle (if the memory doesn't fail on that last). But you won't need different databases on these two to accomplish SetupB... –  Fabricio Araujo Dec 12 '11 at 16:49
    
Unless you put them on different instances/home... –  Fabricio Araujo Dec 12 '11 at 17:00
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.