Translated from 日本語 • View original Hello. This is Komiya. One day, while I was revising the recipe for chef, a colleague said something like this.
"As soon as you click on the link of phpmyadmin's information_schema
The site has become heavier and the disk space has increased by tens of GB, and now it has gone down and calmed down.
Do you know what caused it?"
Clicking information_schema in phpmyadmin is too dangerous - K52. It came with a NIKKI ver3.0.
I didn't understand it well, so I muttered about the phenomenon, and the kind MySQL ACE yoku0825 told me that innodb_stats_on_metadata=1 might be ambiguous. When I googled it, I came across a page called innodb_stats_on_metadata - TAKUMI SAKAMOTO'S BLOG and thought I knew what had happened.
Prediction of what happened: ・The table update status was in a state where the condition for statistical update was reached ・Clicking on the information_schema link triggered a statistical update equivalent to show table status ・Processing equivalent to ANALYZE_TABLE was run to update the statistical information. ・The disk overflowed because a copy of the table was made in tmpdir, and the utilization rate decreased because it was replaced and the process was completed. ・The CPU load is thought to be due to the TABLE maintenance process equivalent to ALTER and the disk I/O associated with it.
I found a description of the conditions for innodb statistical information updates on the Han blog. Thank you as always. I was happy. And this alone is not enough, so I'll add a little.
As for which version to turn on or off, if you look at the official manual, it seems to be OFF from 5.6.6 and ON before that.
Next, if you set innodb-stats-on-metadata to 0, you will need to perform maintenance yourself, but what should I do?
If you just want to add basic maintenance and update statistics, you should use ANALYZE TABLE, or if you want to defragment, you should use ALTER TABLE. InnoDB's OPTIMIZE only executes ALTER internally, so if you don't want to be locked by READ, you can use ALTER TABLE. It is recommended to estimate the time it will take in a test environment where the data (and specifications) are as similar to the actual test as possible. I have attached a link to the manual in the table. The left is a 5.1 Japanese link and the right is a 5.6 English link.
・ Beware of the reference site innodb_stats_on_metadata - TAKUMI SAKAMOTO'S BLOG Han's Computer Road: The Correct Way to Interact with InnoDB Tables for Adults. Han's computer path: Let's master the ALTER TABLE. Introduction to MySQL Trouble Analysis Table Maintenance Statements MySQL :: MySQL 5.5 Reference Manual :: 13.7.2 Table Maintenance Statements Non-Service Schema Change with Percona Toolkit pt-online-schema-change | Safe Use pt-online-schema-change - Around the World
From 5.6, some ALTER TABLEs can be played online, but it seems better to keep the following as follows. Daily memorandum: I was trying the online ALTER TABLE for MySQL 5.6 and encountered innodb_online_alter_log_max_size MySQL was killed by OOM Killer in the online ALTER TABLE | GMO Media Engineer Blog
There are many people who use MySQL and are familiar with it, so I think it's good to have a lot of information even if something happens. Thank you for reading.