| Bug #72115 | Very slow create/alter table with partitions and STATS_PERSISTENT=1 | ||
|---|---|---|---|
| Submitted: | 24 Mar 2014 19:29 | Modified: | 22 Jul 2015 7:17 |
| Reporter: | Jan Lindstrom | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.6.16 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Mar 2014 7:12]
Valeriy Kravchuk
This is what I see with 5.6.16 on 64-bit Windows 7 with Oracle binaries and binary log enabled: mysql> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> ALTER TABLE queries ENGINE=InnoDB; Query OK, 0 rows affected (1 min 54.96 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show profiles; +----------+--------------+-----------------------------------+ | Query_ID | Duration | Query | +----------+--------------+-----------------------------------+ | 1 | 114.94625875 | ALTER TABLE queries ENGINE=InnoDB | +----------+--------------+-----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show profile for query 1; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.024094 | | checking permissions | 0.000030 | | checking permissions | 0.023054 | | init | 0.000042 | | Opening tables | 0.672969 | | setup | 0.046957 | | creating table | 0.118509 | | After create | 0.000110 | | System lock | 86.725938 | | copy to tmp table | 0.708365 | | rename result table | 26.622773 | | end | 0.000044 | | Waiting for query cache lock | 0.000010 | | end | 0.000037 | | query end | 0.000279 | | closing tables | 0.000040 | | freeing items | 0.002828 | | cleaning up | 0.000183 | +------------------------------+-----------+ 18 rows in set, 1 warning (0.04 sec)
[25 Mar 2014 9:46]
Jan Lindstrom
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-linux-gnu/libc-2.17.so times 274 0.8402 /lib/x86_64-linux-gnu/libc-2.17.so __memcpy_ssse3_back 174 0.5335 /home/jan/mysql-5.6.16/sql/mysqld yyparse() 167 0.5121 /home/jan/mysql-5.6.16/sql/mysqld ut_delay(unsigned long) 160 0.4906 /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.4876 /lib/x86_64-linux-gnu/libc-2.17.so _int_malloc 152 0.4661 /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**) 146 0.4477 [vdso] (tgid:29314 range:0x7fffe8931000-0x7fffe8933000) [vdso] (tgid:29314 range:0x7fffe8931000-0x7fffe8933000) 133 0.4078 /home/jan/mysql-5.6.16/sql/mysqld log_group_write_buf(log_group_t*, unsigned char*, unsigned long, unsigned long, unsigned long) 121 0.3710 /lib/x86_64-linux-gnu/libpthread-2.17.so pthread_mutex_lock 117 0.3588 /lib/x86_64-linux-gnu/libc-2.17.so _int_free 108 0.3312 /home/jan/mysql-5.6.16/sql/mysqld mtr_commit(mtr_t*) 99 0.3036 /lib/x86_64-linux-gnu/libc-2.17.so __strlen_sse2_pminub 93 0.2852 /home/jan/mysql-5.6.16/sql/mysqld sync_array_print_long_waits(unsigned long*, void const**) 90 0.2760 /home/jan/mysql-5.6.16/sql/mysqld cmp_dtuple_rec_with_match_low(dtuple_t const*, unsigned char const*, unsigned long const*, unsigned long, unsigned long*, unsigned long*) 86 0.2637 /lib/x86_64-linux-gnu/libc-2.17.so __strcmp_sse42 86 0.2637 /lib/x86_64-linux-gnu/libc-2.17.so malloc 67 0.2054 /lib/x86_64-linux-gnu/libc-2.17.so vfprintf 66 0.2024 /home/jan/mysql-5.6.16/sql/mysqld btr_cur_search_to_nth_level(dict_index_t*, unsigned long, dtuple_t const*, unsigned long, unsigned long, btr_cur_t*, unsigned long, char const*, unsigned long, mtr_t*) 64 0.1962 /home/jan/mysql-5.6.16/sql/mysqld log_write_up_to(unsigned long, unsigned long, unsigned long) [clone .part.19] 64 0.1962 /home/jan/mysql-5.6.16/sql/mysqld page_cur_search_with_match(buf_block_t const*, dict_index_t const*, dtuple_t const*, unsigned long, unsigned long*, unsigned long*, unsigned long*, unsigned long*, page_cur_t*) 64 0.1962 /lib/x86_64-linux-gnu/libaio.so.1.0.1 /lib/x86_64-linux-gnu/libaio.so.1.0.1
[25 Mar 2014 11:46]
MySQL Verification Team
Here is my result on machine AMD Phenom(tm) 9650 Quad-Core Processor - 8 GB RAM OS Windows 7 64-bits:
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.17 Source distribution
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 5.6 > use test
Database changed
mysql 5.6 > 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 (19.66 sec)
mysql 5.6 > ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (43.40 sec)
Records: 0 Duplicates: 0 Warnings: 0
[25 Mar 2014 12:41]
MySQL Verification Team
Hello Jan, Thank you for the bug report and test case. Verified as described. Thanks, Umesh
[25 Mar 2014 12:42]
MySQL Verification Team
// Looks like buffer pool size matters
// innodb file per table enabled in below tests
## With innodb buffer pool size 8M
mysql> DROP TABLE IF EXISTS queries;
Query OK, 0 rows affected, 1 warning (0.00 sec)
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 (2 min 3.59 sec)
mysql>
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1 min 39.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set (0.00 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1 min 37.43 sec)
Records: 0 Duplicates: 0 Warnings: 0
## With innodb buffer pool size 128M
[root@cluster-repo mysql-5.6.16]# bin/mysql -u root -p test
Enter password:
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-log 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> DROP TABLE IF EXISTS queries;
Query OK, 0 rows affected (6.21 sec)
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 (1 min 17.60 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1 min 9.96 sec)
Records: 0 Duplicates: 0 Warnings: 0
[25 Mar 2014 14:55]
Arnaud Adant
Can not reproduced with 5.6.17 :
-> PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.20 sec)
mysql> alter table queries engine=InnoDB;
Query OK, 0 rows affected (0.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------------+
| log_bin | ON |
| log_bin_basename | /ssd2/aadant/data/helios03-bin |
| log_bin_index | /ssd2/aadant/data/helios03-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)
mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 8388608 |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)
[25 Mar 2014 22:42]
Arnaud Adant
This problem could be impacted by this bug fix in 5.6.17 : http://bugs.mysql.com/bug.php?id=70063 At least, it should decrease the CPU usage a bit.
[26 Mar 2014 6:46]
Marko Mäkelä
Which innodb_buffer_pool_size are you using? Can you repeat this with a larger buffer pool? It should be no surprise that InnoDB will be slow if you make it I/O bound by using a too small innodb_buffer_pool_size, or (for write-heavy workloads) too small innodb_log_file_size. In the OProfile output, the system seems to be spending a lot of time in the kernel (I suppose, reading and writing pages) and in the InnoDB page checksum calculator (reading or writing pages). The CRC32C checksum should be much faster than secure than the one you seem to be using. But, the root cause of the slowness seems to be a too small buffer pool for your workload.
[26 Mar 2014 11:29]
Valeriy Kravchuk
I think you should check all details with engineer who had verified the bug...
Anyway, in my case buffer pool was, indeed, small, 8M. But why should it matter that much to create empty table that takes 6M or so on disk (total size of .ibd files created for partitions)?
It's still slow with --no-defaults on fast enough Linux box (128M buffer pool), for example:
[openxs@chief 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.14 MySQL Community Server (GPL)
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> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)
mysql> CREATE TABLE `queries` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
...
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */;
Query OK, 0 rows affected (1 min 16.21 sec)
mysql> alter table queries ENGINE=InnoDB stats_persistent=0;
Query OK, 0 rows affected (22.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table queries ENGINE=InnoDB;
Query OK, 0 rows affected (17.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
Note that while starts_persistent seems to matter, it's still slow to rebuild a table of this size:
[openxs@chief 5.6]$ ls -l data/test/queries*
-rw-rw---- 1 openxs openxs 13160 Mar 26 13:18 data/test/queries.frm
-rw-rw---- 1 openxs openxs 168 Mar 26 13:18 data/test/queries.par
-rw-rw---- 1 openxs openxs 196608 Mar 26 13:18 data/test/queries#P#p0.ibd
...
-rw-rw---- 1 openxs openxs 196608 Mar 26 13:18 data/test/queries#P#p9.ibd
Same test with 5.5 on the same box, innodb_file_per_table=1, innodb_buffer_pool_size=128M:
[openxs@chief 5.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 2
Server version: 5.5.34 MySQL Community Server (GPL)
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> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> CREATE TABLE `queries` (
-> `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
-> `run` varchar(32) NOT NULL DEFAULT '',
-> `query` text NOT NULL,
...
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */;
Query OK, 0 rows affected (12.76 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (16.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
ALTER on 5.5 (while slow for my liking) is still faster even comparing to 5.6 with no persistent_stats option.
[26 Mar 2014 12:40]
Arnaud Adant
>ALTER on 5.5 (while slow for my liking) is still faster even comparing to 5.6 >with no persistent_stats option. That's expected. Try performance_schema = 0
[26 Mar 2014 12:55]
Marko Mäkelä
Is this a new bug, or could this be closed as a duplicate of Bug#70139 Performance of "ALTER TABLE..." queries that is primarily caused by the temporary files allocated by InnoDB?
[26 Mar 2014 13:23]
MySQL Verification Team
// 5.6.17
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 (2 min 1.13 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1 min 40.37 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show variables like '%log_bin%';
+---------------------------------+----------------------------------------------------------------+
| Variable_name | Value |
+---------------------------------+----------------------------------------------------------------+
| log_bin | ON |
| log_bin_basename | /data/ushastry/server/mysql-5.6.17/data/cluster-repo-bin |
| log_bin_index | /data/ushastry/server/mysql-5.6.17/data/cluster-repo-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+----------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 8388608 |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.17 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.17-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.5 |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1 min 27.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set global innodb_stats_persistent = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (28.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set global innodb_stats_persistent = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1 min 20.67 sec)
Records: 0 Duplicates: 0 Warnings: 0
// WITH P_S diabled
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | OFF |
+--------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON |
+-------------------------+-------+
1 row in set (0.00 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (40.29 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> set global innodb_stats_persistent = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (8.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
^^ without P_S, innodb_stats_persistent disabled - it is way faster
[26 Mar 2014 21:30]
Marko Mäkelä
OK, it looks like that there are more causes of slowness than the InnoDB temporary files. So, this cannot be closed as a duplicate of Bug#70139. I think that this should be split into 2 performance bugs: InnoDB persistent statistics, and performance_schema. There might already exist bugs for both, which this bug would be a duplicate of.
[6 Apr 2014 15:10]
jocelyn fournier
Hi, On my side, with a big buffer pool, I do not see much difference with & without innodb_stats_persistent (& P_S OFF) : show variables like '%buffer_pool%'; +-------------------------------------+----------------+ | Variable_name | Value | +-------------------------------------+----------------+ | innodb_buffer_pool_dump_at_shutdown | OFF | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | | innodb_buffer_pool_instances | 8 | | innodb_buffer_pool_load_abort | OFF | | innodb_buffer_pool_load_at_startup | OFF | | innodb_buffer_pool_load_now | OFF | | innodb_buffer_pool_populate | OFF | | innodb_buffer_pool_size | 4294967296 | +-------------------------------------+----------------+ set global innodb_stats_persistent = 0; ALTER TABLE queries ENGINE=InnoDB; Query OK, 0 rows affected (20.80 sec) set global innodb_stats_persistent = 1; ALTER TABLE queries ENGINE=InnoDB; Query OK, 0 rows affected (22.49 sec) Before the regression, with an older version of InnoDB plugin : ALTER TABLE queries ENGINE=InnoDB; Query OK, 0 rows affected (9.86 sec) So it seems another regression, not related to P_S or persistent stats has been introduced. (note that setting old_alter_table=1 does not provide any speed up in my case) Thanks and regards, Jocelyn Fournier
[9 Apr 2014 8:57]
Marko Mäkelä
Jocelyn, I believe that what you are seeing is simply Bug#70139, which is caused by InnoDB unnecessarily creating temporary files. Did you try adding ,LOCK=SHARED to the ALTER TABLE clause? That would skip some overhead when you do not need to be able to concurrently update the table.
[9 Apr 2014 15:18]
jocelyn fournier
Hi Marko, I've tested the create & alter with both old_alter_table=1 & old_alter_table=0 without any changes in performances (or I have not understand correctly the issue described in bug 70139). As well, the LOCK=SHARED doesn't change the performances : ALTER TABLE queries ENGINE=InnoDB,LOCK=SHARED; Query OK, 0 rows affected (19.48 sec) Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE queries ENGINE=InnoDB; Query OK, 0 rows affected (19.64 sec) Records: 0 Duplicates: 0 Warnings: 0
[14 Jul 2014 15:32]
MySQL Verification Team
Interesting why I cannot repeat any problem on 5.6.19 (8M or 8G buffer pool was similar timings).
mysqld.exe --no-defaults --skip-grant-tables --console --port=3307 --log-bin --innodb-buffer-pool-size=8G
-> PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.23 sec)
mysql>
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (1.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.00 sec)
[15 Jul 2014 6:31]
MySQL Verification Team
I figure out my non-repeatability issue. It is because I had write caching enabled. After turning it off, the very slow performance happens:
-> PARTITION p30 VALUES IN (30) ENGINE = InnoDB,
-> PARTITION p31 VALUES IN (31) ENGINE = InnoDB) */;
Query OK, 0 rows affected (40.62 sec)
mysql>
mysql> ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (31.71 sec)
Records: 0 Duplicates: 0 Warnings: 0
Doesn't it seem unnecessary to have to flush disk buffers for temporary innodb tables?
[15 Jul 2014 7:44]
MySQL Verification Team
vtunes output1 for my test
Attachment: bug72115_image1.jpg (image/jpeg, text), 410.21 KiB.
[15 Jul 2014 7:45]
MySQL Verification Team
vtunes output2 for my test
Attachment: bug72115_image2.jpg (image/jpeg, text), 382.13 KiB.
[15 Aug 2014 14:10]
Marko Mäkelä
Posted by developer: This seems to be a feature request, to skip unnecessary flushing during DDL. In mysql-trunk, during WL#7142 preparation, I think I disabled fsync() for FIL_TYPE_TEMPORARY tablespaces. But, ALTER TABLE does not really use a "temporary" table. It is a temporary, but persistent (redo-logged) copy of a persistent table. In mysql-trunk, WL#7277 reduces the amount of redo log volume generated by ALTER TABLE. Due to this, it *must* flush the changes to the data pages before the ALTER TABLE transaction is committed to the data dictionary. I am reassigning this bug to get an opinion if anything can be done to improve the performance.
[30 Dec 2014 7:47]
Marko Mäkelä
A bug was filed for the problem that I foresaw in my previous comment: Bug#74472 ddl like add index is very slow in 5.7.5
[27 May 2015 9:07]
Marko Mäkelä
Posted by developer: The updates in this bug mixed multiple issues in one. The initially reported issue was a simple misconfiguration (using a too small buffer pool). The regression that was introduced in WL#7277 in MySQL 5.7 was fixed in the upcoming 5.7.8 release. The remaining fixable problem seems to be two performance regressions that were introduced in MySQL 5.6: Enabling PERFORMANCE_SCHEMA or STATS_PERSISTENT=1 will make DDL operations slower.
[27 May 2015 9:52]
Marko Mäkelä
Posted by developer: There are two more 5.6 regressions in this are that were already fixed: Bug#16868967 MYSQL USES SIGNIFICANTLY MORE MEMORY FOR ALTER TABLE THAN EXPECTED (fixed in MySQL 5.6.16) Bug#17657223 EXCESSIVE TEMPORARY FILE USAGE IN ALTER TABLE (fixed in MySQL 5.7.5)
[22 Jul 2015 7:16]
Thirunarayanan Balathandayuthapani
Disabled write cache. Started the server with default buffer pool size(128MB) Reported version mysql-5.6.13: ------------------------------ CREATE TABLE `queries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `run` varchar(32) NOT NULL DEFAULT '', ........... ........... 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 (56.02 sec) alter table queries engine=innodb; -------------- alter table queries engine=innodb -------------- Query OK, 0 rows affected (1 min 3.79 sec) Records: 0 Duplicates: 0 Warnings: 0 Latest version mysql-5.6.25: ---------------------------- CREATE TABLE `queries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `run` varchar(32) NOT NULL DEFAULT '', ........... ........... 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 (18.32 sec) ALTER TABLE queries ENGINE=InnoDB -------------- Query OK, 0 rows affected (24.83 sec) Records: 0 Duplicates: 0 Warnings: 0 Fix for bug#17323202 went into mysql-5.6.17. This improved the stats_persistent performance. I am closing this bug.
[22 Jul 2015 7:17]
Thirunarayanan Balathandayuthapani
Disabled write cache. Started the server with default buffer pool size(128MB) Reported version mysql-5.6.13: ------------------------------ CREATE TABLE `queries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `run` varchar(32) NOT NULL DEFAULT '', ........... ........... 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 (56.02 sec) alter table queries engine=innodb; -------------- alter table queries engine=innodb -------------- Query OK, 0 rows affected (1 min 3.79 sec) Records: 0 Duplicates: 0 Warnings: 0 Latest version mysql-5.6.25: ---------------------------- CREATE TABLE `queries` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `run` varchar(32) NOT NULL DEFAULT '', ........... ........... 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 (18.32 sec) ALTER TABLE queries ENGINE=InnoDB -------------- Query OK, 0 rows affected (24.83 sec) Records: 0 Duplicates: 0 Warnings: 0 Fix for bug#17323202 went into mysql-5.6.17. This improved the stats_persistent performance. I am closing this bug.
Description: jan@jan-GE70-0NC-0ND ~/mysql/o10 $ /usr/local/mysql/bin/mysql -u root -S /home/jan/mysql/o10/mysql.sock2 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.6.16-log Source distribution 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> 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 (2 min 46.88 sec) mysql> ALTER TABLE queries ENGINE=InnoDB; Query OK, 0 rows affected (3 min 2.12 sec) Records: 0 Duplicates: 0 Warnings: 0 How to repeat: 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;