#4513 phpmyadmin run very slow (information_schema)

[j4m3sbr]

I have the following scenario with 2 servers (shared hosting):

Linux Server #1 - MySQL 5.5 - 8000 databases

Linux Server #2 - MySQL 5.6 - 7000 databases

By placing phpMyAdmin 4.xx, the first access is very slow on both servers (more than 16 seconds to render the page) and innodb_stats_on_metadata is already OFF. Enabling debug, which is unable to identify the queries that it performs the information_schema, as follows:

[1f94c8d385c8b4d595d10b6542624c40] => Array (
[count] => 1
[query] => SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA, (select DB_first_level from ( SELECT distinct SUBSTRING_INDEX(SCHEMA_NAME, '', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE AND SCHEMA_NAME NOT REGEXP 'information_schema' ) t ORDER BY DB_first_level ASC LIMIT 0, 250) t2 where 1 = locate(concat(DB_first_level, ''), concat(SCHEMA_NAME, '_')) order by SCHEMA_NAME ASC
[time] => 3.34156107903
[trace] => Array (
[0] => Array(
[0] => ./libraries/DatabaseInterface.class.php#205: PMA_DatabaseInterface->_dbgQuery()
[1] => ./libraries/DatabaseInterface.class.php#1705: PMA_DatabaseInterface->tryQuery()
[2] => ./libraries/navigation/Nodes/Node.class.php#383: PMA_DatabaseInterface->fetchResult()
[3] => ./libraries/navigation/NavigationTree.class.php#211: Node->getData()
[4] => ./libraries/navigation/NavigationTree.class.php#674: PMA_NavigationTree->_buildPath()
[5] => ./libraries/navigation/Navigation.class.php#52: PMA_NavigationTree->renderState()
[6] => ./libraries/Header.class.php#377: PMA_Navigation->getDisplay()
[7] => ./libraries/Response.class.php#262: PMA_Header->getDisplay()
[8] => ./libraries/Response.class.php#275: PMA_Response->_getDisplay()
[9] => ./libraries/Response.class.php#373: PMA_Response->_htmlResponse()
[10] => ./libraries/OutputBuffering.class.php#114: PMA_Response::response()
[11] => PMA_OutputBuffering::stop()
)
)
)

[5f54e097676bb853308c7efc720c99f8] => Array (
[count] => 2
[query] => select COUNT(*) from ( SELECT distinct SUBSTRING_INDEX(SCHEMA_NAME, '_', 1) DB_first_level FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE AND SCHEMA_NAME NOT REGEXP 'information_schema' ) t
[time] => 1.7902340889
[trace] => Array (
[0] => Array (
[0] => ./libraries/DatabaseInterface.class.php#205: PMA_DatabaseInterface->_dbgQuery()
[1] => ./libraries/DatabaseInterface.class.php#1563: PMA_DatabaseInterface->tryQuery()
[2] => ./libraries/navigation/Nodes/Node.class.php#406: PMA_DatabaseInterface->fetchValue()
[3] => ./libraries/navigation/NavigationTree.class.php#1072: Node->getPresence()
[4] => ./libraries/navigation/NavigationTree.class.php#677: PMA_NavigationTree->_fastFilterHtml()
[5] => ./libraries/navigation/Navigation.class.php#52: PMA_NavigationTree->renderState()
[6] => ./libraries/Header.class.php#377: PMA_Navigation->getDisplay()
[7] => ./libraries/Response.class.php#262: PMA_Header->getDisplay()
[8] => ./libraries/Response.class.php#275: PMA_Response->_getDisplay()
[9] => ./libraries/Response.class.php#373: PMA_Response->_htmlResponse()
[10] => ./libraries/OutputBuffering.class.php#114: PMA_Response::response()
[11] => PMA_OutputBuffering::stop()
)

    [1] => Array (
        [0] => ./libraries/DatabaseInterface.class.php#205: PMA_DatabaseInterface->_dbgQuery()
        [1] => ./libraries/DatabaseInterface.class.php#1563: PMA_DatabaseInterface->tryQuery()
        [2] => ./libraries/navigation/Nodes/Node.class.php#406: PMA_DatabaseInterface->fetchValue()
        [3] => ./libraries/navigation/NavigationTree.class.php#1148: Node->getPresence()
        [4] => ./libraries/navigation/NavigationTree.class.php#678: PMA_NavigationTree->_getPageSelector()
        [5] => ./libraries/navigation/Navigation.class.php#52: PMA_NavigationTree->renderState()
        [6] => ./libraries/Header.class.php#377: PMA_Navigation->getDisplay()
        [7] => ./libraries/Response.class.php#262: PMA_Header->getDisplay()
        [8] => ./libraries/Response.class.php#275: PMA_Response->_getDisplay()
        [9] => ./libraries/Response.class.php#373: PMA_Response->_htmlResponse()
        [10] => ./libraries/OutputBuffering.class.php#114: PMA_Response::response()
        [11] => PMA_OutputBuffering::stop()
    )
)

)

[Justin]

I reported this as well:

https://sourceforge.net/p/phpmyadmin/bugs/4391/

Read that thread. Apparently they are trying to reproduce, but it should not be difficult, just create a few thousand databases and users.

[j4m3sbr]

From what I read in your report the error occurs with the root user, but mine happens to all users and each user has access to only one database.

And each database has about 60 tables.

[j4m3sbr]

I forgot to inform that phpMyAdmin is running on a server just for him, with 16Gb of RAM and 4 CPU cores.

[Madhura Jayaratne]

Quoting Marc Delisle's comment from bug #4391

Are you in position to try the development version? A fix is now in this version, for the upcoming 4.3.0 release. There is a configuration directive to set, see explanations in the comments of https://github.com/phpmyadmin/phpmyadmin/pull/1375

See http://www.phpmyadmin.net/home_page/downloads.php

[j4m3sbr]

The problem occurs again. And when I put DisableIS = true, does not display the customer base in the listing.