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

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Won't Fix
Affects Version/s: 10.0.9
Fix Version/s: N/A
Component/s: Storage Engine - XtraDB
Labels:
- upstream-fixed

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

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

mysql bug 72115 (fixed)

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

Activity

People

Assignee:: Jan Lindström (Inactive)

Reporter:: jocelyn fournier

Votes:: 0 Vote for this issue

Watchers:: 10 Start watching this issue

Dates

Created:: 2014-03-12 17:01

Updated:: 2022-11-01 09:52

Resolved:: 2022-11-01 09:52

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.