Description:
I’m running single node mysql cluster on:
Intel(R) Xeon(R) CPU E5-1650 v3 @ 3.50GHz (12 cores)
128 GB DDR4 RAM, RAID0 SSD.
only mysql running on this machine.
NDBCLUSTER has only 1 VERY SIMPLE table and SELECT works 3x slower then InnoDB/MyISAM.
means its too far away of mysql promotions NDB as Realtime DB.
How to repeat:
1. Create table:
CREATE TABLE `TEST` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`COL1` varchar(45) DEFAULT NULL,
`COL2` varchar(45) DEFAULT NULL,
`COL3` varchar(45) DEFAULT NULL,
`COL4` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`),
KEY `TIME` (`TIME`)
) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
2. Insert TEST RAW:
INSERT INTO TEST(COL1,COL2,COL3,COL4) VALUES (1,2,3,4);
3. Insert 3000000 rows:
DELIMITER $$
CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i < 3000000 DO
INSERT INTO `TEST` (`COL1`, `COL2`, `COL3`, `COL4`)
SELECT `COL1`, `COL2`, `COL3`, `COL4`
FROM `TEST`
WHERE id = 1;
SET i = i + 1;
END WHILE;
END$$
DELIMITER ;
CALL insert_test_data();
4. Perform SELECT query using LIKE:
select count(*) from TEST where time like '2015%'
+----------+
| count(*) |
+----------+
| 3000000 |
+----------+
1 row in set (2.95 sec)
using InnoDB instead of NDBCLUSTER:
1 row in set (1.25 sec)
using MEMORY instead of NDBCLUSTER:
1 row in set (0.72 sec)
Why its that bad ?
Description: I’m running single node mysql cluster on: Intel(R) Xeon(R) CPU E5-1650 v3 @ 3.50GHz (12 cores) 128 GB DDR4 RAM, RAID0 SSD. only mysql running on this machine. NDBCLUSTER has only 1 VERY SIMPLE table and SELECT works 3x slower then InnoDB/MyISAM. means its too far away of mysql promotions NDB as Realtime DB. How to repeat: 1. Create table: CREATE TABLE `TEST` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `COL1` varchar(45) DEFAULT NULL, `COL2` varchar(45) DEFAULT NULL, `COL3` varchar(45) DEFAULT NULL, `COL4` varchar(255) DEFAULT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `ID_UNIQUE` (`ID`), KEY `TIME` (`TIME`) ) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 2. Insert TEST RAW: INSERT INTO TEST(COL1,COL2,COL3,COL4) VALUES (1,2,3,4); 3. Insert 3000000 rows: DELIMITER $$ CREATE PROCEDURE insert_test_data() BEGIN DECLARE i INT DEFAULT 1; WHILE i < 3000000 DO INSERT INTO `TEST` (`COL1`, `COL2`, `COL3`, `COL4`) SELECT `COL1`, `COL2`, `COL3`, `COL4` FROM `TEST` WHERE id = 1; SET i = i + 1; END WHILE; END$$ DELIMITER ; CALL insert_test_data(); 4. Perform SELECT query using LIKE: select count(*) from TEST where time like '2015%' +----------+ | count(*) | +----------+ | 3000000 | +----------+ 1 row in set (2.95 sec) using InnoDB instead of NDBCLUSTER: 1 row in set (1.25 sec) using MEMORY instead of NDBCLUSTER: 1 row in set (0.72 sec) Why its that bad ?