Optimization for thousands of schemas in MySQL

I have an application that stores isolated (and sharded) user data in separate MySQL schemas (aka databases) with with identical table structure (3 InnoDB tables), one for each account. We're using Amazon RDS to store the data and there are currently about 30k schemas per RDS instance. My question is about how to optimize the MySQL settings (or in terms of RDS, the Parameter Group settings) for a large开发者_JAVA百科 number of schemas.

I know there is a reflex to condemn the practice of having thousands of identical schemas, suggesting instead that there be a single schema with something like an indexed "account_id" column. The argument generally given is that the penalty for shard-walking or schema changes outweigh the benefits. In our case, we need to keep the data isolated and there is never any interaction between different accounts.

What are some ways to maximize performance in a situation like this?

Warning in advance: I have no clue about Amazon RDS, so this answer may be absolute nonsense. I am answering from a generic MySQL perspective.

Well, your setup is not entirely optimal, but there are some ways to tune it. You want to prevent opening/closing tables too often, so you want a lot of tables open at the same time.

To do this:

• Make sure your OS allows MySQL to have a lot of open files
• Set the table_cache properly

You can find some more references in the MySQL manual.

How high you actually want this limit depends on the constraints of your resources. Each open table takes memory - I'm not sure how much. A script like the tuning primer or mysqltuner.pl can help you to prevent overcommitting your memory.