Very slow create/alter table with partitions and STATS_PERSISTENT=1
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
|||
Percona Server moved to https://jira.percona.com/projects/PS |
Fix Released
|
Medium
|
Unassigned | ||
5.1 |
Invalid
|
Undecided
|
Unassigned | ||
5.5 |
Invalid
|
Undecided
|
Unassigned | ||
5.6 |
Fix Released
|
Medium
|
Unassigned |
Bug Description
Percona Server:
=============
jan@jan-
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 Source distribution
Copyright (c) 2009-2013 Percona LLC and/or its affiliates
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test;
Database changed
mysql> CREATE TABLE `queries` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `run` varchar(32) NOT NULL DEFAULT '',
-> `query` text NOT NULL,
-> `parsed_query_md5` varchar(32) NOT NULL DEFAULT '',
-> `query_time` float(13,2) NOT NULL DEFAULT '0.00',
-> `timeline` float(13,2) NOT NULL DEFAULT '0.00',
-> `db_name` varchar(64) NOT NULL DEFAULT '',
-> `id_db_access` int(10) unsigned NOT NULL DEFAULT '0',
-> `id_backtrace` int(10) unsigned NOT NULL DEFAULT '0',
-> `id_license_domain` int(10) unsigned NOT NULL DEFAULT '0',
-> `id_license` int(10) unsigned NOT NULL DEFAULT '0',
-> `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
-> `date` datetime NOT NULL DEFAULT '1980-01-01 00:00:00',
-> `day` tinyint(3) unsigned NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`,`day`),
-> KEY `run` (`run`),
-> KEY `query_time` (`id_license_
-> KEY `date` (`id_license_
-> KEY `date_2` (`date`),
-> KEY `id_license_domain` (`id_license_
-> KEY `id_license_
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY LIST ( day)
-> (PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
-> PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
-> PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
-> PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
-> PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
-> PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
-> PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
-> PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
-> PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
-> PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
-> PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
-> PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
-> PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
-> PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
-> PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
-> PARTITION p15 VALUES IN (15) ENGINE = InnoDB,
-> PARTITION p16 VALUES IN (16) ENGINE = InnoDB,
-> PARTITION p17 VALUES IN (17) ENGINE = InnoDB,
-> PARTITION p18 VALUES IN (18) ENGINE = InnoDB,
-> PARTITION p19 VALUES IN (19) ENGINE = InnoDB,
-> PARTITION p20 VALUES IN (20) ENGINE = InnoDB,
-> PARTITION p21 VALUES IN (21) ENGINE = InnoDB,
-> PARTITION p22 VALUES IN (22) ENGINE = InnoDB,
-> PARTITION p23 VALUES IN (23) ENGINE = InnoDB,
-> PARTITION p24 VALUES IN (24) ENGINE = InnoDB,
-> PARTITION p25 VALUES IN (25) ENGINE = InnoDB,
-> PARTITION p26 VALUES IN (26) ENGINE = InnoDB,
-> PARTITION p27 VALUES IN (27) ENGINE = InnoDB,
-> PARTITION p28 VALUES IN (28) ENGINE = InnoDB,
-> PARTITION p29 VALUES IN (29) ENGINE = InnoDB,
-> PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */
-> ;
Query OK, 0 rows affected (3 min 42.08 sec)
InnoDB:
======
jan@jan-
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.8-MariaDB-log Source distribution
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> CREATE TABLE `queries` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `run` varchar(32) NOT NULL DEFAULT '',
-> `query` text NOT NULL,
-> `parsed_query_md5` varchar(32) NOT NULL DEFAULT '',
-> `query_time` float(13,2) NOT NULL DEFAULT '0.00',
-> `timeline` float(13,2) NOT NULL DEFAULT '0.00',
-> `db_name` varchar(64) NOT NULL DEFAULT '',
-> `id_db_access` int(10) unsigned NOT NULL DEFAULT '0',
-> `id_backtrace` int(10) unsigned NOT NULL DEFAULT '0',
-> `id_license_domain` int(10) unsigned NOT NULL DEFAULT '0',
-> `id_license` int(10) unsigned NOT NULL DEFAULT '0',
-> `type` tinyint(3) unsigned NOT NULL DEFAULT '0',
-> `date` datetime NOT NULL DEFAULT '1980-01-01 00:00:00',
-> `day` tinyint(3) unsigned NOT NULL DEFAULT '0',
-> PRIMARY KEY (`id`,`day`),
-> KEY `run` (`run`),
-> KEY `query_time` (`id_license_
-> KEY `date` (`id_license_
-> KEY `date_2` (`date`),
-> KEY `id_license_domain` (`id_license_
-> KEY `id_license_
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> /*!50100 PARTITION BY LIST ( day)
-> (PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
-> PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
-> PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
-> PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
-> PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
-> PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
-> PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
-> PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
-> PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
-> PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
-> PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
-> PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
-> PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
-> PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
-> PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
-> PARTITION p15 VALUES IN (15) ENGINE = InnoDB,
-> PARTITION p16 VALUES IN (16) ENGINE = InnoDB,
-> PARTITION p17 VALUES IN (17) ENGINE = InnoDB,
-> PARTITION p18 VALUES IN (18) ENGINE = InnoDB,
-> PARTITION p19 VALUES IN (19) ENGINE = InnoDB,
-> PARTITION p20 VALUES IN (20) ENGINE = InnoDB,
-> PARTITION p21 VALUES IN (21) ENGINE = InnoDB,
-> PARTITION p22 VALUES IN (22) ENGINE = InnoDB,
-> PARTITION p23 VALUES IN (23) ENGINE = InnoDB,
-> PARTITION p24 VALUES IN (24) ENGINE = InnoDB,
-> PARTITION p25 VALUES IN (25) ENGINE = InnoDB,
-> PARTITION p26 VALUES IN (26) ENGINE = InnoDB,
-> PARTITION p27 VALUES IN (27) ENGINE = InnoDB,
-> PARTITION p28 VALUES IN (28) ENGINE = InnoDB,
-> PARTITION p29 VALUES IN (29) ENGINE = InnoDB,
-> PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */
-> ;
Query OK, 0 rows affected (4.86 sec)
Test case:
========
CREATE TABLE `queries` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`run` varchar(32) NOT NULL DEFAULT '',
`query` text NOT NULL,
`parsed_
`query_time` float(13,2) NOT NULL DEFAULT '0.00',
`timeline` float(13,2) NOT NULL DEFAULT '0.00',
`db_name` varchar(64) NOT NULL DEFAULT '',
`id_db_access` int(10) unsigned NOT NULL DEFAULT '0',
`id_backtrace` int(10) unsigned NOT NULL DEFAULT '0',
`id_license_
`id_license` int(10) unsigned NOT NULL DEFAULT '0',
`type` tinyint(3) unsigned NOT NULL DEFAULT '0',
`date` datetime NOT NULL DEFAULT '1980-01-01 00:00:00',
`day` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`,`day`),
KEY `run` (`run`),
KEY `query_time` (`id_license_
KEY `date` (`id_license_
KEY `date_2` (`date`),
KEY `id_license_domain` (`id_license_
KEY `id_license_
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST ( day)
(PARTITION p0 VALUES IN (0) ENGINE = InnoDB,
PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
PARTITION p4 VALUES IN (4) ENGINE = InnoDB,
PARTITION p5 VALUES IN (5) ENGINE = InnoDB,
PARTITION p6 VALUES IN (6) ENGINE = InnoDB,
PARTITION p7 VALUES IN (7) ENGINE = InnoDB,
PARTITION p8 VALUES IN (8) ENGINE = InnoDB,
PARTITION p9 VALUES IN (9) ENGINE = InnoDB,
PARTITION p10 VALUES IN (10) ENGINE = InnoDB,
PARTITION p11 VALUES IN (11) ENGINE = InnoDB,
PARTITION p12 VALUES IN (12) ENGINE = InnoDB,
PARTITION p13 VALUES IN (13) ENGINE = InnoDB,
PARTITION p14 VALUES IN (14) ENGINE = InnoDB,
PARTITION p15 VALUES IN (15) ENGINE = InnoDB,
PARTITION p16 VALUES IN (16) ENGINE = InnoDB,
PARTITION p17 VALUES IN (17) ENGINE = InnoDB,
PARTITION p18 VALUES IN (18) ENGINE = InnoDB,
PARTITION p19 VALUES IN (19) ENGINE = InnoDB,
PARTITION p20 VALUES IN (20) ENGINE = InnoDB,
PARTITION p21 VALUES IN (21) ENGINE = InnoDB,
PARTITION p22 VALUES IN (22) ENGINE = InnoDB,
PARTITION p23 VALUES IN (23) ENGINE = InnoDB,
PARTITION p24 VALUES IN (24) ENGINE = InnoDB,
PARTITION p25 VALUES IN (25) ENGINE = InnoDB,
PARTITION p26 VALUES IN (26) ENGINE = InnoDB,
PARTITION p27 VALUES IN (27) ENGINE = InnoDB,
PARTITION p28 VALUES IN (28) ENGINE = InnoDB,
PARTITION p29 VALUES IN (29) ENGINE = InnoDB,
PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */
ALTER TABLE queries ENGINE=InnoDB;
summary: |
- XtraDB very sllow on create table/alter table compared to InnoDB + XtraDB very slow on create table/alter table compared to InnoDB |
tags: | added: performance xtradb |
summary: |
- XtraDB very slow on create table/alter table compared to InnoDB + Very slow create/alter table with partitions and STATS_PERSISTENT=1 |
Used configuration
[mysqld] mysql/pdb/ err.log mysql/share/ english mysql/pdb/ mysql.sock2 mysql/pdb/ hostname. pid2 dir=/usr/ local/mysql/ share
datadir = /home/jan/mysql/pdb
log-err = /home/jan/
language = /usr/local/
socket = /home/jan/
port = 3306
pid-file = /home/jan/
lc_messages_
lc_messages=en_US