Altering big table with gh-ost on Google Cloud
We need to add some indexes and one additional column to a large MySQL table.
MySQL is Google Cloud SQL version
Some table info:
- currently holding
1 331 363 322rows
- size of the table
- size of the index on the table
- every day we crate around
2 000 000new rows and update around
50 000of old rows
- no foreign keys
We tried adding indexes directly to this table but we had to stop it because of this issue: https://www.percona.com/blog/2019/06/27/innodb-alter-table-add-index-and-insert-performance/
So, we decided to go with the
gh-ost. Currently, this tool is running for
240h31m30s and it is at
51.8% with the current progress of
2.6% per day and it slowing down every day.
To speed up the things we tried to play with
dml-batch-size but none of them helped to speed up the things. We only saw an increase in replication lag on replicas.
On Google Cloud SQL instance you can’t enable bin log on the replicas so we had to run it against the master (link, link).
Also, I have created an issue with all parameters that we use and some sample of logs on
gh-ost repo: https://github.com/github/gh-ost/issues/845
Is there anything else that we could try to tweak or should we try some other tool?
Go to Source