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