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

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.