phpmyadmin opens MySQL table lists very slowly

问题:

I can log into phpmyadmin and see databases very quickly. Once I click on one of them and try to see the tables list it's very slow. Is there anything I'm missing? I didn't have this situation before updating from Ubuntu 10.04 to Ubuntu 12.04.

回答1:

Open \config.inc.php file and add these two lines of code in it:

$cfg['MaxExactCount'] = 0;
$cfg['MaxExactCountViews'] = 0;

And of course you can skip second line if you have not any views at the database.

回答2:

This is because you have innoDB tables with lot of rows. InnoDB does not store the number of rows in a table but MyISAM does. So for each InnoDB table PHPMyAdmin invokes SELECT count(*) FROM query which is very slow if number of rows is very high. To resolve this you should edit config.inc.php file and set $cfg['MaxExactCount']. This will invoke count(*) sql for tables that has less MaxExactCount rows.

$cfg['MaxExactCount'] = 20000;

Meaning form phpmyadmin manual

For InnoDB tables, determines for how large tables phpMyAdmin should get the exact row count using SELECT COUNT. If the approximate row count as returned by SHOW TABLE STATUS is smaller than this value, SELECT COUNT will be used, otherwise the approximate count will be used.

回答3:

In case when you have several VIEWS with lots (>10^5) of records it works terribly slow even when MaxExactCountViews and MaxExactCount both set to 100.

Find

'static public function countRecords'

in

libraries\Table.class.php

, put following code in begining of this method:

if ($is_view == true && isset($GLOBALS['cfg']['MaxExactCountViews'])) {
    /* dirty hack to avoid performance issue with views when ['cfg']['MaxExactCount'] and ['cfg']['MaxExactCountViews'] does not help it */
    $tmp_tables = PMA_DBI_get_tables_full($db, $table);
    PMA_Table::$cache[$db][$table] = $tmp_tables[$table];
    PMA_Table::$cache[$db][$table]['ExactRows'] = $GLOBALS['cfg']['MaxExactCountViews'];
    return (int) $GLOBALS['cfg']['MaxExactCountViews']; 
}

Set $GLOBALS['cfg']['MaxExactCountViews'] value in config afterwards. phpMyAdmin now will always display this value for all VIEWS. It also will work much faster :-)

回答4:

The way I solved your problem was by caching the output of SHOW TABLE STATUS FROM <DATABASE> into a table named f.i. showtablecache, say every 2 minutes. You can do this with some cron script for your database(s).

You could then edit the file /usr/share/phpmyadmin/libraries/database_interface.lib.php and replace the slow SHOW TABLE STATUS FROM ... with a SELECT ... FROM showtablecache WHERE ... on the new cache table.

You could also leave the phpmyadmin source alone and put a mysql-proxy instance in between that does the query rewriting for you. All you then have to do is change the $dbport variable in config-db.php :)

Using mysql-proxy for this is especially useful if you have this problem with a non-open source tool other than phpmyadmin. Like some native, maybe proprietary workbench application. (Database workbench by Upscene does something similar (if I recall correctly))

---

Queries for in the cron script:

START TRANSACTION;

DELETE FROM showtablecache WHERE database_ = '<DATABASE>';

INSERT INTO showtablecache
SELECT 
    '<DATABASE>'
    , 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 = '<DATABASE>';

COMMIT;

So instead of SHOW TABLE STATUS FROM <DATABASE> you use:

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
    showtablecache
WHERE
    Database_ = <DATABASE>;

More details about this fix here: http://blog.cppse.nl/fix-slow-phpmyadmin

回答5:

I originally used @"Andrew Kondratev" answer, without the "if view" conditional stuff, and then started looking closer at the rest of that method, and realized that was almost exactly the code that would run if $force_exact was false. I have a new, simpler hack that doesn't break quite as much and works for tables as well.

Just like with Andrew's hack:

• Find where the installation lives, such as rpm -ql phpMyAdmin | grep Table.class.php (or your local OS equivalent).
• EDIT: ./libraries/Table.class.php (in my case /usr/share/phpMyAdmin/libraries/Table.class.php
• Look for static public function countRecords (line 563 in my case)

• Insert the following at the top of that function (after the {):

          /* Tommy's Hack from http://goo.gl/HMTnLc */
          $force_exact = false;
          /* End Tommy's Hack - USE AT YOUR OWN RISK! */
  

• In my case the "defaults" already have the following:

  config.default.php: * @global integer $cfg['MaxExactCount']
  config.default.php:$cfg['MaxExactCount'] = 0;
  config.default.php: * @global integer $cfg['MaxExactCountViews']
  config.default.php:$cfg['MaxExactCountViews'] = 0;
  

• However, you can always add it to your config.inc.php:

  $cfg['MaxExactCountViews'] = 0;//disable trying to count the number of rows in any view
  $cfg['MaxExactCount'] = 0;//disable correcting the InnoDB estimates
  

I believe the problem is actually over in tbl_info.inc.php where it sets $force_exact to true while displaying tables. IMO, the only time that number would need to be "exact" is if you were trying to view the last page, and even then probably not.

回答6:

Another source of slowness for phpMyAdmin running on a local server comes from its apparent ignoring of the hosts file. Try changing any occurrences of "localhost" in config.inc.php to "127.0.0.1". It created a great speedup for me on a Windows computer.

回答7:

For me, the problem was that xdebug profiler was on for all sites. It wrote over 40MB of logs when loading the table list in phpmyadmin. (Turning it off accelerated the load from over 15 secs to 2-3 secs).

Here are my new xdebug settings, I don't know which one did the trick:

xdebug.profiler_enable=0
xdebug.profiler_enable_trigger=1 # this still allows manual logs
xdebug.remote_autostart=0
xdebug.remote_enable=0

回答8:

I posted a fix for a general situation where you query information_schema.tables

Slow query on information_schema.tables