3

I would like to organize the MySQL tables of a complex application into some groups, to avoid looking at a one big bin of tables. One big bin is harder to administrate when e.g. I would like to reset certain groups of related tables, or if I'd later wish to move a group of related tables to a different MySQL server altogether, and it is also more error prone when developing new DDL code.

I can apply a sense of grouping with my own table name prefixing "convention", but thought about using table spaces or even separate schemata. What would be the primary disadvantages of using separate table spaces or schemata for applying a sense of grouping upon my tables?

| improve this question | |
  • Also I thought people would relate to the performance aspects of making joins between (tables of) separate table spaces – matt Jan 27 '15 at 9:44
3
+25

DISADVANTAGE #1

InnoDB only has one Buffer Pool. The benefits of partitioning or compartmentalizing database tables for different clients can still be bottlenecked by database buffers (log buffer, InnoDB Buffer Pool)

DISADVANTAGE #2

Scaling becomes dependent on manual operation. This applies to scaling up disks, hardware (disk controllers), and memory.

DISADVANTAGE #3

More tables means more open file handles to cache distinct tables. Requires more flush to disk.

DISADVANTAGE #4

Introducing new groups of related tables regurgitates the first three(3) disadvantages and exacerbates the overall design.

See my other posts on such setups and other drawbacks

SUGGESTIONS

You might have to compensate these deficiencies with lots of RAM with the InnoDB Buffer Pool and the Log Buffer getting the lion's share.

You could create a hybrid setup where InnoDB Log Files are on one disk and InnoDB System Tablespace is on another, and all the data on a third disk. This concept is well expressed in YOSHINORI MATSUNOBU'S BLOG (FaceBook DB Engineer).

| improve this answer | |
  • Thanks! I will review the links. But can you explain here why scaling is worse if you split to table spaces (#2) and #3 doesn't seem like a disadvantage, the amount of tables stays the same, except for some system tables "duplication", so I don't entirely see how that make a significant difference, maybe you can clarify.. (?) – matt Jan 27 '15 at 9:18
  • Potentially, it would be easier to shard in the future. So, I think it should by counted as advantage – ravnur Jan 27 '15 at 15:17
  • @ravnur It's harder to scale back. It's also harder to redesign should related groups have to be redistributed or merged. – RolandoMySQLDBA Jan 27 '15 at 15:19
  • innodb_buffer_pool_instances somewhat eliminates your DISADVANTAGE #1. – Rick James Jan 30 '15 at 5:39
2

Take a look at my answers to similar questions here (some bits don't apply) and a thread here.

Interestingly, RolandoMySQLDBA seems to be discouraging a multi-tenant approach here, yet he gave a +1 to accepted answer in this thread. Rolando?

The excellent accepted answer in that thread echoes my own experience. Be flexible, and be prepared to make an effort on monitoring and balancing your server loads and you should be in a better position to respond to customer demand.

With respect to your point about cross tablespace/schema queries - they should not be problematic. Cross tablespace queries will be ACID (assuming InnoDB), but cross schema ones can't be by definition. However, for end-of-month reports and the like, they're fine, since you're unlikely to require any query/data interaction between your different tenants, but you will want to know how much traffic/money a given client used/paid in a given period.

You may have some data duplication issues with a multi-tenant approach, but that's a matter for you to assess in the light of your application/usage/servers/budget...

| improve this answer | |
  • 1
    This question asked for disadvantages only and that's what I named, just disadvantages. That other answer from Dave Rix (dba.stackexchange.com/a/7929/877) named advantages that can outweigh disadvantages. Even with my own answer to that same question (dba.stackexchange.com/a/7930/877) I described a mutlitentant situation I had to manage at the customer's request (I had no say with scale-out or scale-up). I have discussed it before (dba.stackexchange.com/a/1690/877) Having face nothing but disadvantges over the years, Dave Rix's answer was refreshing, cool and deserved +1. – RolandoMySQLDBA Jan 29 '15 at 3:59
  • Thanks, BTW, most posters seem to talk about "customers" as if I'm architecting for a customer. I am architecting for a product. – matt Jan 29 '15 at 8:53
  • Which will surely be sold/rented/SaaS/DBaaS to customers? – Vérace Jan 29 '15 at 9:11
2

Within a single instance of MySQL, there are few, if any, advantages in splitting your data into multiple databases and/or tables.

With PARTITIONing and InnoDB, you get separate "tablespaces", but there are very few applications that get any performance gain with such. They are listed here: http://mysql.rjweb.org/doc.php/partitionmaint#use_cases_for_partitioning

A "database" is implemented as an OS directory. A table is implemented as one to three files in that database/directory. If you have more than, say, 10K items in a directory (that is, more than 10K databases or 10K tables in a database), the OS will be sluggish. Plus you will push the limits of several caches.

Someday, WordPress will understand this, and change their 1-db per user approach.

Multi-tennancy -- Sure. I like the CGroups approach to better control resources given to each MySQL instance.

Manually putting different things on different disks. No. You are better off (usually) by using RAID striping, especially RAID-5 or RAID-10. Be sure to have a hardware RAID controller with battery-backed-write-controller; that way, writes are instantaneous, and you are still protected from power failures.

Sharding -- Yes; if it is applicable. But you have to do a lot of work yourself, or get Spider or Clustrix.

Also consider Galera / PXC / MariaDB -- they give you multi-master automagically, plus HA.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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