Mysql – What does “Sending data” mean in the thesql process list

mariadbMySQLperformancequery-performance

I'm seeing a query getting stuck in the process list for long period of times with the Status of "Sending data". What does this status mean?

Best Answer

If someone knows more, please do fill in the gaps.

TL;DR "Sending data" seems to mean we are sending data about the query to the function that gathers that information together. It is NOT sending data to the MySQL or MariaDB client. This probably has something to do with the assembly of a JOIN command.

Finding very little good clarification on the Internet, I started looking through the MariaDB 10.2 code for "Sending Data" (yea, open source). Take all this with a some caution as I am not a C++ hacker.

You can find that in Line 10155 of sql/mysqld.cc where it is associated with stage_sending_data. This is found in sql_select.cc Line 3561 where it is at the end of the JOIN::exec_inner function. This function is called from the function JOIN::exec(). JOIN::exec is called from the function mysql_select(). This is called from handle_select(). It appears that "Sending data" should only show up on queries with JOINs.

I think I see mysql_select() set the status "Init" or stage_init. Then "Executing" or stage_executing in JOIN::exec_inner() while it does some checks for various things that I assume break JOINs or take special attention.

Then it sets that status to "Sending data" or stage_sending_data. In the next few lines after this status is set, it runs do_select(this, procedure);. At this point it appears to actually start gathering the information needed to provide an answer to the query. Contrary to my expectations, it is pretty clear that "Sending data" is not sending data to the client (over the network or otherwise), but to another function. It isn't yet ready to give a response. do_select() proceeds to do things like sorting temporary tables, evaluating conditions on HAVING, WHERE and stuff. It also removes locks. All that is after "Sending data" status is set.

It might be the last Status setting before actually returning the data. I haven't dug enough to state that clearly. A lot happens in do_select() so I expect the status could get changed in there somewhere, but it probably is dependent on various factors. At the end of mysql_select it does set the status to "end" or stage_end.

Refactor Your Query

Your original query is:

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    esp_game g
    INNER JOIN esp_gameplayer gp ON g.gameID = gp.gameID and gp.summonerID=401129
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

If the summonerID drives which gameIDs to retrieve, then the query needs to have esp_gameplayer be the lead table in the query

SELECT
    g.gameID, g.gameType, g.mapID,
    g.createDate, gt.gameTypePortugueseDesc
FROM
    (SELECT gameID FROM esp_gameplayer WHERE summonerID=401129) gp
    INNER JOIN esp_game g ON g.gameID = gp.gameID
    INNER JOIN gameTypeMap gt ON g.gameType = gt.gameTypeID
ORDER BY g.createDate DESC LIMIT 10;

Change the esp_gameplayer PRIMARY KEY

CREATE TABLE IF NOT EXISTS `esp_gameplayer` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

To reverse the PRIMARY KEY do the following:

CREATE TABLE IF NOT EXISTS `esp_gameplayer_new` (
  `gameID` int(11) unsigned NOT NULL,
  `summonerID` int(11) unsigned NOT NULL,
  `championID` int(11) NOT NULL,
  `teamID` int(11) NOT NULL,
  `isUpdated` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`summonerID`,`gameID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO esp_gameplayer_new SELECT * FROM esp_gameplayer;
ALTER TABLE esp_gameplayer RENAME esp_gameplayer_old;
ALTER TABLE esp_gameplayer_new RENAME esp_gameplayer;

Since you asked for a specific summonerID, reversing the PRIMARY KEY gathers all the corresponding gameIDs faster for summoner 401129

Make the changes, rerun the EXPLAIN plan. It should improve a great deal.

Give it a Try !!!

MySQL UPDATE query stuck on “Sending Data” State

PROBLEM

In the EXPLAIN plan, select_type is SIMPLE.

That's a full index scan VisitDiffTimes and range scan on CustomerVisits.

SUGGESTION #1

You need to reorder the columns in the PRIMARY KEY of VisitDiffTimes

ALTER TABLE VisitDiffTimes DROP PRIMARY KEY;
ALTER TABLE VisitDiffTimes ADD PRIMARY KEY (`OrderID`, `CustomerID`, `MerchantID`);

This will eliminate the full scan on VisitDiffTimes

SUGGESTION #2 (Optional)

Create a compound index on those two columns for CustomerVisits

ALTER TABLE CustomerVisits
    DROP INDEX OrderID,
    ADD INDEX OrderID_CustomerID_ndx (OrderID,CustomerID)
;

The JOIN may end up cleaner

SUGGESTION #3 (Optional)

Set the join_buffer_size to 16M

Add this to my.cnf

[mysqld]
join_buffer_size = 16M

You don't have to restart. Just login as root@localhost and run

mysql> SET GLOBAL join_buffer_size = 16 * 1024 * 1024;

GIVE IT A TRY !!!

SUGGESTION #4 (Optional)

Another Index with columns (CustomerID,OrderID)

ALTER TABLE CustomerVisits
    DROP INDEX CustomerID,
    ADD INDEX CustomerID_OrderID_ndx (CustomerID,OrderID)
;