Re: OPTIMIZE TABLE VERY SLOW In one RDS instance
Posted by: Stanley Wong
Date: September 20, 2021 01:51PM

I could not try this at the production instance, I spin up another instance using the production snapshot and same instance spec.

This table 'abc` is taking 45 minutes in the production server. But it is only taking 15 mins in the new instance that I have just spin up .

optimize table `abc`;
/* Affected rows: 0 Found rows: 2 Warnings: 0 Duration for 1 query: 00:13:17 */

Msg text : Table does not support optimize, doing recreate + analyze instead
Status : OK

I notice there are some warning message in the production error logs during the optimizing . Not sure it is related .

2021-09-18T17:33:37.742057Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 5193ms. The settings might not be optimal. (flushed=108 and evicted=0, during the time.)

2021-09-18T17:39:01.197915Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11421ms. The settings might not be optimal. (flushed=25 and evicted=0, during the time.)

2021-09-18T17:49:17.111991Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 6918ms. The settings might not be optimal. (flushed=119 and evicted=0, during the time.)

Options: ReplyQuote


Subject
Written By
Posted
Re: OPTIMIZE TABLE VERY SLOW In one RDS instance
September 20, 2021 01:51PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.