I found a strange situation that would like to have your help, here is my scenario.
Webserver runs Apache + PHP + MySQL(5.6.12) Master mode
OLAP runs MySQL(5.6.19) Slave mode
So, when users make a request to get a report from the webserver, it sends SQL stmt to OLAP to get result. Here is the problem, if the smtm has a small date range like 1 month, the data will return normally, but if the report takes a long time to process because it has a date range like one year, it will return nothing.
Let me explain "it will return nothing" meaning.
- The webserver sends a long-processed SQL stmt to the OLAP server.
- The OLAP server process the stmt for a while ( "Query" status in MySQL processlist) then turns to "Sleep" status.
- After going to Sleep status, it waits until 180 second (wait_timeout) which was set in MySQL, and then gets killed.
- In term of networking status, it shows ESTABLISHED, then FIN_WAIT1 and close connection with the webserver like normal TCP connection states.
- I tried to use Navicate for query the same stmt, Navicate gives me a result.
- I tried to use strace in both the web and OLAP, what I can get is Apache open "read" system call, and waiting data back from the OLAP server. At the same time, OLAP shows "poll" system call, but never return result back to the web server.
Please help me if you have been through the same situation.
Thanks.