Re: Instant DDL on ADD Column puts queries in opening tables state
Posted by: Sunny Nagra
Date: February 02, 2022 10:20AM

The table_open_cache is already at 4096 and we have 1 schema that contains 519 tables.

Does this need to be increased even more?

Show engine InnoDB status shows lots of semaphore waits:

2022-02-01T09:50:03.982054Z 0 [Warning] [MY-012985] [InnoDB] A long semaphore wait:
--Thread 140302610929408 has waited at ha_innodb.cc line 7009 for 241 seconds the semaphore:
Mutex at 0x7fd42b95f470, Mutex DICT_SYS created dict0dict.cc:1024, lock var 1

There are lots of these whilst the ALTER TABLE is running to add 2 columns using ALGORITHM=INSTANT on a 68G table, that contains 20M rows.

Options: ReplyQuote




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.