191

What does it mean if the Mysql query:

SHOW PROCESSLIST;

returns "Sending data" in the State column?

I imagine it means the query has been executed and MySQL is sending “result” Data to the client but I'm wondering why its taking so much time (up to an hour).

Thank you.

Improve this question
6
  • 1
    It means it's transmitting data from its process to the client. If you're seeing Sending data as a step that takes time after you run SHOW PROFILE then the time consumed actually belongs to the step before. Commented Apr 27, 2012 at 8:24
  • Unfortunately I take a message, The 'SHOW PROFILE' feature is disabled, I have to get MySQL built with 'enable-profiling' .But thank you for your response. Commented Apr 27, 2012 at 8:38
  • 4
    No, you're not listening.. the reason that Sending data shows as taking up time is because it's a MySQL profiling bug, the time shown there belongs to the step before, which should be Executing query or something similar. It just means your query takes time to execute. Sending data step is usually fast, unless you stream hundreds of megabytes of data. Commented Apr 27, 2012 at 9:16
  • 5
    Perhaps that question could be renamed to "What does 'Sending data' state means in MySQL". It will be easier to find the question. Commented Feb 4, 2013 at 10:56
  • 1
    @antitoxic I suggested a similar edit. Commented Jun 5, 2013 at 6:12

3 Answers 3

Reset to default
300

This is quite a misleading status. It should be called "reading and filtering data".

This means that MySQL has some data stored on the disk (or in memory) which is yet to be read and sent over. It may be the table itself, an index, a temporary table, a sorted output etc.

If you have a 1M records table (without an index) of which you need only one record, MySQL will still output the status as "sending data" while scanning the table, despite the fact it has not sent anything yet.

MySQL 8.0.17 and later: This state is no longer indicated separately, but rather is included in the Executing state.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

The documentation that further explains that the reasoning is most likely because of a lot of time doing disk access: dev.mysql.com/doc/refman/5.0/en/general-thread-states.html
And what if MySQL is "sending data" while using 99% CPU, with very little disk I/O?
@RustyX How about the data being already present in buffer in RAM and it is sorting some 100k's or 1M's of rows?
does "sending data" include the network load?
@egraldlo: yes.
|
29

In this state:

The thread is reading and processing rows for a SELECT statement, and sending data to the client.

Because operations occurring during this this state tend to perform large amounts of disk access (reads).

That's why it takes more time to complete and so is the longest-running state over the lifetime of a given query.

Improve this answer

Comments

0

The question has been answered correctly, here provides some details in code level(not enough room in comments), take 5.7 since there is no such state from 8.0

Stage Sending data starts at THD::enter_stage: 'Sending data', during this stage, it includes

  1. send_result_set_metadata which sends result metadata as well as metadata of each fields in result set
  2. do_select where the real hard work happens, it invokes storage engine to collect data, which involves IO operations

As we can see, do_select happens in the state of Sending data, the Sending data state is actually doing the select

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.