4

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 ?

4

PROLOGUE

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.

MyISAM

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.

InnoDB

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

  • INSERT, UPDATE, DELETE (altering data and index pages)
  • ANALYZE TABLE (altering index pages)
  • SELECT (data snapshots via MVCC)

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

EPILOGUE

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.

  • Is there a way to estimate how much memory they will use? The database size is 1.8 MB on average. But the table does NOT have reads/writes to it very often. – Chris Muench Apr 22 '14 at 18:18
  • I am just looking for a ballpark. If it is 2 mb, how much overhead will that add to my server? – Chris Muench Apr 22 '14 at 18:28
1

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

  • Ok so it still gets cached until it something else causes it to get kicked out. I wish I did a better job of discarding unused data, but it is pretty hard to know for sure if I can drop a database or not as some of them are inactive, but I am not sure if they are 100% inactive. – Chris Muench Apr 22 '14 at 19:11
  • Yes - It can make sense to remove the unused tables to simplify backups. Also archiving unused data in used tables can make sense because it improves query performance in cases where indexes can not be used effectively. – Morgan Tocker Apr 22 '14 at 21:08

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.