Re: [Maria-developers] [Commits] 167a96b5157: MDEV-9959: A serious MariaDB server performance bug
On Tue, Mar 27, 2018 at 04:48:10PM +0530, Varun wrote:
> revision-id: 167a96b5157049408a6ad4bca7abcd376af93fb5
> (mariadb-10.3.0-644-g167a96b5157)
> parent(s): 4359c6b4806605c78987e50cab3a6b42016b7603
> author: Varun Gupta
> committer: Varun Gupta
> timestamp: 2018-03-27 16:45:46 +0530
> message:
>
> MDEV-9959: A serious MariaDB server performance bug
>
> step#1: if a derived table has SELECT DISTINCT, provide index statistics for
> it so that the join optimizer in the
> upper select knows that ref access to the table will produce one row.
>
> Added handling for multiple selects in the derived table
>
> ---
> mysql-test/r/cte_nonrecursive.result| 8 +--
> mysql-test/r/cte_recursive.result | 6 +-
> mysql-test/r/derived_cond_pushdown.result | 82
> -
> mysql-test/r/derived_view.result| 2 +-
> mysql-test/r/join_cache.result | 6 +-
> mysql-test/r/mdev9959.result| 46 ++
> mysql-test/r/subselect_extra.result | 2 +-
> mysql-test/r/subselect_extra_no_semijoin.result | 2 +-
> mysql-test/t/mdev9959.test | 25
> sql/sql_lex.h | 2 +
> sql/sql_union.cc| 60 ++
> sql/sql_yacc.yy | 3 +
> sql/table.cc| 19 ++
> 13 files changed, 209 insertions(+), 54 deletions(-)
>
> diff --git a/mysql-test/r/derived_cond_pushdown.result
> b/mysql-test/r/derived_cond_pushdown.result
> index 32d3c88cc8d..3723e25a494 100644
> --- a/mysql-test/r/derived_cond_pushdown.result
> +++ b/mysql-test/r/derived_cond_pushdown.result
> @@ -5187,7 +5187,7 @@ explain select * from v2_union as v,t2 where
> ((v.a=6) or (v.a=8)) and (v.c>200) and (v.a=t2.a);
> id select_type table typepossible_keys key key_len ref
> rowsExtra
> 1PRIMARY t2 ALL NULLNULLNULLNULL9 Using
> where
> -1PRIMARY ref key0key05 test.t2.a
> 6 Using where
> +1PRIMARY ref key0key05 test.t2.a
> 1 Using where
As agreed on the call: need to check what is the cause of this. Here, the temp
table has distinct rows, that is, {a,b,c} are distinct. But ref access only
uses the first component, where does rows=1 come from? (if this is how
best_access_path computes an estimate for prefix when it only has the estimate
for the full key ... fine)
> 2DERIVED t1 ALL NULLNULLNULLNULL20 Using
> where; Using temporary; Using filesort
> 3UNION t1 ALL NULLNULLNULLNULL20 Using
> where; Using temporary; Using filesort
> 4UNION t1 ALL NULLNULLNULLNULL20 Using
> where; Using temporary; Using filesort
> @@ -5213,7 +5213,7 @@ EXPLAIN
>"key_length": "5",
>"used_key_parts": ["a"],
>"ref": ["test.t2.a"],
> - "rows": 6,
> + "rows": 1,
>"filtered": 100,
>"attached_condition": "v.c > 200",
>"materialized": {
> @@ -5358,7 +5358,7 @@ a b c a b c d
> explain select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6);
> id select_type table typepossible_keys key key_len ref
> rowsExtra
> 1PRIMARY t2 ALL NULLNULLNULLNULL9 Using
> where
> -1PRIMARY ref key0key05 test.t2.a
> 4 Using where
> +1PRIMARY ref key0key05 test.t2.a
> 1 Using where
> 2DERIVED t1 ALL NULLNULLNULLNULL20 Using
> where
> 3UNION t1 ALL NULLNULLNULLNULL20 Using
> where
> NULL UNION RESULT ALL NULLNULLNULLNULL
> NULL
> @@ -5382,7 +5382,7 @@ EXPLAIN
>"key_length": "5",
>"used_key_parts": ["a"],
>"ref": ["test.t2.a"],
> - "rows": 4,
> + "rows": 1,
>"filtered": 100,
>"attached_condition": "v.c > 6",
>"materialized": {
> @@ -5476,7 +5476,7 @@ a b c a b c d
> explain select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or
> (v.b<20));
> id select_type table typepossible_keys key key_len ref
> rowsExtra
> 1PRIMARY t2 ALL NULLNULLNULLNULL9 Using
> where
> -1PRIMARY ref key0key05 test.t2.a
> 4 Using where
> +1PRIMARY ref key0key05 test.t2.a
> 1 Using where
> 2DERIVED t1 ALL NULLNULLNULLNULL20 Using
> where
> 3UNION t1 ALL NULLNULLNULLNULL20 Using
> where
> NULL UNION RESULT ALL NULLNULLN
Re: [Maria-developers] [Commits] 167a96b5157: MDEV-9959: A serious MariaDB server performance bug
On Tue, Mar 27, 2018 at 04:48:10PM +0530, Varun wrote: > revision-id: 167a96b5157049408a6ad4bca7abcd376af93fb5 > (mariadb-10.3.0-644-g167a96b5157) > parent(s): 4359c6b4806605c78987e50cab3a6b42016b7603 > author: Varun Gupta > committer: Varun Gupta > timestamp: 2018-03-27 16:45:46 +0530 > message: > > MDEV-9959: A serious MariaDB server performance bug > > step#1: if a derived table has SELECT DISTINCT, provide index statistics for > it so that the join optimizer in the > upper select knows that ref access to the table will produce one row. > > Added handling for multiple selects in the derived table ... > diff --git a/mysql-test/r/mdev9959.result b/mysql-test/r/mdev9959.result > new file mode 100644 > index 000..049e0350cca > --- /dev/null > +++ b/mysql-test/r/mdev9959.result > @@ -0,0 +1,46 @@ > +create table t1(a int); > +insert into t1 values (1),(2),(3),(4),(5),(6); > +create table t2(a int, b int,c int); > +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); > +create table t3(a int, b int); > +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); > +table "" should have type=ref and rows=1 > +one select in derived table > +with distinct > +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q > where t1.a=q.a; > +id select_type table typepossible_keys key key_len ref > rowsr_rows filteredr_filtered Extra > +1PRIMARY t1 ALL NULLNULLNULLNULL6 6.00 > 100.00 100.00 Using where > +1PRIMARY ref key0key05 test.t1.a > 1 1.00100.00 100.00 > +2DERIVED t2 ALL NULLNULLNULLNULL6 6.00 > 100.00 100.00 Using temporary; Using filesort > +# multiple selects in derived table > +# NO UNION ALL > +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union > (select t2.a,t2.b from t2 order by c))q where t1.a=q.a; > +id select_type table typepossible_keys key key_len ref > rowsr_rows filteredr_filtered Extra > +1PRIMARY t1 ALL NULLNULLNULLNULL6 6.00 > 100.00 100.00 Using where > +1PRIMARY ref key0key05 test.t1.a > 1 1.00100.00 100.00 > +2DERIVED t2 ALL NULLNULLNULLNULL6 6.00 > 100.00 100.00 > +3UNION t2 ALL NULLNULLNULLNULL6 6.00 > 100.00 100.00 > +NULL UNION RESULT ALL NULLNULLNULLNULL > NULL6.00NULLNULL How would you explain the above? The derived table is a union of two selects, each of which produces 6 rows. Neither of the selects have DISTINCT attribute. Why does the optimization fire and set ref=1 in this case? (if both parts of UNION had distinct, this would make sense. But if neither does, I don't see any logic) BR Sergei -- Sergei Petrunia, Software Developer MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog ___ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
