Adventures in implementing a MySQL dump/restore progress bar
-
@lettucemode said:
Thanks to everyone who demonstrated Blakeyrat's Second Law by posting possible solutions.
Woo! Blakeyrat's Second Law cited! He shoots... he scores!
-
@pjt33 said:
My personal record for a bug report submitted (with a suggested patch) but bug not yet fixed is is 9.3 years and counting. Although that isn't an open source project.
@Sun's ridonkulous bug reporter software said:
PLEASE NOTE: JDK6 is formerly known as Project Mustang
Did you note that? Maybe the problem is you didn't note that!
-
@pjt33 said:
@The_Assimilator said:
Java (or at least, most of it) is open source nowadays.
Sort-of - Oracle's distribution of it isn't, but Sun forked out OpenJDK before going under. I suppose I could try filing a bug report with OpenJDK, because they probably didn't copy the old Bug Parade into their tracker when the fork happened.If you want to increase the chances of it to be fixed (and it hurts you enough to invest a bit of time to create a patch and a unit test against latest JDK8 preview), and you don't mind signing the OCA, you can go this route. All accepted patches to OpenJDK from a contributor that signed the OCA (which do not break the TCKs, of course) will be included into Oracle's next official JDK (JDK8). Don't hold your breath that it will be backported to JDK7, though.
Just adding it to OpenJDK bugtracker may help, too, but only if someone else finds it important enough to do that work...
The oldest bug I (accidentally) reported a duplicate for (that is still unfixed and annoyed me more than once) is 66763 in Thunderbird
-
@Cassidy said:
If you're willing to publish your script(s) somewhere as a tried-n-tested method, I'd be interested in them. The lack of progress thing during backups has annoyed me at times (although I haven't had to wait long, it's just the "silent treatment" that fuels my impatience).
Sorry for the bump, but here you are.
-
@lettucemode said:
Sorry for the bump, but here you are.
Now TRWTF is the black background and dark grey text, right?
-
@boomzilla said:
@lettucemode said:
Sorry for the bump, but here you are.
Now TRWTF is the black background and dark grey text, right?
I've wasted so many hours of my life trying to find a good theme...I am not a clever man :(
-
@lettucemode said:
@boomzilla said:
@lettucemode said:
Sorry for the bump, but here you are.
Now TRWTF is the black background and dark grey text, right?
I've wasted so many hours of my life trying to find a good theme...I am not a clever man :(
body {color: #B0B0B0;}
-
@lettucemode said:
@Cassidy said:
If you're willing to publish your script(s) somewhere as a tried-n-tested method, I'd be interested in them. The lack of progress thing during backups has annoyed me at times (although I haven't had to wait long, it's just the "silent treatment" that fuels my impatience).
Sorry for the bump, but here you are.
You reinvented pv, but for Windows.
-
@lettucemode said:
Not to mention that the requirements call for truncating each table in the database immediately after dumping
Sorry to respond to something in the original thread, but I was busy working while all of that was going on. I assume you lock the tables involved before dumping the data and truncating the tables, and only unlock after? Alternatively, your program that does all of this work is the only source for data and you like living on the edge? Because if there's other apps that enter data into this thing while all of this is going on, and that data gets truncated without being saved, I suspect your auditors might be a little more concerned about that than the fact your progress bar is sometimes off a little.
If you're unable to lock the tables due to political reasons (I've seen that happen), that pretty much guarantees data loss if you're using your process - even with the "instant" truncates you now have. (Because, after all, the political obstruction probably wouldn't happen if there wasn't *something* else that likes writing to those tables.) There is, however, a dirty little solution to that issue, if you can stand a very short MySQL server downtime right before you dump. I can share if you're interested and that is not, by itself, enough of a hint.
-
@morbiuswilters said:
You reinvented pv, but for Windows.
Someone had to.
@tgape said:
Sorry to respond to something in the original thread, but I was busy working while all of that was going on. I assume you lock the tables involved before dumping the data and truncating the tables, and only unlock after? Alternatively, your program that does all of this work is the only source for data and you like living on the edge? Because if there's other apps that enter data into this thing while all of this is going on, and that data gets truncated without being saved, I suspect your auditors might be a little more concerned about that than the fact your progress bar is sometimes off a little.
If you're unable to lock the tables due to political reasons (I've seen that happen), that pretty much guarantees data loss if you're using your process - even with the "instant" truncates you now have. (Because, after all, the political obstruction probably wouldn't happen if there wasn't *something* else that likes writing to those tables.) There is, however, a dirty little solution to that issue, if you can stand a very short MySQL server downtime right before you dump. I can share if you're interested and that is not, by itself, enough of a hint.
The dumping and truncating operations will only happen during plant fuel outages - these are periods when the plant will temporarily stop operating so that the nuclear fuel can be cycled. During this time, the system that normally writes to the database won't be used. So right now I don't lock the tables since nothing else is using it, but I'll probably add it next chance I get just in case. There are no political reasons for it, so I'll pass on the trick, but thanks :)
-
@lettucemode said:
The dumping and truncating operations will only happen during plant fuel outages - these are periods when the plant will temporarily stop operating so that the nuclear fuel can be cycled. During this time, the system that normally writes to the database won't be used. So right now I don't lock the tables since nothing else is using it, but I'll probably add it next chance I get just in case. There are no political reasons for it, so I'll pass on the trick, but thanks :)
I didn't read the whole thread, but if you're just dumping and restoring then why don't you use a hot copy? It will be much faster and can easily give you a progress since you know the size.
-
@morbiuswilters said:
@lettucemode said:
The dumping and truncating operations will only happen during plant fuel outages - these are periods when the plant will temporarily stop operating so that the nuclear fuel can be cycled. During this time, the system that normally writes to the database won't be used. So right now I don't lock the tables since nothing else is using it, but I'll probably add it next chance I get just in case. There are no political reasons for it, so I'll pass on the trick, but thanks :)
I didn't read the whole thread, but if you're just dumping and restoring then why don't you use a hot copy? It will be much faster and can easily give you a progress since you know the size.
I didn't know what that was, so I looked it up. Looks like it's a Perl script that works on Linux or NetWare. The database is running in a Windows environment and I'd have to wade through a fair bit of red tape to get Perl installed on the workstations. Thanks, though.
Unless you mean just generally copying the actual MySQL files during downtime...that solution causes a few issues since one of the requirements is to be able to load data sets of past outages for examination while also using the application normally. I won't be on site while the plant is operating and asking the operators to do anything with MySQL directly isn't feasible.
-
@lettucemode said:
@morbiuswilters said:
@lettucemode said:
The dumping and truncating operations will only happen during plant fuel outages - these are periods when the plant will temporarily stop operating so that the nuclear fuel can be cycled. During this time, the system that normally writes to the database won't be used. So right now I don't lock the tables since nothing else is using it, but I'll probably add it next chance I get just in case. There are no political reasons for it, so I'll pass on the trick, but thanks :)
I didn't read the whole thread, but if you're just dumping and restoring then why don't you use a hot copy? It will be much faster and can easily give you a progress since you know the size.
I didn't know what that was, so I looked it up. Looks like it's a Perl script that works on Linux or NetWare. The database is running in a Windows environment and I'd have to wade through a fair bit of red tape to get Perl installed on the workstations. Thanks, though.
Unless you mean just generally copying the actual MySQL files during downtime...that solution causes a few issues since one of the requirements is to be able to load data sets of past outages for examination while also using the application normally. I won't be on site while the plant is operating and asking the operators to do anything with MySQL directly isn't feasible.
There are scripts that do it, but a hot copy just means copying the underlying database files and loading them on another machine; it works on any OS MySQL runs on. It's much faster than a dump/reload (only really limited by the bandwidth between the machines you are copying from/to). You don't have to take the server offline to do it, although you do need to do a flush tables with read lock for MyISAM tables; InnoDB tables don't need to be flushed because they are crash-resistant. Believe me, there'd be no way to move multi-TB databases without it. However, if you don't have access to the MySQL datadir then it's a non-starter.
