[Maria-discuss] Performance issues after upgrading to 10.0
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
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
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
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
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
How did you perform the upgrade exactly? I'd probably first try rebuilding the tables/indexes with OPTIMIZE TABLE. Perhaps do a comparison of GLOBAL_VARIABLES if possible between the 5.5 and 10 versions. Rhys -----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 3:44 PM To: maria-discuss@lists.launchpad.net Subject: [Maria-discuss] Performance issues after upgrading to 10.0 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
Optimize Table is not needed, you just need to run mysql_upgrade to rebuild affected tables and/or statistics --GL On Thu, Feb 11, 2016 at 4:06 PM <Rhys.Campbell@swisscom.com> wrote:
How did you perform the upgrade exactly?
I'd probably first try rebuilding the tables/indexes with OPTIMIZE TABLE. Perhaps do a comparison of GLOBAL_VARIABLES if possible between the 5.5 and 10 versions.
Rhys
-----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 3:44 PM To: maria-discuss@lists.launchpad.net Subject: [Maria-discuss] Performance issues after upgrading to 10.0
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
_______________________________________________ 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
On Thu, Feb 11, 2016 at 03:43:54PM +0100, 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;
...
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
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@mariadb.com>:
On Thu, Feb 11, 2016 at 03:43:54PM +0100, 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;
...
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
participants (4)
-
azurit＠pobox.sk -
Guillaume Lefranc -
Rhys.Campbell＠swisscom.com -
Sergey Petrunia