I have inactive databases that are on the server just for archive purposes.
I know I can do mysqldump for all of them, but I am not 100% sure they are inactive.
When I started my system I didn't have a way to determine active/inactive. If there are no queries on them, will they still consume memory ?
As I mentioned earlier in Adding new tables -- memory usage increases, adding new tables increases memory usage. That being the case, tables that are not in active use would still consume memory in the INFORMATION_SCHEMA.
Determining what is active is an arbitrary process. If you need to see when was the last time a table had written changes, you have to find out based on the Storage Engine of each table.
Learning the last time a MyISAM table had written changes is straightforward.
Suppose you need to see all MyISAM tables that have not been written to in over a month. Simply run this query again INFORMATION_SCHEMA.tables:
SELECT table_schema,table_name,update_time FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND engine='MyISAM' and update_time > (NOW() - INTERVAL 1 MONTH);
Note: update_time for a MyISAM table is the timestamp of the .MYD file. If you run ANALYZE TABLE mydb.mytable;, the index statistics are written to the .MYI file. The .MYI's file timestamp is not reflected in the INFORMATION_SCHEMA.
Getting InnoDB timestamps is quite a challenge because InnoDB does not store update_time in the INFORMATION_SCHEMA. If innodb_file_per_table is disabled, everything InnoDB and its grandmother is encased in the system tablespace file ibdata1. Gettting the timestamp of ibdata1 gives you the last time any write was received. That could be anything
Therefore, with innodb_file_per_table disabled, there is not way to know.
On the other hand, if innodb_file_per_table is enabled, the question remains: HOW DO YOU FIND OUT THE LAST TIME AN INNODB TABLE WAS WRITTEN ?
You must check the file timestamp of the .ibd.
Rather than reinvent the wheel, please read my posts on how to get that info
Dec 21, 2011 : Fastest way to check if InnoDB table has changedJun 03, 2013 : Is there a way to find the least recently used tables in a schema?The main point to keep in mind is to find out timestamp of the table and subtract it from NOW() (or UNIX_TIMESTAMP(NOW()) from the OS point-of-view). You have to arbitrarily determine how old is too old for an inactive table.
This is a somewhat complicated question.
When mysqld first starts up, it will allocate a bunch of memory for itself, but the operating system will actually delay allocation until the memory is first read or written to. That is to say that you will often see in a program like top that the VIRT size is much larger than the RES size until the server warms up.
Within mysqld caches like the innodb_buffer_pool_size and key_buffer_size will have a maximum size (that you can change in your my.cnf file). When these buffers are full, then they will discard the least recently used (LRU) contents to make room for new content to enter the cache.
So yes, after some inactivity (and assuming a full cache) the tables that are not in active use will be removed from the cache.
In MySQL 5.6 it is possible to see the contents of the innodb_buffer_pool via a series of meta-data tables in information schema. I have an example of this (to show ideal minimum cache size) on my blog here:
http://www.tocker.ca/2013/05/31/estimating-mysqls-working-set-with-information_schema.html
