maria-discuss team mailing list archive

[Guillaume Lefranc <guillaume.lefranc@xxxxxxxxxxx>]

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@xxxxxxxxxxxx> 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@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
>
> _______________________________________________
> 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
>
-- 
Guillaume Lefranc
Remote DBA Services Manager
MariaDB Corporation