March 24 2013

Fixing slow phpmyadmin by rewriting queries using mysql-proxy

The performance problem

Opening phpmyadmin becomes quite annoying if the initial loading takes around 30 seconds, after logging in. Viewing the queries being executed by phpmyadmin with meta log monitor easily shows the bottleneck is "SHOW TABLE STATUS FROM '<DATABASE>'". It is the only query requiring > 14 seconds in this case.

Phpmyadmin executes this query twice: in it's navigation frame for a listing of tables and in the right frame for the detailed listing.

Create cache for: SHOW TABLE STATUS FROM <DATABASE>

The output of this query is not that accurate. Running it twice, and comparing the output shows some columns are estimates. So if we would cache it for two minutes that would probably not harm phpmyadmin.

Then I created a table where to store the SHOW TABLE STATUS output into (I realize the types I used are probably overkill for some fields ).

CREATE TABLE showtablecache (
  Database_       VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Name_           VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Engine_         VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Version         Integer(11),
  Row_format_     VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Rows_           BigInt(20),
  Avg_row_length  BigInt(20),
  Data_length     BigInt(20),
  Max_data_length BigInt(20),
  Index_length    BigInt(20),
  Data_free       BigInt(20),
  Auto_increment_ BigInt(20),
  Create_time     DateTime,
  Update_time     DateTime,
  Check_time      DateTime,
  Collation_      VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Checksum        VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Create_options  VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci,
  Comment_        VarChar(255) CHARACTER SET latin1 COLLATE latin1_swedish_ci
) ENGINE=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;

This doesn't work by the way: INSERT INTO showtablecache SHOW TABLE STATUS FROM '<DATABASE>' (leaving the "Database_" column from above CREATE TABLE). I didn't really expect that to work, but I would have been pleasantly surprised if it did .

I created a resultset with similar output to the SHOW TABLE STATUS query with a SELECT query on INFORMATION_SCHEMA.TABLES. And perform INSERT's with the following cron:

<?php
$res = $conn->select('SHOW DATABASES');

while ($row = $res->assoc())
{
    $dbname = current($row);
    print "Caching $dbname...";

    $conn->startTransaction();
    $conn->update('DELETE FROM showtablecache WHERE Database_ = \'' . $conn->escape($dbname) . '\'');
    $conn->update('INSERT INTO showtablecache
                   SELECT \''.$conn->escape($dbname).'\'
                     ,TABLE_NAME
                     ,ENGINE
                     ,VERSION
                     ,ROW_FORMAT
                     ,TABLE_ROWS
                     ,AVG_ROW_LENGTH
                     ,DATA_LENGTH
                     ,MAX_DATA_LENGTH
                     ,INDEX_LENGTH
                     ,DATA_FREE
                     ,AUTO_INCREMENT
                     ,CREATE_TIME
                     ,UPDATE_TIME
                     ,CHECK_TIME
                     ,TABLE_COLLATION
                     ,CHECKSUM
                     ,CREATE_OPTIONS
                     ,TABLE_COMMENT
                   FROM
                     INFORMATION_SCHEMA.TABLES
                   WHERE
                     table_schema = \'' . $conn->escape($dbname).'\'');
    $conn->endTransaction(true);
    print "done.\n";
}
?>

I run this script every 2 minutes. You really want the transaction because you don't want the cache to be empty between the DELETE and the time the INSERT finishes.

Rewrite query to use our cache using mysql-proxy

The final step was to rewrite the SHOW TABLE STATUS queries to SELECT queries on our new cache table. This turns out to be quite simple using a neat Lua script for mysql-proxy:

   0 ---
   1 -- Uses MySQL-Proxy to rewrite annoyingly slow SHOW TABLE STATUS FROM `<DATABASE>` queries to a SELECT from our periodic cache
   2 --
   3 -- @author trigen
   4 --
   5 function read_query( packet )
   6     if string.byte(packet) == proxy.COM_QUERY then
   7         local query = string.sub(packet, 2)
   8
   9         -- attempt to match the query, and more specifically extract the requested database
  10         local dbname = string.match(string.upper(query), "^SHOW TABLE STATUS FROM \`(%a*)\`")
  11         if dbname then
  12             local newquery = string.format('SELECT' ..
  13                 '  Name_ AS `Name`,' ..
  14                 '  Engine_ AS `Engine`,' ..
  15                 '  Version,' ..
  16                 '  Row_format_ AS `Row_format`,' ..
  17                 '  Rows_ AS `Rows`,' ..
  18                 '  Avg_row_length,' ..
  19                 '  Data_length,' ..
  20                 '  Max_data_length,' ..
  21                 '  Index_length,' ..
  22                 '  Data_free,' ..
  23                 '  Auto_increment_ AS `Auto_increment`,' ..
  24                 '  Create_time,' ..
  25                 '  Update_time,' ..
  26                 '  Check_time,' ..
  27                 '  Collation_ AS `Collation`,' ..
  28                 '  Checksum,' ..
  29                 '  Comment_ AS `Comment`,' ..
  30                 '  Create_options' ..
  31                 ' FROM' ..
  32                 '  tweakers.showtablecache' ..
  33                 ' WHERE Database_ = %q', dbname);
  34             print('CONVERTING ' .. query .. '  ===>  ' .. newquery);
  35             proxy.queries:append(1, string.char(proxy.COM_QUERY) .. newquery )
  36             return proxy.PROXY_SEND_QUERY
  37         end
  38     end
  39 end

Save that script to pma.lua, start proxy with:

mysql-proxy -P :4041 --keepalive --proxy-backend-addresses=YOUR_SERVER:3306 --proxy-lua-script=pma.lua

Example output of mysql-proxy:

CONVERTING SHOW TABLE STATUS FROM `tweakers`  ===>  SELECT  Name_ AS `Name`,  Engine_ AS `Engine`,  Version,  Row_format_ AS `Row_format`,  Rows_ AS `Rows`,  Avg_row_length,  Data_length,  Max_data_length,  Index_length,  Data_free,  Auto_increment_ AS `Auto_increment`,  Create_time,  Update_time,  Check_time,  Collation_ AS `Collation`,  Checksum,  Comment_ AS `Comment`,  Create_options FROM  tweakers.showtablecache WHERE Database_ = "TWEAKERS"
CONVERTING SHOW TABLE STATUS FROM `tweakers`  ===>  SELECT  Name_ AS `Name`,  Engine_ AS `Engine`,  Version,  Row_format_ AS `Row_format`,  Rows_ AS `Rows`,  Avg_row_length,  Data_length,  Max_data_length,  Index_length,  Data_free,  Auto_increment_ AS `Auto_increment`,  Create_time,  Update_time,  Check_time,  Collation_ AS `Collation`,  Checksum,  Comment_ AS `Comment`,  Create_options FROM  tweakers.showtablecache WHERE Database_ = "TWEAKERS"
...

Example output for metalogmon:

Some notes

I never did any Lua scripting before, I find this website quite useful as it provides a console for Lua (but for other languages as well). This way you can test some stuff without constantly restarting mysql-proxy.

With regards to the caching, if you create a new table, this table will become visible once the cron updated the cache. So you don't want to set the delay for too long. You could do some extra scripting for encountered CREATE TABLE statements in mysql-proxy, or make a more advanced cron script that checks the faster "SHOW TABLES;" more often to see if any caching needs more priority.