gh-ost not keeping up with binlog. Tuning tips?

[jmilliron]

jmilliron@dmz:~$ echo status | nc -U /tmp/gh-ost.sock
# Migrating `live_production`.`xxx`; Ghost table is `live_production`.`_xxx_gho`
# Migrating ip-yyy:3306; inspecting ip-yyy:3306; executing on dmz
# Migration started at Mon Jun 03 21:18:08 +0000 2019
# chunk-size: 5000; max-lag-millis: 1500ms; dml-batch-size: 100; max-load: Threads_running=400; critical-load: Threads_running=600; nice-ratio: 0.000000
# throttle-additional-flag-file: /home/jmilliron/gh-ost-throttle_ghost
# Serving on unix socket: /tmp/gh-ost.sock
Copy: 546980000/50134741422 1.1%; Applied: 127818460; Backlog: 1000/1000; Time: 44h21m58s(total), 44h21m57s(copy); streamer: mysql-bin-changelog.312631:29804582; State: migrating; ETA: 4022h6m43s
jmilliron@dmz:~$

I've got a pretty big table on AWS Aurora MySQL that I'm trying to alter. Writer instance is a db.r5.12xlarge. Seems fairly idle to me. CPU is low and the schema change is causing zero impact to query or DML latency. No increase in threads running.

However, during the day, it's falling behind on the binlog/changes to the table.

I've already got DML batch size turned up all the way and nice turned down all the way. There are no replicas via binlog replication, so lag doesn't matter. Getting nowhere near threads running. It's like 30 max.

Any tips for helping gh-ost keep up? Looks like I'm using version 1.0.47 from a Ubuntu package.

[shlomi-noach]

Are you perhaps running gh-ost from a host with high latency to the MySQL server? That would have a massive impact.

[jmilliron]

Interesting. Thanks. Didn't think of that.

Host is in the same VPC but not the same availability zone. Just did some rudimentary testing and it looks like simple queries from a host in the same AZ are a couple of ms faster.

[timvaillancourt]

Closing as answered. Feel free to open a new issue with more testing/details

[jmilliron]

Circling back on this. The problem was that we also had binlog replication setup to our Data Warehouse and that seemed to be causing some contention. Saw massive gh-ost performance improvement when we delayed binlog replication a bit by using these two settings:

aurora_binlog_replication_max_yield_seconds=300
aurora_binlog_read_buffer_size=136314880

Default is:

aurora_binlog_replication_max_yield_seconds=0
aurora_binlog_read_buffer_size=5242880

You do have to revert the settings before you try and cut over the migration though. Otherwise gh-ost will fail to sync the two tables.

[timvaillancourt]

@jmilliron thanks for the feedback!

I've actually never used Aurora so these settings are news to me. This lead me to wonder if these settings should be added to the recommendations for Aurora 🤔. Would they be useful for all use cases?

And last random thought, you mentioned reverting these tunings; could gh-ost be automating the revert of the settings?

[jmilliron]

Definitely think it could be noted. I can take a stab at that.

For additional automation, the script would need AWS API access to make the changes to the parameter group since we're dealing with RDS/Aurora.

[timvaillancourt]

Definitely think it could be noted. I can take a stab at that.

Great thanks @jmilliron!

For additional automation, the script would need AWS API access to make the changes to the parameter group since we're dealing with RDS/Aurora.

Hmm, that sounds ugly but possible. I was hoping they'd let you run SET 😆