#2606 Optional disable use of (slow) INFORMATION_SCHEMA

3.5.6
wont-fix
nobody
5
2014-08-22
2007-12-12
Marc Delisle
No

Discussion

1 2 > >> (Page 1 of 2)
  • Logged In: YES
    user_id=326580
    Originator: NO

    MySQL is working on this

    http://bugs.mysql.com/bug.php?id=19588

    it is possible that making INFORMATION_SCHEMA optional will only help for a short time, cause as i know sooner or later SHOW commands will be mapped to the same code as INFORMATION_SCHEMA

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    just another note: if you encounter this problem, you should also ask your provider to update MySQL to at least 5.1.22

     
    • summary: Optional INFORMATION_SCHEMA --> Optional disable use of (slow) INFORMATION_SCHEMA
     
  • Logged In: YES
    user_id=326580
    Originator: NO

    added config switch in SVN trunk (3.0-dev), if no problems occur will be backportet to QA_2_11 - please test

     
    • labels: --> Navigation/Usability
    • assigned_to: nobody --> cybot_tm
    • status: open --> open-accepted
     
  • Jürgen Wind
    Jürgen Wind
    2007-12-13

    Logged In: YES
    user_id=1383652
    Originator: NO

    hmm,
    the last modification broke the $cfg['MaxTableList'] limit.

    ---
    >you should also ask your provider to update MySQL to at least 5.1.22
    http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
    says as of 5.1.23 and *only* if you specify an exact db name in your queries
    (which is not the case when you scan all databases via I_S)

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    "which is not the case when you scan all databases via I_S"

    disable tree view in navigation frame, do not use server_database with full statistics

    i intentionally disabled use of I_S only on common processed places in the code

     
  • Marc Delisle
    Marc Delisle
    2007-12-13

    Logged In: YES
    user_id=210714
    Originator: YES

    With my setup I am getting mixed results with trunk than with QA_2_11.

    Testing with 5.1.22
    $cfg['DisableIS'] = true
    $cfg['MaxDbList'] = 100;
    $cfg['MaxTableList'] = 100;
    Number of datatabases: 10000
    Two of them containing 10000 tables, all other are empty.

    With QA_2_11, initial login: 11 sec.
    open a db of 10000 tables: 50 sec.
    go to second page of this db: 18 sec.
    in navi frame, go to any page of the db list: 1 sec.

    With trunk, initial login: 6 sec.
    open a db of 10000 tables: I stopped to count after 2 min!

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    improved processing of tables in database_structure view - please check again

    using MySQL 5.1.22 i cannot see differences in performance using I_S or SHOW, they seem very random - but in most cases having a db with 1000 tables both variants take about 3 seconds (summarized query times displayed in footer with $cfg['DBG']['enable'] = true)

     
  • Marc Delisle
    Marc Delisle
    2007-12-13

    Logged In: YES
    user_id=210714
    Originator: YES

    P.S. I suggest creating another variable for this debugging, because $cfg['DBG']['enable'] is supposed to be for the DBG extension stuff.

    Yes, much better.
    With trunk, initial login: 5 sec.
    open a db of 10000 tables: 44 sec.
    go to second page of this db: 15 sec.
    in navi frame, go to any page of the db list: 1 sec.

    open another db of 10000 tables: 29 sec.

    13 queries executed21 times in 47.6904308796 seconds

    Array
    (
    [queries] => Array
    (
    [62dfb7024dcbed7e20d94826d6bbe2d2] => Array
    (
    [count] => 2
    [query] => SELECT VERSION()
    [time] => 0.000468015670776
    )

    [d82e8428e8f6c504f5917495f5a952a8] => Array
    (
    [count] => 2
    [query] => SET NAMES 'utf8' COLLATE 'utf8_general_ci';
    [time] => 0.000532150268555
    )

    [0821c5d2870c5ca803051cc3f5e77224] => Array
    (
    [count] => 2
    [query] => SHOW CHARACTER SET;
    [time] => 0.00114107131958
    )

    [21c9749980e3011ad1f59dc2999284e8] => Array
    (
    [count] => 2
    [query] => SHOW COLLATION;
    [time] => 0.00191617012024
    )

    [511ba1c5b3abd4dc4a9d80e9ccfec7aa] => Array
    (
    [count] => 2
    [query] => SHOW DATABASES;
    [time] => 0.586776018143
    )

    [1b628edd8d4af33df523cba31cac8304] => Array
    (
    [count] => 2
    [query] => SHOW VARIABLES LIKE 'profiling'
    [time] => 0.00111293792725
    )

    [aaaaca11a10761a6cbddbef270d3ec4f] => Array
    (
    [count] => 3
    [query] => SELECT COUNT(*) FROM mysql.user
    [time] => 0.000699043273926
    )

    [e68fa7dee50b737170de8f6026228617] => Array
    (
    [count] => 1
    [query] => SHOW TABLES FROM `db1`;
    [time] => 26.8426251411
    )

    [4a66725aea3df04ca91b558ba113d115] => Array
    (
    [count] => 1
    [query] => SHOW TABLE STATUS FROM `db1`;
    [time] => 19.5926659107
    )

    [6552fcadce6a493a1cba1b91fc0a48a8] => Array
    (
    [count] => 1
    [query] => SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'db1' LIMIT 1;
    [time] => 0.00379514694214
    )

    [6159910ecb80988445903cf47fc85032] => Array
    (
    [count] => 1
    [query] => SHOW VARIABLES LIKE 'storage_engine';
    [time] => 0.00130295753479
    )

    [f28bd62919afdeb2f5142ecad1fb0030] => Array
    (
    [count] => 1
    [query] => SELECT SPECIFIC_NAME,ROUTINE_NAME,ROUTINE_TYPE,DTD_IDENTIFIER FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA= 'db1';
    [time] => 0.0616149902344
    )

    [ca11aa0c1bbd6278395f2ddd40a84a41] => Array
    (
    [count] => 1
    [query] => SHOW GRANTS
    [time] => 0.002436876297
    )

    )

    )

     
  • Marc Delisle
    Marc Delisle
    2008-01-06

    Logged In: YES
    user_id=210714
    Originator: YES

    Sebastian, any comment?

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    still i cannot see performance differences with I_S or SHOW in latest MySQL versions ...

     
  • Marc Delisle
    Marc Delisle
    2008-01-17

    Logged In: YES
    user_id=210714
    Originator: YES

    With how many databases, how many tables, and which version exactly?

    Also, about my comment for $cfg['DBG']?

     
  • Vogon Jelz
    Vogon Jelz
    2008-01-21

    Logged In: YES
    user_id=1957055
    Originator: NO

    I found out a few things about the usage of `information_schema` on hosted servers e.g. STRATO.
    I_S is not always slow. But pma sometimes creates queries that will be executed very slow. those should be changed.
    ---------------------------------------------
    1. in PMA_DBI_get_tables_full
    the word BINARY in the query makes it extremely slow:

    without BINARY:
    2008-01-21 00:33:50 - SELECT *, [...] FROM `information_schema`.`TABLES`
    WHERE `TABLE_SCHEMA` IN ('DB321438')
    2008-01-21 00:33:50 - executed in 0.01612114906311

    with BINARY:
    2008-01-21 00:36:43 -
    SELECT *, [...] FROM `information_schema`.`TABLES`
    WHERE BINARY `TABLE_SCHEMA` IN ('DB321438')
    2008-01-21 00:37:01 - executed in 17.983309030533
    ---------------------------------------------
    2. getting the table headers in sql.php.inc
    if the original query in the SQL form goes to I_S for example:
    SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` IN ('DB321438')

    PMA executes this query in 0.02 seconds then it wants to create the headers:
    2008-01-21 01:13:51 - SELECT COUNT(*) FROM `information_schema`.`TABLES`
    2008-01-21 01:14:15 - executed in 23.892692089081

    refering to http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
    non constant or missing database names should be avoided.
    I think the same WHERE condition should be added.
    ---------------------------------------------

    I made those two small modifications to my pma 3.0-dev and now it is fast again.
    Even with 'I_S' switched on.

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    thank you, i will look into it

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    BINARY is required to distinguish between TEST and test - as stated in the comment

     
  • Marc Delisle
    Marc Delisle
    2008-01-21

    Logged In: YES
    user_id=210714
    Originator: YES

    I wonder what's more important: case sensitivity on db names, or speed. Maybe we could add a cfg parameter for those who need to support case sensitivity on db names? By default it would be FALSE, then the default for $cfg['Servers'][$i]['DisableIS'] could be also false?

     
  • Logged In: YES
    user_id=326580
    Originator: NO

    @vogon_jelz: i have tested on another ISP (Hosteurope, 5.0.32) and cannot see any differences with or without BINARY, what MySQL version is STRATO running?

    about 2): if someone wants to browse I_S pma cannot just say, "no, this will be too slow", or what do you expect phpMyAdmin should do?

     
  • Vogon Jelz
    Vogon Jelz
    2008-01-22

    Logged In: YES
    user_id=1957055
    Originator: NO

    STRATO is running version 5.0.51

    about BINARY): nobody should use databases with same names in different cases on one server. I agree with lem9, case sensitivity could be off by default.

    about 2): of course not. ;-)
    If somebody enters a SQL to I_S without a WHERE it's his own fault.

    But if I enter a correct SQL with a WHERE condition for example
    "SELECT * FROM `information_schema`.`TABLES`
    WHERE `TABLE_SCHEMA` IN ('DB321438')"
    pma could create the following to get the table headers:
    "SELECT COUNT(*) FROM `information_schema`.`TABLES`
    WHERE `TABLE_SCHEMA` IN ('DB321438')"

    Would it help if I create a test database with FTP-access for you on my webspace?

     
  • Vogon Jelz
    Vogon Jelz
    2009-08-19

    After months using my own patched PMA I downloaded PMA3.2.1 to see what has changed.
    The new version looks very good...
    ...except it is still slow on STRATO hosted server running mySQL 5.0.67

    Even though it is not a fault in PMA but an implementation problem in mySQL and perhaps an access problem at STRATO it makes it annoying to use pma 3.x

    The reason is still the "BINARY" in the WHERE clause created in database_interface.lib.php.
    If you delete it, the query to I_S takes less than 0.1s instead of 6 seconds.

    for debug output see also
    https://sourceforge.net/forum/message.php?msg_id=7570824
    (post from today)

     
  • Marc Delisle
    Marc Delisle
    2012-01-15

    What about recent MySQL server like 5.5.x?

     
  • Marc Delisle
    Marc Delisle
    2012-01-15

    • assigned_to: cybot_tm --> nobody
    • status: open-accepted --> pending-accepted
     
  • Marc Delisle
    Marc Delisle
    2013-01-18

    • status: pending-accepted --> open
    • milestone: --> 2.11.9
     
  • Marc Delisle
    Marc Delisle
    2013-01-20

    • milestone: 2.11.9 --> 3.5.6
     
1 2 > >> (Page 1 of 2)