Percona Server moved to https://jira.percona.com/projects/PS

Very slow create/alter table with partitions and STATS_PERSISTENT=1

Bug #1296551 reported by Jan Lindström
8
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server moved to https://jira.percona.com/projects/PS
Fix Released
Medium
Unassigned
Percona Server moved to https://jira.percona.com/projects/PS 5.6.17-65.0
5.1
Invalid
Undecided
Unassigned
5.5
Invalid
Undecided
Unassigned
5.6
Fix Released
Medium
Unassigned
Percona Server moved to https://jira.percona.com/projects/PS 5.6.17-65.0

Bug Description

Percona Server:
=============

jan@jan-GE70-0NC-0ND ~/mysql/pdb $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/pdb/mysql.sock2
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_domain`,`query_time`),
    -> KEY `date` (`id_license_domain`,`date`),
    -> KEY `date_2` (`date`),
    -> KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`),
    -> KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`)
    -> ) 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-GE70-0NC-0ND ~/mysql/pdb $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/db10/mysql.sock2
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_domain`,`query_time`),
    -> KEY `date` (`id_license_domain`,`date`),
    -> KEY `date_2` (`date`),
    -> KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`),
    -> KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`)
    -> ) 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_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_domain`,`query_time`),
  KEY `date` (`id_license_domain`,`date`),
  KEY `date_2` (`date`),
  KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`),
  KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`)
) 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;

Revision history for this message
Jan Lindström (jplindst) wrote :

Used configuration

[mysqld]
datadir = /home/jan/mysql/pdb
log-err = /home/jan/mysql/pdb/err.log
language = /usr/local/mysql/share/english
socket = /home/jan/mysql/pdb/mysql.sock2
port = 3306
pid-file = /home/jan/mysql/pdb/hostname.pid2
lc_messages_dir=/usr/local/mysql/share
lc_messages=en_US

Revision history for this message
Jan Lindström (jplindst) wrote :

Results from alter table:

jan@jan-GE70-0NC-0ND ~/mysql/pdb $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/pdb/mysql.sock2
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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (4 min 34.69 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> exit
Bye
jan@jan-GE70-0NC-0ND ~/mysql/pdb $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/db10/mysql.sock2
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;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (11.98 sec)
Records: 0 Duplicates: 0 Warnings: 0

Revision history for this message
Jan Lindström (jplindst) wrote :

Relates to: https://mariadb.atlassian.net/browse/MDEV-5838

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :
Download full text (4.0 KiB)

It's easy to show that there is a problem with any recent enough Percona Server 5.6:

openxs@ao756:~$ mysql -uroot -proot test
Warning: Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.6.15-63.0 Percona Server (GPL), Release 63.0

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> 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 NU `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_domain`,`query_time`),
    -> KEY `date` (`id_license_domain`,`date`),
    -> KEY `date_2` (`date`),
    -> KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`),
    -> ) ENGINE=Inno KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`)
    -> ) 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 -> p4 VA 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,
    ->...

Read more...

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :
Download full text (3.8 KiB)

Percona Server 5.5 (and XTraDB in it) does not seem affected:

openxs@ao756:~/dbs/p5.5$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.36-34.0 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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> 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_domain`,`query_time`),
    -> KEY `date` (`id_license_domain`,`date`),
    -> KEY `date_2` (`date`),
    -> KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`),
    -> KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`)
    -> ) 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 (2...

Read more...

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :
Download full text (4.0 KiB)

On the other hand, I have some reasons to think that this can be upstream problem. I wonder what exact InnoDB version do you have in that MariaDB 10.0.8? With upstream 5.6.16 (sorry, -debug) build I have at hand I see slow execution:

openxs@ao756:~/dbs/5.6$ bin/mysql --no-defaults -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16-debug MySQL Community Server (GPL)

Copyright (c) 2000, 2014, 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> 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_domain`,`query_time`),
    -> KEY `date` (`id_license_domain`,`date`),
    -> KEY `date_2` (`date`),
    -> KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`),
    -> KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`)
    -> ) 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 VALU...

Read more...

Revision history for this message
Jan Lindström (jplindst) wrote :
Download full text (4.0 KiB)

Hi,

We have 5.6.15 InnoDB:

jan@jan-GE70-0NC-0ND ~/mysql/db10 $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/db10/mysql.sock2
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.0.10-MariaDB-log Source distribution

Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> show variables like '%version%';
+-------------------------+---------------------+
| Variable_name | Value |
 +-------------------------+---------------------+
| innodb_version | 5.6.15 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 10.0.10-MariaDB-log |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_malloc_library | bundled jemalloc |
 +-------------------------+---------------------+
8 rows in set (0.00 sec)

MariaDB [test]> CREATE TABLE `queries2` ( `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_domain`,`query_time`), KEY `date` (`id_license_domain`,`date`), KEY `date_2` (`date`), KEY `id_license_domain` (`id_license_domain`,`parsed_query_md5`,`date`), KEY `id_license_domain_2` (`id_license_domain`,`parsed_query_md5`,`query_time`) ) 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, ...

Read more...

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

Yes, seems faster. But slow comparing to 5.5 anyway. Do we have to report upstream MySQL 5.6 bug here also?

Revision history for this message
Jan Lindström (jplindst) wrote :

Very slow create/alter table also with Oracle MySQL 5.6.16:

http://bugs.mysql.com/bug.php?id=72115

Laurynas Biveinis (laurynas-biveinis)
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
Revision history for this message
Jan Lindström (jplindst) wrote : Re: XtraDB very slow on create table/alter table compared to InnoDB
Download full text (5.5 KiB)

jan@jan-GE70-0NC-0ND ~/mysql-5.6.16 $ sudo opreport --demangle=smart --symbols --long-filenames --merge \
> tgid /home/jan/mysql-5.6.16/sql/mysqld | head -n 20
 Using /var/lib/oprofile/samples/ for samples directory.
warning: /no-vmlinux could not be found.
warning: [vdso] (tgid:29314 range:0x7fffe8931000-0x7fffe8933000) could not be found.
CPU: Intel Ivy Bridge microarchitecture, speed 2.401e+06 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
samples % image name symbol name
20581 64.5233 /no-vmlinux /no-vmlinux
2584 8.1011 /home/jan/mysql-5.6.16/sql/mysqld buf_calc_page_new_checksum(unsigned char const*)
672 2.1068 /lib/x86_64-linux-gnu/libc-2.17.so __memset_sse2
321 1.0064 /home/jan/mysql-5.6.16/sql/mysqld yylex()
284 0.8904 /lib/x86_64-linux-gnu/libc-2.17.so times
283 0.8872 /home/jan/mysql-5.6.16/sql/mysqld os_aio_linux_handle(unsigned long, fil_node_t**, void**, unsigned long*)
274 0.8590 /lib/x86_64-linux-gnu/libc-2.17.so __memcpy_ssse3_back
174 0.5455 /home/jan/mysql-5.6.16/sql/mysqld yyparse()
167 0.5236 /home/jan/mysql-5.6.16/sql/mysqld ut_delay(unsigned long)
160 0.5016 /home/jan/mysql-5.6.16/sql/mysqld buf_page_get_gen(unsigned long, unsigned long, unsigned long, unsigned long, buf_block_t*, unsigned long, char const*, unsigned long, mtr_t*)
159 0.4985 /lib/x86_64-linux-gnu/libc-2.17.so _int_malloc
152 0.4765 /home/jan/mysql-5.6.16/sql/mysqld rec_get_offsets_func(unsigned char const*, dict_index_t const*, unsigned long*, unsigned long, mem_block_info_t**)
140 0.4389 [vdso] (tgid:29314 range:0x7fffe8931000-0x7fffe8933000) [vdso] (tgid:29314 range:0x7fffe8931000-0x7fffe8933000)
133 0.4170 /home/jan/mysql-5.6.16/sql/mysqld log_group_write_buf(log_group_t*, unsigned char*, unsigned long, unsigned long, unsigned long)
119 0.3731 /lib/x86_64-linux-gnu/libpthread-2.17.so pthread_mutex_lock
117 0.3668 /lib/x86_64-linux-gnu/libc-2.17.so _int_free
108 0.3386 /home/jan/mysql-5.6.16/sql/mysqld mtr_commit(mtr_t*)
jan@jan-GE70-0NC-0ND ~/mysql-5.6.16 $ sudo opreport --demangle=smart --symbols --long-filenames --merge tgid /home/jan/mysql-5.6.16/sql/mysqld | head -n 30
Using /var/lib/oprofile/samples/ for samples directory.
warning: /no-vmlinux could not be found.
warning: [vdso] (tgid:29314 range:0x7fffe8931000-0x7fffe8933000) could not be found.
CPU: Intel Ivy Bridge microarchitecture, speed 2.401e+06 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (No unit mask) count 100000
samples % image name symbol name
21128 64.7860 /no-vmlinux /no-vmlinux
2584 7.9235 /home/jan/mysql-5.6.16/sql/mysqld buf_calc_page_new_checksum(unsigned char const*)
723 2.2170 /lib/x86_64-linux-gnu/libc-2.17.so __memset_sse2
321 0.9843 /home/jan/mysql-5.6.16/sql/mysqld yylex()
319 0.9782 /home/jan/mysql-5.6.16/sql/mysqld os_aio_linux_handle(unsigned long, fil_node_t**, void**, unsigned long*)
284 0.8708 /lib/x86_64-l...

Read more...

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Is there any XtraDB-specific performance regression against the same minor version of InnoDB?

Revision history for this message
Jan Lindström (jplindst) wrote :

I have not found proof for that.

Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

OK, if this is an upstream InnoDB, let's make this bug report a reference to http://bugs.mysql.com/bug.php?id=72115.

tags: added: innodb
removed: xtradb
tags: added: upstream
Laurynas Biveinis (laurynas-biveinis)
summary: - XtraDB very slow on create table/alter table compared to InnoDB
+ Very slow create/alter table with partitions and STATS_PERSISTENT=1
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1489

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.