MySQL query state: Sending data

lcasby

I have a dedicated MySQL database server serving two AwareIM application servers; one production and one test. The test server database is a subset of the production data. The hardware of the test server is newer with more memory, but my issue is much more apparent on the test server.

The issue is that some processes take a long time with a select query getting stuck in sending data state in the mysql server for several minutes. The AwareIM log viewer shows all the rules have completed, but the UI just shows a process box that says Sending data... The rules will complete in seconds, but the UI can take several minutes to return from the Sending data box. Meanwhile, show processlist on the mysql server shows a select query in sending data state.

When I run the slow query on the database server manually, it returns results immediately. I've verified memory and CPU are not an issue, and I've verified the network is not a bottleneck between any of the servers.

My production app server is a 32bit Windows 2003 server, and my test server is a 64bit Windows 2008 server. I'm the only user on the test server. Both are running the same version of AwareIM, and the same BSV code. Shouldn't I get the same or better speed from the test server?

Anyone have any suggestions on how I can further investigate this issue? I was hoping to get this test server running faster than the production server, and to swap the server roles.

Thanks.

nlarson

What are the aware server memory settings? Are they the same in both?

lcasby

Thanks for the reply.

The production server has
AwareIM: 512MB
Tomcat: 1024 MB
JBoss: 256MB

I tested the test server with the same and at 1024MB for each process with no improvement. None of the processes are even close to their max memory.

aware_support

Are you sure that the system is stuck sending data to/from Aware IM to the database server? If so, I don't know what the problem could be.

If not, then possible causes of the slow down are:
1) Query runs slow in MySQL (unless you have verified that exactly the same query that Aware IM sends to MySQL executes fine when run from within MySQL itself)
2) Aware IM is slow creating object instances from the data returned by the query - can happen if MySQL returns large number of rows and/or created objects are big. If this is the case try limiting number of rows returned by MySQL

nlarson

several of the threads I saw checking this via Google mention indexing issues, perhaps the query needs to be optimized and the the test sub-set is not large enough to stress test with.