#3869 (ok 4.3) Count(*) on information_scheme.INNODB_BUFFER_PAGE with a huge bufferpool

[Kees Hoekzema]

When using phpmyadmin to look at the information scheme database, phpmyadmin will try to do a count(*) on all the tables.

For most tables this is OK, but if you have a large databaseserver with, for example, a 200GB innodb pool, than doing a count(*) on information_schema.INNODB_BUFFER_PAGE will slow down your server considerably and make it barely usable for half an hour (and you are unable to kill the query until it has comleted the count):

Query_time: 1883.320497 Lock_time: 0.000028 Rows_sent: 0 Rows_examined: 0
SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE;

I think phpmyadmin should be a bit more careful using count(*) in the information_scheme database if the server has a large bufferpool.

[Ayush Choubey]

Indeed this is one of the slowest queries i would have seen. Took about a couple of second in my machine too.

but i would say that's just MySQL way of handling the query.

I guess we can notify the user that "this query is going to take some time" before that loading bar which appears at the center of the screen;for any query related to information_schema

or what more can be done is to "give the user an option to kill the query" and then if ajax is enabled( i guess V4 is totally ajax dependent) we can send an ajax request to kill it and then re-ping the connection.

By the way, isn't this more of a feature request than a bug report, i mean i don't know,i am pretty new here

[Kees Hoekzema]

The problem with the kill is.. the kill doesn't kick in until after the query is finished. When i ran this by accident on a productionserver i obviously tried to kill it, but it would stay in a 'killed' state until it was done executing.

It would look like this in the 'show processlist':
| Id | User | Host | db | Command | Time | State | Info
| 804458 | root | - | NULL | Killed | 1782 | executing | SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE

[Ayush Choubey]

Then how do you think we should proceed? How about the earlier option of giving the user a warning that :

"As of last transaction this table may contain more than NUMBER(a particular amount of rows) and the query may take more than NUMBER(a particular amount of seconds). Do you really want to execute the query?"

It cannot prevent the queries from being run but it can definately give the user that this may rows are there.

Those numbers will be stored in PMA's database

and may be once in a month we can create a child process and assign it to run the query and update those numbers(you know kind of scheduling).

How about it? Just an though!!

[Marc Delisle]

Well, this is not a query that the user has the intention of doing. For example, when accessing information_schema and clicking on Structure, I suspect that the counting occurs.
Maybe we should just avoid the counting when displaying the Structure of information_schema.

[Marc Delisle]

Kees,
are you really reporting a problem about phpMyAdmin 3.4.10? This is not the current stable version.

[Marc Delisle]

I don't have such a large innodb pool. However, when testing with 3.4.10 and tracing the queries generated when showing the Structure of information_schema, indeed there are a lot of SELECT COUNT(*) statements.
However, in 4.0.0-rc1, I could not see such statements in the tracing. Can you test 4.0.0-rc1 with your innodb pool?

[Kees Hoekzema]

Well, i'd rather not break a production server again by running those queries against it, but i found the second best thing; a server with a pool of 20G.

Using phpmyadmin 4.0.0-rc1 and just selecting the information_scheme table i see these queries in the slow log:

Query_time: 23.904965 Lock_time: 0.000033 Rows_sent: 1 Rows_examined: 1310720
use information_schema;
SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE;

Query_time: 23.230571 Lock_time: 0.000055 Rows_sent: 1 Rows_examined: 1303210
SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE_LRU;

Those 23 seconds will take longer the bigger the pool is. With a 150G pool they'll take ~1000 seconds and with a 200G pool it took me over 1800s to run them.

The server with the 150/200G pool has 2x Xeon E5-2643 3,3GHz w/ 256G memory, the server with the 20G pool is a 2x Xeon X5660 2,80GHz with 48G memory.

I'll file a bugreport with mysql as well, because that query will slow down your server to a crawl.

[Marc Delisle]

Thanks for pointing me to the slow log.

[Marc Delisle]

Kees,
which MySQL version?

[neol]

Maybe it's bug in MySQL and it's fixed in MySQL 5.6.5 (http://jorgenloland.blogspot.ru/2012/05/performance-improvements-for-big.html)

I faced with similar problem on PMA 3.4.11.1deb2, MySQL 5.5.30, innodb_buffer_pool_size = 4G

Slow log record:
Query_time: 5.582877 Lock_time: 0.000130 Rows_sent: 1 Rows_examined: 150154
SET timestamp=1369258458;
SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE_LRU;

Most likely it's not relevant to this ticket, but this query is executed to each access to navigation.php. I'm done patching this file and excluding all information_schema tables from processing in strings like this:

$tables = PMA_getTableList($db['name']);

I do not think that is the right way, but the responsiveness of the PMA has become quite acceptable on most databases, but not when viewing the information_schema.

Create new ticket for this problem or i'm idiot and need more googling?

[Kees Hoekzema]

It's been a while, i almost forgot about this bug (because i editted phpmyadmin myself to prevent these queries).

Anyway, the problem still exists, and i'm not the only one who has this problem if i see https://sourceforge.net/p/phpmyadmin/bugs/4513/.

The mysql versions:
- 5.6.14-rel62.0 - Percona Server with XtraDB (GPL), Release rel62.0, Revision 483
12G bufferpool size
Query_time: 28.862935
SELECT COUNT() FROM information_schema.INNODB_BUFFER_PAGE;
Query_time: 17.058225
SELECT COUNT() FROM information_schema.INNODB_BUFFER_PAGE_LRU;

• 5.6.15-63.0 - Percona Server (GPL), Release 63.0
  25G bufferpool
  Query_time: 18.92
  SELECT COUNT() FROM information_schema.INNODB_BUFFER_PAGE;
  Query_time: 19.24
  SELECT COUNT() FROM information_schema.INNODB_BUFFER_PAGE_LRU;

• phpMyAdmin Version information: 4.2.9deb0.1

I also have several servers with a 200+G pool; those can easily work for minutes or hours on those queries (all the wile phpmyadmin is unresponsive).

The solution i have hacked in place now is to simply return a '1' in Table.class.php/countRecords if $db == "information_schema"

[Madhura Jayaratne]

Additional fix for this bug in performance improvement pull request related to information_schema:
https://github.com/madhuracj/phpmyadmin/commit/492d0f133f8b8cc05a38e4024d09488716b86124

[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