MYSQL "Sending data" shows different value each time

Question

I am a student and trying to test the performance of different databases for a project. What I was trying to do is to generate 0-99999 (by self-join a 0-9 number table several times) and measure timing. I am surprised by my results, and am wondering if anyone can help explain them (they're shown below).

Here is my test procedure:

BEGIN

DECLARE vduration DECIMAL(8,4) DEFAULT 0;
DECLARE vid INT DEFAULT 0; 
DECLARE vcount INT DEFAULT 0;
DECLARE vprofilingid INT DEFAULT 0;
DECLARE a INT DEFAULT 0 ;

simple_loop: LOOP
     SET a = a+1;
     FLUSH QUERY CACHE;  

     SET profiling=1;
     SELECT n1.n + n2.n*10 + n3.n*100 + n4.n*1000 + n5.n*10000
     FROM
         baseline.num n1
       , baseline.num n2
       , baseline.num n3
       , baseline.num n4
       , baseline.num n5
     LIMIT 100000; 
     SET profiling=0;

     SELECT COUNT(*) INTO vcount FROM baseline.result;
     IF vcount=0 THEN
       SET vid=1;
     ELSE 
       SELECT MAX(Rid)+1 INTO vid FROM baseline.result;
     END IF; 

     SELECT MAX(DISTINCT(query_id)) INTO vprofilingid
     FROM information_schema.profiling;

     SELECT SUM(duration) INTO vduration
     FROM information_schema.profiling WHERE query_id=vprofilingid; 

     INSERT INTO baseline.result VALUES (vid, vduration); 

     IF a=5 THEN
       LEAVE simple_loop;
     END IF;
END LOOP simple_loop;             

END

Results:

RID     DURATION
8       0.0406
9       1.8610
10      1.8401
11      1.8558
12      1.8638

Running SHOW PROFILES reveals:

8   0.04059275  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
9   1.86098975  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
10  1.84006350  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
11  1.85582025  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000
12  1.86381750  select n1.n+n2.n*10+n3.n*100+n4.n*1000+n5.n*10000 from baseline.num n1, baseline.num n2,baseline.num n3,baseline.num n4,baseline.num n5 Limit 100000

Why is the first time faster? This was opposite to my assumption.

I noticed there is a difference in the SENDINT DATA,

SELECT * FROM information_schema.profiling
WHERE query_id > 7 and state = 'Sending data';

Shows:

8     10    Sending data    0.040310
9     10    Sending data    1.860891
10    10    Sending data    1.839958
11    10    Sending data    1.855719
12    10    Sending data    1.863717

Plus, if I set the loop to run just once, all results are around 0.04 seconds. Did I do something wrong here? I am really confused.

Time for "Sending Data" step actually refers to the step before sending data. — N.B.
– N.B., Commented Oct 25, 2011 at 7:58
This is an unrealistic "benchmark" and won't give you any usable figures to measure the database performance under real load. — user330315
– user330315, Commented May 20, 2012 at 15:51
@Johan - for that mysql version, the sending data step is a part of executing the query, so yes - it does. Time spent there actually refers to the query execution, not the piping the data that's been crunched. — N.B.
– N.B., Commented May 20, 2012 at 16:13

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Here's a link to all the thread states that MySQL has.

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

Notice the section on sending data:

Sending data

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), it is often the longest-running state over the lifetime of a given query.

Because the sending data includes disk read time, the values can differ a lot, depending on whether the data was in the HDD-cache, RAM cache, MySQL's cache etc.

Your first query does not read any data from disk thus the reading data + sending it step is much faster.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 20, 2012 at 15:26

Johan

77.4k28 gold badges204 silver badges346 bronze badges

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

Collectives™ on Stack Overflow

MYSQL "Sending data" shows different value each time

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related