0

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?

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.)

  • Would you say that backing up a Wordpress site with 800 posts and 15K users/data - so pretty small - times about 10 backups - can have any noticeable effect on performance given we are on a very fast server with 8 GB of RAM? – LOSTinDB Jul 2 '16 at 23:10
1

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

I mentioned this in the following posts over the years

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.

  • Is there a way to ensure a database is using no memory? – LOSTinDB Jun 22 '16 at 0:37

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.