Bug #39990 poor performace of "SHOW VARIABLES"
Submitted: 12 Oct 2008 16:19 Modified: 13 Oct 2008 9:00
Reporter: wang xiaolin Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.23, 5.1.28 OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D3 (Medium)

[12 Oct 2008 16:19] wang xiaolin
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 。
[12 Oct 2008 17:05] Valeriy Kravchuk
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.
[13 Oct 2008 9:00] Sergei Glukhov
'SHOW VARIABLES' is based on I_S _VARIABLES tables,
so it's duplicate of bug#39955.