Bug #38090 "show variables" command is very slow in ndb 6.2, ndb 6.3

• Submitted: 14 Jul 2008 4:44
• Modified: 18 Jan 2009 14:07
• Reporter: ws lee
• Status: No Feedback
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: ndb 6.2, ndb 6.3
• OS: Any (Linux, Solaris)
• Assigned to: Assigned Account
• CPU Architecture: Any

[14 Jul 2008 4:44] ws lee

Description:
"show variables" command is very slow in ndb 6.2, ndb 6.3.
(but, mysql 5.1.23 with --with-ndbcluster is no problem)
It cause seriously bad response in total web performance.

In ndb 6.3, "show variables" time is 0.3s~1.2s
but In mysql 5.1.23(with ndb), "show variables" time is 0.2s~0.3s

<In ndb 6.3>
mysql> select version();
+-----------------------------+
| version()                   |
+-----------------------------+
| 5.1.23-ndb-6.3.10-telco-log | 
+-----------------------------+

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                    | Transactions | XA  | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| ndbcluster | DEFAULT | Clustered, fault-tolerant tables                           | YES          | NO  | NO         | 

mysql> show variables;
...
...
282 rows in set (0.22 sec)

mysql> show variables;
...
...
282 rows in set (1.04 sec)

<In mysql 5.1.23(with ndb)>
mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.1.23-rc-log | 
+---------------+

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                    | Transactions | XA  | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+-----+------------+
| ndbcluster | DEFAULT | Clustered, fault-tolerant tables                           | YES          | NO  | NO         | 

mysql> show variables;
...
...
273 rows in set (0.03 sec)

mysql> show variables;
...
...
273 rows in set (0.03 sec)

How to repeat:
Run this script.

# vi test-runtime-mysql5.1.23.sh
date
i=0
while [ $i -lt 50 ]
do
  i=`expr $i + 1`
  /usr/bin/echo "$i"
  /usr/local/mysql5.1.23/bin/mysql -uroot -e"show variables"
done
date

# vi test-runtime-ndb6.3.10.sh
date
i=0
while [ $i -lt 50 ]
do
  i=`expr $i + 1`
  /usr/bin/echo "$i"
  /usr/local/ndb6.3.10/bin/mysql -uroot -e"show variables"
done
date

#./test-runtime-mysql5.1.23.sh
Mon Jul 14 13:32:58 JST 2008
...
...
Mon Jul 14 13:33:01 JST 2008

#./test-runtime-ndb6.3.10.sh
Mon Jul 14 13:33:18 JST 2008
...
...
Mon Jul 14 13:34:09 JST 2008

According to upper result,
ndb 6.3.10 took time 51 sec.
but mysql 5.1.23(with ndbcluster) took time just 3 sec.

when i use ndb 6.3.10, it casue serious bad performance my web service.

Please check!

[15 Jul 2008 17:47] Hartmut Holzgraefe

With ndb handler compiled in but not used SHOW VARIABLES times on my box are constantly at 0.3s instead of 0.00 - 0.01 as they used to be, with ndb handler enabled and two data nodes running on the same box times are between 0.3s (typical) and almost 2s (worst case seen so far).

Same for SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES

[16 Jul 2008 0:42] ws lee

To. Hartmut Holzgraefe

Thanks for your check.

Are you run test-runtime-mysql5.1.23.sh?
You must run this script in ndbcluster compiled with 「--with-innodb compile option.」and in ON state of innodb engine.

P.S)
Where can i Bug Triage's definition?

[18 Dec 2008 10:27] Jonas Oreland

no clue to why this is...
needs investigation to determine E/R

[19 Jan 2009 0:02] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".