#4592 (ok 4.4) Editing columns (tbl_structure.php) is incredibly slow

[DisgruntledGoat]

Whenever I try to edit a column definition of a table, it takes a really long time for the form to appear.

It's taking approximately 10 seconds per field edited - in other words if I click edit next to one field it's about 10 seconds, but if I tick 4 fields and click edit below the list, it takes 40 seconds!!

I've checked using the network tab in Chrome Dev Tools and the time is all spent "waiting" on the tbl_structure.php request, so I assume it's an issue with the PHP script (and not JavaScript).

Has been happening for a few versions now. Just updated to 4.2.11 and it's still occurring.

[DisgruntledGoat]

I tried this on the 4.4.0-dev version since I checked it out for another issue. Here it still takes around 8-10 seconds for the request, but when editing multiple fields it's still 8 seconds, not 8 per field.

[Marc Delisle]

Please attach an export of your table's structure. Also, did you try reproducing this on http://demo.phpmyadmin.net ?

I cannot reproduce this with the sakila sample database (tried the actor.actor_id column on MySQL 5.5.40).

[DisgruntledGoat]

None of the demos are working, they all show blank pages.

[Marc Delisle]

DisgruntledGoat,
http://demo.phpmyadmin.net is currently working.

Please attach an export of your table's structure.

[DisgruntledGoat]

Nope, demo is still not working. There's now an when logging in:

Warning in ./libraries/dbi/DBIMysqli.class.php#114
 mysqli_real_connect(): (28000/1045): Access denied for user 'root'@'[my IP]' (using password: NO)

[Madhura Jayaratne]

As already noted some of the queries that ran per column is running per table in 4.3
Following are the relevant commits

https://github.com/phpmyadmin/phpmyadmin/commit/2abae135e67a044db6bdd4940704da01917d20d7
https://github.com/phpmyadmin/phpmyadmin/commit/e277e4d80f334f38c2e6276cf1341426c886bdf6

[Madhura Jayaratne]

I am unable to reproduce either. For a single column it takes about 2-3 seconds while for 10 columns it takes about 4-5 seconds

[Yurii]

I have a similar problem during editing table field. I found the query that take a long time using "show full process list" command in mysql client from linux console.

SELECT column_name, table_name,table_schema
FROM information_schema.key_column_usage
WHERE referenced_column_name = 'type'
AND referenced_table_name = 'mm_UserAddress'
AND referenced_table_schema = 'my_database_name';

When there were 220 databases in my mysql server then query took 3 min 5.08 sec. I removed unused databases (number of databases was reduced to 117) and then the query took 32.66 sec. It is also too long.

I like phpMyAdmin 4 but unfortunately must use phpMyAdmin 3 until this problem is not resolved.

[Marc Delisle]

Yurii, are you using version 4.2.11 too? If so, can you try 4.3.0-alpha1 ?

[Yurii]

Marc,

Please note that there is important thing that I didn't mention in my previous comment. The problem occurs only when query runs a first time. The second and next attempts finished in 0.82 sec. Looks like results are loaded from some kind of cache.

Originally I found the problem in phpMyAdmin version 4.2.7. I installed 4.2.11 and 4.3.0-alpha1 versions but can't confirm the problem exists because results still cached. I am sure the problem exists since 4.2.11 generates the same SQL query as 4.2.7 and 4.3.0-alpha1 generates very similar query that not looks more optimized, see below:

SELECT column_name, table_name, table_schema, referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name = 'mm_UserAddress'
AND referenced_table_schema = 'my_database_name';

Regarding the cache. Sql query cache is disabled (query_cache_size = 0) on my server. Maybe results stored in innoDB buffer pool, OS or hardware cache.

Anyway looks like problem is in a SQL query itself. It is not optimized. Also it gets information about foreign keys on the field edit page when this information is not needed there. It is needed on the Relations page on which the "Relation view" link leads in the "Structure" tab. I can be wrong but it looks exactly like this.

If the query is needed for some reason on the field edit page then I see only way that can solve the problem in phpMyAdmin - add configurable option that allows to disable foreign keys between multiple databases. In this case an additional expression can be added to query - "AND table_schema = 'my_database_name'" and execution should be faster.

[Madhura Jayaratne]

Hi Yurii,

Foreign key information is used in the column edit page to disable editing the name of a column with foreign keys defined on them. Trying to edit such a column would result in MySQL error and this information is used to inform the user beforehand.

Adding "AND table_schema = 'my_database_name'" would not work since inter database foreign keys are allowed.

Since querying information_schema can be very slow especially when there are many databases, in 4.3 we made the following commit.
https://github.com/phpmyadmin/phpmyadmin/commit/5a4d8a385529a2e3bec24edf62d1dc30c835895c
When the 'DisableIS' configuration is enabled, information_schema will not be queried for this purpose. The downside is you will not be indicated about the column having foreign keys and you will only realize that you can not edit the name of it when you submit the form.

[DisgruntledGoat]

This issue is occurring on every table I've tried, but here's a dump of one for reference.

I've tried setting up the table in a new database and it was about 1s quicker. (Potentially the stuff set up in 'relation view' makes a difference?) However, it's still taking over 7 seconds, from when I click the Edit button next to a field, to the form showing up.

[Madhura Jayaratne]

I tested with 4.4.0-dev using your table structure.

With $cfg['Servers'][$i]['DisableIS'] = true, editing a single column takes about 2 seconds and editing all the columns take about 3 seconds.

With $cfg['Servers'][$i]['DisableIS'] = false, the first attempt to edit all the columns took about 5 seconds. However subsequent attempts took only 3 seconds. Editing a single column took only 2 seconds.

Do you have hundred or thousands of databases in your system?

[DisgruntledGoat]

No, I have about 10 databases, but this particular database has over 100 tables in it (the other database are only 10-20 tables).

This issue was occurring for me on localhost. I tried on my server with the same database (there is only one database there, same 100+ tables) and it's not slow at all - only ~400ms for the request to tbl_structure.php. Also the demo is finally working and that seems fast too.

So there must be something different about my localhost setup. I already tried optimizing all the tables with no difference.

However, when I go to the Databases tab and click "Enable Statistics", several of the databases show quite high "Overhead" (several MB or GB in some cases). Where exactly is this number coming from, since I just optimised my tables? The one on my server does have high overhead too, so I don't know if this is normal or not.

[Marc Delisle]

DisgruntledGoat,
You may forget about this "Overhead" display.
Starting from version 4.3.0, Overhead is no longer shown in Databases > Enable statistics. See the reason here:

https://github.com/phpmyadmin/phpmyadmin/commit/582b02262bcda6edb10ffc6df1b67c47468bbe59

[DisgruntledGoat]

OK thanks. Do you have any other ideas why this might be so slow on my system? Is there a way I can debug this myself?

[Marc Delisle]

Is there a difference between your localhost and your server, in terms of PHP and MySQL versions?

Or can it just be explained by the difference in machine power?

[DisgruntledGoat]

My localhost has more databases as described above, that's basically the only difference. My first live server has just the one database. My second one has several DBs though still not as many tables as localhost. Both live servers are still fast. My computer would be a little slower than the servers but the rest of PMA runs fine so it can't be that.

I've taken a closer look through the code myself and determined it's the query in PMA_getChildReferences, which is the same thing that Yurii posted. On localhost, running this query takes several seconds. On localhost the KEY_COLUMN_USAGE table has 770 rows while my live server has 122 rows. That's about 7 times as many, but the query takes way longer than 7 times as long to run (5s vs 0.02s).

[DisgruntledGoat]

Did some more research and found this Stack Overflow question: http://stackoverflow.com/questions/7283915/poor-performance-of-information-schema-key-column-usage-in-mysql
Setting innodb_stats_on_metadata=0 in my.cnf completely removes the huge delay for me.

But I suppose that won't be a viable solution for everyone that comes across this issue. I wonder if there are some better ways to improve the performance of that query.

[Madhura Jayaratne]

MySQL version 5.6.6+ allows renaming columns having foreign keys and from phpMyAdmin version 4.4 onwards, for such environments, KEY_COLUMN_USAGE table is not longer accessed.

[Madhura Jayaratne]

Fixed with https://github.com/phpmyadmin/phpmyadmin/commit/0cc92a82629e8dc9be1ceb62d31ea41227130479