← Back to team overview

maria-discuss team mailing list archive

Re: Performance issues after upgrading to 10.0

 

Yes, data are approximately the same.





Citát Rhys.Campbell@xxxxxxxxxxxx:

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





References