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

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? — Philᵀᴹ, Apr 21 '14 at 19:02
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. — Chris Muench, Apr 21 '14 at 19:12

score 4 · Accepted Answer · edited Apr 13 '17 at 12:43

4

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.

edited Apr 13 '17 at 12:43

Community

1

answered Apr 21 '14 at 19:39

RolandoMySQLDBA

182,700
33
317
520

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)? – Sybil Sep 20 '18 at 09:31
1

@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 (https://dba.stackexchange.com/questions/3335/how-is-information-schema-implemented-in-mysql/3336#3336)) is a total nightmare. Archive DB should be migrated away to free memory. – RolandoMySQLDBA Sep 20 '18 at 13:44

Adding new tables -- memory usage increases

1 Answers1

Linked