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 line 7009 for 241 seconds the semaphore:
Mutex at 0x7fd42b95f470, Mutex DICT_SYS created, 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, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.