[MDEV-5838] XtraDB more than x2 slower during create table / alter table compare to classic InnoDB - JIRA

Details

Type: Bug
Status: Open
Priority: Minor
Resolution: Unresolved
Affects Version/s: 10.0.9
Fix Version/s: 10.0
Component/s: None
Labels:
- upstream

Description

Hi,

Since the switch from 10.0.8 to 10.0.9 I noticed my unit tests have been 2x slower.
Indeed, when comparing classical ALTER and CREATE operation, XtraDB is 2x slower than Oracle InnoDB plugin, on an empty table !

With native MySQL plugin :
(adding
ignore_builtin_innodb
plugin-load-add=ha_innodb
)

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 (5.13 sec)

ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (9.86 sec)               
Records: 0  Duplicates: 0  Warnings: 0

DROP TABLE queries;
Query OK, 0 rows affected (1.56 sec)

With XtraDB :

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 (16.60 sec)

ALTER TABLE queries ENGINE=InnoDB;
Query OK, 0 rows affected (20.78 sec)              
Records: 0  Duplicates: 0  Warnings: 0

DROP TABLE queries;
Query OK, 0 rows affected (1.56 sec)

=> CREATE TABLE : 5,13s => 16,6s (x3 degradation !)
=> ALTER TABLE : 9,89s => 20,78s (x2 degradation)
=> DROP TABLE : no change

Thanks and regards,
Jocelyn Fournier

Gliffy Diagrams

Attachments

Issue Links

relates to

MDEV-6241 MariaDB 10.0.11 (32-bit) Too Slow Create XtraDB Table 2.5x than MariaDB 5.5.37 (32-bit)

Closed

links to

XtraDB very sllow on create table/alter table compared to InnoDB

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Elena Stepanova added a comment - 2014-03-12 17:36

Hi,

Could you please also provide the complete .cnf file(s)? (or output of SHOW GLOBAL VARIABLES if you prefer)

Thanks

Show

Elena Stepanova added a comment - 2014-03-12 17:36 Hi, Could you please also provide the complete .cnf file(s)? (or output of SHOW GLOBAL VARIABLES if you prefer) Thanks

8 older comments

Hide

Permalink

Jan Lindström added a comment - 2014-03-24 12:17

Only clear difference between InnoDB and XtraDB seems to be this function

41 1.8570 /home/elenst/bzr/10.0-rel/sql/mysqld log_block_calc_checksum_innodb(unsigned char const*)

In InnoDB this is not evident but on XtraDB it is.

Show

Jan Lindström added a comment - 2014-03-24 12:17 Only clear difference between InnoDB and XtraDB seems to be this function 41 1.8570 /home/elenst/bzr/10.0-rel/sql/mysqld log_block_calc_checksum_innodb(unsigned char const*) In InnoDB this is not evident but on XtraDB it is.

Hide

Permalink

jocelyn fournier added a comment - 2014-03-24 13:12

Since XtraDB is using a function pointer 'log_checksum_algorithm_ptr' introduced by https://code.launchpad.net/~alexey2k/percona-server/5.6-log-checksum/+merge/191918 to call log_block_calc_checksum_innodb, I think it breaks the inlining which is done in standard InnoDB code.

Show

jocelyn fournier added a comment - 2014-03-24 13:12 Since XtraDB is using a function pointer 'log_checksum_algorithm_ptr' introduced by https://code.launchpad.net/~alexey2k/percona-server/5.6-log-checksum/+merge/191918 to call log_block_calc_checksum_innodb, I think it breaks the inlining which is done in standard InnoDB code.

Hide

Permalink

Jan Lindström added a comment - 2014-03-24 21:30

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

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

Show

Jan Lindström added a comment - 2014-03-24 21:30 Very slow create/alter table also with Oracle MySQL 5.6.16: http://bugs.mysql.com/bug.php?id=72115

Hide

Permalink

Jan Lindström added a comment - 2014-03-25 13:17 - edited

InnoDB

MariaDB [test]> show profile for query 2;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000354 |
| checking permissions |  0.000018 |
| Opening tables       |  0.000039 |
| System lock          |  0.000006 |
| Table lock           |  0.000099 |
| creating table       | 29.697951 |
| After create         |  0.000032 |
| Writing to binlog    |  0.000104 |
| query end            |  0.000018 |
| closing tables       |  0.000017 |
| freeing items        |  0.000016 |
| updating status      |  0.000033 |
| logging slow query   |  0.000009 |
| cleaning up          |  0.000046 |
+----------------------+-----------+
14 rows in set (0.00 sec)

XtraDB

MariaDB [test]> show profile for query 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000486 |
| checking permissions |  0.000032 |
| Opening tables       |  0.000030 |
| System lock          |  0.000006 |
| Table lock           |  0.000098 |
| creating table       | 30.469348 |
| After create         |  0.000031 |
| Writing to binlog    |  0.000099 |
| query end            |  0.000015 |
| closing tables       |  0.000017 |
| freeing items        |  0.000016 |
| updating status      |  0.000037 |
| logging slow query   |  0.000008 |
| cleaning up          |  0.000037 |
+----------------------+-----------+
14 rows in set (0.00 sec)

Show

Jan Lindström added a comment - 2014-03-25 13:17 - edited InnoDB MariaDB [test]> show profile for query 2; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | starting | 0.000354 | | checking permissions | 0.000018 | | Opening tables | 0.000039 | | System lock | 0.000006 | | Table lock | 0.000099 | | creating table | 29.697951 | | After create | 0.000032 | | Writing to binlog | 0.000104 | | query end | 0.000018 | | closing tables | 0.000017 | | freeing items | 0.000016 | | updating status | 0.000033 | | logging slow query | 0.000009 | | cleaning up | 0.000046 | +----------------------+-----------+ 14 rows in set (0.00 sec) XtraDB MariaDB [test]> show profile for query 1; +----------------------+-----------+ | Status | Duration | +----------------------+-----------+ | starting | 0.000486 | | checking permissions | 0.000032 | | Opening tables | 0.000030 | | System lock | 0.000006 | | Table lock | 0.000098 | | creating table | 30.469348 | | After create | 0.000031 | | Writing to binlog | 0.000099 | | query end | 0.000015 | | closing tables | 0.000017 | | freeing items | 0.000016 | | updating status | 0.000037 | | logging slow query | 0.000008 | | cleaning up | 0.000037 | +----------------------+-----------+ 14 rows in set (0.00 sec)

Hide

Permalink

Jan Lindström added a comment - 2014-04-24 11:38

Decreasing the importance because only partitioned tables are affected.

Show

Jan Lindström added a comment - 2014-04-24 11:38 Decreasing the importance because only partitioned tables are affected.

XtraDB more than x2 slower during create table / alter table compare to classic InnoDB

Details

Description

Gliffy Diagrams

Attachments

Issue Links

Activity

People

Dates

Agile