0

I used MySQL WorkBench to get the most used tables in the MySQL database. I went to

Performance > Performance Reports > Database Schema Statistics > Schema Tables Statistics

and saw all tables that have been used and the number of reads and writes on them and etc. However, we have some old tables that we don't use anymore. We were using these tables a lot and that's why MySQL Workbench statistics didn't help me to identify those tables. Is there any way that I can see I/O reads and writes over time? Or something that can help me identify the old tables?

Improve this question

1 Answer 1

Reset to default
1

It is not possible.

As for missing tables that are used... Perhaps the statistics only record I/O. And those busy tables are small and continually cached in the buffer_pool, hence no I/O.

As for incorrectly saying that a table or index is not used.... Alas, MySQL provides no 100% reliable way to determine that a table or index is not needed. A simple example is a table (or an index) that is used by a corn job once a month. Even if you could gather statistics on that table for 3 weeks, you could miss it.

(Meanwhile, I don't think that Workbench has any source of information other than via MySQL.)

Improve this answer
7
  • I was thinking to take a snapshot from Schema Tables Statistics next month and compare it to the old one to identify the tables that are least used. This way I can help the customer to identify the unused table from there.
    – Fariman Kashani
    Aug 23, 2021 at 9:35
  • 1
    @FarimanKashani - Don't get yourself fired because of inadequate statistics. Why does he need to drop unused tables? Yes, they take disk space, but that is all. If disk space is the only problem, intensively search for code uses of the biggest table. Don't risk further action.
    – Rick James
    Aug 23, 2021 at 14:40
  • my client wants to move on to multitenancy with a database per tenant. That was the reason I thought it's a good idea to clean up the database from unused tables. Thanks for your advice. I'll let them be there and reduce the possible risk ;)
    – Fariman Kashani
    Aug 23, 2021 at 14:53
  • @FarimanKashani - One DB per tenant (on a single MySQL instance) won't necessarily help with speed or space. (There is not enough details for me to make that statement with certainty.)
    – Rick James
    Aug 23, 2021 at 15:31
  • 1
    What's the smallest RDS instance? If that is bigger than even your biggest customer, then it's not worth your effort to clean up the tables. I assume you will be increasing the fee to cover the specific RDS instance for each user. Will you be giving each customer access to RDS? (That will lead to lots of support calls to you from users who try to access RDS directly.)
    – Rick James
    Aug 23, 2021 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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