Adding new tables -- memory usage increases

Question

I added more tables to my mysql server. 20% increase in tables that are all innodb. I have 200 + databases that use the same schema.

Does adding more tables increase memory usage? I am using about 1.7 gb of ram for mysql where before I was using 1.4-1.5gb

• Philᵀᴹ: What is the exact problem you're having? You've configured mysql to use a certain amount of memory and it's using it. Do you want to restrict the amount if data cached?
• Chris Muench: Yes, I want to use less memory without impacting performance a lot. It started using more memory as soon as I added more tables. I didn't change anything in my.cnf so I am stumped on why it would go up.

Answer 1

Does it increase memory ??? Absolutely !!!

I wrote a post about INFORMATION_SCHEMA (Jun 15, 2011 : How is INFORMATION_SCHEMA implemented in MySQL?)

In that post I explained how metadata for MySQL tables are stored in temporary tables using the MEMORY storage engine. For example, when you create a table mydb.mytable, look at what gets populated into these tables:

• INFORMATION_SCHEMA.TABLES (One row for mydb.mytable)
• INFORMATION_SCHEMA.PARTITIONS (If mydb.mytable is partitioned, one row for each partition)
• INFORMATION_SCHEMA.COLUMNS Info about every column in mydb.mytable
• INFORMATION_SCHEMA.STATISTICS : Information about index statistics in mydb.mytable
• INFORMATION_SCHEMA.KEY_COLUMN_USAGE : Information about a column's use in all indexes in mydb.mytable
• See the full list of INFORMATION_SCHEMA tables

In short, all these tables have to increase in memory usage for each table you create. In your particular case, that would be multiplied by 200.

• Sybil: do you know how much memory is used for one database with (lets say) 200 tables? It should be very less when no queries on tables (archive for example)?
• RolandoMySQLDBA: @Ivanov Archive tables make the situation magnitudes worse. Right now, I have a client with 11000+ DBs and 1000000+ (1 million tables). The majority of the tables have not been in used in the last 3 months. Trying to run queries against INFORMATION_SCHEMA (which is comprised of MEMORY tables with no indexes (dba.stackexchange.com/questions/3335/…)) is a total nightmare. Archive DB should be migrated away to free memory.