maria-discuss team mailing list archive
-
maria-discuss team
-
Mailing list archive
-
Message #03325
Re: Performance issues after upgrading to 10.0
The cardinality for the brand_id index is reported as hugely different. 188 V 48555. Do these tables definitely contain the same data?
-----Original Message-----
From: Maria-discuss [mailto:maria-discuss-bounces+rhys.campbell=swisscom.com@xxxxxxxxxxxxxxxxxxx] On Behalf Of azurit@xxxxxxxx
Sent: Thursday, February 11, 2016 4:13 PM
Cc: maria-discuss@xxxxxxxxxxxxxxxxxxx
Subject: Re: [Maria-discuss] Performance issues after upgrading to 10.0
From 5.5:
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
brands 0 PRIMARY 1 brand_id
A 149 NULL NULL BTREE
Table Non_unique Key_name Seq_in_index Column_name
Collation Cardinality Sub_part Packed Null Index_type
Comment Index_comment
products 0 PRIMARY 1 product_id A
48555 NULL NULL BTREE
products 1 category_id 1 category_id A
48555 NULL NULL YES BTREE
products 1 featured_image_id 1 featured_image_id A
48555 NULL NULL YES BTREE
products 1 brand_id 1 brand_id A
48555 NULL NULL YES BTREE
products 1 slug 1 slug A
48555 100 NULL BTREE
products 1 hidden 1 hidden A
48555 NULL NULL BTREE
From 10.0:
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| brands | 0 | PRIMARY | 1 | brand_id | A
| 149 | NULL | NULL | | BTREE | |
|
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index |
Column_name | Collation | Cardinality | Sub_part | Packed | Null
| Index_type | Comment | Index_comment |
+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| products | 0 | PRIMARY | 1 |
product_id | A | 61882 | NULL | NULL |
| BTREE | | |
| products | 1 | category_id | 1 |
category_id | A | 10 | NULL | NULL | YES
| BTREE | | |
| products | 1 | featured_image_id | 1 |
featured_image_id | A | 61882 | NULL | NULL | YES
| BTREE | | |
| products | 1 | brand_id | 1 | brand_id
| A | 188 | NULL | NULL | YES | BTREE
| | |
| products | 1 | slug | 1 | slug
| A | 61882 | 100 | NULL | | BTREE
| | |
| products | 1 | hidden | 1 | hidden
| A | 2 | NULL | NULL | | BTREE
| | |
+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
I'm running MariaDB 10.0.23 from Debian Jessie.
Upgrade was done correctly with mysql_upgrade (this was done by Debian itself, i just upgraded the OS by standard way but, as i said, Debian packages are doing correct upgrade and then 'check table for upgrade'
for every table). But i also done OPTIMIZE TABLE for every table - no luck.
Citát Guillaume Lefranc <guillaume.lefranc@xxxxxxxxxxx>:
> Can you post the output of:
>
> SHOW INDEX IN brands;
> SHOW INDEX IN products;
>
> for both databases?
>
> And IMHO, if this is the case this would warrant a bug report. Are you
> running the latest 10.0 release?
>
> On Thu, Feb 11, 2016 at 3:44 PM <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;
>>
>> Table structures:
>>
>> CREATE TABLE `brands` (
>> `brand_id` int(11) NOT NULL AUTO_INCREMENT,
>> `title` varchar(50) NOT NULL,
>> `slug` varchar(50) NOT NULL,
>> `description` text NOT NULL,
>> `discount_suv_1` float NOT NULL,
>> `discount_suv_2` float NOT NULL,
>> `discount_van_1` float NOT NULL,
>> `discount_van_2` float NOT NULL,
>> `discount_orp_1` float NOT NULL,
>> `discount_orp_2` float NOT NULL,
>> PRIMARY KEY (`brand_id`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8
>>
>>
>> CREATE TABLE `products` (
>> `product_id` int(11) NOT NULL AUTO_INCREMENT,
>> `category_id` int(11) DEFAULT NULL,
>> `featured_image_id` int(11) DEFAULT NULL,
>> `brand_id` int(11) DEFAULT NULL,
>> `title` text NOT NULL,
>> `slug` text NOT NULL,
>> `price` float NOT NULL,
>> `custom_price` float NOT NULL,
>> `description` text NOT NULL,
>> `hidden` tinyint(1) NOT NULL,
>> `homepage` tinyint(1) NOT NULL,
>> `sale` tinyint(1) NOT NULL,
>> `width` float NOT NULL,
>> `height` int(11) NOT NULL,
>> `diameter` int(11) NOT NULL,
>> `resistance` varchar(50) NOT NULL,
>> `breaking_distance` varchar(50) NOT NULL,
>> `noise` varchar(50) NOT NULL,
>> `ean` varchar(50) NOT NULL,
>> `availability` char(1) NOT NULL,
>> `li` varchar(15) NOT NULL,
>> `si` varchar(15) NOT NULL,
>> `classes` varchar(5) NOT NULL,
>> `all_year` tinyint(1) NOT NULL,
>> `type` char(3) NOT NULL,
>> `holes_count` float NOT NULL,
>> `pitch` float NOT NULL,
>> `et` float NOT NULL,
>> `code` varchar(50) NOT NULL,
>> `custom` tinyint(1) NOT NULL,
>> `car_type` varchar(100) NOT NULL,
>> `models` text NOT NULL,
>> `imported` tinyint(1) NOT NULL,
>> `is_runflat` tinyint(1) NOT NULL,
>> `stock_count` int(11) NOT NULL,
>> `parser` char(1) NOT NULL,
>> PRIMARY KEY (`product_id`),
>> KEY `category_id` (`category_id`),
>> KEY `featured_image_id` (`featured_image_id`),
>> KEY `brand_id` (`brand_id`),
>> KEY `slug` (`slug`(100)),
>> KEY `hidden` (`hidden`),
>> CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`)
>> REFERENCES `categories` (`category_id`) ON DELETE SET NULL,
>> CONSTRAINT `products_ibfk_4` FOREIGN KEY (`featured_image_id`)
>> REFERENCES `product_images` (`product_image_id`) ON DELETE SET NULL,
>> CONSTRAINT `products_ibfk_5` FOREIGN KEY (`brand_id`) REFERENCES
>> `brands` (`brand_id`) ON DELETE SET NULL
>> ) ENGINE=InnoDB AUTO_INCREMENT=63638 DEFAULT CHARSET=utf8
>>
>>
>>
>> Table brands: 149 rows
>> Table products: 62074 rows
>>
>>
>>
>> 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
>>
>>
>> Any hints what's going on? What about the difference in the explain
>> command?
>>
>> Thank you.
>>
>> azur
>>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help : https://help.launchpad.net/ListHelp
>>
> --
> Guillaume Lefranc
> Remote DBA Services Manager
> MariaDB Corporation
_______________________________________________
Mailing list: https://launchpad.net/~maria-discuss
Post to : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help : https://help.launchpad.net/ListHelp
Follow ups
References
