#2099 slow information_schema retrieval

2.8.0.3
fixed
Marc Delisle
Interface (555)
5
2013-06-11
2006-04-07
mpopp75
No

I tried to update phpMyAdmin for www.db4free.net, which
stores 4,400 databases and 87,000 tables, from 2.7.0-
pl1 to 2.8.0.3. After that, it wasn't possible to open
a database anymore.

I found out that pma 2.8 makes extensive use of
information_schema and I guess that that's the cause of
the problem.

Discussion

  • Marc Delisle
    Marc Delisle
    2006-04-07

    • assigned_to: nobody --> lem9
     
  • Marc Delisle
    Marc Delisle
    2006-04-07

    Logged In: YES
    user_id=210714

    Do you mean that a normal user that only can see a limited
    number of databases, cannot work with version 2.8.0.3 on
    your server?

    We knew that PMA does not support thousands of databases
    when view with a privileged account.

     
  • mpopp75
    mpopp75
    2006-04-07

    Logged In: YES
    user_id=1198806

    The databases are displayed correctly, but when a database
    is selected, the server does not manage to open the database
    under certain circumstances.

    You can try it out here:
    http://www.db4free.net/phpMyAdmin-2.8.0.3/

    with user pma280 and password pma280.

    I inserted 50 tables into database pma280. While selecting
    this database, I find these queries in this processlist
    output:

    Id: 17171
    User: pma280
    Host: mis04.de:41773
    db: pma280
    Command: Query
    Time: 2
    State: preparing
    Info: SELECT TABLE_NAME FROM information_schema.VIEWS
    WHERE TABLE_SCHEMA = 'p
    ma280' AND TABLE_NAME = 'tt2'

    These queries from information_schema.VIEWS seem to
    immensely slow down the process.

     
  • Marc Delisle
    Marc Delisle
    2006-04-08

    Logged In: YES
    user_id=210714

    I noticed the problem on your server. You are using an older
    client library (4.0.22) and the "mysql" extension, which is
    not recommended on your MySQL 5.0.20.

    On my test server which is PHP 5.1.3RC1, client lib 4.1.12
    (not perfect but better than 4.0.22), "mysqli" extension,
    MySQL 5.1.7-beta, I have put 4400 databases with 20 tables
    in each (88000 tables total). When a simple user clicks on
    his db name, he waits 10 seconds then the db appears and he
    can work normally. Not perfect but at least, work is possible.

    After a click on information_schema, a delay of 25 seconds
    happens. Not funny.

    I'm not saying that we can't improve phpMyAdmin here, but
    can you test with the minimum recommended library and mysqli?

     
    • labels: 509104 --> Interface
     
  • Logged In: YES
    user_id=326580

    MySQL recommends that the mysql client lib version should be
    equal to the server version.

    But anyway i saw this problem of slow information_schema
    often too, possible we should file a bug report on mysql -
    or check if there is already anything about this.

     
  • Marc Delisle
    Marc Delisle
    2006-05-14

    Logged In: YES
    user_id=210714

    mpopp75, any feedback please?

     
  • mpopp75
    mpopp75
    2006-05-14

    Logged In: YES
    user_id=1198806

    This is now reported as MySQL bug: http://bugs.mysql.com/
    bug.php?id=19588

    I don't have influence on the libraries on this specific
    server, but I think that this problem is much more
    widespread. Several client applications that use
    information_schema to retrieve meta data suffer from poor
    performence when the server stores high amounts of data -
    like MySQL Front and SQLyog.

     
  • Marc Delisle
    Marc Delisle
    2006-05-15

    Logged In: YES
    user_id=210714

    Thanks Markus. Can we close this phpMyAdmin bug report? I
    could transform it into a FAQ entry.

     
  • Logged In: YES
    user_id=326580

    i think we should let this bug open - just wait how fast
    mysql is doing something on this problem - if it takes too
    long we should do someting about this - possible a config
    switch to not use information_schema, should be easy.

     
  • mpopp75
    mpopp75
    2006-05-15

    Logged In: YES
    user_id=1198806

    I don't know how much work it is to change phpMyAdmin to use
    SHOW commands in the mean time and how long it takes until
    MySQL solves this issue. But cybot_tm's suggestion looks
    very reasonable - to let this bug report open and if it
    takes longer on MySQL's side to consider changing
    phpMyAdmin's way of retrieving the data.

     
  • Marc Delisle
    Marc Delisle
    2006-05-15

    • summary: Problems with big servers (information_schema?) --> slow information_schema retrieval
    • assigned_to: lem9 --> nobody
     
  • Marc Delisle
    Marc Delisle
    2006-12-26

    Logged In: YES
    user_id=210714
    Originator: NO

    I just made some tests with MySQL 5.0.27 and phpMyAdmin 2.10.0-dev. My test server has 4400 databases, each containing 19 tables, each containing one column.

    With our normal SELECT... FROM INFORMATION_SCHEMA logic:
    - initial nav display: 14 sec.
    - choose a db and see its display: 16 sec.

    Then I tweaked libraries/database_interface.lib.php, in PMA_DBI_get_tables_full() I forced it to use the older SHOW TABLE STATUS FROM ... logic:
    - initial nav display: 12 sec.
    - choose a db and see its display: 16 sec.
    --------

    It looks like they fixed this MySQL problem, do you agree?
    If I get some confirmations here, I could modify the function to use the "old" logic if MySQL < 5.0.27 is detected.

     
  • Marc Delisle
    Marc Delisle
    2006-12-26

    • assigned_to: nobody --> lem9
     
  • Marc Delisle
    Marc Delisle
    2007-01-17

    Logged In: YES
    user_id=210714
    Originator: NO

    Another test under MySQL 5.0.27: on the server with 4400 databases, I populated one of them with 400 tables and it seems to take forever to get the db list in the right frame.

     
  • mpopp75
    mpopp75
    2007-01-21

    Logged In: YES
    user_id=1198806
    Originator: YES

    Hi,

    thanks a lot! I have installed phpMyAdmin 2.9.2 including this patch at db4free.net and it really seems to be a bit faster. I have to watch it over time until I can definitely say how much.

     
  • Marc Delisle
    Marc Delisle
    2007-03-08

    Logged In: YES
    user_id=210714
    Originator: NO

    Can we close this?

     
  • Marc Delisle
    Marc Delisle
    2007-06-01

    Logged In: YES
    user_id=210714
    Originator: NO

    Markus?

     
  • Marc Delisle
    Marc Delisle
    2007-09-05

    • status: open --> pending-fixed
     
  • Logged In: YES
    user_id=1312539
    Originator: NO

    This Tracker item was closed automatically by the system. It was
    previously set to a Pending status, and the original submitter
    did not respond within 14 days (the time period specified by
    the administrator of this Tracker).

     
    • status: pending-fixed --> closed-fixed
     
  • Michal Čihař
    Michal Čihař
    2013-06-11

    • Status: closed-fixed --> fixed