Bug #37983 | ndb 6.2.15 is slow mysql 5.1.23 | ||
---|---|---|---|
Submitted: | 9 Jul 2008 6:26 | Modified: | 26 Oct 2009 12:47 |
Reporter: | ws lee | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S5 (Performance) |
Version: | mysql-5.1-telco-6.2 | OS: | Solaris (SunOS 5.11(but, maybe all OS)) |
Assigned to: | Magnus Blåudd | CPU Architecture: | Any |
[9 Jul 2008 6:26]
ws lee
[27 Oct 2008 21:27]
Jonathan Miller
I don't have a solaris test system to test this on, so the Norway team might have to be involved. currently I get the following. I will now setup on plain 5.1 and rerun test. mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.1.28-ndb-6.2.17 | +-------------------+ 1 row in set (0.00 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (14.12 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (6.82 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (7.04 sec)
[28 Oct 2008 0:45]
Jonathan Miller
mysql> select version(); +-----------+ | version() | +-----------+ | 5.1.30 | +-----------+ 1 row in set (0.00 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (5.29 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (4.92 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 917151 | +----------+ 1 row in set (5.27 sec)
[28 Oct 2008 0:48]
Jonathan Miller
script for setup
Attachment: test_bug37983.sh (application/x-shellscript, text), 344 bytes.
[28 Oct 2008 14:19]
Jonathan Miller
Hi, We need to have more hosts information. X86? Sun version? Open sun solaris? etc... Thanks! /Jeb
[29 Oct 2008 9:53]
ws lee
To. Jonathan Miller I think this problems is not OS problem. It maybe MySQL Version problem. Detail of my system Solris 11. # uname -srvmpi SunOS 5.11 snv_40 i86pc i386 i86pc In first bugreport, config was each 1 data node at 2 server. Today, I retest. today test machine is 2 data node in 1 server. config.ini ----------- [NDBD DEFAULT] NoOfReplicas=2 [NDB_MGMD] hostname=localhost [NDBD] hostname=localhost [NDBD] hostname=localhost [MYSQLD] hostname=localhost ------------- mysql> show create table t1; CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `created_t` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `c2` varchar(15) NOT NULL, `c3` text, `c4` varchar(1000) DEFAULT NULL, PRIMARY KEY (`c1`) USING BTREE ) ENGINE=ndbcluster DEFAULT CHARSET=utf8 [case 1] in mysql 5.1.23 with ndb mysql> select version(); +---------------+ | version() | +---------------+ | 5.1.23-rc-log | +---------------+ 1 row in set (0.00 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 1463340 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 1462262 | +----------+ 1 row in set (6.32 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 1462262 | +----------+ 1 row in set (6.92 sec) [case 2] in mysql5.1.23-ndb6.2.15 mysql> select version(); +-----------------------+ | version() | +-----------------------+ | 5.1.23-ndb-6.2.15-log | +-----------------------+ 1 row in set (0.00 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 1463340 | +----------+ 1 row in set (0.08 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 1462262 | +----------+ 1 row in set (15.20 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 1462262 | +----------+ 1 row in set (15.68 sec) [case 3] in mysql5.1.27-ndb6.3.17 mysql> select version(); +-----------------------+ | version() | +-----------------------+ | 5.1.27-ndb-6.3.17-log | +-----------------------+ 1 row in set (0.00 sec) mysql> select count(*) from t1; +----------+ | count(*) | +----------+ | 1463340 | +----------+ 1 row in set (0.08 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 1462262 | +----------+ 1 row in set (16.55 sec) mysql> select count(*) from t1 where length(c3) < 256; +----------+ | count(*) | +----------+ | 1462262 | +----------+ 1 row in set (15.04 sec) <Result> mysql5.1.23rc(with ndb) - 6s ndb6.2 - 15s ndb6.3 - 15s both ndb6.2 and ndb6.3 is apparently 2 times slower than mysql5.1.23rc(with ndb) version. this is corresopond to all table full scan.
[12 Oct 2009 8:55]
Jonas Oreland
isnt this likely fixed by magnus changes to ndb usage of clock_monotonic reassign to magnus (who might close this as duplicate)
[26 Oct 2009 12:47]
Magnus Blåudd
Can't really find any slow down. Using MySQL Cluster 7.0 I get better or same values. See below where I ran the query 5 times on a table with 400000 rows. If this query is important to a specific application, I would suggest rewriting the query to avoid shipping all records in the whole table over to mysqld for evaluation of the expression. Please turn to MySQL Support for further help. / Magnus select version(); version() 5.1.41-pb16-log CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c3` text, PRIMARY KEY (`c1`) USING BTREE ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; select count(*) from t1; 400000 set @start = now(); select count(*) from t1 where length(c3) < 256; count(*) 49601 select count(*) from t1 where length(c3) < 256; count(*) 49601 select count(*) from t1 where length(c3) < 256; count(*) 49601 select count(*) from t1 where length(c3) < 256; count(*) 49601 select count(*) from t1 where length(c3) < 256; count(*) 49601 set @end = now(); select TIMEDIFF(@end, @start); TIMEDIFF(@end, @start) 00:05:17 drop table t1; select version(); version() 5.1.39-ndb-7.0.9-pb299-log CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c3` text, PRIMARY KEY (`c1`) USING BTREE ) ENGINE=ndbcluster DEFAULT CHARSET=utf8; select count(*) from t1; 400000 set @start = now(); select count(*) from t1 where length(c3) < 256; count(*) 49579 select count(*) from t1 where length(c3) < 256; count(*) 49579 select count(*) from t1 where length(c3) < 256; count(*) 49579 select count(*) from t1 where length(c3) < 256; count(*) 49579 select count(*) from t1 where length(c3) < 256; count(*) 49579 set @end = now(); select TIMEDIFF(@end, @start); TIMEDIFF(@end, @start) 00:04:37 drop table t1;