2

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;
1

1 Answer 1

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

GIVE IT A TRY !!!

3
  • 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 ! Feb 17, 2015 at 5:00
  • One more detail. There is SW Raid on my server machine Feb 17, 2015 at 6:02
  • 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 Feb 22, 2015 at 4:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.