1

I did INSERT INTO SELECT of a huge table (1TB). When the process is finished, MySQL/InnoDB still does post-processing (creating updating secondary indices, etc.) for hours.

Since these are internal processes, they do not appear in SHOW PROCESSLIST or SHOW ENGINE INNODB STATUS \G.

I can only check the process by I/O activities.

Is there any way to properly monitor the ongoing processes?

7
  • Have you tried looking at the performance schema?
    – Vérace
    Oct 14 at 14:54
  • @Vérace performance schema is disabled by default in MariaDB (not sure about MySQL). Which table does store the pertinent information?
    – Googlebot
    Oct 14 at 15:27
  • Disabling the PS is a bit like removing your car's seat belts, the back seats, the passenger seat... to achieve a slight performance gain. You cannot manage what you cannot measure. I would look at (from here) - here and here...
    – Vérace
    Oct 14 at 15:58
  • Can you show us the PROCESSLIST while the INSERT is running, then again while it is doing the "post-processing"? INSERT cannot create an index; it can modify existing indexes. Please provide more details on "creating secondary indices".
    – Rick James
    Oct 14 at 17:27
  • 2
    It's probably merging the change buffer into the index pages. That manual page shows some ways to monitor the change buffer size and its progress. The deferred I/O caused by the change buffer merges is normal. You could disable your change buffer before you do the big INSERT, but then your INSERT would take hours. Oct 14 at 19:01
1

It's probably merging the change buffer into the index pages. That manual page shows some ways to monitor the change buffer size and its progress.

The deferred I/O caused by the change buffer merges is normal.

You could disable your change buffer (see the linked manual page) before you do the big INSERT, but then your INSERT would take hours because all index writes would be write-through.

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.