maria-discuss team mailing list archive

[<Rhys.Campbell@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxxx] On Behalf Of azurit@xxxxxxxx
Sent: Thursday, February 11, 2016 3:44 PM
To: maria-discuss@xxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp