1000's of tables or multiple instances of mysql?

Question

I've inherited a company application that uses 58 tables per "object", and the application can have N objects. We are looking at an install of 75 - 100 objects for an application, which is 4300-5000 tables.

What we are trying to figure out is do we want to use one database and prefix the table names per object, or use one database per object (the application supports both). The only difference would be for each install of the application, we'd need additional mysql instances on different ports if we were to do per database.

Has anyone done anything similar? Are there any issues (outside of management) of having 4000+ tables in a database?

Edit

Thanks for the updates. As to a bunch of the comments

1) The company pays very well...I'd be dumb not to be taking this job. I wish just writing great code put the $$ in my bank account

2) Our clients are happy with the product. We've thought about re-writing it, but aside from the costs, we'd miss the market. While the structure is bad, the app works better than what most clients have.

3) Object isn't the best term...it's not like an object/class, but objects inside the application. I guess I can just say bucket instead.

Answer 1

Hard to tell without seeing actual schema (and why on Earth an object can require 58 tables), but if you will ever need to join these tables, you'll need to keep them in a single database.

Having many MyISAM tables in one database means and using them all at once means a need to have large table_cache.

Answer 2

If you're repeating 58 tables a hundred times, I'd suggest that normalization rules have been trampled on. It's not likely that your company will revisit the schema design for this product, but I'd recommend it based on the information you've provided.

Don't make it worse by distributing the databases. How can latency help?

Answer 3

MySql stores each table as a file, and there is no limit aside from your OS and your hard drive. However, there's a reason that's not commonly discussed- having thousands of tables is almost certainly the wrong thing to do, your database schema is probably badly in need of redesign.

Answer 4

Oddly enough, I've SEEN this sort of application, and the best thing I can tell you to do is re-write the application. Stop selling the application until the design can be re-thought to NOT involve 58 tables per object.

Answer 5

I worked for a company that implemented something similar to what you're talking about. We were using a product called RAMCO, and it implemented in either way, and we opted to keep all tables in a single database. We chose this method for multiple reasons (including easier management, backups, replication, and clustering). The interesting thing about this is that while you have such a disjointed data model, the actual amount of data you're putting in it isn't any more than you'd put into a different model. For this reason our backups were approximately 12GB uncompressed. As far as the engine, it can handle that many tables just fine.

This being said, I agree with every other answer so far that your company should definitely re-think their data architecture (or if I were you I would re-think whether I really wanted to work for a company that would implement something like that). In my case, the comapny refused to drop the product, so I found other employment.

Answer 6

Provided you can guarantee that the number of tables will not increase without limit, having a reasonably large number of tables is no problem.

Really the main problems are the table cache (which should really be bigger than the total number of tables, unless you want performance to suck) and limitations in the engine and operating system.

We regularly use MyISAM with 2000+ tables per server, there is no specific problem if you've tuned and tested it to work with that volume.

Be sure that you do performance testing with the intended number of tables. Opening tables can be expensive, so be sure you don't do it more than necessary.

Some engines (MyISAM) will throw some cached data (key blocks only) away when you close a table, this is incredibly bad if you're closing them on a regular basis.

Depending on the options, your engine may require one or more file descriptors per open table. Normally (on Linux anyway) mysql should tune the maximum number correctly using ulimit, but in some cases it may not.

At some point you might run into an os-wide limit (32k by default I think on some Linux versions), and have to increase it with a sysctl or some other tweak.