• Post Reply Bookmark Topic Watch Topic
  • New Topic

MySQL : Long Procedure, disconnect...  RSS feed

 
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys : I've got a query that runs for 8+ hours. I want to be able to start it from home at night and look at the results in the morning. I tried this, but I woke up and sadly saw that my ssh connection was lost a few hours in to the command.

How can I start a mysql process and let it keep going after the client logs out ?
 
author & internet detective
Marshal
Posts: 37518
554
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jay,
If it is a UNIX/Linux client, you can use the nohup command. This disconnects your ssh session from the actual command being run. Be sure to pipe the output to a file so you know if there were errors.
 
jay vas
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks, jeanne.

Im a little confused though.... MySQL disconnects a query if the client logs out.

So when i run this command over ssh, what exactly happens ?

1) Does mysql get fooled into thinking there is a client ? Or does
2) A client get created which does not go away ?

Thanks ! Please let me know I'm very curious.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 37518
554
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jay,
Wikipedia has a good description of how the nohup command works.

I'm going to move this to our UNIX forum now that we are discussing long running processes rather than JDBC.
 
author and jackaroo
Marshal Commander
Posts: 12156
256
C++ Firefox Browser IntelliJ IDE Java Mac Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So when i run this command over ssh, what exactly happens ?

Are you asking about what happens when using the nohup command? If so, did you read the wiki page Jeanne pointed you to? Did that make sense or are you after more clarification (and if so, in what area?)

Another alternative might be to look at the screen program (man page. With that, you ssh into your system, execute screen (which will appear to just give you another prompt), then execute whatever command(s) are going to take a long time. You can then close your terminal / shutdown your local computer / disconnect your modem / whatever. Later, when you log in again, you type "screen -r" and it will resume your previous session. MySQL and such programs will not have terminated since from their perspective there has been an active screen attached at all times.

Note that screen is far more powerful than I have just described, but a lot of people tend to forget that it exists these days - back in the days when we were connecting over incredibly slow unreliable modems to terminal servers that only gave us a single terminal, it was almost guaranteed that anyone doing remote support would use screen since it gave us multiple virtual terminals and could survive the guaranteed line dropping.

Regards, Andrew
 
Ranch Hand
Posts: 1923
Linux Postgres Database Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You might look at "screen", a tool to logoff and login to your session again.

I didn't used it myself, but read about it often - should be quiet easy.
 
Saloon Keeper
Posts: 18800
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I was running an "IBM mainframe" (Project Hercules S/370 emulator) on a system that was in a locked datacenter half a kilometer from my office. The "nohup" and screen programs were ideal for that, since Hercules wanted about 4 different displays itself and I would tap in and out at work.

"Nohup", by the way, is short for "No Hangup" and dates back to the time when a lot of people were on dialup lines to time-sharing systems. By using NOHUP to launch a process, you could dial in, start a long-running process such as hamster DNA sequencing, then hang up without killing the process. Which freed up your phone line and kept the telephone bill expenses down.

"screen" goes one step further, since it preserves an actual console session(s) that you can attach to, detach from, and reattach to. For Hercules I had 2 primary ones:

1. The "hardware console", which is a curses-style GUI that allows controlling and monitoring the simulated CPU and peripheral devices.

2. The "hardcopy console", which took the place of the physical typewriter/printer device that would have been used by the primary system operator on a real IBM mainframe.
 
Rancher
Posts: 4686
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by jay vas:
Hi guys : I've got a query that runs for 8+ hours. I want to be able to start it from home at night and look at the results in the morning. I tried this, but I woke up and sadly saw that my ssh connection was lost a few hours in to the command.

How can I start a mysql process and let it keep going after the client logs out ?


start it in background, pipe the output to a file.

But you have a more fundamental problem. any query that takes 8+ hours is broken. If you get more records, will it take 16+ hours. This does not scale.

It needs serious debugging and perhaps optimization. And if that is impossible, do a select into a scratch table for major work sections, so you can restart it part way through the effort.

I have never seen a SQL query that I can't make run four times faster. Never in 30+ years of doing database work. Sometimes, I'll admit, it takes a lot of work to make it run faster.

IMHO, 8+ hours is at least eight times too slow to be used in production.
 
Tim Holloway
Saloon Keeper
Posts: 18800
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pat Farrell:

But you have a more fundamental problem. any query that takes 8+ hours is broken. If you get more records, will it take 16+ hours. This does not scale.


I wish. I made myself a hero once by getting a process down to 10 hours. Before I went in and tuned it, it was taking 5 days. It was doing complex fuzzy matches against millions of records. To get the speed boost, not only did I have to fine-tune the SQL itself, I had to build a set of hash keys that could be used to pare down each transaction to 1000 possibilities or less and cache the top 20 or so potential matches in RAM.

Fortunately those kinds of challenges are fairly rare. Or maybe not - if there'd been more of them, perhaps I'd not have gotten outsourced to the lowest bidder for everything else.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 37518
554
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Tim Holloway:
"Nohup", by the way, is short for "No Hangup" and dates back to the time when a lot of people were on dialup lines to time-sharing systems. By using NOHUP to launch a process, you could dial in, start a long-running process such as hamster DNA sequencing, then hang up without killing the process. Which freed up your phone line and kept the telephone bill expenses down.

Interesting!
 
Tim Holloway
Saloon Keeper
Posts: 18800
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, but did you know that vi was designed to require the minimum number of keystrokes - and no special keys such as cursor arrows? It made remote work on a 300-baud dialin slightly less intolerable.

I used to use mainframe TSO command-line over a 1200-baud link. It was better than driving in at 2am, but not by a whole lot.
 
Pat Farrell
Rancher
Posts: 4686
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Tim Holloway:
Ah, but did you know that vi was designed to require the minimum number of keystrokes - and no special keys such as cursor arrows? It made remote work on a 300-baud dialin slightly less intolerable.


Did you know that most of vi's command philosophy is straight from teco from the PDP-10, which started when 110 baud ASR-33s were common. 300 baud was fast. teco was designed for hard copy terminals, so there was no cursor.

And you can still use 'vi' well without cursor arrows.

If the PHB are happy with 8+ hour queries, cool. But that's way too long for my tastes. And I can always make a query be twice faster (take half the time) for a low low fee.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 37518
554
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I didn't know either of those vi tidbits. I did appreciate vi over dialup (although not as bad as the one you are describing - I did have 40-47K). I liked that I could type ahead and then wait for the terminal to catch up.
 
Tim Holloway
Saloon Keeper
Posts: 18800
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Jeanne Boyarsky:
I liked that I could type ahead and then wait for the terminal to catch up.


A crime I'm still guilty of. At the time, I could be about 3 lines ahead of the character echo, not just command execution. Eh, who needs to actually see what they're typing?
 
Tim Holloway
Saloon Keeper
Posts: 18800
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pat Farrell:


If the PHB are happy with 8+ hour queries, cool. But that's way too long for my tastes. And I can always make a query be twice faster (take half the time) for a low low fee.


Actually, Pat, the PHB was ecstatic. Remember, before I tuned it, it took 5 days! And that wasn't 5 as days as in 40 hours, it was 5 days as in 24*5 hours. Continual compute time. It was a little bit more than just a simple query and the completion time wanted to expand geometrically with the size of one of the input data sets. I had to meter the thing silly and redesign the core algorithms several times to crunch it down that far. I won't say there wasn't room to optimize it even further, but the labor involved wasn't worth the cost to do it.

I work cheaper than I should, but cost more than they'd like. Mostly because few others in town can do that sort of tuning at all.
 
Pat Farrell
Rancher
Posts: 4686
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Tim Holloway:
It was a little bit more than just a simple query and the completion time wanted to expand geometrically with the size of one of the input data sets. I had to meter the thing silly and redesign the core algorithms several times to crunch it down that far. I won't say there wasn't room to optimize it even further, but the labor involved wasn't worth the cost to do it.


I take this to mean that further improvement wasn't justified now. Clearly getting from days to 8 hours was well worth the effort.

Those geometric increases can really get you. I worked on a project where the developer, who should have known better, used a O(n^4) algorithm. It tested fine with 100 records. The first day of production had several hundred thousand, and we expected tens of thousands of new records growth per day. You just can't do n^4 algorithms when 'n' is big. We beat it down to O(n^2) and called that good enough.

The car racing guys have a saying: You can only beat cubic inches with cubic dollars, how fast do you want to go ($$$)?
 
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have recently become familiar with the GNU Screen tool and it has changed the way I use remote connections. Log in, start screen with "screen" and run your task. 8 hours later, log back in and reconnect to the same screen session. It saves a lot of time with normal file editing, especially if you have been timed out when you get up to get some coffee, etc. Others may get annoyed if you keep leaving old screen sessions open.

screen -ls shows available screen sessions
screen -r 3436 re-attach to one of your screen sessions
CTRL-A C - create a new screen
CTRA-A 1 - switch to screen 1
CTRL-A w - list available terminals
 
Tim Holloway
Saloon Keeper
Posts: 18800
74
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pat Farrell:


I take this to mean that further improvement wasn't justified now. Clearly getting from days to 8 hours was well worth the effort.


Well worth the effort. The whole system was resented, since it consumed both human and computer resources and produced no revenue. However if it wasn't run on a regular basis, the corporate charter would have been revoked and the board, IT and legal departments would end up vacationing in Guantanamo.

So we made a good-faith effort and reserved our resources for revenue items.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!