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

4.1.5
fixed
None
1
2014-12-05
2014-01-21
No

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.

Discussion

<< < 1 2 (Page 2 of 2)
  • Wayne Davison

    Wayne Davison - 2014-05-08

    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'@'%';
    
     
    Last edit: Wayne Davison 2014-05-08
  • Madhura Jayaratne

    • assigned_to: Madhura Jayaratne
     
  • Madhura Jayaratne

    • summary: Super slow page rendering with tens of thousands of DBs --> (ok 4.3) Super slow page rendering with tens of thousands of DBs
    • status: open --> resolved
    • Priority: 5 --> 1
     
  • Marc Delisle

    Marc Delisle - 2014-12-05
    • Status: resolved --> fixed
     
<< < 1 2 (Page 2 of 2)

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks