MySQL Create Table with Partition Slow on server machine

innodbMySQLpartitioningwindows

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;

Best Answer

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

MySQL Create Table with Partition Slow on server machine

Best Answer

ANALYTICAL EXPERIMENT

COMPARISON OF STORAGE ENGINES

MyISAM

File Listing from Windows Command Line

InnoDB

File Listing from Windows Command Line

CONCLUSION OF THE EXPERIMENT

YOUR ORIGINAL QUESTION

RECOMMENDATION

GIVE IT A TRY !!!

Related Question

Best Answer

ANALYTICAL EXPERIMENT

COMPARISON OF STORAGE ENGINES

MyISAM

File Listing from Windows Command Line

InnoDB

File Listing from Windows Command Line

CONCLUSION OF THE EXPERIMENT

YOUR ORIGINAL QUESTION

RECOMMENDATION

GIVE IT A TRY !!!

Related Solutions

SQL Server Enterprise – Are We a Good Fit for Table Partitioning?

MySQL Create Table with Partition Very Slow on server machine

Related Question