The --exact-rowcount option is buggy on large tables

[zhxnlai]

Context

During a schema change, gho-st copies every row from the original table to a ghost table. This results in a lock on the original table and a long running query similar to the following

mysql> show open tables where in_use>0;
+------------+----------------------+--------+-------------+
| Database   | Table                | In_use | Name_locked |
+------------+----------------------+--------+-------------+
| my_service | my_table             |      1 |           0 |
+------------+----------------------+--------+-------------+
1 row in set (0.00 sec)

mysql> show full processlist;
...
| 1594199 | my_service_ddl | 10.137.166.91:35626  | my_service         | Query       |    0 | Sending data                                                  | select  /* gh-ost `my_service`.`my_table` iteration:108 */
						`id`
					from
						`my_service`.`my_table`
					where ((`id` > _binary'1131891')) and ((`id` < _binary'310834793') or ((`id` = _binary'310834793')))
					order by
						`id` asc
					limit 1
					offset 9999 |

It appears that when --exact-rowcount is enabled, ghost grabs another lock on the original table and starts an addition query to calculate a more accurate progress ETA. By default, row counting happens in parallel to row copying.

mysql> show open tables where in_use>0;
+------------+----------------------+--------+-------------+
| Database   | Table                | In_use | Name_locked |
+------------+----------------------+--------+-------------+
| my_service | my_table             |      2 |           0 |
+------------+----------------------+--------+-------------+
1 row in set (0.00 sec)

mysql> show full processlist;
...
| 1594046 | my_service_ddl | 10.137.166.91:57666  | my_service         | Query       |   71 | Sending data                  | select /* gh-ost */ count(*) as rows from `my_service`.`my_table`                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
...

Problem

After all the rows are copied to the ghost table, gh-ost attempts to cut-over the original table.

Usually, the row counting query finishes before all the rows are copied to the ghost table. At the time of cut-over, because both queries have finished, there is no lock on the origin table. Hence the schema change is successful.

However, we repeatedly found that on a large table (> 400GB), row counting query takes longer than row copying. At the time of cut-over, the row counting query is still running and holding a lock on the original table.

This causes the schema change to fail with this error: "Error 1205: Lock wait timeout exceeded; try restarting transaction", which was described in #773

Proposals

If you run into this issue, try disabling --exact-rowcount.

Improvements that we could make

• Document this bug; add a warning.
• Teach gh-ost to terminate the row counting query before cutting over the original table.

[shlomi-noach]

the row counting query takes longer than row copying. At the time of cut-over, the row counting query is still running and holding a lock on the original table.

Very interesting -- thank you for this analysis! I've never seen this scenario happen, and once you point it out, it makes sense.

I'm not the owner of this repository at this time, but would you consider a pull request to update the docs? If not, someone else would hopefully do it, and hopefully also fix the code.

[timvaillancourt]

@zhxnlai / @shlomi-noach: I'm curious having support for cancelling the row-count query would solve this issue?

Some ideas that come to mind:

• Passing a cancellable context.Context to the row-count query
  • Cancel the row-count context before cut-over
• Running a KILL <query id> on the row-count query (feels more hacky)

For now I think documenting this limitation sounds good, this issue doesn't come up often

[shlomi-noach]

Cancel the row-count context before cut-over

This will not kill the query. Only way as far as I can tell is to KILL <query-id> which is indisputably hacky

[timvaillancourt]

Cancel the row-count context before cut-over

This will not kill the query. Only way as far as I can tell is to KILL <query-id> which is indisputably hacky

Ahh yes, the query won't be killed on the server 🤔👍. Sounds like documentation is the best approach