Bug #40100 show variables command is very slow in ndb6.3.7 with innodb
Submitted: 17 Oct 2008 9:15 Modified: 18 Oct 2012 13:48
Reporter: ws lee Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:mysql5.1.27-ndb6.3.17 OS:Any
Assigned to: Omer Barnir CPU Architecture:Any

[17 Oct 2008 9:15] ws lee
Description:
In Innodb engine activated state, "show variable" command is very very slow.

Use this script in testing.
# vi test-runtime-show_variables.sh
date
i=0
while [ $i -lt 50 ]
do
  i=`expr $i + 1`
  /usr/bin/echo "$i"
  /usr/local/mysql5.1.27-ndb6.3.17/bin/mysql -uroot -e"show variables"
done
date

How to repeat:
- Case 1(In innodb engine activated state)
select engine,support from information_schema.engines where engine='innodb'

mysql> select engine,support from information_schema.engines where engine='innodb';
+--------+---------+
| engine | support |
+--------+---------+
| InnoDB | YES     | 
+--------+---------+
1 row in set (0.00 sec)

# ./test-runtime-show_variables.sh
Fri Oct 17 18:09:21 JST 2008
...
...
Fri Oct 17 18:09:27 JST 2008

this case taskes time 6sec.

- Case 2(In innodb engine Inactivated state)
# vi /etc/my.cnf
skip-innodb

mysql> select engine,support from information_schema.engines where engine='innodb';
Empty set (0.00 sec)

# ./test-runtime-show_variables.sh
Fri Oct 17 18:10:36 JST 2008
...
...
Fri Oct 17 18:11:07 JST 2008

this case takes time 30sec.

In my application, short query is repeated very much.
in this time, in mysql show variables is repeated every time all query.
in this result, my application performance very very slow in innodb active.
[17 Oct 2008 9:21] ws lee
I am sorry.
Correct reporting is this.

- Case 1(In innodb engine Inactivated state)
select engine,support from information_schema.engines where engine='innodb'
# vi /etc/my.cnf
skip-innodb

mysql> select engine,support from information_schema.engines where engine='innodb';
Empty set (0.00 sec)

# ./test-runtime-show_variables.sh
Fri Oct 17 18:09:21 JST 2008
...
...
Fri Oct 17 18:09:27 JST 2008

this case taskes time 6sec.

- Case 2(In innodb engine activated state)
# vi /etc/my.cnf
# skip-innodb 

mysql> select engine,support from information_schema.engines where engine='innodb';
+--------+---------+
| engine | support |
+--------+---------+
| InnoDB | YES     | 
+--------+---------+
1 row in set (0.00 sec)

# ./test-runtime-show_variables.sh
Fri Oct 17 18:10:36 JST 2008
...
...
Fri Oct 17 18:11:07 JST 2008

this case takes time 30sec.

In my application, short query is repeated very much.
thus, in mysql show variables is repeated every time all query.
in this result, my application performance very very slow in innodb active.
[27 Oct 2008 17:28] Jonathan Miller
Hi,

I do see a diff if loop values are set high enough, but I do not see this as a cluster issue as the cluster engine is not involved during execution.

#skip-innodb loops =50
#1 (6 seconds)
Mon Oct 27 18:02:49 CET 2008
Mon Oct 27 18:02:55 CET 2008
#2 (6 seconds)
Mon Oct 27 18:02:57 CET 2008
Mon Oct 27 18:03:03 CET 2008
#3 (6 seconds)
Mon Oct 27 18:03:07 CET 2008
Mon Oct 27 18:03:13 CET 2008
#4 (7 Seconds)
Mon Oct 27 18:03:14 CET 2008
Mon Oct 27 18:03:21 CET 2008
Average return = 6.25 seconds

skip-innodb loops =50
#1 (2 seconds)
Mon Oct 27 18:04:10 CET 2008
Mon Oct 27 18:04:12 CET 2008
#2 (3 seconds)
Mon Oct 27 18:04:14 CET 2008
Mon Oct 27 18:04:17 CET 2008
#3 (3 seconds)
Mon Oct 27 18:04:18 CET 2008
Mon Oct 27 18:04:21 CET 2008
#4 (2 seconds)
Mon Oct 27 18:04:31 CET 2008
Mon Oct 27 18:04:33 CET 2008
Average return = 2.5 seconds

#skip-innodb loops = 500 Avg MySQLD cpu usage 105%
#1  (66 seconds)
Mon Oct 27 18:07:50 CET 2008
Mon Oct 27 18:08:56 CET 2008
#2  (67 seconds)
Mon Oct 27 18:09:17 CET 2008
Mon Oct 27 18:10:24 CET 2008
Average return 66.5 seconds

skip-innodb loops = 500 Avg MySQLD cpu usage 88%
#1 (24 seconds)
Mon Oct 27 18:20:12 CET 2008
Mon Oct 27 18:20:36 CET 2008
#2 (25 second)
Mon Oct 27 18:20:58 CET 2008
Mon Oct 27 18:21:23 CET 2008
Average return 24.5 seconds
[27 Oct 2008 17:28] Jonathan Miller
Script. You will need to edit for your system

Attachment: test_runtime_show.sh (application/x-shellscript, text), 167 bytes.

[28 Oct 2008 1:25] ws lee
To. Jonathan Miller

hi, this is not likely cluster issue.
just, very slow in showing of innodb variables.
if innodb variables off, not slow.
[18 Sep 2012 13:48] MySQL Verification Team
Please try with most released server version. Thanks.
[19 Oct 2012 1:00] 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".