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?
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.)
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.
Is there a way to ensure a database is using no memory?
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.