MySQL Bugs: #39990: poor performace of "SHOW VARIABLES"

Bug #39990	poor performace of "SHOW VARIABLES"
Submitted:	12 Oct 2008 18:19	Modified:	13 Oct 2008 11:00
Reporter:	wang xiaolin
Status:	Duplicate
Category:	Server: I_S	Severity:	S3 (Non-critical)
Version:	5.1.23, 5.1.28	OS:	Any
Assigned to:	Sergey Gluhov	Target Version:
Tags:	regression
Triage:	Needs Triage: D3 (Medium)

Description:
when I execute this command "SHOW VARIABLES" in MySQL 5.1.23,
It cost 120+ ms .

after I add 
max_heap_table_size=64M
tmp_table_size=64M
in my.cnf

It cost 59 ms .

but in the Same machine , if run mysql 5.0.33,
It cost only 12 ms .

this is mysql's my.cnf:

[root@mfx228 local]# cat /etc/my.cnf |grep -vE "#|^$"
[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache =256
sort_buffer_size =1M
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
transaction-isolation = READ-COMMITTED
max_connections=1024
skip-name-resolve  
back_log = 300
log-slow-queries = slow.log
long_query_time = 1
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_size =128M 
innodb_log_buffer_size = 16M 
innodb_log_file_size =8M 
innodb_file_per_table
innodb_status_file
innodb_thread_concurrency= 32
query_cache_size=512M
query_cache_limit=2M
max_heap_table_size=64M
tmp_table_size=64M
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

CentOS release 4.6 (Final)
[root@mfx228 local]# uname
Linux
 uname -a
Linux mfx228 2.6.9-67.ELsmp #1 SMP Fri Nov 16 12:48:03 EST 2007 i686 i686 i386 GNU/Linux

How to repeat:
time mysql -uroot -p123456 -h10.4.6.228 -e "SHOW VARIABLES "
real    0m0.061s
user    0m0.005s
sys     0m0.006s

compair to MySQL 5.1 and 5.0 。

Thank you for a problem report. Indeed, I need 0.02 sec at most with 5.0.66a on Windows to
get 229 variables, and 0.09 sec at bets on the same laptop to get 266 variables with
5.1.28.

'SHOW VARIABLES' is based on I_S _VARIABLES tables,
so it's duplicate of bug#39955.