3

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
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:

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.

  • 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)? – Ivanov Sep 20 '18 at 9: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 (dba.stackexchange.com/questions/3335/…)) is a total nightmare. Archive DB should be migrated away to free memory. – RolandoMySQLDBA Sep 20 '18 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.