Altering big table with gh-ost on Google Cloud

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 5.7.25-google-log.

Some table info:

  • currently holding 1 331 363 322 rows
  • size of the table 409GB
  • size of the index on the table 473GB
  • every day we crate around 2 000 000 new rows and update around 50 000 of 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 chunk-size, Threads_running, 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
Author: ffox003