Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql profiler "Sending data"

Tags:

mysql

Is there an explanation of these statuses anywhere?

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

my specific question is in regards to this query:

select count(*)
from 135_5m.record_updates u, 135_5m.records r
where  r.record_id = u.record_id  and 
  (u.date_updated > null or null is null)  and 
  u.date_updated <= '2011-01-03';

which returns a single number - 4053904. So why would the majority of the time be spent in "Sending data"? Is it just poorly named? Surely "Sending data" must be doing more than just sending data?

+--------------------------------+-----------+-------+
| Status                         | Duration  | Swaps |
+--------------------------------+-----------+-------+
| starting                       |  0.000224 |     0 |
| checking query cache for query |  0.000188 |     0 |
| checking permissions           |  0.000012 |     0 |
| checking permissions           |  0.000017 |     0 |
| Opening tables                 |  0.000036 |     0 |
| System lock                    |  0.000015 |     0 |
| Table lock                     |  0.000067 |     0 |
| init                           |  0.000105 |     0 |
| optimizing                     |  0.000052 |     0 |
| statistics                     |  0.000254 |     0 |
| preparing                      |  0.000061 |     0 |
| executing                      |  0.000017 |     0 |
| Sending data                   | 32.079549 |     0 |
| end                            |  0.000036 |     0 |
| query end                      |  0.000012 |     0 |
| freeing items                  |  0.000089 |     0 |
| storing result in query cache  |  0.000022 |     0 |
| logging slow query             |  0.000008 |     0 |
| logging slow query             |  0.000008 |     0 |
| cleaning up                    |  0.000011 |     0 |
+--------------------------------+-----------+-------+
like image 580
andersonbd1 Avatar asked Sep 03 '10 18:09

andersonbd1


People also ask

How to use MySQL profiler?

Enable profiling by setting profiling to 1 or ON : mysql> SET profiling = 1; SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15.

What is sending data in MySQL?

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.

How to create profile in MySQL?

Creating a MySQL configuration objectGo to Server Load Balance > Application Resources. Select the Application Profile tab if it is not already selected. Click Add to open the Application Profile configuration editor. In the Name field, enter a unique profile name.


1 Answers

http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

Executing means the thread has started execution, Sending data apparently covers both the processing of the rows and sending the count back to the client.

like image 158
Mikko Wilkman Avatar answered Sep 22 '22 21:09

Mikko Wilkman