github / github/gh-ost

Change lock_wait_timeout’s value to reduce risk at --cut-over=default

Open
#1,255 0 comments 2 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
13.6k
Forks
1.4k
Avg merge
2h 31m
Merged PRs (30d)
4

Description

Hi,
I think it would be better to use AtomicCutOverMagicLock() lock_wait_timeout value to -cut-over-lock-timeout-seconds and AtomicCutoverRename() lock_wait_timeout value to -cut-over-lock-timeout-seconds * 2.

AtomicCutOverMagicLock() is the first to acquire a lock on the original table. If LOCK TABLE Statement will be waiting on MDL here, other user sessions will be affected. The default is this.migrationContext.CutOverLockTimeoutSeconds * 2 = 6 sec, so It would be good to be able to change to a smaller value.

```
tableLockTimeoutSeconds := this.migrationContext.CutOverLockTimeoutSeconds * 2
```
https://github.com/github/gh-ost/blob/master/go/logic/applier.go#L968

```
this.migrationContext.Log.Infof("Setting RENAME timeout as %d seconds", this.migrationContext.CutOverLockTimeoutSeconds)
query := fmt.Sprintf(`set session lock_wait_timeout:=%d`, this.migrationContext.CutOverLockTimeoutSeconds)
```
https://github.com/github/gh-ost/blob/master/go/logic/applier.go#L1057

Also, there are cases where gh-ost does not finish when running with -cut-over-lock-timeout-seconds=1, because RENAME TABLE Statement times out before UNLOCK TABLES Statement is executed.

```
2023-02-19 23:27:41 INFO Setting RENAME timeout as 1 seconds
2023-02-19 23:27:41 INFO Session renaming tables is 358623
2023-02-19 23:27:41 INFO Issuing and expecting this to block: rename /* gh-ost */ table `db1`.`t0` to `db1`.`_t0_del`, `db1`.`_t0_gho` to `db1`.`t0`
2023-02-19 23:27:42 ERROR Error 1205: Lock wait timeout exceeded; try restarting transaction
2023-02-19 23:27:42 INFO Will now proceed to drop magic table and unlock tables
2023-02-19 23:27:42 INFO Dropping magic cut-over table
2023-02-19 23:27:42 INFO Releasing lock from `db1`.`t0`, `db1`.`_t0_del`
2023-02-19 23:27:42 INFO Tables unlocked
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.