MySQL Forums
Forum List  »  Performance

Why INSERT performance has not increase in sub-table?
Posted by: Yao Yao
Date: November 03, 2014 03:02AM

Hi~

Recently I did an insertion test.

MySQL Version 5.6.20 CentOS 5.4 InnoDB
X3430 (4 core) 8G RAM WD blue disk NO RAID
Default configuration ( my.cnf )

First, i started 50 connections and inserted 5 million records into a table , the elapsed time is like the following:

Records : 0-100 100-200 200-300 300-400 400-500
Seconds : 108 164 280 575 670

Then i divided the table into 10 sub-table, 50 connections also, insert 500,000 record into each sub-table, total 5 million records. The test results are as follows:

Records : 0-100 100-200 200-300 300-400 400-500
Seconds : 114 187 499 775 587


So, from the first test’s result, when the record amount is less than 1 million, insertion speed is very fast, but why use 10 sub-table has not increase performance, even slower than the first test?



Table structure (a auto_increment primary key and a varchar index) and test procedures as follows:

CREATE TABLE `testa` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`var1` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',
`int1` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


DROP PROCEDURE IF EXISTS TestA;
CREATE PROCEDURE TestA(
IN paramVar1 VARCHAR(64),
IN paramInt1 INT
)
label_start : BEGIN

START TRANSACTION;

SET @paramVar1 = paramVar1;
SET @paramInt1 = paramInt1;
SET @statement = CONCAT("INSERT INTO `testa`(`Var1`, `Int1` ) VALUES ( ?, ? );" );
PREPARE stmt FROM @statement;
EXECUTE stmt USING @paramVar1, @paramInt1;
DEALLOCATE PREPARE stmt;

COMMIT;

END;



DROP PROCEDURE IF EXISTS CreateTestBTable;
CREATE PROCEDURE CreateTestBTable( )
BEGIN

DECLARE TABLE_COUNT INT DEFAULT 10;
DECLARE m_nLoopCount INT DEFAULT 0;

SET m_nLoopCount = 0;
REPEAT

SET @statement = CONCAT(
"CREATE TABLE `testb_", m_nLoopCount, "` (",
"`Id` int(11) NOT NULL AUTO_INCREMENT,",
"`var1` varchar(64) COLLATE utf8_bin NOT NULL DEFAULT '',",
"`int1` int(11) NOT NULL DEFAULT '0',",
"PRIMARY KEY (`Id`),",
"KEY `VarIndex` (`var1`)",
") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;" );
PREPARE stmt FROM @statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET m_nLoopCount = m_nLoopCount + 1;

UNTIL m_nLoopCount >= TABLE_COUNT
END REPEAT;

END;


DROP PROCEDURE IF EXISTS DropTestBTable;
CREATE PROCEDURE DropTestBTable( )
BEGIN

DECLARE TABLE_COUNT INT DEFAULT 10;
DECLARE m_nLoopCount INT DEFAULT 0;

SET m_nLoopCount = 0;
REPEAT

SET @statement = CONCAT( "DROP TABLE `testb_", m_nLoopCount, "`;" );
PREPARE stmt FROM @statement;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET m_nLoopCount = m_nLoopCount + 1;

UNTIL m_nLoopCount >= TABLE_COUNT
END REPEAT;

END;

DROP PROCEDURE IF EXISTS TestB;
CREATE PROCEDURE TestB(
IN paramVar1 VARCHAR(64),
IN paramInt1 INT
)
label_start : BEGIN


DECLARE TABLE_COUNT INT DEFAULT 10;

START TRANSACTION;

SET @TableIndex = CRC32(paramVar1) % TABLE_COUNT;
SET @paramVar1 = paramVar1;
SET @paramInt1 = paramInt1;
SET @statement = CONCAT("INSERT INTO `testb_", @TableIndex, "`(`Var1`, `Int1` ) VALUES ( ?, ? );" );
PREPARE stmt FROM @statement;
EXECUTE stmt USING @paramVar1, @paramInt1;
DEALLOCATE PREPARE stmt;

COMMIT;

END;

Options: ReplyQuote


Subject
Views
Written By
Posted
Why INSERT performance has not increase in sub-table?
2537
November 03, 2014 03:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.