Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

Remote access of MySQL server extremly slow

But the querying machine is just one hop / 0.2 ms away...

7:54 pm on Nov 20, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:May 25, 2006
posts: 182
votes: 0

I'm really puzzled.

Last night my dedicated database server (MySQL) got rebooted (data center maintenance) and from then on database queries from my webserver, just 1 hop and 0.2 milliseconds (via traceroute) take ages. (Everything was fine before.)

An example query like 'SELECT this FROM that WHERE some_condition_met' from my webserver takes 10 seconds to execute, but the same query performed on the database server itself takes only 0.01 seconds (done via cli).

So it's not a query issue, it could be done much faster.

File transfers between the two hosts take place at 20-30 MB/sec, but the result set of this query is about 1 MB in size.

So it's not a bandwith issue, it could be done much faster.

Profiling shows that it's 'sending data' that takes 10 seconds, but why?
It should be done in 0.2 or 0.3 seconds, maybe 0.5.

I'm clueless right now and don't even know what to look for. No configuration changes, no different startup parameters, network connection just fine.

Does anybody have an idea what's going wrong here?

12:13 am on Dec 18, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member whoisgregg is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Dec 9, 2003
votes: 0

I was hoping someone would chime in with a possible solution. This one has me stumped.

Did you ever discover the cause of the problem?

8:15 am on Dec 18, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:May 25, 2006
posts: 182
votes: 0

The server is currently off the net for a closer hardware inspection.
(Takes already some time, but the folks of the data center where this server is hosted are having some major scheduled modernization right now)

The support people say it must be a problem in the network hardware.
I thought about something like that also, because everything works fine when working on the machine locally without any net traffic involved.

So nothing new yet, I'll keep you informed.

5:30 pm on Dec 21, 2009 (gmt 0)

New User

5+ Year Member

joined:Dec 21, 2009
posts: 4
votes: 0

did the mysql server shutdown gracefully when the reboot happen?

You might have some broken tables.

I run an analyze and optimize on all your tables.

Be careful when running the optimize, as it will lock the tables and if you have any active site accessing the database, it will be locked out for the duration of the optimize process.

let us know what happened in the end!

7:35 pm on Dec 23, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:May 25, 2006
posts: 182
votes: 0

It was a broken interface of the connecting switch which got replaced, everything is working fine now and obviously it wasn't a database issue at all.

So sorry for any inconvenience and thanks for your help!