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;

Related question on MySQL Forum forums.mysql.com/read.php?24,392502,392502 — user1070759, Commented Feb 14, 2015 at 8:15

Community · Accepted Answer · 2017-04-13 12:42:44Z

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 !!!

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, Commented Feb 17, 2015 at 5:00
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 — user1070759, Commented Feb 22, 2015 at 4:39

Stack Exchange Network

MySQL Create Table with Partition Slow on server machine

1 Answer 1

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 !!!

Your Answer

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

Linked

Hot Network Questions

MySQL Create Table with Partition Slow on server machine

1 Answer 1

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 !!!

Your Answer

Sign up or log in

Post as a guest

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

Linked

Related

Hot Network Questions

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