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;