#3944 DisableIS configuration setting ignored in navigation panel since 4.0.0

[candev]

The DisableIS configuration setting is ignored since version 4.0.0 in the navigation panel (perhaps other places also). This has significant performance considerations in large shared hosting environments where there are many databases and tables.

[Marc Delisle]

Please give some figures about "significant performance considerations", and which MySQL version are you using?

[candev]

OK. Here is version info:

Database server
•Server: Localhost via UNIX socket
•Software: MySQL
•Software version: 5.1.70 - Source distribution (BetterLinux 01 Sep 5 2013 23:04:22)
•Protocol version: 10
•Server charset: UTF-8 Unicode (utf8)
Web server
•LiteSpeed
•Database client version: libmysql - 5.1.70
•PHP extension: mysqli
phpMyAdmin
•Version information: 3.5.8.2, latest stable version: 4.0.8

About the performance: I have pma 3.5.8.2 and also 4.0.8 (same config) installed on a shared hosting server. pma 3.5.8.2 consistently loads immediately with my 2 selected databases (this is same setup as in ticket #3945). 4.0.8 just won't load, the page hangs. Eventually, I have to cancel the page load or I'll get booted for excessive resource usage. I don't know how long I should wait to let the page load? I have already had another shared hosting account cancelled due to excessive resource usage testing pma 4+.

[Marc Delisle]

I made a test server with MySQL 5.1.70, PHP 5.5.3 and mysqli. However, the web server is Apache.
The server has 1000 databases with 100 tables in each db (all InnoDB). Sadly, I cannot reproduce your problem.

[Martyn]

I suspect libraries/navigation/Nodes/Node.class.php is a likely target in this. Some of the hanging queries I have experienced in 4.x have stemmed from here.

line 362 says the following:
// @todo obey the DisableIS directive

That is for the function getData

This seems consistent with the OPs assertion that DisableIS is not being enforced correctly throughout.

The queries it generates are often innocent enough, but performance can be slow once there are enough entries in the IS, though of course that aspect is a MySQL issue rather than a phpMyAdmin issue.

[Marc Delisle]

Martyn,
Indeed, the new code in 4.x is related to the navi panel.

We are discussing in the phpmyadmin-devel mailing list about upping the minimum MySQL version for phpMyAdmin 4.1.0. It's probable that the DisableIS and ShowDatabasesCommand directives will disappear and we'll only use INFORMATION_SCHEMA. Sadly I cannot reproduce these performance problems under MySQL 5.1.

Considering that MySQL 5.5 has been GA since 2010-12-03, it's likely that phpMyAdmin 4.1.0 will require MySQL 5.5.8, but the decision has not been made yet; we'll try to listen to our community...

[Martyn]

I have also experienced some sporadic issues in 5.5 and 5.6 (various subversions, all of which are Percona builds). None of our machines run 5.1

Depending on the type of page, and database setup, some of the issues seem to be as a result of response time from the IS, and other times a bad choice by PMA.

The intermittent nature would suggest that MySQL is the bottleneck, since PMA would be pretty consistent in what it does.
What I found worked best for our situation in the past with 3rd part libraries is for things like getting column names, replacing IS queries with their old school counterparts. IS seems to be more efficient when dealing with large datasets than older commands on large datasets, but if IS has a lot of information, older precise queries appear to perform much better.

I was about to start modifying some code to that end in PMA, but I was first checking the bugs to see if it had previously been attempted.

On a slightly related note, I did notice on first installation, that SHOW DATABASES was executing in the nav pane even with ShowDatabasesCommand set to false, though as said in another ticket, setting only_db did help resolve that one.

[Martyn]

Also, I believe its worth noting the following blog posting:
http://www.mysqlperformanceblog.com/2011/12/23/solving-information_schema-slowness/

While we have this setting set this way, im sure not every PMA user does, and that would explain some of the additional IS slowdowns some users have mentioned, but that cannot be recreated

[Marc Delisle]

Thanks for the link (one of our developers mentionned this as well). I am not sure to which MySQL version this applies, however. Did you see an improvement on both MySQL 5.5 and 5.6 with this setting?

[Marc Delisle]

Well, changing this variable requires the SUPER privilege, therefore we cannot use this as a general solution for this problem.

[Martyn]

innodb_stats_on_metadata was added in 5.1.17 and has remained defaulted to "on" until 5.6.6 when it defaulted off.

Discounting innodb_stats_on_metadata, IS is better on 5.5 than 5.1, but the problem isnt completely resolved (even with innodb_stats_on_metadata) as can be seen by the enclosed trace.

5.6 seems to show more improvement (that or i have just been lucky thus far, I have not compared enough to make a confident statement in either direction)

A dump from debug confirms my thought on the location of one of the periodically slower queries though:
[count] => 1
[query] => SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE TRUE AND ( SCHEMA_NAME LIKE 'id_x' )ORDER BY SCHEMA_NAME ASC LIMIT 0, 250
[time] => 2.6496150493622
[trace] => Array
(
[0] => Array
(
[0] => ./libraries/DatabaseInterface.class.php#169: PMA_DatabaseInterface->_dbgQuery()
[1] => ./libraries/DatabaseInterface.class.php#1764: PMA_DatabaseInterface->tryQuery()
[2] => ./libraries/navigation/Nodes/Node.class.php#368: PMA_DatabaseInterface->fetchResult()
[3] => ./libraries/navigation/NavigationTree.class.php#205: Node->getData()
[4] => ./libraries/navigation/NavigationTree.class.php#646: PMA_NavigationTree->_buildPath()
[5] => ./libraries/navigation/Navigation.class.php#54: PMA_NavigationTree->renderState()
[6] => ./libraries/Header.class.php#379: PMA_Navigation->getDisplay()
[7] => ./libraries/Response.class.php#264: PMA_Header->getDisplay()
[8] => ./libraries/Response.class.php#277: PMA_Response->_getDisplay()
[9] => ./libraries/Response.class.php#374: PMA_Response->_htmlResponse()
[10] => ./libraries/OutputBuffering.class.php#116: PMA_Response::response()
[11] => PMA_OutputBuffering::stop()
)

This is from a 5.5.30 (percona) build.

[Marc Delisle]

We are deprecating the DisableIS directive for phpMyAdmin 4.1; this version will require a minimum of MySQL 5.5.