it is possible that making INFORMATION_SCHEMA optional will only help for a short time, cause as i know sooner or later SHOW commands will be mapped to the same code as INFORMATION_SCHEMA
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
With my setup I am getting mixed results with trunk than with QA_2_11.
Testing with 5.1.22
$cfg['DisableIS'] = true
$cfg['MaxDbList'] = 100;
$cfg['MaxTableList'] = 100;
Number of datatabases: 10000
Two of them containing 10000 tables, all other are empty.
With QA_2_11, initial login: 11 sec.
open a db of 10000 tables: 50 sec.
go to second page of this db: 18 sec.
in navi frame, go to any page of the db list: 1 sec.
With trunk, initial login: 6 sec.
open a db of 10000 tables: I stopped to count after 2 min!
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
improved processing of tables in database_structure view - please check again
using MySQL 5.1.22 i cannot see differences in performance using I_S or SHOW, they seem very random - but in most cases having a db with 1000 tables both variants take about 3 seconds (summarized query times displayed in footer with $cfg['DBG']['enable'] = true)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
P.S. I suggest creating another variable for this debugging, because $cfg['DBG']['enable'] is supposed to be for the DBG extension stuff.
Yes, much better.
With trunk, initial login: 5 sec.
open a db of 10000 tables: 44 sec.
go to second page of this db: 15 sec.
in navi frame, go to any page of the db list: 1 sec.
open another db of 10000 tables: 29 sec.
13 queries executed21 times in 47.6904308796 seconds
I found out a few things about the usage of `information_schema` on hosted servers e.g. STRATO.
I_S is not always slow. But pma sometimes creates queries that will be executed very slow. those should be changed.
---------------------------------------------
1. in PMA_DBI_get_tables_full
the word BINARY in the query makes it extremely slow:
without BINARY:
2008-01-21 00:33:50 - SELECT *, [...] FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('DB321438')
2008-01-21 00:33:50 - executed in 0.01612114906311
with BINARY:
2008-01-21 00:36:43 -
SELECT *, [...] FROM `information_schema`.`TABLES`
WHERE BINARY `TABLE_SCHEMA` IN ('DB321438')
2008-01-21 00:37:01 - executed in 17.983309030533
---------------------------------------------
2. getting the table headers in sql.php.inc
if the original query in the SQL form goes to I_S for example:
SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` IN ('DB321438')
PMA executes this query in 0.02 seconds then it wants to create the headers:
2008-01-21 01:13:51 - SELECT COUNT(*) FROM `information_schema`.`TABLES`
2008-01-21 01:14:15 - executed in 23.892692089081
I wonder what's more important: case sensitivity on db names, or speed. Maybe we could add a cfg parameter for those who need to support case sensitivity on db names? By default it would be FALSE, then the default for $cfg['Servers'][$i]['DisableIS'] could be also false?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
@vogon_jelz: i have tested on another ISP (Hosteurope, 5.0.32) and cannot see any differences with or without BINARY, what MySQL version is STRATO running?
about 2): if someone wants to browse I_S pma cannot just say, "no, this will be too slow", or what do you expect phpMyAdmin should do?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
about BINARY): nobody should use databases with same names in different cases on one server. I agree with lem9, case sensitivity could be off by default.
about 2): of course not. ;-)
If somebody enters a SQL to I_S without a WHERE it's his own fault.
But if I enter a correct SQL with a WHERE condition for example
"SELECT * FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('DB321438')"
pma could create the following to get the table headers:
"SELECT COUNT(*) FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('DB321438')"
Would it help if I create a test database with FTP-access for you on my webspace?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
After months using my own patched PMA I downloaded PMA3.2.1 to see what has changed.
The new version looks very good...
...except it is still slow on STRATO hosted server running mySQL 5.0.67
Even though it is not a fault in PMA but an implementation problem in mySQL and perhaps an access problem at STRATO it makes it annoying to use pma 3.x
The reason is still the "BINARY" in the WHERE clause created in database_interface.lib.php.
If you delete it, the query to I_S takes less than 0.1s instead of 6 seconds.
Logged In: YES
user_id=326580
Originator: NO
MySQL is working on this
http://bugs.mysql.com/bug.php?id=19588
it is possible that making INFORMATION_SCHEMA optional will only help for a short time, cause as i know sooner or later SHOW commands will be mapped to the same code as INFORMATION_SCHEMA
Logged In: YES
user_id=326580
Originator: NO
just another note: if you encounter this problem, you should also ask your provider to update MySQL to at least 5.1.22
Logged In: YES
user_id=326580
Originator: NO
added config switch in SVN trunk (3.0-dev), if no problems occur will be backportet to QA_2_11 - please test
Logged In: YES
user_id=1383652
Originator: NO
hmm,
the last modification broke the $cfg['MaxTableList'] limit.
---
>you should also ask your provider to update MySQL to at least 5.1.22
http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
says as of 5.1.23 and *only* if you specify an exact db name in your queries
(which is not the case when you scan all databases via I_S)
Logged In: YES
user_id=326580
Originator: NO
"which is not the case when you scan all databases via I_S"
disable tree view in navigation frame, do not use server_database with full statistics
i intentionally disabled use of I_S only on common processed places in the code
Logged In: YES
user_id=210714
Originator: YES
With my setup I am getting mixed results with trunk than with QA_2_11.
Testing with 5.1.22
$cfg['DisableIS'] = true
$cfg['MaxDbList'] = 100;
$cfg['MaxTableList'] = 100;
Number of datatabases: 10000
Two of them containing 10000 tables, all other are empty.
With QA_2_11, initial login: 11 sec.
open a db of 10000 tables: 50 sec.
go to second page of this db: 18 sec.
in navi frame, go to any page of the db list: 1 sec.
With trunk, initial login: 6 sec.
open a db of 10000 tables: I stopped to count after 2 min!
Logged In: YES
user_id=326580
Originator: NO
improved processing of tables in database_structure view - please check again
using MySQL 5.1.22 i cannot see differences in performance using I_S or SHOW, they seem very random - but in most cases having a db with 1000 tables both variants take about 3 seconds (summarized query times displayed in footer with $cfg['DBG']['enable'] = true)
Logged In: YES
user_id=210714
Originator: YES
P.S. I suggest creating another variable for this debugging, because $cfg['DBG']['enable'] is supposed to be for the DBG extension stuff.
Yes, much better.
With trunk, initial login: 5 sec.
open a db of 10000 tables: 44 sec.
go to second page of this db: 15 sec.
in navi frame, go to any page of the db list: 1 sec.
open another db of 10000 tables: 29 sec.
13 queries executed21 times in 47.6904308796 seconds
Array
(
[queries] => Array
(
[62dfb7024dcbed7e20d94826d6bbe2d2] => Array
(
[count] => 2
[query] => SELECT VERSION()
[time] => 0.000468015670776
)
[d82e8428e8f6c504f5917495f5a952a8] => Array
(
[count] => 2
[query] => SET NAMES 'utf8' COLLATE 'utf8_general_ci';
[time] => 0.000532150268555
)
[0821c5d2870c5ca803051cc3f5e77224] => Array
(
[count] => 2
[query] => SHOW CHARACTER SET;
[time] => 0.00114107131958
)
[21c9749980e3011ad1f59dc2999284e8] => Array
(
[count] => 2
[query] => SHOW COLLATION;
[time] => 0.00191617012024
)
[511ba1c5b3abd4dc4a9d80e9ccfec7aa] => Array
(
[count] => 2
[query] => SHOW DATABASES;
[time] => 0.586776018143
)
[1b628edd8d4af33df523cba31cac8304] => Array
(
[count] => 2
[query] => SHOW VARIABLES LIKE 'profiling'
[time] => 0.00111293792725
)
[aaaaca11a10761a6cbddbef270d3ec4f] => Array
(
[count] => 3
[query] => SELECT COUNT(*) FROM mysql.user
[time] => 0.000699043273926
)
[e68fa7dee50b737170de8f6026228617] => Array
(
[count] => 1
[query] => SHOW TABLES FROM `db1`;
[time] => 26.8426251411
)
[4a66725aea3df04ca91b558ba113d115] => Array
(
[count] => 1
[query] => SHOW TABLE STATUS FROM `db1`;
[time] => 19.5926659107
)
[6552fcadce6a493a1cba1b91fc0a48a8] => Array
(
[count] => 1
[query] => SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'db1' LIMIT 1;
[time] => 0.00379514694214
)
[6159910ecb80988445903cf47fc85032] => Array
(
[count] => 1
[query] => SHOW VARIABLES LIKE 'storage_engine';
[time] => 0.00130295753479
)
[f28bd62919afdeb2f5142ecad1fb0030] => Array
(
[count] => 1
[query] => SELECT SPECIFIC_NAME,ROUTINE_NAME,ROUTINE_TYPE,DTD_IDENTIFIER FROM information_schema.ROUTINES WHERE ROUTINE_SCHEMA= 'db1';
[time] => 0.0616149902344
)
[ca11aa0c1bbd6278395f2ddd40a84a41] => Array
(
[count] => 1
[query] => SHOW GRANTS
[time] => 0.002436876297
)
)
)
Logged In: YES
user_id=210714
Originator: YES
Sebastian, any comment?
Logged In: YES
user_id=326580
Originator: NO
still i cannot see performance differences with I_S or SHOW in latest MySQL versions ...
Logged In: YES
user_id=210714
Originator: YES
With how many databases, how many tables, and which version exactly?
Also, about my comment for $cfg['DBG']?
Logged In: YES
user_id=1383652
Originator: NO
see also:
https://sourceforge.net/forum/forum.php?thread_id=1889931&forum_id=72909
(last post from today)
Logged In: YES
user_id=1957055
Originator: NO
I found out a few things about the usage of `information_schema` on hosted servers e.g. STRATO.
I_S is not always slow. But pma sometimes creates queries that will be executed very slow. those should be changed.
---------------------------------------------
1. in PMA_DBI_get_tables_full
the word BINARY in the query makes it extremely slow:
without BINARY:
2008-01-21 00:33:50 - SELECT *, [...] FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('DB321438')
2008-01-21 00:33:50 - executed in 0.01612114906311
with BINARY:
2008-01-21 00:36:43 -
SELECT *, [...] FROM `information_schema`.`TABLES`
WHERE BINARY `TABLE_SCHEMA` IN ('DB321438')
2008-01-21 00:37:01 - executed in 17.983309030533
---------------------------------------------
2. getting the table headers in sql.php.inc
if the original query in the SQL form goes to I_S for example:
SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` IN ('DB321438')
PMA executes this query in 0.02 seconds then it wants to create the headers:
2008-01-21 01:13:51 - SELECT COUNT(*) FROM `information_schema`.`TABLES`
2008-01-21 01:14:15 - executed in 23.892692089081
refering to http://dev.mysql.com/doc/refman/5.1/en/information-schema-optimization.html
non constant or missing database names should be avoided.
I think the same WHERE condition should be added.
---------------------------------------------
I made those two small modifications to my pma 3.0-dev and now it is fast again.
Even with 'I_S' switched on.
Logged In: YES
user_id=326580
Originator: NO
thank you, i will look into it
Logged In: YES
user_id=326580
Originator: NO
BINARY is required to distinguish between TEST and test - as stated in the comment
Logged In: YES
user_id=210714
Originator: YES
I wonder what's more important: case sensitivity on db names, or speed. Maybe we could add a cfg parameter for those who need to support case sensitivity on db names? By default it would be FALSE, then the default for $cfg['Servers'][$i]['DisableIS'] could be also false?
Logged In: YES
user_id=326580
Originator: NO
@vogon_jelz: i have tested on another ISP (Hosteurope, 5.0.32) and cannot see any differences with or without BINARY, what MySQL version is STRATO running?
about 2): if someone wants to browse I_S pma cannot just say, "no, this will be too slow", or what do you expect phpMyAdmin should do?
Logged In: YES
user_id=1957055
Originator: NO
STRATO is running version 5.0.51
about BINARY): nobody should use databases with same names in different cases on one server. I agree with lem9, case sensitivity could be off by default.
about 2): of course not. ;-)
If somebody enters a SQL to I_S without a WHERE it's his own fault.
But if I enter a correct SQL with a WHERE condition for example
"SELECT * FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('DB321438')"
pma could create the following to get the table headers:
"SELECT COUNT(*) FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` IN ('DB321438')"
Would it help if I create a test database with FTP-access for you on my webspace?
After months using my own patched PMA I downloaded PMA3.2.1 to see what has changed.
The new version looks very good...
...except it is still slow on STRATO hosted server running mySQL 5.0.67
Even though it is not a fault in PMA but an implementation problem in mySQL and perhaps an access problem at STRATO it makes it annoying to use pma 3.x
The reason is still the "BINARY" in the WHERE clause created in database_interface.lib.php.
If you delete it, the query to I_S takes less than 0.1s instead of 6 seconds.
for debug output see also
https://sourceforge.net/forum/message.php?msg_id=7570824
(post from today)
What about recent MySQL server like 5.5.x?