maria-discuss team mailing list archive

[azurit@xxxxxxxx]

Here it is, the command was:
RESET QUERY CACHE; FLUSH STATUS; SELECT b.*, p.category_id FROM brands  
b JOIN products p USING(brand_id) GROUP BY category_id, brand_id ORDER  
BY title ASC; SHOW STATUS LIKE 'Handler%';



5.5
---
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 11051 |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 190   |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 62730 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 62508 |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+





10.0
----
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 150   |
| Handler_read_last          | 0     |
| Handler_read_next          | 62206 |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 187   |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 339   |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 62206 |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+






I already tried to remove order by and also group by but it didn't  
make any difference related to speed.





Citát Sergey Petrunia <sergey@xxxxxxxxxxx>:

> On Thu, Feb 11, 2016 at 03:43:54PM +0100, azurit@xxxxxxxx wrote:
> > Hi,
> >
> > we upgraded MariaDB 5.5 to 10.0 on one server last night and are now
> > dealing with severe performance issues. Here is one case where it's
> > notable:
> >
> > Query - SELECT b.*, p.category_id FROM brands b JOIN products p
> > USING(brand_id) GROUP BY category_id, brand_id ORDER BY title ASC;
> ...
> > Query time on 5.5: about 0.2s
> > Query time on 10.0: between 2 and 9s (if not cached)
> >
> > Explain on 5.5: http://watchdog.sk/5.5.png
> > Explain on 10.0: http://watchdog.sk/10.png
> This is weird, query plans look the same. However, p.rows = 376, which means
> that on version 10.0 the optimizer expects to read a lot more rows.
>
> Can you run the queries again (on both 5.5 and 10.0) and check how many rows
> they actually read? This can be done as follows:
>
> FLUSH STATUS;
> <query>;
> SHOW STATUS LIKE 'Handler%';
>
> I would also try removing the ORDER BY clause and see if the difference in
> query execution time is still there.
>
> BR
>  Sergei
> --
> Sergei Petrunia, Software Developer
> MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog