Possible problems with gh-ost, pool-of-threads, and hot tables #482
Comments
|
👋 I'm sorry to hear about your outage. A few things strike odd here, and some logging would be helpful in analyzing the case. The first concerning issue is that the lock timeout didn't work. This is a protection mechanism designed to solve any and all issues that may arise from locking (and you certainly seem to have an issue with locking). What I would expect to see is recurring timeouts, leading to an eventual failure of the migration (an acceptable behavior). Logging would help us analyze what happens with regard this lock.
You strike a delicate point here.
It does require similar locking. The steps are:
The similarity is that the original table is locked, with incoming DML queries waiting on it, then we |
|
Thanks @shlomi-noach pasted at the bottom are some manually redacted logs.
Oh no I didn't mean that at all. I meant gh-ost didn't timeout retrieving the lock. The relevant snippet from the logs is: With Lines 813 to 817 in b336409 That tells me that we not only executed the locking code, but it returned! Meaning that there shouldn't even be a long running transaction prior to the lock query that gh-ost is waiting on for the table we're going to rename. Please correct me on this one if I'm off. But if we had a long running transaction using this table, our gh-ost query that issued the Therefore since the query completed and returned without error, we know that nothing prior is affecting us (C1..C9 in #82) So what is curious is that the followup atomic renaming of the tables just seemed to get lost in the ether. And that should be extremely quick after we have a hold of the locks. What is curious is that this statement gets printed: Lines 501 to 502 in b336409 and from reading the code, it sounds like gh-ost waits until that change log state makes it to the binary log. And that happens prior to the rename. So if my threadpool starved that gh-ost connection from actually executing the change log update, I imagine that is what led to C11..C19+ connections waiting until the original table lock timed out or the connection ended when I issued Ctrl-C. (Causing the actual outage we experienced). If I'm off on any of that reasoning, please feel free to correct me! With that in mind, I'm heavily considering enabling and using the extra port that percona offers https://www.percona.com/doc/percona-server/LATEST/performance/threadpool.html#extra_port and having gh-ost connect and migrate from that. I've confirmed with them that the connections on the extra port use the gh-ost run output |
|
We're going to re-attempt this same migration this week (possibly next) against the extra port. I'll report my experience here afterwards. By the way, this issue doesn't need to remain open. It's not a bug or anything on gh-ost's side that I can see. I mainly opened it to share our experiences in case someone else in my scenario runs into this. |
|
Actually, looks like I am going to need help after all. Doing some tests, it doesn't seem like I can instruct gh-ost to migrate mysql on a different port. $ mysql -e "show global variables where variable_name in ('port', 'extra_port')"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| extra_port | 23306 |
| port | 3306 |
+---------------+-------+
/usr/local/bin/gh-ost \
--initially-drop-ghost-table \
--initially-drop-old-table \
--assume-rbr \
--port="23306" \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--max-lag-millis=1500 \
--user="ghost" \
--ask-pass \
--database="mydb" \
--table="mytable" \
--verbose \
--alter="ADD mynewcol decimal(11,2) DEFAULT 0.0 NOT NULL" \
--exact-rowcount \
--concurrent-rowcount \
--default-retries=120 \
--panic-flag-file=/tmp/ghost.panic.flag \
--postpone-cut-over-flag-file=/tmp/ghost.postpone.flag \
[--execute]
...
2017-09-05 16:48:53 FATAL Unexpected database port reported: 3306This looks like it'll require a PR. https://github.com/github/gh-ost/search?utf8=%E2%9C%93&q=Unexpected+database+port+reported&type= First, are you all ok with a PR allowing me to override the port and check the extra port? Second, do you have an suggestions/guidance on the design for that before I submit one? |
2 tasks
|
Sorry, just noting I'll be out for a few days and will look into this early next week. |
|
No worries at all man. Take care. |
|
I just wanted to give an update here. We have now had 3 successful migrations run with a build from my branch against the extra port. Including the original table that we had trouble with. Now granted, they have all run in off-peak traffic hours, but we're rebuilding our confidence that gh-ost won't take us down again. I'm sure we'll run another gh-ost migration during prime time hours soon. |
|
I didn't expect "soon" to be "today", but it was. And it was a smashing success. We migrated two tables 4M and 11.5M rows. The second is arguably hotter than the one that gave us issues two weeks ago. Both migrated without a hitch using the extra port and my branch's build. @shlomi-noach man, I gotta say. Thank you (and all the other contributors!!) for your hard work on gh-ost. It really is unlocking several hard-to-budge doors on our side. |
|
OK good, today is the day I got to look at this, and I'm glad this works for you! I reviewed and I'd like to suggest a slightly simplified approach that doesn't assume percona/mariadb. |
|
I believe this can be closed. Gonna close to help clean up the open issues! |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I could use some help with a downtime we encountered using gh-ost today.
First we had no problem with the replica only tests and testing minor tables but we just tried it out on what we thought could be a meaty table. Sitting at 500k rows, it is much closer to one of our smaller tables than our largest. And it's not actively/heavily written to, but it very read heavily. And I think that's what got us into trouble.
gh-ost was able to clone the table and waited for me to drop the postpone flag. Once I did the writer-server's MySQL became unresponsive. Even when I logged into it and tried to connect directly my MySQL connection would hang. Now this is a very beefy machine, and the load average didn't budge. So it wasn't a matter of the hardware stalling, but definitely the way MySQL was processing.
We are using Percona MySQL 5.7 with the
thread_handling=pool-of-threadsturned on (a result of our upgrade from 5.6), and I think what may have happened according to the description outlined in #82 is that we must have starved the thread pool once gh-ost tried to lock this hot table for renaming.I don't think the statements prior to gh-ost applying for the lock stalled us, but perhaps all of the queries immediately after gh-ost applied for the lock did. From the outside they began collecting up waiting for gh-ost to execute the atomic rename. That we can see happening by a pile up of our unicorn processes. gh-ost didnt seem to timeout applying for the lock ("INFO Tables locked") but it never renamed the table. It seemed to restart the sync process once more (restarted the "# Migrating ..." block) and that's when I killed it manually.
But I wonder if the prioritization of the actual rename got lost within MySQL and most queries were waiting for that to finish so gh-ost would give up the lock. That is my initial diagnosis anyway.
I need to do some digging to try and figure out if this is perhaps a configuration tuning issue. Perhaps we need to reconsider moving back from pool-of-threads to one-thread-per-connection. But I was hoping I could share that experience and see if you folks had some insight to help point me in the right direction. Maybe someone else has already run into this exact issue and could share their solution.
The threadpool size is certainly one configuration to re-evaluate but I think we may need to look into the thread pool high priority options in https://www.percona.com/doc/percona-server/LATEST/performance/threadpool.html
One direct question I wanted to ask: if we decided to try the two step rename with
--cut-over=two-stepdoes it require the same locking that is currently happening in the atomic rename? I know we could error queries between those two steps executing, but I wonder if that is another option worth exploring, or if I should be focusing all of my energy on MySQL's configuration.The text was updated successfully, but these errors were encountered: