| Bug #39955 | SELECT on INFORMATION_SCHEMA.GLOBAL_VARIABLES takes too long | ||
|---|---|---|---|
| Submitted: | 9 Oct 2008 15:52 | Modified: | 10 Dec 2008 20:35 |
| Reporter: | Oli Sennhauser | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 5.1.27-ndb-6.3.17-innodb-log, 5.1-bzr | OS: | Any |
| Assigned to: | Sergei Glukhov | CPU Architecture: | Any |
[9 Oct 2008 18:56]
Valeriy Kravchuk
Thank you for a problem report. I can not repeat this "concistency" of slow execution with 5.1.28 on Windows, for example: mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = ' hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | toshiba-user | +---------------+----------------+ 1 row in set (0.55 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = ' hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | toshiba-user | +---------------+----------------+ 1 row in set (0.14 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = ' hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | toshiba-user | +---------------+----------------+ 1 row in set (0.11 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = ' hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | toshiba-user | +---------------+----------------+ 1 row in set (0.09 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = ' hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | toshiba-user | +---------------+----------------+ 1 row in set (0.11 sec) So, please, try to repeat on newer 5.1.x versions and give some details (like my.cnf settings).
[10 Oct 2008 8:34]
Oli Sennhauser
reproducing the bug
Attachment: bug_39955.log (application/octet-stream, text), 15.64 KiB.
[10 Oct 2008 8:35]
Oli Sennhauser
used my.cnf
Attachment: my.cnf (application/octet-stream, text), 189 bytes.
[10 Oct 2008 9:00]
Oli Sennhauser
revised version. forget about the old one...
Attachment: bug_39955.log (application/octet-stream, text), 6.63 KiB.
[10 Oct 2008 9:42]
Sveta Smirnova
Consistent slow result on Mac for me: mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (1.05 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (0.59 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (0.57 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (0.59 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (0.52 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (0.54 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | apple | +---------------+----------------+ 1 row in set (0.52 sec)
[10 Oct 2008 10:06]
Sveta Smirnova
Thank you for the feedback. Verified as described in second log file.
[12 Oct 2008 16:41]
wang xiaolin
hi Oli Sennhauser : I meet this problem too . This boring me so much .. I increase to parameter to my MySQL5.1 : max_heap_table_size=64M tmp_table_size=64M then ,the "show variables;" or "select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; " will cost 59 ms 。 but that is too long ,in MySQL 5.0 it just take 12ms . hope get some good news from mysql .
[13 Oct 2008 7:24]
Oli Sennhauser
Hi Wang When this is an issue for you, I can recommend the following work around (for xxx_VARIABLES only!): * Create your own table (for example CREATE TABLE test.GLOBAL_VARIABLES LIKE INFORMATION_SCHEMA.GLOBAL_VARIABLES). * In my case I had to increase the MAX_HEAP_TABLE_SIZE to 20M (if you do not want to do this, change the VARCHAR(very big) to a VARCHAR(128) or so. * Add a primary key on the VARIABLE_NAME field (alter table GLOBAL_VARIABLES add PRIMARY KEY (VARIABLE_NAME);) * Then periodically update this table by a replace into GLOBAL_VARIABLES select * from INFORMATION_SCHEMA.GLOBAL_VARIABLES (for example every 5 minutes or once a day or so...) The you get the hyper-fast results you expect! Regards Oli
[13 Oct 2008 10:46]
MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=39990 was marked as duplicaate of this bug.
[14 Oct 2008 0:56]
wang xiaolin
hi Oli Sennhauser : this is a good idea really . But , "show variables " is invoked at connector/j driver when it connect to mysql . I do not want to modifiy the driver source ..
[17 Oct 2008 8:20]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/56438 2672 Sergey Glukhov 2008-10-17 Bug#39955 SELECT on INFORMATION_SCHEMA.GLOBAL_VARIABLES takes too long VARIABLE_VALUE field is decreased to 1024 symbols. (affected I_S tables: GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, SESSION_STATUS). The only variable which can be longer than 1024 is init_connect. The variable will be truncated with warning. Additional fix: Added where condition filter which speed up queries which have where condition with expressions which use VARIABLE_NAME field.
[24 Oct 2008 8:40]
Alexey Botchkov
ok to push
[13 Nov 2008 12:17]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/58625 2708 Sergey Glukhov 2008-11-13 Bug#39955 SELECT on INFORMATION_SCHEMA.GLOBAL_VARIABLES takes too long VARIABLE_VALUE field is decreased to 1024 symbols. (affected I_S tables: GLOBAL_VARIABLES, SESSION_VARIABLES, GLOBAL_STATUS, SESSION_STATUS). The only variable which can be longer than 1024 is init_connect. The variable will be truncated with warning. Additional fix: Added where condition filter which speed up queries which have where condition with expressions which use VARIABLE_NAME field.
[13 Nov 2008 13:01]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/58628 2925 Sergey Glukhov 2008-11-13 [merge] 5.1-bugteam->6.0-bugteam merge(bug#39955, bug#38909)
[8 Dec 2008 10:19]
Bugs System
Pushed into 5.1.31 (revid:sergey.glukhov@sun.com-20081113120911-8e7qk91hc82tfdr0) (version source revid:azundris@mysql.com-20081114081134-pswy3m5go7r64m1p) (pib:5)
[8 Dec 2008 11:32]
Bugs System
Pushed into 6.0.9-alpha (revid:sergey.glukhov@sun.com-20081113125316-lt0z01zqh50w8gva) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)
[10 Dec 2008 20:35]
Paul DuBois
Noted in 5.1.31, 6.0.9 changelog. Retrieval speed from the following INFORMATION_SCHEMA tables was improved by shortening the VARIABLE_VALUE column to 1024 characters: GLOBAL_VARIABLES, SESSIONL_VARIABLES, GLOBAL_STATUS, and SESSIONL_STATUS. As a result of this change, any variable value longer than 1024 characters will be truncated with a warning. This affects only the init_connect system variable.
[10 Dec 2008 20:36]
Paul DuBois
Typo in previous comment: SESSIONL_VARIABLES and SESSIONL_STATUS should be SESSION_VARIABLES and SESSION_STATUS.
[19 Jan 2009 11:21]
Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 12:59]
Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:05]
Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
Description: Running a simple single row fetch query on the I_S.GLOBAL_VARIABLES table takes consistently half a second!!! How to repeat: SELECT NULL, CONCAT('App sim from ', USER(), ' on ', VARIABLE_VALUE), NULL FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +------+---------------------------------------------------------+------+ | NULL | CONCAT('App sim from ', USER(), ' on ', VARIABLE_VALUE) | NULL | +------+---------------------------------------------------------+------+ | NULL | App sim from root@localhost on master | NULL | +------+---------------------------------------------------------+------+ 1 row in set (0.47 sec) explain SELECT NULL, CONCAT('App sim from ', USER(), ' on ', VARIABLE_VALUE), NULL FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +----+-------------+------------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | GLOBAL_VARIABLES | ALL | NULL | NULL | NULL | NULL | NULL | Using where | +----+-------------+------------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) It is not related to the CONCAT SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES where VARIABLE_NAME = 'hostname'; +---------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------+----------------+ | HOSTNAME | master | +---------------+----------------+ 1 row in set (0.50 sec) Suggested fix: Adding the index could probably help. I am wondering why it takes at all so long. a FTS on a 200 row table should also be very fast. Is the reason possibly the varchar(20480) field???