#4243 (ok 4.3) Super slow page rendering with tens of thousands of DBs

[Wayne Davison]

I recently upgraded phpmyadmin from 3.x to 4.x in a DB setup that has about 25,000 DBs (though only a small number of per-page DBs are visible, due to our only_db setting), and found that page load times were in the realm of 60-90 seconds each -- agonizingly slow (we run MySQL 5.5, btw). I traced the cause down to all the counting and selecting against the information_schema DB being done for DB names: anytime an explicit name isn't mentioned (e.g. LIKE is used) the query took 20, 40, even 60 seconds to complete.

I eventually made enough changes to the code to get page loads down to normal, and will mention some of the things I discovered:

• The only SELECT of information_schema's DB names that is quick is an exact match against SCHEMA_NAME w/o any charset transformation (i.e. no use of BINARY casts).
• Even a SELECT using IN('db1', 'db2') is super slow.
• Doing SELECT queries with a restricted (non-super) user is slower than the super-user (probably due to all the security checks that the super-user can avoid) so don't be fooled by any speed tests you run using a root login.
• Doing a COUNT() of DB info in the information_schema is just as super slow as a SELECT.
• The use of SHOW DATABASES LIKE is quite fast.

Thus, for a general fix, I'd recommend that you change the code that does any DB-name matching to do a SHOW DATABASES LIKE call, get the list of DB names, and then loop over it with individual-DB-name SELECTs into information_schema.

FYI, the fixes I did were a simpler set of changes that work for our particular site. If you're curious, here's what I did:

• I changed all the count operations (e.g. getPresence() calls) into array-length operations of the only_db array (since that is always a full list of the user's tables in our setup).
• I removed all BINARY casts in the database-name matching code.
• I removed all use of LIKE in the database-name matching code.
• I changed the code path in getDatabasesFull() that lists all DBs (when $database isn't specified) into a loop over the only_db list of DB names, adding each individual DB lookup into the $databases[$db_name] array.

[Wayne Davison]

My 150K count was actually double user count because the mk-show-grants lines have a GRANT USAGE and a GRANT {perms}... line for each user. Basically we have 3 users per DB (25K x 3 = 75K) and that makes for 150K lines of mk-show-grants output.

The basic idiom is for a ro, and rw, and an admin user. All 3 get USAGE:

GRANT USAGE ON *.* TO 'prefix123ro'@'%' IDENTIFIED BY PASSWORD '*...';
etc...

and each user gets appropriate perms (replace "prefix123" with each DB's prefix):

GRANT SELECT ON `prefix123\_%`.* TO 'prefix123ro'@'%';

GRANT CREATE TEMPORARY TABLES, DELETE, INSERT, SELECT, SHOW VIEW, UPDATE ON `prefix123\_%`.* TO 'prefix123rw'@'%';

GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, INDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW, UPDATE ON `prefix123\_%`.* TO 'prefix123admin'@'%';

[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