#484 Slow Loading times with large Databases

[tmsmaster]

We suffer very bad performance when surfing in a database with phpmyadmin 3.2.2 and a mysql 5.0.51a
We have a very large DB where some tables have > 15 mio rows and the total row count is above 100 Mio. For the database the Query "show table status from .." performs very bad and takes 5-15s. This query does not only seem to be issued on pages where the information is necessary but also on pages where none of the info retrieved by this query is used. One example is the normal result page of a query. We also have noticed that if we kill the query on the mysql server, the page still shows correctly, without any information missing.
So for us there would be 2 points that need to be improved:
1. Only issue the query when the information really is needed
2. Maybe cache the information that is retreived for some time, this info does not change that much

[Marc Delisle]

Those tables are InnoDB, right?

[tmsmaster]

yes that's true, sorry i forgot to mention it

[Marc Delisle]

I'm not saying that we can't improve phpMyAdmin about this. However I made a quick test, first with MySQL 5.1.40 then with 5.0.87. I created a database containing 3 InnoDB tables, each having 15M rows. Using mysql command-line, the "SHOW TABLE STATUS FROM mydb LIKE 'mytable' " executes in 0.1 second.

[Marc Delisle]

tmsmaster,can you try a test similar to mine and report your results?

[tmsmaster]

We have run some tests with both 5.0.81 and 5.0.87, using the amd64 linux community edition tarballs. We used a database containing two copies of a table sized about 35 million rows, one copy being innodb and the other being myisam, and a handful of small tables. We had mysql build a fresh ibdata* file set prior to the test. In both cases, SHOW TABLE STATUS behaviour was similar, taking 1 to 5 seconds, usually around 5 seconds upon first invocation and around 1.5 when repeated soon after. With 5.0.81, we also tested without the innodb copy of the large table present, resulting in an absolutely instant response.

[Marc Delisle]

Thanks. I need to know exactly which operation you are doing in order to trace where the "SHOW TABLE STATUS" comes from; in fact, the PMA_Table::countRecords() does some caching of results.

When you write ""one example is the normal result page of a query", do you mean that you are browsing an Innodb table and you have to wait 5-15 seconds each time you go to the next page?

[Marc Delisle]

Ok, I just saw that there is a SHOW TABLE STATUS from `the_current_db` very often, I'll check why.

[tmsmaster]

Hi, with the normal result page i meant that i go to "SQL" and type in a query, and execute it. This takes 5-10 sec even if the raw query time only is 0,1sec
This especiially seems to happen if the executed query performs some left joins with another table but this is only an assumption

is there any way i can turn on some "debug" to see the queries that where executed?

[Marc Delisle]

Assuming you are using mysqli extension, change libraries/dbi/mysqli.dbi.lib.php in function PMA_DBI__try_query() and add an echo of $query.

[Marc Delisle]

Is it better with this patch:
Index: libraries/database_interface.lib.php
===================================================================
--- libraries/database_interface.lib.php (revision 13105)
+++ libraries/database_interface.lib.php (working copy)
@@ -353,7 +353,7 @@
// this is why we fall back to SHOW TABLE STATUS even for MySQL >= 50002
if (empty($tables)) {
foreach ($databases as $each_database) {
- if (true === $tbl_is_group) {
+ if ($table || true === $tbl_is_group) {
$sql = 'SHOW TABLE STATUS FROM '
. PMA_backquote($each_database)
.' LIKE \'' . PMA_escape_mysql_wildcards(addslashes($table)) . '%\'';

[tmsmaster]

Unfortunately it does not, the query is still done when issueing a query:
The Query Printout shows:
SET CHARACTER SET 'utf8';
SET collation_connection = 'utf8_general_ci';
SELECT * FROM `table` WHERE 1 LIMIT 0, 100
SHOW TABLE STATUS FROM `db` LIKE 'table%'

Even when just clicking on SQL the qry:
SHOW TABLE STATUS FROM `db` LIKE 'table%'

is issued

[Marc Delisle]

Well, this patch was to replace a
SHOW TABLE STATUS FROM `the_db`
by a more specific SHOW TABLE STATUS with the table name, not to remove the
SHOW TABLE STATUS.
So, in terms of speed, it's not better?

[tmsmaster]

loading time is improved a little but this is still not satisfactory, why is the query issued here in the first place? The info is not used on that page?

[Marc Delisle]

In general, this query is done to get the number of rows from the table. I'm sorry, which page are you talking about?

[tmsmaster]

I am talking about the normal page where the query results are printed (sql.php) the total number of rows is not shown here and i don't know for what this would be necessary :)

[Marc Delisle]

Not sure I understand you. When query results are displayed, there is usually a "Showing rows" message at the top, with the total number of rows.

[tmsmaster]

Yes that's correct but this only refers to the number of total rows retrievable by the query, not the to the total number of rows in the table, the show table status from ... qry is also issued at the end and as already said if i kill the query, no information is missing on the page and it displays just fine.
I hope this helps to explain things :)

[Marc Delisle]

Ok I got your point about the number of rows. The problem is that libraries/tbl_info.inc.php is called by many scripts which need other information elements that are returned by SHOW TABLE STATUS. Some of these could be cached in session but it would require much research to find out which one, and when the cache must be invalidated.

The alternative of SHOW TABLE TABLE (which is used when $cfg['Servers'][$i]['DisableIS'] is set to false) is to ask INFORMATION_SCHEMA, and this also gives bad or very bad response times for big databases.

[Galen]

any progress on this?
Our company db is about 150G total in size. I was really suffering from this slowness resulted from "show table status from DBName". it took more than 35s to run. and the real bad thing is: it runs every time in response to any clicks on phpmyadmin. that sounds not necessary and optimization seems possible?

Thanks
Galen

[Marc Delisle]

Sorry, no progress on this. See my previous message about "much research".

[Dan Wilga]

I was plagued by this problem, so I investigated. First, for the impatient, here's a quick workaround. Set this setting in your Servers section:

$cfg['Servers'][$i]['DisableIS'] = false;

and this one in the general settings:

$cfg['NaturalOrder'] = false;

Now for the explanation. After reading the prior posts, I turned SQL debugging on and discovered that, indeed, SHOW TABLE STATUS was taking a really long time. Combined with profing, it showed that every one of my 350 database tables was being opened every time I did anything in PHPMyAdmin. Some of these tables contain hundreds of thousands of rows and take over a second each to open.

This led me to try the suggestion of re-enabling INFORMATION SCHEMA usage (DisableIS option). This was better, but still pretty slow. The culprit turned out to be this query in the code that handles natural order sorting of database table names:

$max_name_sql = "SELECT MAX(LENGTH(TABLE_NAME)) FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('" . implode("', '", $this_databases) . "')";

This query was taking 5.5 seconds. Turning off natural order sorting avoids this code, and everything is zippy once more.

Since IS queries are so slow, might I suggest optimizing the above query using a loop in PHP to compare the lengths of the table names returned by 'SHOW TABLES IN'? This may be one of the few cases where it's faster to not use SQL to do a calculation.