Re: [Maria-discuss] Performance issues after upgrading to 10.0

azurit Thu, 11 Feb 2016 07:13:32 -0800

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_idA 149 NULL NULL BTREE

Table Non_unique Key_name Seq_in_index Column_nameCollation Cardinality Sub_part Packed Null Index_typeComment Index_commentproducts 0 PRIMARY 1 product_id A48555 NULL NULL BTREEproducts 1 category_id 1 category_id A48555 NULL NULL YES BTREEproducts 1 featured_image_id 1 featured_image_id A48555 NULL NULL YES BTREEproducts 1 brand_id 1 brand_id A48555 NULL NULL YES BTREEproducts 1 slug 1 slug A48555 100 NULL BTREEproducts 1 hidden 1 hidden A48555 NULL NULL BTREE








From 10.0:

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| brands | 0 | PRIMARY | 1 | brand_id | A| 149 | NULL | NULL | | BTREE | ||

+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+



+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

+----------+------------+-------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| 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 Debianitself, i just upgraded the OS by standard way but, as i said, Debianpackages are doing correct upgrade and then 'check table for upgrade'for every table). But i also done OPTIMIZE TABLE for every table - noluck.





Citát Guillaume Lefranc <guillaume.lefr...@mariadb.com>:

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 <azu...@pobox.sk> 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@lists.launchpad.net
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@lists.launchpad.net
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Re: [Maria-discuss] Performance issues after upgrading to 10.0

Reply via email to