Do dormant databases impact server performance?

Question

On our web server, my team has been creating new databases in MySQL to manage backups. They simply create new database_DATE and copy over our live site. Which made me think today... Can having a database (no possibility of ever being used) impact performance (guessing memory) of the server?

Answer 1

Case 1: An extra table or database will eventually be pushed out of cache. It is harmless. Cure (but not worth doing): restart mysqld.

Case 2: The 'system' is building thousands of tables and/or databases. Each table/database involves at least one entry in the containing OS directory. As this directory gets fuller, the OS operations to access files/directories (hence tables/databases) slows down. Granted, it takes thousands, maybe tens of thousands of such entries before the impact is painful, but there is impact. Cure: Stop building so many tables/databases, and DROP the excess.

Case 3: Someone or something does SHOW DATABASES or SHOW TABLES or SELECT ... FROM information_schema ... and it is sensitive to the number of tables/databases. These actions will load stuff into memory, possibly blowing out some cache (such as table_open_cache) Cure: Same as for both cases 1 and 2.

Bottom line: When there are hundreds of these databases, tell them to clean up their act! (Until then, don't worry.)

Answer 2

Yes, you are right. Memory is used by dormant databases

I mentioned this in the following posts over the years

• Nov 13, 2015 : #1041 - Out of memory issue in mysql
• Apr 22, 2014 : Do Inactive MySQL Databases Consume Memory?
• Apr 21, 2014 : Adding new tables -- memory usage increases

Lots of tables ? Lots of columns ? Of course, that is less memory for mysqld to work with.

You just asked

Is there a way to ensure a database is using no memory?

If you never access a table, no data or index pages will ever touch the InnoDB Buffer Pool, the MyISAM Key Cache, or any session's read_buffer or read_rnd_buffer.

Notwithstanding, having too many tables with lots of columns will result in nothing more that a lot of dormant information_schema memory. Neither the number of rows in each table nor the size of any of the tables matter.