[Commits] Rev 4284: MDEV-6430: It is impossible to see if "filesort with small limit" optimization was used in file:///home/psergey/dev2/10.0-cp2/

Sergey Petrunya psergey at askmonty.org
Wed Jul 9 12:09:43 EEST 2014


At file:///home/psergey/dev2/10.0-cp2/

------------------------------------------------------------
revno: 4284
revision-id: psergey at askmonty.org-20140709090941-ulrqiburtae3d7e1
parent: psergey at askmonty.org-20140709083200-f583mcwf3g0rx0cq
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0-cp2
timestamp: Wed 2014-07-09 13:09:41 +0400
message:
  MDEV-6430: It is impossible to see if "filesort with small limit" optimization was used
  - Add a Sort_priority_queue_sorts status variable.
=== modified file 'mysql-test/r/myisam_explain_non_select_all.result'
--- a/mysql-test/r/myisam_explain_non_select_all.result	2014-06-03 20:26:27 +0000
+++ b/mysql-test/r/myisam_explain_non_select_all.result	2014-07-09 09:09:41 +0000
@@ -755,6 +755,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	11
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
@@ -1231,6 +1232,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	5
 Sort_scan	1
 # Status of testing query execution:
@@ -1275,6 +1277,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
@@ -1361,6 +1364,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
@@ -1407,6 +1411,7 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_rnd	1
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
@@ -1540,6 +1545,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	5
 Sort_scan	1
 # Status of testing query execution:
@@ -1668,6 +1674,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	5
 Sort_scan	1
 # Status of testing query execution:
@@ -1675,6 +1682,7 @@ Variable_name	Value
 Handler_read_rnd	5
 Handler_read_rnd_next	27
 Handler_update	5
+Sort_priority_queue_sorts	1
 Sort_rows	5
 Sort_scan	1
 
@@ -1712,6 +1720,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
@@ -1719,6 +1728,7 @@ Variable_name	Value
 Handler_read_rnd	1
 Handler_read_rnd_next	27
 Handler_update	1
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 
@@ -1799,12 +1809,14 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_rnd	1
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 
@@ -1844,12 +1856,14 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_rnd	1
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_rnd	1
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	1
 Sort_scan	1
 
@@ -1977,6 +1991,7 @@ Variable_name	Value
 # Status of "equivalent" SELECT query execution:
 Variable_name	Value
 Handler_read_rnd_next	27
+Sort_priority_queue_sorts	1
 Sort_rows	5
 Sort_scan	1
 # Status of testing query execution:
@@ -1984,6 +1999,7 @@ Variable_name	Value
 Handler_read_rnd	5
 Handler_read_rnd_next	27
 Handler_update	4
+Sort_priority_queue_sorts	1
 Sort_rows	5
 Sort_scan	1
 
@@ -2067,6 +2083,7 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_key	1
 Handler_read_next	2
+Sort_priority_queue_sorts	1
 Sort_range	1
 Sort_rows	2
 # Status of testing query execution:
@@ -2075,6 +2092,7 @@ Handler_read_key	1
 Handler_read_next	2
 Handler_read_rnd	2
 Handler_update	2
+Sort_priority_queue_sorts	1
 Sort_range	1
 Sort_rows	2
 
@@ -2105,6 +2123,7 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_key	1
 Handler_read_next	2
+Sort_priority_queue_sorts	1
 Sort_range	1
 Sort_rows	2
 # Status of testing query execution:
@@ -2625,6 +2644,7 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_key	3
 Handler_read_rnd_next	10
+Sort_priority_queue_sorts	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
@@ -2632,6 +2652,7 @@ Variable_name	Value
 Handler_read_key	3
 Handler_read_rnd_next	8
 Handler_update	1
+Sort_priority_queue_sorts	1
 Sort_rows	3
 Sort_scan	1
 
@@ -2674,12 +2695,14 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_key	3
 Handler_read_rnd_next	10
+Sort_priority_queue_sorts	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_key	3
 Handler_read_rnd_next	10
+Sort_priority_queue_sorts	1
 Sort_rows	3
 Sort_scan	1
 
@@ -2724,12 +2747,14 @@ Variable_name	Value
 Variable_name	Value
 Handler_read_key	3
 Handler_read_rnd_next	10
+Sort_priority_queue_sorts	1
 Sort_rows	3
 Sort_scan	1
 # Status of testing query execution:
 Variable_name	Value
 Handler_read_key	3
 Handler_read_rnd_next	10
+Sort_priority_queue_sorts	1
 Sort_rows	3
 Sort_scan	1
 

=== modified file 'mysql-test/r/order_by_sortkey.result'
--- a/mysql-test/r/order_by_sortkey.result	2012-09-01 21:21:59 +0000
+++ b/mysql-test/r/order_by_sortkey.result	2014-07-09 09:09:41 +0000
@@ -45,6 +45,7 @@ FLUSH STATUS;
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -153,6 +154,7 @@ f0	f1	f2
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	100
 Sort_scan	1

=== modified file 'mysql-test/r/single_delete_update.result'
--- a/mysql-test/r/single_delete_update.result	2012-02-15 17:08:08 +0000
+++ b/mysql-test/r/single_delete_update.result	2014-07-09 09:09:41 +0000
@@ -18,6 +18,7 @@ NULL	15
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -36,6 +37,7 @@ DELETE FROM t2 WHERE i > 10 AND i <= 18
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -71,6 +73,7 @@ NULL	15
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	5
 Sort_scan	1
@@ -89,6 +92,7 @@ DELETE FROM t2 WHERE i > 10 AND i <= 18
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	8
 Sort_scan	1
@@ -121,6 +125,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -139,6 +144,7 @@ DELETE FROM t2 WHERE b = 10 ORDER BY a,
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -175,6 +181,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -193,6 +200,7 @@ DELETE FROM t2 WHERE b = 10 ORDER BY a,
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -223,6 +231,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -241,6 +250,7 @@ DELETE FROM t2 WHERE b = 10 ORDER BY a,
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -269,6 +279,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -287,6 +298,7 @@ DELETE FROM t2 WHERE b = 10 ORDER BY a,
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -318,6 +330,7 @@ NULL	13	13
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	1
 Sort_rows	4
 Sort_scan	0
@@ -341,6 +354,7 @@ DELETE FROM t2 WHERE key1 < 13 or key2 <
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	1
 Sort_rows	4
 Sort_scan	0
@@ -378,6 +392,7 @@ NULL	14
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -396,6 +411,7 @@ DELETE FROM t2 WHERE i > 10 AND i <= 18
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -431,6 +447,7 @@ a	b	c
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	5
 Sort_scan	1
@@ -449,6 +466,7 @@ DELETE FROM t2 ORDER BY a, b DESC LIMIT
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	16
 Sort_scan	1
@@ -493,6 +511,7 @@ a	b	c
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -517,6 +536,7 @@ a	b	c
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -535,6 +555,7 @@ DELETE FROM t2 ORDER BY a DESC, b DESC L
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -571,6 +592,7 @@ NULL	15
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -589,6 +611,7 @@ UPDATE t2 SET a = 10 WHERE i > 10 AND i
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -629,6 +652,7 @@ NULL	15
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	5
 Sort_scan	1
@@ -647,6 +671,7 @@ UPDATE t2 SET a = 10 WHERE i > 10 AND i
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	5
 Sort_scan	1
@@ -684,6 +709,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -702,6 +728,7 @@ UPDATE t2 SET d = 10 WHERE b = 10 ORDER
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -738,6 +765,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -756,6 +784,7 @@ UPDATE t2 SET d = 10 WHERE b = 10 ORDER
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -786,6 +815,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -804,6 +834,7 @@ UPDATE t2 SET d = 10 WHERE b = 10 ORDER
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -833,6 +864,7 @@ a	b	c	d
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -851,6 +883,7 @@ UPDATE t2 SET d = 10 WHERE b = 10 ORDER
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	1
 Sort_scan	1
@@ -883,6 +916,7 @@ NULL	13	13
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	1
 Sort_rows	4
 Sort_scan	0
@@ -906,6 +940,7 @@ UPDATE t2 SET i = 123 WHERE key1 < 13 or
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	1
 Sort_rows	4
 Sort_scan	0
@@ -947,6 +982,7 @@ NULL	14
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -965,6 +1001,7 @@ UPDATE t2 SET a = 10 WHERE i > 10 AND i
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -1005,6 +1042,7 @@ a	b	c
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	5
 Sort_scan	1
@@ -1023,6 +1061,7 @@ UPDATE t2 SET c = 10 ORDER BY a, b DESC
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	1
 Sort_range	0
 Sort_rows	5
 Sort_scan	1
@@ -1061,6 +1100,7 @@ a	b	c
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -1085,6 +1125,7 @@ a	b	c
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0
@@ -1103,6 +1144,7 @@ UPDATE t2 SET c = 10 ORDER BY a DESC, b
 SHOW SESSION STATUS LIKE 'Sort%';
 Variable_name	Value
 Sort_merge_passes	0
+Sort_priority_queue_sorts	0
 Sort_range	0
 Sort_rows	0
 Sort_scan	0

=== modified file 'sql/filesort.cc'
--- a/sql/filesort.cc	2014-07-09 08:32:00 +0000
+++ b/sql/filesort.cc	2014-07-09 09:09:41 +0000
@@ -226,6 +226,7 @@ ha_rows filesort(THD *thd, TABLE *table,
   {
     DBUG_PRINT("info", ("filesort PQ is applicable"));
     thd->query_plan_flags|= QPLAN_FILESORT_PRIORITY_QUEUE;
+    status_var_increment(thd->status_var.filesort_pq_sorts_);
     const size_t compare_length= param.sort_length;
     if (pq.init(param.max_rows,
                 true,                           // max_at_top

=== modified file 'sql/mysqld.cc'
--- a/sql/mysqld.cc	2014-06-11 11:03:10 +0000
+++ b/sql/mysqld.cc	2014-07-09 09:09:41 +0000
@@ -7893,6 +7893,7 @@ SHOW_VAR status_vars[]= {
   {"Sort_range",	       (char*) offsetof(STATUS_VAR, filesort_range_count_), SHOW_LONG_STATUS},
   {"Sort_rows",		       (char*) offsetof(STATUS_VAR, filesort_rows_), SHOW_LONG_STATUS},
   {"Sort_scan",		       (char*) offsetof(STATUS_VAR, filesort_scan_count_), SHOW_LONG_STATUS},
+  {"Sort_priority_queue_sorts",(char*) offsetof(STATUS_VAR, filesort_pq_sorts_), SHOW_LONG_STATUS}, 
 #ifdef HAVE_OPENSSL
 #ifndef EMBEDDED_LIBRARY
   {"Ssl_accept_renegotiates",  (char*) &show_ssl_ctx_sess_accept_renegotiate, SHOW_SIMPLE_FUNC},

=== modified file 'sql/sql_class.h'
--- a/sql/sql_class.h	2014-05-09 10:35:11 +0000
+++ b/sql/sql_class.h	2014-07-09 09:09:41 +0000
@@ -704,6 +704,7 @@ typedef struct system_status_var
   ulong filesort_range_count_;
   ulong filesort_rows_;
   ulong filesort_scan_count_;
+  ulong filesort_pq_sorts_;
   /* Prepared statements and binary protocol */
   ulong com_stmt_prepare;
   ulong com_stmt_reprepare;



More information about the commits mailing list