Processlist show most of the queries are sending data for select query

Question

I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and insert), but after few minutes nothing happened. Then I see in database with SHOW PROCESSLIST; most of the query is in SENDING DATA state with 1001 threads. my max query size is 1000. I decrease the size 50 to 10 but no luck. Can anyone suggest me how to solve this problem.

Answer 1

This is very simply a case where your server does not have sufficient capacity (often disk I/O, but sometimes memory or CPU) to handle the workload. Poorly-written queries and insufficient or sub-optimal indexes are a common contributor, as are applications that give up too soon and re-send the same query that's already running, and an improperly sized innodb_buffer_pool_size with InnoDB.

Sending Data was a very poorly-chosen name for the thread state. It simply does not mean that the server is necessarily "sending" anything. The server may, in fact, still be doing table scans and may have nothing, yet, to send anywhere, and the thread will still be in this state. A more appropriate name for the state might have been "preparing data to send."

By contrast, when the server is in fact actually sending data to the client, the state is Writing to net and it's pretty rare to actually see that in the processlist unless the resultset is particularly large or the client is particularly slow about accepting data from the network.

Sending data

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Writing to net

The server is writing a packet to the network.

^{— http://dev.mysql.com/doc/refman/5.6/en/general-thread-states.html}