App hungs up while running a LONG query

fernandojmartin posted 7 years ago in Running SQL scripts
Hi Anse, I hope not to dupe an existing issue, but I haven't found matches to "hung up". :)

Due the nature of some of my projects I often run reports or queries that could take many minutes, more than an hour, or even more.

It's really annoying that the application stays "hung up" during that time, and it hungs up the SO too (multimedia controls will not work, 'START+D / show desktop' neither, etc.

Also, sometimes in a parallel instance of Heidi I can see that the process has finished but the instance that fired it up hasn't
noted this and still stays there holding on for something that will never come. Fortunately in a couple hours will self-declare the end of the query life.

Maybe there's a real limitation, or maybe is a bug that the most of the people haven't noticed because the speed of usual queries.

No matter how big the result: it only stays there wayting for something.

Is it just me? :(
ansgar posted 7 years ago
Hm don't know what to say - this is difficult to reproduce but you may be right. I'm always avoiding big queries as usual, but here I should try to fetch some non-joined tables, only I am unable to know when the result *should* arrive which makes it even harder to reproduce this issue.
fernandojmartin posted 7 years ago
FYI, I work with tables with a couple hundred million rows (usually up to 500mill), so even pulling data from just one table, with the right indexes and all the needed optimizations, the *processor* ("Sending data" thread state) can be the bottleneck and so that a query can take many minutes or much more as I said before.

BTW, I've noted that in such queries the problem is when the client doesn't receives response from the server.
Imagine a SELECT COUNT()... it will only fetch 1 row with an integer value; that's the smallest result after an empty one.
However, to get that result the DB server can spend a lot of time without letting the client to know that it's being stifled.
I've noted the same behavior on the Windows' client (mysql.exe) with the difference that I can kill it with a CTRL+C and it doesn't hungs up my operative system.
Also I was able to determine that the self-killing on both applicaions (yours and mysql.exe) is after 2 hours or 7200 seconds, the way you prefer itsmileso at some point I guessed some similarity on the background working of your application (sorry, I don't know Delphi or whatever you usesmile)
For Heidi, I have to start another instance, go to the process list and kill the thread, what actually doesn't guarantees me that Heidi itself will get noticed about the punishment.
Many times, after killing the thread, the instance that fired up the query keeps there waiting for something that will never come. Oh, poorunhappy , lol.

Count me in on helping you to solve this, since I'm an old full-time user of Heidi.
If you want I can give you access to a DB with some big tables where you can play safely.

Regarding "I am unable to know when the result *should* arrive" I think that getting the/a thread ID (maybe with a pre-query to get one) and "pinging" or checking in parallel for that ID state could be a workaround to know if still exists or it's alive.

Don't know what to do on the "hang up" part of this issue.

Let me know how can I help.
I'd really love it.

Please login to leave a reply, or register at first.