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:
None 
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
Description:
The same query is slow two times in ndb6.2.15 than mysql5.1.23
In mysql 5.1.23 takes time 10s.
but, ndb 5.1.23 takes time 20s.

How to repeat:
firstly, in mysql 5.1.23
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.1.23-rc-log | 
+---------------+
1 row in set (0.00 sec)

mysql> desc t1;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| c1    | int(11)       | NO   | PRI | NULL    | auto_increment | 
| c2    | varchar(15)   | NO   |     | NULL    |                | 
| c3    | text          | YES  |     | NULL    |                | 
| c4    | varchar(1000) | YES  |     | NULL    |                | 
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.06 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: ndbcluster
        Version: 10
     Row_format: Dynamic
           Rows: 100
 Avg_row_length: 0
    Data_length: 311492608
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 917152
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.39 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   889331 | 
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from t1 where length(c3) < 256;
+----------+
| count(*) |
+----------+
|   889016 | 
+----------+
1 row in set (10.51 sec)

mysql> select count(*) from t1 where length(c3) < 256;
+----------+
| count(*) |
+----------+
|   889016 | 
+----------+
1 row in set (9.61 sec)

next, in ndb 6.2.15
mysql> select version();
+-----------------------+
| version()             |
+-----------------------+
| 5.1.23-ndb-6.2.15-log | 
+-----------------------+
1 row in set (0.01 sec)

mysql> desc t1;
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| c1    | int(11)       | NO   | PRI | NULL    | auto_increment | 
| c2    | varchar(15)   | NO   |     | NULL    |                | 
| c3    | text          | YES  |     | NULL    |                | 
| c4    | varchar(1000) | YES  |     | NULL    |                | 
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.02 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: ndbcluster
        Version: 10
     Row_format: Dynamic
           Rows: 889331
 Avg_row_length: 32
    Data_length: 120324096
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 917152
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|   889331 | 
+----------+
1 row in set (0.06 sec)

mysql> select count(*) from t1 where length(c3) < 256;
+----------+
| count(*) |
+----------+
|   889016 | 
+----------+
1 row in set (20.19 sec)

mysql> select count(*) from t1 where length(c3) < 256;
+----------+
| count(*) |
+----------+
|   889016 | 
+----------+
1 row in set (18.97 sec)
[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;