MySQL Create Table with Partition Slow on server machine

Question

Following create table statement with Partition

• runs in 10 Second on My Development Machine windows 7, i5 , 8GBRam, single disk

• runs in 16 Second on Virtual Machine windows server 2012 , xeon 2GHz , 615MB Ram, single disk

• While takes 100 Seconds on my server machine Windows server 2008 R2 Standard, xeon 2.4 2 Processors, 32 GB Ram, ATA SCSI Mirror drives

I am not able to understand why there is so much performance difference ..

CREATE TABLE IF NOT EXISTS `TestSpeed`.`CTM` (
 `ServerID` BIGINT NOT NULL,
 `ClientID` BIGINT NOT NULL,
 `CommunityID` INT NOT NULL,
 PRIMARY KEY (`ClientID`,`CommunityID`),
 UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
 INDEX `IX_CTM_ClientID` (`ClientID` ASC))
PARTITION BY HASH (CommunityID) PARTITIONS 300;

• user1070759: Related question on MySQL Forum forums.mysql.com/read.php?24,392502,392502

Answer 1

ANALYTICAL EXPERIMENT

Looking at CREATE TABLE and seeing that you are creating 300 partitions, I need you to perform an experiment. Please run the following script:

DROP DATABASE IF EXISTS rolandos_test;
CREATE DATABASE rolandos_test;
USE rolandos_test
SET @T1 = UNIX_TIMESTAMP(NOW());
CREATE TABLE CTM_MyISAM (
 `ServerID` BIGINT NOT NULL,
 `ClientID` BIGINT NOT NULL,
 `CommunityID` INT NOT NULL,
 PRIMARY KEY (`ClientID`,`CommunityID`),
 UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
 INDEX `IX_CTM_ClientID` (`ClientID` ASC))
ENGINE=MyISAM
PARTITION BY HASH (CommunityID) PARTITIONS 300;
SET @T2 = UNIX_TIMESTAMP(NOW());
CREATE TABLE CTM_InnoDB (
 `ServerID` BIGINT NOT NULL,
 `ClientID` BIGINT NOT NULL,
 `CommunityID` INT NOT NULL,
 PRIMARY KEY (`ClientID`,`CommunityID`),
 UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
 INDEX `IX_CTM_ClientID` (`ClientID` ASC))
ENGINE=InnoDB
PARTITION BY HASH (CommunityID) PARTITIONS 300;
SET @T3 = UNIX_TIMESTAMP(NOW());
SET @MyISAM_TIME = @T2 - @T1;
SET @InnoDB_TIME = @T3 - @T2;
SET @CTM_Ratio = FORMAT(@InnoDB_TIME/@MyISAM_TIME,2);
SELECT @InnoDB_TIME,@MyISAM_TIME,@CTM_Ratio;

I have the following on my laptop at home

• 6 GB RAM
• 650 GB Disk
• Windows 8.1
• MySQL 5.6.21

I got the following results

mysql> DROP DATABASE IF EXISTS rolandos_test;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> CREATE DATABASE rolandos_test;
Query OK, 1 row affected (0.00 sec)
mysql> USE rolandos_test
Database changed
mysql> SET @T1 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE CTM_MyISAM (
 -> `ServerID` BIGINT NOT NULL,
 -> `ClientID` BIGINT NOT NULL,
 -> `CommunityID` INT NOT NULL,
 -> PRIMARY KEY (`ClientID`,`CommunityID`),
 -> UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
 -> INDEX `IX_CTM_ClientID` (`ClientID` ASC))
 -> ENGINE=MyISAM
 -> PARTITION BY HASH (CommunityID) PARTITIONS 300;
Query OK, 0 rows affected (1.09 sec)
mysql> SET @T2 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE CTM_InnoDB (
 -> `ServerID` BIGINT NOT NULL,
 -> `ClientID` BIGINT NOT NULL,
 -> `CommunityID` INT NOT NULL,
 -> PRIMARY KEY (`ClientID`,`CommunityID`),
 -> UNIQUE INDEX `IX_CTM` (`ServerID` ASC,`CommunityID`),
 -> INDEX `IX_CTM_ClientID` (`ClientID` ASC))
 -> ENGINE=InnoDB
 -> PARTITION BY HASH (CommunityID) PARTITIONS 300;
Query OK, 0 rows affected (2 min 49.83 sec)
mysql> SET @T3 = UNIX_TIMESTAMP(NOW());
Query OK, 0 rows affected (0.00 sec)
mysql> SET @MyISAM_TIME = @T2 - @T1;
Query OK, 0 rows affected (0.02 sec)
mysql> SET @InnoDB_TIME = @T3 - @T2;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @CTM_Ratio = FORMAT(@InnoDB_TIME/@MyISAM_TIME,2);
Query OK, 0 rows affected (0.02 sec)
mysql> SELECT @InnoDB_TIME,@MyISAM_TIME,@CTM_Ratio;
+--------------+--------------+------------+
| @InnoDB_TIME | @MyISAM_TIME | @CTM_Ratio |
+--------------+--------------+------------+
| 170 | 1 | 170.00 |
+--------------+--------------+------------+
1 row in set (0.00 sec)
mysql>

COMPARISON OF STORAGE ENGINES

MyISAM

It took 1.09 seconds to make 300 partitions using MyISAM. . It create the following:

• .frm file (Manage the Table)
• .par file (Manage Partitions)
• 300 .MYD files (Partition Data)
• 300 .MYI files (Partition Indexes)

File Listing from Windows Command Line

C:\MySQL_5.6.21\data\rolandos_test>dir ctm_myisam*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF
 Directory of C:\MySQL_5.6.21\data\rolandos_test
02/15/2015 10:02 PM 0 ctm_myisam#P#p0.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p0.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p1.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p1.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p10.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p10.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p100.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p100.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p101.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p101.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p102.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p102.MYI
.
.
.
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p95.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p96.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p96.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p97.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p97.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p98.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p98.MYI
02/15/2015 10:02 PM 0 ctm_myisam#P#p99.MYD
02/15/2015 10:02 PM 1,024 ctm_myisam#P#p99.MYI
02/15/2015 10:02 PM 8,650 ctm_myisam.frm
02/15/2015 10:02 PM 1,708 ctm_myisam.par
 602 File(s) 317,558 bytes
 0 Dir(s) 674,767,982,592 bytes free

InnoDB

It took 2 min 49.83 seconds to make 300 partitions using InnoDB. It create the following:

• .frm file (Manage the Table)
• .par file (Manage Partitions)
• 300 .ibd files:

File Listing from Windows Command Line

C:\MySQL_5.6.21\data\rolandos_test>dir ctm_innodb*
 Volume in drive C is TI10665200H
 Volume Serial Number is A273-2EFF
 Directory of C:\MySQL_5.6.21\data\rolandos_test
02/15/2015 10:02 PM 131,072 ctm_innodb#p#p0.ibd
02/15/2015 10:02 PM 131,072 ctm_innodb#p#p1.ibd
02/15/2015 10:02 PM 131,072 ctm_innodb#p#p10.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p100.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p101.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p102.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p103.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p104.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p105.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p106.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p107.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p108.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p109.ibd
02/15/2015 10:02 PM 131,072 ctm_innodb#p#p11.ibd
.
.
.
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p90.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p91.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p92.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p93.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p94.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p95.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p96.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p97.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p98.ibd
02/15/2015 10:05 PM 131,072 ctm_innodb#p#p99.ibd
02/15/2015 10:02 PM 8,650 ctm_innodb.frm
02/15/2015 10:02 PM 1,708 ctm_innodb.par
 302 File(s) 39,331,958 bytes
 0 Dir(s) 674,766,995,456 bytes free
C:\MySQL_5.6.21\data\rolandos_test>

CONCLUSION OF THE EXPERIMENT

InnoDB's storage engine management is a definite bottleneck. This is not such much the number of files. Why ? If the number of files per partition was the sole factor, it should have taken 0.545 seconds to create half the number of file. It took 2 min 49.83 sec. How ?

MySQL's partition management is essentially the same. Once the storage engine layer is reached, InnoDB's data dictionary is evidently monitoring each partition's registration.

YOUR ORIGINAL QUESTION

Now that you know how InnoDB is functioning while files are being created, now look at your disks.

You have Disks on 3 other Windows OS platforms

• Windows 7 (10 seconds)
• Windows 2012 VM (16 seconds)
• Windows 2008 (1 min 40 seconds (100 seconds))

I can confidently say the following

• All three of your servers have better disk performance than my laptop's disk
• ATA SCSI is not your Friend

RECOMMENDATION

• GET FASTER DISKS
• Please don't use RAID5 : See my post Is raid 5 suitable for a mysql installation?
• Make sure you have Battery-Backed Cache
• Check the Batteries on the Disk Controller

GIVE IT A TRY !!!

• user1070759: Thank you for your analysis. Here are my results from server machine: 182(innodb) 1(MyIsam) , On my dev machine 30(innodb) 0(MyIsam). It is the Innodb engine combined with disk interaction is the reason for slow performance. Is your Laptop having mirror disks or single disk. Can we conclusively say that Mirror disks on server are causing this issue, so I can switch to a server without Mirror Disks. Thanks again !
• user1070759: One more detail. There is SW Raid on my server machine
• user1070759: It is definitely IO issue. I moved to a SSD disk with no RAID and it takes only 2 seconds for innodb table creation with 300 partitions