Speed up copying
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
We are trying to add some additional indexes and one column to a table that has around `1 320 000 000` rows and it is growing by about`2 000 000` rows per day.
When we started `gh-ost` it did around 15% on the first day, and now it is doing only 5% or less per day.
Currently, it takes around 25min to do `0.1%`.
We have tried increasing `chunk-size` from `1000` to `10000` and increasing `max-lag-millis` but that did not speed things much, it just added more lag between master and replica.
We are doing this on the Google Cloud SQL instances, and `gh-ost` is running on a separate VM.
Load on the master and replica never goes over 20%.
Initial parameters:
```
gh-ost \
--verbose \
--host=10.x.x.x \
--throttle-control-replicas="10.x.x.x" \
--max-lag-millis=1500 \
--user= \
--password= \
--database= \
--table= \
--alter="add key idx_name1(column1, column2), add key idx_name2(column3, column4), add column column5 json" \
--chunk-size=1000 \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--critical-load-interval-millis=5000 \
--default-retries=120 \
--allow-on-master \
--cut-over=default \
--exact-rowcount \
--concurrent-rowcount \
--panic-flag-file=/tmp/ghost.TABLE.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.TABLE.postpone.flag \
--throttle-flag-file=/tmp/ghost.TABLE.throttle.flag \
--serve-socket-file=/tmp/ghost.TABLE.sock \
```
Sample from `*_ghc` table:
```
2020-05-11 08:16:24 copy iteration 301004 at 1589184984 Copy: 297093000/1317500388 22.5%; Applied: 2073248; Backlog: 1/1000; Time: 50h28m30s(total), 50h28m30s(copy); streamer: mysql-bin.057739:64505760; Lag: 0.01s, State: migrating; ETA: 173h21m48s
2020-05-11 08:15:54 copy iteration 301001 at 1589184954 Copy: 297063000/1317500364 22.5%; Applied: 2073222; Backlog: 4/1000; Time: 50h28m0s(total), 50h28m0s(copy); streamer: mysql-bin.057739:57073345; Lag: 0.01s, State: migrating; ETA: 173h21m26s
2020-05-11 08:15:24 copy iteration 300999 at 1589184924 Copy: 297043000/1317500348 22.5%; Applied: 2073205; Backlog: 4/1000; Time: 50h27m30s(total), 50h27m30s(copy); streamer: mysql-bin.057739:52083518; Lag: 0.01s, State: migrating; ETA: 173h20m37s
2020-05-11 08:14:54 copy iteration 300996 at 1589184894 Copy: 297013000/1317500331 22.5%; Applied: 2073187; Backlog: 4/1000; Time: 50h27m0s(total), 50h27m0s(copy); streamer: mysql-bin.057739:44657799; Lag: 0.01s, State: migrating; ETA: 173h20m16s
2020-05-11 08:14:24 copy iteration 300994 at 1589184864 Copy: 296993000/1317500317 22.5%; Applied: 2073171; Backlog: 0/1000; Time: 50h26m30s(total), 50h26m30s(copy); streamer: mysql-bin.057739:39654212; Lag: 0.01s, State: migrating; ETA: 173h19m27s
2020-05-11 08:13:54 copy iteration 300991 at 1589184834 Copy: 296963000/1317500295 22.5%; Applied: 2073146; Backlog: 9/1000; Time: 50h26m0s(total), 50h26m0s(copy); streamer: mysql-bin.057739:32228284; Lag: 0.01s, State: migrating; ETA: 173h19m5s
2020-05-11 08:13:24 copy iteration 300989 at 1589184804 Copy: 296943000/1317500282 22.5%; Applied: 2073130; Backlog: 0/1000; Time: 50h25m30s(total), 50h25m30s(copy); streamer: mysql-bin.057739:27240594; Lag: 0.01s, State: migrating; ETA: 173h18m16s
2020-05-11 08:12:54 copy iteration 300986 at 1589184774 Copy: 296913000/1317500261 22.5%; Applied: 2073106; Backlog: 7/1000; Time: 50h25m0s(total), 50h25m0s(copy); streamer: mysql-bin.057739:19838856; Lag: 0.01s, State: migrating; ETA: 173h17m54s
2020-05-11 08:12:24 copy iteration 300984 at 1589184744 Copy: 296893000/1317500247 22.5%; Applied: 2073090; Backlog: 2/1000; Time: 50h24m30s(total), 50h24m30s(copy); streamer: mysql-bin.057739:14852269; Lag: 0.01s, State: migrating; ETA: 173h17m6s
2020-05-11 08:11:54 copy iteration 300981 at 1589184714 Copy: 296863000/1317500238 22.5%; Applied: 2073080; Backlog: 1/1000; Time: 50h24m0s(total), 50h24m0s(copy); streamer: mysql-bin.057739:7462357; Lag: 0.01s, State: migrating; ETA: 173h16m44s
2020-05-11 08:11:24 copy iteration 300979 at 1589184684 Copy: 296843000/1317500227 22.5%; Applied: 2073066; Backlog: 2/1000; Time: 50h23m30s(total), 50h23m30s(copy); streamer: mysql-bin.057739:2493652; Lag: 0.01s, State: migrating; ETA: 173h15m55s
2020-05-11 08:10:54 copy iteration 300976 at 1589184654 Copy: 296813000/1317500210 22.5%; Applied: 2073046; Backlog: 9/1000; Time: 50h23m0s(total), 50h23m0s(copy); streamer: mysql-bin.057738:101757243; Lag: 0.01s, State: migrating; ETA: 173h15m33s
2020-05-11 08:10:24 copy iteration 300974 at 1589184624 Copy: 296793000/1317500201 22.5%; Applied: 2073030; Backlog: 1/1000; Time: 50h22m30s(total), 50h22m30s(copy); streamer: mysql-bin.057738:96765364; Lag: 0.01s, State: migrating; ETA: 173h14m44s
2020-05-11 08:09:54 copy iteration 300971 at 1589184594 Copy: 296763000/1317500173 22.5%; Applied: 2072998; Backlog: 7/1000; Time: 50h22m0s(total), 50h22m0s(copy); streamer: mysql-bin.057738:89342101; Lag: 0.01s, State: migrating; ETA: 173h14m22s
2020-05-11 08:09:24 copy iteration 300969 at 1589184564 Copy: 296743000/1317500161 22.5%; Applied: 2072985; Backlog: 7/1000; Time: 50h21m30s(total), 50h21m30s(copy); streamer: mysql-bin.057738:84374429; Lag: 0.01s, State: migrating; ETA: 173h13m33s
2020-05-11 08:08:54 copy iteration 300967 at 1589184534 Copy: 296723000/1317500144 22.5%; Applied: 2072967; Backlog: 3/1000; Time: 50h21m0s(total), 50h21m0s(copy); streamer: mysql-bin.057738:79402453; Lag: 0.01s, State: migrating; ETA: 173h12m44s
2020-05-11 08:08:24 copy iteration 300965 at 1589184504 Copy: 296703000/1317500132 22.5%; Applied: 2072949; Backlog: 0/1000; Time: 50h20m30s(total), 50h20m30s(copy); streamer: mysql-bin.057738:74427463; Lag: 0.01s, State: migrating; ETA: 173h11m56s
2020-05-11 08:07:54 copy iteration 300962 at 1589184474 Copy: 296673000/1317500109 22.5%; Applied: 2072921; Backlog: 13/1000; Time: 50h20m0s(total), 50h20m0s(copy); streamer: mysql-bin.057738:67030896; Lag: 0.01s, State: migrating; ETA: 173h11m34s
2020-05-11 08:07:24 copy iteration 300960 at 1589184444 Copy: 296653000/1317500099 22.5%; Applied: 2072902; Backlog: 3/1000; Time: 50h19m30s(total), 50h19m30s(copy); streamer: mysql-bin.057738:62036278; Lag: 0.01s, State: migrating; ETA: 173h10m45s
2020-05-11 08:06:54 copy iteration 300958 at 1589184414 Copy: 296633000/1317500083 22.5%; Applied: 2072884; Backlog: 1/1000; Time: 50h19m0s(total), 50h19m0s(copy); streamer: mysql-bin.057738:57057288; Lag: 0.01s, State: migrating; ETA: 173h9m56s
2020-05-11 08:06:24 copy iteration 300955 at 1589184384 Copy: 296603000/1317500053 22.5%; Applied: 2072850; Backlog: 13/1000; Time: 50h18m30s(total), 50h18m30s(copy); streamer: mysql-bin.057738:49657042; Lag: 0.01s, State: migrating; ETA: 173h9m34s
2020-05-11 08:05:54 copy iteration 300953 at 1589184354 Copy: 296583000/1317500041 22.5%; Applied: 2072832; Backlog: 6/1000; Time: 50h18m0s(total), 50h18m0s(copy); streamer: mysql-bin.057738:44672428; Lag: 0.01s, State: migrating; ETA: 173h8m45s
2020-05-11 08:05:24 copy iteration 300951 at 1589184324 Copy: 296563000/1317500022 22.5%; Applied: 2072812; Backlog: 0/1000; Time: 50h17m30s(total), 50h17m30s(copy); streamer: mysql-bin.057738:39689163; Lag: 0.01s, State: migrating; ETA: 173h7m56s
2020-05-11 08:04:54 copy iteration 300948 at 1589184294 Copy: 296533000/1317500005 22.5%; Applied: 2072790; Backlog: 6/1000; Time: 50h17m0s(total), 50h17m0s(copy); streamer: mysql-bin.057738:32273577; Lag: 0.01s, State: migrating; ETA: 173h7m34s
2020-05-11 08:04:24 copy iteration 300946 at 1589184264 Copy: 296513000/1317499994 22.5%; Applied: 2072779; Backlog: 0/1000; Time: 50h16m30s(total), 50h16m30s(copy); streamer: mysql-bin.057738:27286260; Lag: 0.01s, State: migrating; ETA: 173h6m45s
2020-05-11 08:03:54 copy iteration 300943 at 1589184234 Copy: 296483000/1317499978 22.5%; Applied: 2072761; Backlog: 7/1000; Time: 50h16m0s(total), 50h16m0s(copy); streamer: mysql-bin.057738:19905355; Lag: 0.01s, State: migrating; ETA: 173h6m23s
2020-05-11 08:03:24 copy iteration 300941 at 1589184204 Copy: 296463000/1317499959 22.5%; Applied: 2072740; Backlog: 5/1000; Time: 50h15m30s(total), 50h15m30s(copy); streamer: mysql-bin.057738:14922287; Lag: 0.01s, State: migrating; ETA: 173h5m34s
2020-05-11 08:02:54 copy iteration 300939 at 1589184174 Copy: 296443000/1317499947 22.5%; Applied: 2072728; Backlog: 3/1000; Time: 50h15m0s(total), 50h15m0s(copy); streamer: mysql-bin.057738:9952530; Lag: 0.01s, State: migrating; ETA: 173h4m45s
2020-05-11 08:02:24 copy iteration 300937 at 1589184144 Copy: 296423000/1317499925 22.5%; Applied: 2072704; Backlog: 0/1000; Time: 50h14m30s(total), 50h14m30s(copy); streamer: mysql-bin.057738:4963593; Lag: 0.01s, State: migrating; ETA: 173h3m55s
2020-05-11 08:01:54 copy iteration 300934 at 1589184114 Copy: 296393000/1317499899 22.5%; Applied: 2072677; Backlog: 3/1000; Time: 50h14m0s(total), 50h14m0s(copy); streamer: mysql-bin.057737:103454633; Lag: 0.01s, State: migrating; ETA: 173h3m33s
2020-05-11 08:01:24 copy iteration 300932 at 1589184084 Copy: 296373000/1317499882 22.5%; Applied: 2072655; Backlog: 5/1000; Time: 50h13m30s(total), 50h13m30s(copy); streamer: mysql-bin.057737:98470633; Lag: 0.01s, State: migrating; ETA: 173h2m44s
2020-05-11 08:00:54 copy iteration 300929 at 1589184054 Copy: 296343000/1317499858 22.5%; Applied: 2072627; Backlog: 8/1000; Time: 50h13m0s(total), 50h13m0s(copy); streamer: mysql-bin.057737:91036864; Lag: 0.01s, State: migrating; ETA: 173h2m22s
2020-05-11 08:00:24 copy iteration 300927 at 1589184024 Copy: 296323000/1317499841 22.5%; Applied: 2072609; Backlog: 4/1000; Time: 50h12m30s(total), 50h12m30s(copy); streamer: mysql-bin.057737:86027346; Lag: 0.01s, State: migrating; ETA: 173h1m33s
2020-05-11 07:59:54 copy iteration 300925 at 1589183994 Copy: 296303000/1317499826 22.5%; Applied: 2072591; Backlog: 0/1000; Time: 50h12m0s(total), 50h12m0s(copy); streamer: mysql-bin.057737:81025822; Lag: 0.01s, State: migrating; ETA: 173h0m44s
2020-05-11 07:59:24 copy iteration 300920 at 1589183964 Copy: 296253000/1317499808 22.5%; Applied: 2072560; Backlog: 8/1000; Time: 50h11m30s(total), 50h11m30s(copy); streamer: mysql-bin.057737:68791026; Lag: 0.01s, State: migrating; ETA: 173h1m16s
2020-05-11 07:58:54 copy iteration 300916 at 1589183934 Copy: 296213000/1317499786 22.5%; Applied: 2072522; Backlog: 2/1000; Time: 50h11m0s(total), 50h11m0s(copy); streamer: mysql-bin.057737:58940420; Lag: 0.01s, State: migrating; ETA: 173h1m21s
2020-05-11 07:58:24 copy iteration 300911 at 1589183904 Copy: 296163000/1317499761 22.5%; Applied: 2072486; Backlog: 3/1000; Time: 50h10m30s(total), 50h10m30s(copy); streamer: mysql-bin.057737:46665589; Lag: 0.01s, State: migrating; ETA: 173h1m53s
2020-05-11 07:57:54 copy iteration 300907 at 1589183874 Copy: 296123000/1317499748 22.5%; Applied: 2072468; Backlog: 4/1000; Time: 50h10m0s(total), 50h10m0s(copy); streamer: mysql-bin.057737:36861086; Lag: 0.11s, State: migrating; ETA: 173h1m59s
2020-05-11 07:57:24 copy iteration 300903 at 1589183844 Copy: 296083000/1317499724 22.5%; Applied: 2072441; Backlog: 5/1000; Time: 50h9m30s(total), 50h9m30s(copy); streamer: mysql-bin.057737:27048024; Lag: 0.01s, State: migrating; ETA: 173h2m4s
2020-05-11 07:56:54 copy iteration 300899 at 1589183814 Copy: 296043000/1317499709 22.5%; Applied: 2072420; Backlog: 0/1000; Time: 50h9m0s(total), 50h9m0s(copy); streamer: mysql-bin.057737:17206165; Lag: 0.01s, State: migrating; ETA: 173h2m9s
2020-05-11 07:56:24 copy iteration 300894 at 1589183784 Copy: 295993000/1317499688 22.5%; Applied: 2072397; Backlog: 3/1000; Time: 50h8m30s(total), 50h8m30s(copy); streamer: mysql-bin.057737:4944793; Lag: 0.01s, State: migrating; ETA: 173h2m41s
2020-05-11 07:55:54 copy iteration 300890 at 1589183754 Copy: 295953000/1317499669 22.5%; Applied: 2072372; Backlog: 4/1000; Time: 50h8m0s(total), 50h8m0s(copy); streamer: mysql-bin.057736:101503766; Lag: 0.01s, State: migrating; ETA: 173h2m46s
2020-05-11 07:55:24 copy iteration 300886 at 1589183724 Copy: 295913000/1317499654 22.5%; Applied: 2072346; Backlog: 2/1000; Time: 50h7m30s(total), 50h7m30s(copy); streamer: mysql-bin.057736:91758716; Lag: 0.01s, State: migrating; ETA: 173h2m51s
2020-05-11 07:54:54 copy iteration 300883 at 1589183694 Copy: 295883000/1317499635 22.5%; Applied: 2072320; Backlog: 3/1000; Time: 50h7m0s(total), 50h7m0s(copy); streamer: mysql-bin.057736:84352021; Lag: 0.01s, State: migrating; ETA: 173h2m29s
2020-05-11 07:54:24 copy iteration 300881 at 1589183664 Copy: 295863000/1317499623 22.5%; Applied: 2072300; Backlog: 0/1000; Time: 50h6m30s(total), 50h6m30s(copy); streamer: mysql-bin.057736:79342339; Lag: 0.01s, State: migrating; ETA: 173h1m39s
2020-05-11 07:53:54 copy iteration 300878 at 1589183634 Copy: 295833000/1317499611 22.5%; Applied: 2072282; Backlog: 3/1000; Time: 50h6m0s(total), 50h6m0s(copy); streamer: mysql-bin.057736:71913613; Lag: 0.01s, State: migrating; ETA: 173h1m17s
2020-05-11 07:53:24 copy iteration 300875 at 1589183604 Copy: 295803000/1317499593 22.5%; Applied: 2072259; Backlog: 5/1000; Time: 50h5m30s(total), 50h5m30s(copy); streamer: mysql-bin.057736:64479994; Lag: 0.01s, State: migrating; ETA: 173h0m55s
2020-05-11 07:52:54 copy iteration 300873 at 1589183574 Copy: 295783000/1317499583 22.5%; Applied: 2072248; Backlog: 4/1000; Time: 50h5m0s(total), 50h5m0s(copy); streamer: mysql-bin.057736:59474172; Lag: 0.01s, State: migrating; ETA: 173h0m6s
```
How can we speed things up?
Contributor guide
Assessment
This issue has not been assessed yet.