Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to show databases that are not used. For that I tried this query, but the result set is empty:

mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME NOT
        IN (SELECT db FROM INFORMATION_SCHEMA.PROCESSLIST);
     Empty set (0.70 sec)

I use MySQL 5.5.24.

Any suggestions?

share|improve this question

3 Answers 3

The query makes the assumption that the database in use is the DB Session's current database, which you had set at authentication time. Certain factors would bypass your definition:

FACTOR #1 : Fast queries

If you run a query that takes a millisecond/microsecond, there is 99.9%/99.9999% chance your query will miss this millisecond/microsecond window to see that fast query.

FACTOR #2 : Tables with an Explicitly-Named Database

If you connect to database db1 and run SELECT A.* FROM db2.tblname;, then you are actually accessing two databases, one for the session and one for the query.

It's entirely possible to connect with no default database. This would render your query useless. You should be parsing the query to check for Tables with an Explicitly-Named Database. You can get the current query of a DB Connection from the Info column of INFORMATION_SCHEMA.PROCESSLIST.

FACTOR #3 : Open Files Handles

When a table has been accessed, you can look in the OS for the timestamp of tables. For example:

lsof | grep "\.ibd$" | awk '{print $9}'

For a MySQL instance configured for innodb_file_per_table, this will show you all the files for InnoDB tables that have open files handles.

This command

lsof | grep "\.MYD$" | awk '{print $9}'

give you the list of open MyISAM tables.

Take the output of the commands, parse the database out of each file, and count.

FACTOR #4 : File Timestamps

You can go into the OS, get the timestamp of the files for the tables and compute how long ago was a table last accessed. INFORMATION_SCHEMA.TABLES can do this for MyISAM with the UPDATE_TIME column. For InnoDB, you must go to the OS. Please see my StackOverflow post on how.

EPILOGUE

You may need to resort to FACTOR #3,FACTOR #4, or a combination of both to get a realistic view of what databases are in use or not is use.

share|improve this answer

So time is not a factor? As Rolando eluded to you're going to miss things not within the chrono-scope of your query.

Alternatively you could set your long_query_time to 0 to log all queries running on the instance and leave it for some time. You should capture all but lets say you have something like common_schema in place and it could be used infrequently enough to fall outside the capture.

Once you're happy with the duration of the collection you can do some sed|awk to parse the log to count the accesses to the tables/dbs to compare with the list of schemas present on the instance.

There's some lifting to do but not heavy.

share|improve this answer

I found the solution: I modify my Query :

mysql> SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME FROM INFORMATION_SCHEMA.SC
HEMATA where INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME NOT IN(SELECT db FROM INFOR
MATION_SCHEMA.PROCESSLIST where db is not null);

and the result is correct

share|improve this answer
1  
This does not find databases that are "not in use" in any meaningful sense. –  Michael - sqlbot Jul 19 '13 at 4:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.