1

I have a very messed up database with lot of tables that are really not used by the app so I'm tracking all the used tables with the general_log.

I'm making the request to the app and with tail -f /path/to/general_log.log following the connection id in the general log and getting all the tables requested, but this is going to be a very frustrating activity.

I want to know if there's a tool or an easy way to do this

  • It is possible to record all indexes used; will that help? Note: Such a list cannot say for sure that some other index won't be used tomorrow. – Rick James Jan 11 '17 at 3:19
  • 'SELECT * FROM information_schema.processlist WHERE INFO LIKE 'FROM%';' might also help to get some useful data – Shivam Kumar Jan 11 '17 at 4:36
  • I have three(3) questions: 1) How many apps ? 2) How many databases ? 3) What version of MySQL ? – RolandoMySQLDBA Jan 13 '17 at 19:32
  • @RolandoMySQLDBA So there's only one app with 4 servers balancing the traffic, only one database. MySQL version is 5.6. I.E. app: www.reporting.company.com database: reporting – tachomi Jan 13 '17 at 19:50
2
+100

There are two major things you can try

METHOD #1 : General Log as a Table

Convert the General Log into a Table. I already have 13 posts on how to create and rotate it.

You have the general log table open as a MyISAM table, you can query it like any other table.

In you case, you can do select against it by the event_time (if you query by the hour or by the day)

METHOD #2 : Compare Open Tables to INFORMATION_SCHEMA

STEP 01 :

If you run SHOW OPEN TABLES;, you will get a list of every table with open file handles.

It will look something like this:

+---------------------+----------------------------------------------------+--------+-------------+
| Database            | Table                                              | In_use | Name_locked |
+---------------------+----------------------------------------------------+--------+-------------+
.
.
.
+---------------------+----------------------------------------------------+--------+-------------+

Simply dump the output of this command to one text file (call it tables1.txt)

STEP 02 :

SELECT CONCAT(table_schema,'.',table_name) FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys');

Send the output of this query to a text file (call it tables2.txt)

STEP 03 : Filter out MySQL system tables

Remove 'information_schema','performance_schema','mysql','sys' from tables1.txt

STEP 04 : Compare the text files

Any table in table2.txt not in tables1.txt has not been used for reading or writing.

CAVEAT

You would have to leave MySQL running a long time to determine which table has open file handles. Please do not do the following:

  • Restart MySQL
  • FLUSH TABLES;

As long as you do not do these, the file handle will be open. If you do, you'll have to start over accessing the tables over time.

If you just need to find which tables have been written, you do not need to the INFORMATION_SCHEMA. I think INFORMATION_SCHEMA.TABLES.UPDATE_TIME is usable in MySQL 5.7. If you are using MySQL 5.6 or earlier, you can go to the OS.

EXAMPLE: Suppose your datadir is /var/lib/mysql and you want to see what tables have been written in the database reporting. You go to the OS and do this:

cd /var/lib/mysql/reporting
ls -lt ls -lt | grep "\.[iM][bY][DId]"

This will show you every table in the reportingdatabase in descending order by datetime. You can judge from there which tables have recent writes and which do not.

| improve this answer | |
1

You need profiling tool for MySQL like Jet Profiler. In it, you can track most frequently use tables, queries, and such. Without my tool (SQL Server Profiler), I would not be able to do my job!

| improve this answer | |
0

One potential solution is to update all tables with an update trigger that fires on a row when it is updated or inserted. Call this column UPDT_DT_TIME or something along those lines. After all the tables have been active for a sufficient amount of time to hit all the major application workflows (such as end-of-month or end-of-year processing for payment processing) you can go through and query the most recent UPDT_DT_TIME rows from each table and find what your application is using and not using.

| improve this answer | |

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.