0

I have a large MySQL database which has around 1,500 tables and the file of the database is around 30GB. I would like to suggest me some ways to monitor the health of my database in overall and how i can check the health status of mysql schema table.

How can I monitor the health of my database in overall? I use mysql workbence, but since i am new to this, i don't know what to check!

  • What do you consider "health"? Outage? Performance? Hackers? – Rick James Mar 21 '18 at 1:58
  • @leonidas Please view my profile, Network profile for contact info including my website where you will find 'Utility Scripts+' that has a FREE script to findfragtables.sql you may download and run. Post the results of page 1 and we will have some info to work with related to health of the tables listed on page 1. – Wilson Hauck Oct 17 '18 at 21:43
0

To monitor your database's health in overall you could simply use:

mysqlcheck --all-databases --auto-repair

or on weekly basis (if you prefer, due to optimize option resource usage)

mysqlcheck --all-databases --auto-repair --optimize 

To verify tables:

mysql
\u mydb
CHECK TABLE mytable FAST QUICK;

The first command will check the integrity for all tables of all databases, if there's any corruption your table will be repaired automatically. And the second will additionally optimize the table on disk.

Check the explanation about it here: What is the difference between optimize table and analyze table in mysql

  • Thank you for your reply! One last question: When i run these commands, the read/write/insert on the database should be stopped or i can run these commands on a working database? – Leonidas Mar 19 '18 at 14:48
  • This process increases I/O operations, would be safe to run out of working time for the first time and watch resource usage using sar (sysstat) in another session. Or schedule a crontab job to run. Then depending on resource usage you could run on a work database. – Rogerlr Mar 20 '18 at 9:24
  • 1
    Hey there, the problem is that I can't stop the database at all, because my working time is 24/7!! Any idea? – Leonidas Mar 20 '18 at 12:17
  • 1
    Optimize is quite invasive and generally unnecessary, especially for InnoDB. – Rick James Mar 21 '18 at 1:57
  • For your 24x7 scenario avoid to use the optimize and cancel the check operation if it's necessary. You can check tables separatelly and after be sure that your db performance wasn't affected you can check per databases. I'll update the answer with table options. – Rogerlr Mar 21 '18 at 13:14

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.