I am testing performance in a MySQL Server and filling a table with more than 200 million of records. The Stored Procedure is very slow generating the big SQL string. Any help or comment is really welcome.
System Info:
The Stored Procedure creates a INSERT sql query with all the values to be inserted into the table.
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `inputRowsNoRandom`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `inputRowsNoRandom`(IN NumRows BIGINT)
BEGIN
/* BUILD INSERT SENTENCE WITH A LOS OF ROWS TO INSERT */
DECLARE i BIGINT;
DECLARE nMax BIGINT;
DECLARE squery LONGTEXT;
DECLARE svalues LONGTEXT;
SET i = 1;
SET nMax = NumRows + 1;
SET squery = 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, DATE) VALUES ';
SET svalues = '("1", "a1", 100, 1, 500000, "2013-06-14 12:40:45"),';
WHILE i < nMax DO
SET squery = CONCAT(squery, svalues);
SET i = i + 1;
END WHILE;
/*SELECT squery;*/
SET squery = LEFT(squery, CHAR_LENGTH(squery) - 1);
SET squery = CONCAT(squery, ";");
SELECT squery;
/* EXECUTE INSERT SENTENCE */
/*START TRANSACTION;*/
/*PREPARE stmt FROM squery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
*/
/*COMMIT;*/
END$$
DELIMITER ;
Results:
CALL test.inputRowsNoRandom(20000);
CALL test.inputRowsNoRandom(100000);
Result (ordered by duration) - stateduration (summed) in sec || percentage
freeing items 0.00005 50.00000
starting 0.00002 20.00000
executing 0.00001 10.00000
init 0.00001 10.00000
cleaning up 0.00001 10.00000
Total 0.00010 100.00000
Change Of STATUS VARIABLES Due To Execution Of Query
variable value description
Bytes_received 21 Bytes sent from the client to the server
Bytes_sent 97 Bytes sent from the server to the client
Com_select 1 Number of SELECT statements that have been executed
Questions 1 Number of statements executed by the server
Tests:
I have already tested with different MySQL configurations from 12 to 64 threads, setting cache on and off, moving logs to another hardware disk...
Also tested using TEXT, INT..
Additional Information:
Questions:
SELECT squery;
is a NULL string. Whats happening? (error must be there but I dont see it).mysql -u mysqluser -p databasename < numbers.sql
UPDATE:
Don't use loops especially on that scale in RDBMS.
Try to quickly fill your table with 1m rows with a query
INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
SELECT 1, 'a1', 100, 1, 500000, '2013-06-14 12:40:45'
FROM
(
select a.N + b.N * 10 + c.N * 100 + d.N * 1000 + e.N * 10000 + f.N * 100000 + 1 N
from (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) a
, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) b
, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) c
, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) d
, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) e
, (select 0 as N union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) f
) t
It took on my box (MacBook Pro 16GB RAM, 2.6Ghz Intel Core i7) ~8 sec to complete
Query OK, 1000000 rows affected (7.63 sec) Records: 1000000 Duplicates: 0 Warnings: 0
UPDATE1 Now a version of a stored procedure that uses a prepared statement
DELIMITER $$
CREATE PROCEDURE `inputRowsNoRandom`(IN NumRows INT)
BEGIN
DECLARE i INT DEFAULT 0;
PREPARE stmt
FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
VALUES(?, ?, ?, ?, ?, ?)';
SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45';
WHILE i < NumRows DO
EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6;
SET i = i + 1;
END WHILE;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
Completed in ~3 min:
mysql> CALL inputRowsNoRandom(1000000); Query OK, 0 rows affected (2 min 51.57 sec)
Feel the difference 8 sec vs 3 min
UPDATE2 To speed things up we can explicitly use transactions and commit insertions in batches. So here it goes an improved version of the SP.
DELIMITER $$
CREATE PROCEDURE inputRowsNoRandom1(IN NumRows BIGINT, IN BatchSize INT)
BEGIN
DECLARE i INT DEFAULT 0;
PREPARE stmt
FROM 'INSERT INTO `entity_versionable` (fk_entity, str1, str2, bool1, double1, date)
VALUES(?, ?, ?, ?, ?, ?)';
SET @v1 = 1, @v2 = 'a1', @v3 = 100, @v4 = 1, @v5 = 500000, @v6 = '2013-06-14 12:40:45';
START TRANSACTION;
WHILE i < NumRows DO
EXECUTE stmt USING @v1, @v2, @v3, @v4, @v5, @v6;
SET i = i + 1;
IF i % BatchSize = 0 THEN
COMMIT;
START TRANSACTION;
END IF;
END WHILE;
COMMIT;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
Results with different batch sizes:
mysql> CALL inputRowsNoRandom1(1000000,1000); Query OK, 0 rows affected (27.25 sec) mysql> CALL inputRowsNoRandom1(1000000,10000); Query OK, 0 rows affected (26.76 sec) mysql> CALL inputRowsNoRandom1(1000000,100000); Query OK, 0 rows affected (26.43 sec)
You see the difference yourself. Still > 3 times worse than cross join.