Sign in to follow this  

[web] What to do when mySQL query times out

This topic is 4712 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, I'm using mySql C API and some times the mysql_query() failes returning CR_SERVER_LOST wich means: "The connection to the server was lost during the query.". So the queston is should I do anything before querying again? I'm planning to just keep calling mysql_query() until it returs something else than CR_SERVER_LOST. Would that be ok? Thanks.

Share this post


Link to post
Share on other sites
No, that's not a good idea. There's gotta be a reason why it gives a timeout.
Brute-forcing it to succeed will only make it worse (and might lead to more timeouts).
If you really must you could retry it say, 3 times. But it's better to examine why this error occurs: maybe you could optimize the database, set a higher timeout, etc..

Share this post


Link to post
Share on other sites
Quote:
Original post by WanMaster
No, that's not a good idea. There's gotta be a reason why it gives a timeout.
Brute-forcing it to succeed will only make it worse (and might lead to more timeouts).
If you really must you could retry it say, 3 times. But it's better to examine why this error occurs: maybe you could optimize the database, set a higher timeout, etc..


Ok thanks for the warning. How do you change the database's timeout val? I supose I can't because I'm using free DB from www.freesql.org.

The thing is that I have only few queries. And they happen like every 30 secs. But about 15% of the time they fail.

So could the idling be the reason? I read from mysql's site that ping() is used to re-establish the connection if its has been lost but I don't think its completely lost because the other queries still work even previous would fail.

Share this post


Link to post
Share on other sites
Yeah, it could be a lost connection.
I once had the same problem with a wireless network at home. Once in a while the connection was reset for a very brief moment (milliseconds). It prevented me from playing about any online game: they either crashed or quited.
I'm afraid as long as there's no real stable connection, you can't really fix the problem in an easy way.
Maybe the re-querying isn't such a bad idea in this situation, as long don't force it into an eternal loop.
Do have any idea of the lost connection was caused by the (database) server or the client?

Share this post


Link to post
Share on other sites
Can you reproduce the problem with a realistic-size dataset in your development environment? Or is it something which only happens in production?

If it's a single query which is taking too long, that's usually something which can be fixed easily (unless it's doing something which is genuinely difficult and/or your DB is very large).

- What is the load on the DB server?
- What sort of read/write contention is going on? Is your site very high traffic? Are there are lot of reads on the database and few writes, or vice versa?
- Is the problem consistent or inconsistent? It could be a result of high load, starvation caused by too many threads trying to read / write the table at the same time, or something else.

Obviously you will have some monitoring systems in place to determine what exactly is happening.

Personally I'd say that a DB connection timeout should not happen in production (well, except when you are doing stuff like rebooting switches or something).

There are options on MySQL to log slow queries - you could try turning them on (but bear in mind that logging queries is going to have SOME performance overhead itself).

Monitor the IO bandwidth on the DB server - determine what proportion of its time is spent on CPU, waiting for IO, idle etc.

Mark

Share this post


Link to post
Share on other sites
Thanks for your replies.

The queries are actualy very simble, something like this:
"SELECT something FROM someWhere WHERE x=4"

And only few tables in the DB.

I belive the problem is on the server because it's simbly too slow. It's a free server for testing purposes so I guess you cant expect too much from it.

Well, I try to re-query.

Thanks again.

Share this post


Link to post
Share on other sites
The re-query is not the answer.

I suppose one thing I did't ask was what the network between your client and server machines were; I assumed they were on a fast reliable LAN, because this is common for most web and three-tiered apps (for example, game servers).

You should try to reproduce the fault under controlled conditions - if necessary by creating synthetic IO load on your dev server (for instance, try a large number of parallel grep commands on huge text files)

Obviously if you're hosting your DB on a third party's "free" server, the conditions are not very controlled, and you've no idea what else they're trying to pile on to the machine

Mark

Share this post


Link to post
Share on other sites
Quote:
Original post by markr
Obviously if you're hosting your DB on a third party's "free" server, the conditions are not very controlled, and you've no idea what else they're trying to pile on to the machine


Yes so I just have to leave this problem behind for now. I know that isn't a good thing to do but I guess there isn't anything else to do about it than make sure nothing crashes when queries start to fail.

Share this post


Link to post
Share on other sites

This topic is 4712 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this