Skip to content

Commit b35a60f

Browse files
author
Tor Didriksen
committed
Bug#11748783 37359: FILESORT CAN BE MORE EFFICIENT
filesort() initializes space for MIN(Estimate rows in table, max rows that can fit in sort buffer) rows before doing the sort. For queries that have a "where" clause, the number of rows estimated by estimate_rows_upper_bound can be much higher than rows that match the where clause. The initialization is done via make_char_array() in filesort.cc and is very expensive when the estimate is off. Solution: lazy-init of the pointers in the sort buffer.
1 parent e7064dc commit b35a60f

15 files changed

Lines changed: 325 additions & 71 deletions

‎mysql-test/r/filesort_debug.result‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@ DROP TABLE t1;
1616
#
1717
CREATE TABLE t1(f0 int auto_increment primary key, f1 int);
1818
INSERT INTO t1(f1) VALUES (0),(1),(2),(3),(4),(5);
19-
SET session debug= '+d,make_char_array_fail';
19+
SET session debug= '+d,alloc_sort_buffer_fail';
2020
CALL mtr.add_suppression("Out of sort memory");
2121
SELECT * FROM t1 ORDER BY f1 ASC, f0;
2222
ERROR HY001: Out of sort memory, consider increasing server sort buffer size

‎mysql-test/suite/opt_trace/r/filesort_pq.result‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -269,6 +269,7 @@ SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 100 {
269269
"rows": 100,
270270
"examined_rows": 100,
271271
"number_of_tmp_files": 0,
272+
"sort_buffer_size": 25080,
272273
"sort_mode": "<sort_key, additional_fields>"
273274
}
274275
}
@@ -446,6 +447,7 @@ SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30 {
446447
"rows": 31,
447448
"examined_rows": 100,
448449
"number_of_tmp_files": 0,
450+
"sort_buffer_size": 7068,
449451
"sort_mode": "<sort_key, additional_fields>"
450452
}
451453
}
@@ -658,6 +660,7 @@ SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30 {
658660
"rows": 31,
659661
"examined_rows": 100,
660662
"number_of_tmp_files": 0,
663+
"sort_buffer_size": 13144,
661664
"sort_mode": "<sort_key, additional_fields>"
662665
}
663666
}
@@ -884,6 +887,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20 {
884887
"rows": 21,
885888
"examined_rows": 100,
886889
"number_of_tmp_files": 0,
890+
"sort_buffer_size": 8904,
887891
"sort_mode": "<sort_key, additional_fields>"
888892
}
889893
}
@@ -1120,6 +1124,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10 {
11201124
"rows": 21,
11211125
"examined_rows": 100,
11221126
"number_of_tmp_files": 0,
1127+
"sort_buffer_size": 8904,
11231128
"sort_mode": "<sort_key, additional_fields>"
11241129
}
11251130
}
@@ -1291,6 +1296,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10 {
12911296
"rows": 11,
12921297
"examined_rows": 100,
12931298
"number_of_tmp_files": 0,
1299+
"sort_buffer_size": 4664,
12941300
"sort_mode": "<sort_key, additional_fields>"
12951301
}
12961302
}
@@ -1429,6 +1435,7 @@ SELECT CONCAT("hello ", f2) AS foo FROM t1 ORDER BY foo LIMIT 2 {
14291435
"rows": 3,
14301436
"examined_rows": 100,
14311437
"number_of_tmp_files": 0,
1438+
"sort_buffer_size": 1254,
14321439
"sort_mode": "<sort_key, additional_fields>"
14331440
}
14341441
}
@@ -1566,6 +1573,7 @@ SELECT * from t1 ORDER BY rand(2) LIMIT 2 {
15661573
"rows": 3,
15671574
"examined_rows": 100,
15681575
"number_of_tmp_files": 0,
1576+
"sort_buffer_size": 72,
15691577
"sort_mode": "<sort_key, rowid>"
15701578
}
15711579
}
@@ -1744,6 +1752,7 @@ SELECT * FROM t1 ORDER BY f1 ASC, f0 LIMIT 30 {
17441752
"rows": 31,
17451753
"examined_rows": 100,
17461754
"number_of_tmp_files": 0,
1755+
"sort_buffer_size": 7068,
17471756
"sort_mode": "<sort_key, additional_fields>"
17481757
}
17491758
}
@@ -1956,6 +1965,7 @@ SELECT * FROM t1 ORDER BY f2 DESC, f0 LIMIT 30 {
19561965
"rows": 31,
19571966
"examined_rows": 100,
19581967
"number_of_tmp_files": 0,
1968+
"sort_buffer_size": 13144,
19591969
"sort_mode": "<sort_key, additional_fields>"
19601970
}
19611971
}
@@ -2182,6 +2192,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 20 {
21822192
"rows": 21,
21832193
"examined_rows": 100,
21842194
"number_of_tmp_files": 0,
2195+
"sort_buffer_size": 8904,
21852196
"sort_mode": "<sort_key, additional_fields>"
21862197
}
21872198
}
@@ -2418,6 +2429,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 10 OFFSET 10 {
24182429
"rows": 21,
24192430
"examined_rows": 100,
24202431
"number_of_tmp_files": 0,
2432+
"sort_buffer_size": 8904,
24212433
"sort_mode": "<sort_key, additional_fields>"
24222434
}
24232435
}
@@ -2589,6 +2601,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 0 OFFSET 10 {
25892601
"rows": 11,
25902602
"examined_rows": 100,
25912603
"number_of_tmp_files": 0,
2604+
"sort_buffer_size": 4664,
25922605
"sort_mode": "<sort_key, additional_fields>"
25932606
}
25942607
}
@@ -2774,6 +2787,7 @@ ORDER BY f1, f0 LIMIT 30 {
27742787
"rows": 31,
27752788
"examined_rows": 100,
27762789
"number_of_tmp_files": 0,
2790+
"sort_buffer_size": 7068,
27772791
"sort_mode": "<sort_key, additional_fields>"
27782792
}
27792793
}
@@ -2926,6 +2940,7 @@ ORDER BY f1, f0 LIMIT 0 {
29262940
"rows": 1,
29272941
"examined_rows": 100,
29282942
"number_of_tmp_files": 0,
2943+
"sort_buffer_size": 228,
29292944
"sort_mode": "<sort_key, additional_fields>"
29302945
}
29312946
}
@@ -3122,6 +3137,7 @@ ORDER BY f2, f0 LIMIT 20 {
31223137
"rows": 21,
31233138
"examined_rows": 100,
31243139
"number_of_tmp_files": 0,
3140+
"sort_buffer_size": 8904,
31253141
"sort_mode": "<sort_key, additional_fields>"
31263142
}
31273143
}
@@ -3298,6 +3314,7 @@ ORDER BY f2, f0 LIMIT 0 {
32983314
"rows": 1,
32993315
"examined_rows": 100,
33003316
"number_of_tmp_files": 0,
3317+
"sort_buffer_size": 424,
33013318
"sort_mode": "<sort_key, additional_fields>"
33023319
}
33033320
}
@@ -3484,6 +3501,7 @@ ORDER BY f2, f0 LIMIT 10 OFFSET 10 {
34843501
"rows": 21,
34853502
"examined_rows": 100,
34863503
"number_of_tmp_files": 0,
3504+
"sort_buffer_size": 8904,
34873505
"sort_mode": "<sort_key, additional_fields>"
34883506
}
34893507
}
@@ -3660,6 +3678,7 @@ ORDER BY f2, f0 LIMIT 0 OFFSET 10 {
36603678
"rows": 11,
36613679
"examined_rows": 100,
36623680
"number_of_tmp_files": 0,
3681+
"sort_buffer_size": 4664,
36633682
"sort_mode": "<sort_key, additional_fields>"
36643683
}
36653684
}
@@ -3942,6 +3961,7 @@ ORDER BY tmp.f1, f0 LIMIT 30 {
39423961
"rows": 31,
39433962
"examined_rows": 1500,
39443963
"number_of_tmp_files": 0,
3964+
"sort_buffer_size": 775,
39453965
"sort_mode": "<sort_key, rowid>"
39463966
}
39473967
}
@@ -4216,6 +4236,7 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 {
42164236
"rows": 61,
42174237
"examined_rows": 1500,
42184238
"number_of_tmp_files": 0,
4239+
"sort_buffer_size": 1525,
42194240
"sort_mode": "<sort_key, rowid>"
42204241
}
42214242
}
@@ -4495,6 +4516,7 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 {
44954516
"rows": 61,
44964517
"examined_rows": 1500,
44974518
"number_of_tmp_files": 0,
4519+
"sort_buffer_size": 1525,
44984520
"sort_mode": "<sort_key, rowid>"
44994521
}
45004522
}
@@ -4802,6 +4824,7 @@ ORDER BY tmp.f1, f0 LIMIT 30 OFFSET 30 {
48024824
"rows": 61,
48034825
"examined_rows": 1185,
48044826
"number_of_tmp_files": 0,
4827+
"sort_buffer_size": 1525,
48054828
"sort_mode": "<sort_key, rowid>"
48064829
}
48074830
}
@@ -5109,6 +5132,7 @@ SELECT * FROM v1 {
51095132
"rows": 31,
51105133
"examined_rows": 500,
51115134
"number_of_tmp_files": 0,
5135+
"sort_buffer_size": 7068,
51125136
"sort_mode": "<sort_key, additional_fields>"
51135137
}
51145138
}
@@ -5405,6 +5429,7 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30 {
54055429
"rows": 101,
54065430
"examined_rows": 500,
54075431
"number_of_tmp_files": 0,
5432+
"sort_buffer_size": 23028,
54085433
"sort_mode": "<sort_key, additional_fields>"
54095434
}
54105435
}
@@ -5439,6 +5464,7 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30 {
54395464
"rows": 31,
54405465
"examined_rows": 100,
54415466
"number_of_tmp_files": 0,
5467+
"sort_buffer_size": 6851,
54425468
"sort_mode": "<sort_key, rowid>"
54435469
}
54445470
}
@@ -6039,6 +6065,7 @@ LIMIT 30 {
60396065
"rows": 101,
60406066
"examined_rows": 500,
60416067
"number_of_tmp_files": 0,
6068+
"sort_buffer_size": 23028,
60426069
"sort_mode": "<sort_key, additional_fields>"
60436070
}
60446071
}
@@ -6077,6 +6104,7 @@ LIMIT 30 {
60776104
"rows": 101,
60786105
"examined_rows": 500,
60796106
"number_of_tmp_files": 0,
6107+
"sort_buffer_size": 42824,
60806108
"sort_mode": "<sort_key, additional_fields>"
60816109
}
60826110
}
@@ -6117,6 +6145,7 @@ LIMIT 30 {
61176145
"rows": 31,
61186146
"examined_rows": 325,
61196147
"number_of_tmp_files": 0,
6148+
"sort_buffer_size": 6975,
61206149
"sort_mode": "<sort_key, rowid>"
61216150
}
61226151
}
@@ -6284,6 +6313,7 @@ GROUP BY 1 ORDER BY 2,1 LIMIT 5 {
62846313
"rows": 6,
62856314
"examined_rows": 10,
62866315
"number_of_tmp_files": 0,
6316+
"sort_buffer_size": 198,
62876317
"sort_mode": "<sort_key, rowid>"
62886318
}
62896319
}
@@ -6534,6 +6564,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 30 {
65346564
"rows": 31,
65356565
"examined_rows": 500,
65366566
"number_of_tmp_files": 0,
6567+
"sort_buffer_size": 13144,
65376568
"sort_mode": "<sort_key, additional_fields>"
65386569
}
65396570
}
@@ -6718,6 +6749,7 @@ SELECT * FROM t1 WHERE f1>10 ORDER BY f2, f0 LIMIT 15 OFFSET 15 {
67186749
"rows": 31,
67196750
"examined_rows": 500,
67206751
"number_of_tmp_files": 0,
6752+
"sort_buffer_size": 13144,
67216753
"sort_mode": "<sort_key, additional_fields>"
67226754
}
67236755
}
@@ -6903,6 +6935,7 @@ ORDER BY f2, f0 LIMIT 15 OFFSET 15 {
69036935
"rows": 31,
69046936
"examined_rows": 500,
69056937
"number_of_tmp_files": 0,
6938+
"sort_buffer_size": 13144,
69066939
"sort_mode": "<sort_key, additional_fields>"
69076940
}
69086941
}
@@ -7194,6 +7227,7 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30 {
71947227
"rows": 101,
71957228
"examined_rows": 500,
71967229
"number_of_tmp_files": 0,
7230+
"sort_buffer_size": 23028,
71977231
"sort_mode": "<sort_key, additional_fields>"
71987232
}
71997233
}
@@ -7228,6 +7262,7 @@ SELECT * FROM v1 ORDER BY f2, f0 LIMIT 30 {
72287262
"rows": 31,
72297263
"examined_rows": 100,
72307264
"number_of_tmp_files": 0,
7265+
"sort_buffer_size": 6851,
72317266
"sort_mode": "<sort_key, rowid>"
72327267
}
72337268
}
@@ -7591,6 +7626,7 @@ ORDER BY d1.f2 DESC LIMIT 30 {
75917626
"rows": 31,
75927627
"examined_rows": 500,
75937628
"number_of_tmp_files": 0,
7629+
"sort_buffer_size": 6944,
75947630
"sort_mode": "<sort_key, additional_fields>"
75957631
}
75967632
}
@@ -7619,6 +7655,7 @@ ORDER BY d1.f2 DESC LIMIT 30 {
76197655
"rows": 31,
76207656
"examined_rows": 620,
76217657
"number_of_tmp_files": 0,
7658+
"sort_buffer_size": 6727,
76227659
"sort_mode": "<sort_key, rowid>"
76237660
}
76247661
}
@@ -7881,6 +7918,7 @@ SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 1) {
78817918
"rows": 2,
78827919
"examined_rows": 500,
78837920
"number_of_tmp_files": 0,
7921+
"sort_buffer_size": 36,
78847922
"sort_mode": "<sort_key, additional_fields>"
78857923
}
78867924
}
@@ -11637,6 +11675,7 @@ SELECT * FROM t1 WHERE f1 = (SELECT f1 FROM t1 ORDER BY 1 LIMIT 2) {
1163711675
"rows": 3,
1163811676
"examined_rows": 500,
1163911677
"number_of_tmp_files": 0,
11678+
"sort_buffer_size": 54,
1164011679
"sort_mode": "<sort_key, additional_fields>"
1164111680
}
1164211681
}
@@ -11903,6 +11942,7 @@ SELECT * FROM t1 ORDER BY f2 LIMIT 100 {
1190311942
"rows": 101,
1190411943
"examined_rows": 438500,
1190511944
"number_of_tmp_files": 0,
11945+
"sort_buffer_size": 21715,
1190611946
"sort_mode": "<sort_key, rowid>"
1190711947
}
1190811948
}

‎mysql-test/suite/opt_trace/r/general2_no_prot.result‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1119,6 +1119,7 @@ TRACE
11191119
"rows": 4,
11201120
"examined_rows": 4,
11211121
"number_of_tmp_files": 0,
1122+
"sort_buffer_size": 252,
11221123
"sort_mode": "<sort_key, rowid>"
11231124
} /* filesort_summary */
11241125
}
@@ -1354,6 +1355,7 @@ TRACE
13541355
"rows": 3,
13551356
"examined_rows": 3,
13561357
"number_of_tmp_files": 0,
1358+
"sort_buffer_size": 273,
13571359
"sort_mode": "<sort_key, rowid>"
13581360
} /* filesort_summary */
13591361
}
@@ -4860,6 +4862,7 @@ GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk {
48604862
"rows": 8,
48614863
"examined_rows": 8,
48624864
"number_of_tmp_files": 0,
4865+
"sort_buffer_size": 378,
48634866
"sort_mode": "<sort_key, rowid>"
48644867
} /* filesort_summary */
48654868
}

‎mysql-test/suite/opt_trace/r/general2_ps_prot.result‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1137,6 +1137,7 @@ TRACE
11371137
"rows": 4,
11381138
"examined_rows": 4,
11391139
"number_of_tmp_files": 0,
1140+
"sort_buffer_size": 252,
11401141
"sort_mode": "<sort_key, rowid>"
11411142
} /* filesort_summary */
11421143
}
@@ -1372,6 +1373,7 @@ TRACE
13721373
"rows": 3,
13731374
"examined_rows": 3,
13741375
"number_of_tmp_files": 0,
1376+
"sort_buffer_size": 273,
13751377
"sort_mode": "<sort_key, rowid>"
13761378
} /* filesort_summary */
13771379
}
@@ -4913,6 +4915,7 @@ GROUP BY field2 ORDER BY alias1.col_int_key,alias2.pk {
49134915
"rows": 8,
49144916
"examined_rows": 8,
49154917
"number_of_tmp_files": 0,
4918+
"sort_buffer_size": 378,
49164919
"sort_mode": "<sort_key, rowid>"
49174920
} /* filesort_summary */
49184921
}

‎mysql-test/suite/opt_trace/r/general_no_prot_none.result‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5835,6 +5835,7 @@ trace
58355835
"rows": 2,
58365836
"examined_rows": 2,
58375837
"number_of_tmp_files": 0,
5838+
"sort_buffer_size": 252,
58385839
"sort_mode": "<sort_key, rowid>"
58395840
} /* filesort_summary */
58405841
}

‎mysql-test/suite/opt_trace/r/general_ps_prot_none.result‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5793,6 +5793,7 @@ trace
57935793
"rows": 2,
57945794
"examined_rows": 2,
57955795
"number_of_tmp_files": 0,
5796+
"sort_buffer_size": 252,
57965797
"sort_mode": "<sort_key, rowid>"
57975798
} /* filesort_summary */
57985799
}

0 commit comments

Comments
 (0)