Yes, data are approximately the same. Citát Rhys.Campbell@swisscom.com:
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@lists.launchpad.net] On Behalf Of azurit@pobox.sk Sent: Thursday, February 11, 2016 4:13 PM Cc: maria-discuss@lists.launchpad.net 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@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 <azurit@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