0

I have been struggling with this issue for 4 days now. Recently We migrated our DC to Cloud. After that we are facing this issue when a query is sent from a remote machine it stays in the "writing to net" state for a long peroid of time(from 1200 - 3000 seconds). When I run the same query in the localhost it returns the results within seconds.

I have tweaked few server variables like net_read_timeout and net_write_timeout but this is of no use.

Server Configuration: MySQL Community version 5.1.69 CentOS release 6.4 KERNEL version : 2.6.32-358.el6.x86_64(This is very old Kernel version maybe this is causing the whole problem)

Any Kind of Help would be deeply appreciated.

Sherry_MySQL
  • 1
  • 2
  • Random guess: a network MTU or packet loss issue? "In the localhost," meaning on the new database server? (People sometimes use "localhost" in a casual sense when they really mean "on my workstation," and I assume that this isn't what you mean.) The timeout variables won't do anything except kill the stalled query sooner. My next step would be TCP packet tracing on the server and the client, looking for retries, packets leaving one server and not arriving at the client, and ICMP messages like "fragmentation required but DF bit set." Which cloud? – Michael - sqlbot Jan 29 '17 at 01:07
  • Also, instead of Stack Overflow, this question should go on either [Server Fault](http://serverfault.com) or [Database Administrators](http://dba.stackexchange.com) (one or the other, not both). Both sites are excellent, but my instinct says you want Server Fault, because I think it more likely at this point that this is ultimately going to be a server/network issue rather than database issue. I *think* you can click `flag` and ask a moderator to migrate the question. I could be wrong but it is probably worth a shot. Stack Overflow is focused on "programming." – Michael - sqlbot Jan 29 '17 at 01:13
  • Michael-sqlbot We performed the ping test for 3 hours and there was no data packet loss. We moved to Azure Cloud. – Sherry_MySQL Jan 29 '17 at 01:54
  • If it's not packet loss, then you will want to look into MTU-related issues. Remotely, from a client machine experiencing the issue, run the query `SELECT REPEAT('A',1);`. Then change the 1 to 2, then 3, then 4... incrementing by 1 each time. Script this, of course. If you have an MTU issue, you shoud experience a hang and get no response, at some point before the value reaches 1500. – Michael - sqlbot Jan 29 '17 at 03:24

0 Answers0