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....@lists.launchpad.net] On Behalf Of azu...@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
