[Phpmyadmin-devel] Performance improvements for servers having large number of databases
Hi all,
Today morning I spent some time testing the performance improvements that were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50 meaning that about 1000 databases are loaded in the initial page of navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query execution time, the overall time to load the page is dominated by the time taken to render 1000 nodes in navigation, which is about 8s for master and 11s for QA_4_2.
However the rendering time can be significantly improved by setting [1] to a lower value, which currently defaults to 250. If I remember right, this directive was set to a higher value to prevent navigation from having extra spaces below [2]. Since this issue is no longer there (now when [1] is set to, say 50, 50 databases or database groups are displayed) I suggest to set [1] to a lower value.
[1] http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte... [2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Madhura Jayaratne a écrit :
Hi all,
Today morning I spent some time testing the performance improvements that were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50 meaning that about 1000 databases are loaded in the initial page of navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query execution time, the overall time to load the page is dominated by the time taken to render 1000 nodes in navigation, which is about 8s for master and 11s for QA_4_2.
However the rendering time can be significantly improved by setting [1] to a lower value, which currently defaults to 250. If I remember right, this directive was set to a higher value to prevent navigation from having extra spaces below [2]. Since this issue is no longer there (now when [1] is set to, say 50, 50 databases or database groups are displayed) I suggest to set [1] to a lower value.
[1] http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte... [2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Hi Madhura, Good idea.
On Mon, Nov 3, 2014 at 10:08 AM, Marc Delisle marc@infomarc.info wrote:
Madhura Jayaratne a écrit :
Hi all,
Today morning I spent some time testing the performance improvements that were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50 meaning that about 1000 databases are loaded in the initial page of navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query
execution
time, the overall time to load the page is dominated by the time taken to render 1000 nodes in navigation, which is about 8s for master and 11s for QA_4_2.
However the rendering time can be significantly improved by setting [1]
to
a lower value, which currently defaults to 250. If I remember right, this directive was set to a higher value to prevent navigation from having
extra
spaces below [2]. Since this issue is no longer there (now when [1] is
set
to, say 50, 50 databases or database groups are displayed) I suggest to
set
[1] to a lower value.
[1]
http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte...
[2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Hi Madhura, Good idea.
I've set this to 25, which seemed to fit to the screen without showing scroll bars.
On Mon, Nov 3, 2014 at 10:31 AM, Madhura Jayaratne madhura.cj@gmail.com wrote:
On Mon, Nov 3, 2014 at 10:08 AM, Marc Delisle marc@infomarc.info wrote:
Madhura Jayaratne a écrit :
Hi all,
Today morning I spent some time testing the performance improvements
that
were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50 meaning that about 1000 databases are loaded in the initial page of navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query
execution
time, the overall time to load the page is dominated by the time taken
to
render 1000 nodes in navigation, which is about 8s for master and 11s
for
QA_4_2.
However the rendering time can be significantly improved by setting [1]
to
a lower value, which currently defaults to 250. If I remember right,
this
directive was set to a higher value to prevent navigation from having
extra
spaces below [2]. Since this issue is no longer there (now when [1] is
set
to, say 50, 50 databases or database groups are displayed) I suggest to
set
[1] to a lower value.
[1]
http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte...
[2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Hi Madhura, Good idea.
I've set this to 25, which seemed to fit to the screen without showing scroll bars.
-- Thanks and Regards,
Madhura Jayaratne
Now, that I got access to the test server of Ann + J.M. with I could test the performance improvements done so far. Test server has more than 25000 databases.
For a privileged user: As also reported by Marc, signing in take about 7-8 seconds while navigating to pages in navigation now takes only about 3-4 seconds. This is due to lower default value for 'FirstLevelNavigationItems' configuration. Expanding a database in navigation also takes about 3 seconds. Similarly going into a database (viewing database structure page) took about 4-5 seconds.
Other database level operation such as searching in a database, viewing routines, events, triggers, going in to the designer took acceptable times. Similarly table level operations such as browsing, grid editing, editing, searching, inserting were also at an acceptable level. Editing the table structure which took some long time now takes only 3 - 5 seconds depending on the number of fields.
Database filters work normally as well.
For an unprivileged user: Login in and other operations take staggering long times for an unprivileged user. I tried viewing the list of databases (just 2, information_schema and one database by the username) for such user with the mysql console. Both SHOW DATABASES as well as SELECT schema_name FROM information_schema.schemata takes about 48 seconds.
On a positive note SHOW DATABASES LIKE '<username>%'; takes just a fraction of seconds. However only for the users at large hosting providers we can use such a query to retrieve the database list. If we are to take advantage of this, we probably will have a add a new configuration to indicate that unprivileged users are connecting to a particular installation of phpMyAdmin and hosting providers will have to use two installations, one for unprivileged users and one for privileged users.
I am working on such a solution today and I will be able to test it by tonight or tomorrow morning. I would appreciate your feedback on whether such a solution is practical.
Madhura Jayaratne a écrit :
On Mon, Nov 3, 2014 at 10:31 AM, Madhura Jayaratne madhura.cj@gmail.com wrote:
On Mon, Nov 3, 2014 at 10:08 AM, Marc Delisle marc@infomarc.info wrote:
Madhura Jayaratne a écrit :
Hi all,
Today morning I spent some time testing the performance improvements
that
were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50 meaning that about 1000 databases are loaded in the initial page of navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query
execution
time, the overall time to load the page is dominated by the time taken
to
render 1000 nodes in navigation, which is about 8s for master and 11s
for
QA_4_2.
However the rendering time can be significantly improved by setting [1]
to
a lower value, which currently defaults to 250. If I remember right,
this
directive was set to a higher value to prevent navigation from having
extra
spaces below [2]. Since this issue is no longer there (now when [1] is
set
to, say 50, 50 databases or database groups are displayed) I suggest to
set
[1] to a lower value.
[1]
http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte...
[2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Hi Madhura, Good idea.
I've set this to 25, which seemed to fit to the screen without showing scroll bars.
-- Thanks and Regards,
Madhura Jayaratne
Now, that I got access to the test server of Ann + J.M. with I could test the performance improvements done so far. Test server has more than 25000 databases.
For a privileged user: As also reported by Marc, signing in take about 7-8 seconds while navigating to pages in navigation now takes only about 3-4 seconds. This is due to lower default value for 'FirstLevelNavigationItems' configuration. Expanding a database in navigation also takes about 3 seconds. Similarly going into a database (viewing database structure page) took about 4-5 seconds.
Other database level operation such as searching in a database, viewing routines, events, triggers, going in to the designer took acceptable times. Similarly table level operations such as browsing, grid editing, editing, searching, inserting were also at an acceptable level. Editing the table structure which took some long time now takes only 3 - 5 seconds depending on the number of fields.
Database filters work normally as well.
For an unprivileged user: Login in and other operations take staggering long times for an unprivileged user. I tried viewing the list of databases (just 2, information_schema and one database by the username) for such user with the mysql console. Both SHOW DATABASES as well as SELECT schema_name FROM information_schema.schemata takes about 48 seconds.
On a positive note SHOW DATABASES LIKE '<username>%'; takes just a fraction of seconds. However only for the users at large hosting providers we can use such a query to retrieve the database list. If we are to take advantage of this, we probably will have a add a new configuration to indicate that unprivileged users are connecting to a particular installation of phpMyAdmin and hosting providers will have to use two installations, one for unprivileged users and one for privileged users.
I am working on such a solution today and I will be able to test it by tonight or tomorrow morning. I would appreciate your feedback on whether such a solution is practical.
I bet that hosting providers would remind us that before version 4.2, they were using a single installation for both kind of users.
Also, using SHOW DATABASES LIKE '<username>%';
is making an assumption about how the rights are assigned, and might not work in all cases.
On Wed, Nov 5, 2014 at 8:41 AM, Marc Delisle marc@infomarc.info wrote:
Madhura Jayaratne a écrit :
On Mon, Nov 3, 2014 at 10:31 AM, Madhura Jayaratne <madhura.cj@gmail.com
wrote:
On Mon, Nov 3, 2014 at 10:08 AM, Marc Delisle marc@infomarc.info
wrote:
Madhura Jayaratne a écrit :
Hi all,
Today morning I spent some time testing the performance improvements
that
were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50
meaning
that about 1000 databases are loaded in the initial page of
navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query
execution
time, the overall time to load the page is dominated by the time taken
to
render 1000 nodes in navigation, which is about 8s for master and 11s
for
QA_4_2.
However the rendering time can be significantly improved by setting
[1]
to
a lower value, which currently defaults to 250. If I remember right,
this
directive was set to a higher value to prevent navigation from having
extra
spaces below [2]. Since this issue is no longer there (now when [1] is
set
to, say 50, 50 databases or database groups are displayed) I suggest
to
set
[1] to a lower value.
[1]
http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte...
[2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Hi Madhura, Good idea.
I've set this to 25, which seemed to fit to the screen without showing scroll bars.
-- Thanks and Regards,
Madhura Jayaratne
Now, that I got access to the test server of Ann + J.M. with I could test the performance improvements done so far. Test server has more than 25000 databases.
For a privileged user: As also reported by Marc, signing in take about 7-8 seconds while navigating to pages in navigation now takes only about 3-4 seconds. This
is
due to lower default value for 'FirstLevelNavigationItems' configuration. Expanding a database in navigation also takes about 3 seconds. Similarly going into a database (viewing database structure page) took about 4-5 seconds.
Other database level operation such as searching in a database, viewing routines, events, triggers, going in to the designer took acceptable
times.
Similarly table level operations such as browsing, grid editing, editing, searching, inserting were also at an acceptable level. Editing the table structure which took some long time now takes only 3 - 5 seconds
depending
on the number of fields.
Database filters work normally as well.
For an unprivileged user: Login in and other operations take staggering long times for an unprivileged user. I tried viewing the list of databases (just 2, information_schema and one database by the username) for such user with
the
mysql console. Both SHOW DATABASES as well as SELECT schema_name FROM information_schema.schemata takes about 48 seconds.
On a positive note SHOW DATABASES LIKE '<username>%'; takes just a
fraction
of seconds. However only for the users at large hosting providers we can use such a query to retrieve the database list. If we are to take
advantage
of this, we probably will have a add a new configuration to indicate that unprivileged users are connecting to a particular installation of phpMyAdmin and hosting providers will have to use two installations, one for unprivileged users and one for privileged users.
I am working on such a solution today and I will be able to test it by tonight or tomorrow morning. I would appreciate your feedback on whether such a solution is practical.
I bet that hosting providers would remind us that before version 4.2, they were using a single installation for both kind of users.
Also, using SHOW DATABASES LIKE '<username>%';
is making an assumption about how the rights are assigned, and might not work in all cases.
Found a better way to handle this.
By analyzing GRANTs, it is possible to figure out whether the user has global privileges or if not the set of databases or wildcards the user has permissions. These databases or wildcards can then be uses with SHOW DATABASES LIKE to retrieve databases.
I implemented this on the master and this gives reasonable performance for non privileged users on the test server.
Madhura Jayaratne a écrit :
On Wed, Nov 5, 2014 at 8:41 AM, Marc Delisle marc@infomarc.info wrote:
Madhura Jayaratne a écrit :
On Mon, Nov 3, 2014 at 10:31 AM, Madhura Jayaratne <madhura.cj@gmail.com
wrote:
On Mon, Nov 3, 2014 at 10:08 AM, Marc Delisle marc@infomarc.info
wrote:
Madhura Jayaratne a écrit :
Hi all,
Today morning I spent some time testing the performance improvements
that
were done for the servers having a large number of databases.
My setup has about 5000 databases grouped in to database groups of 20 databases. Navigation items at first level [1] is limited to 50
meaning
that about 1000 databases are loaded in the initial page of
navigation.
While loading the initial page, QA_4_2: 1008 queries executed 1011 times in 1.00407 s master: 12 queries executed 17 times in 0.51211s
Even though master is about 2 times faster in terms of the query
execution
time, the overall time to load the page is dominated by the time taken
to
render 1000 nodes in navigation, which is about 8s for master and 11s
for
QA_4_2.
However the rendering time can be significantly improved by setting
[1]
to
a lower value, which currently defaults to 250. If I remember right,
this
directive was set to a higher value to prevent navigation from having
extra
spaces below [2]. Since this issue is no longer there (now when [1] is
set
to, say 50, 50 databases or database groups are displayed) I suggest
to
set
[1] to a lower value.
[1]
http://docs.phpmyadmin.net/en/latest/config.html#cfg_FirstLevelNavigationIte...
[2] https://sourceforge.net/p/phpmyadmin/mailman/message/30077320/
Hi Madhura, Good idea.
I've set this to 25, which seemed to fit to the screen without showing scroll bars.
-- Thanks and Regards,
Madhura Jayaratne
Now, that I got access to the test server of Ann + J.M. with I could test the performance improvements done so far. Test server has more than 25000 databases.
For a privileged user: As also reported by Marc, signing in take about 7-8 seconds while navigating to pages in navigation now takes only about 3-4 seconds. This
is
due to lower default value for 'FirstLevelNavigationItems' configuration. Expanding a database in navigation also takes about 3 seconds. Similarly going into a database (viewing database structure page) took about 4-5 seconds.
Other database level operation such as searching in a database, viewing routines, events, triggers, going in to the designer took acceptable
times.
Similarly table level operations such as browsing, grid editing, editing, searching, inserting were also at an acceptable level. Editing the table structure which took some long time now takes only 3 - 5 seconds
depending
on the number of fields.
Database filters work normally as well.
For an unprivileged user: Login in and other operations take staggering long times for an unprivileged user. I tried viewing the list of databases (just 2, information_schema and one database by the username) for such user with
the
mysql console. Both SHOW DATABASES as well as SELECT schema_name FROM information_schema.schemata takes about 48 seconds.
On a positive note SHOW DATABASES LIKE '<username>%'; takes just a
fraction
of seconds. However only for the users at large hosting providers we can use such a query to retrieve the database list. If we are to take
advantage
of this, we probably will have a add a new configuration to indicate that unprivileged users are connecting to a particular installation of phpMyAdmin and hosting providers will have to use two installations, one for unprivileged users and one for privileged users.
I am working on such a solution today and I will be able to test it by tonight or tomorrow morning. I would appreciate your feedback on whether such a solution is practical.
I bet that hosting providers would remind us that before version 4.2, they were using a single installation for both kind of users.
Also, using SHOW DATABASES LIKE '<username>%';
is making an assumption about how the rights are assigned, and might not work in all cases.
Found a better way to handle this.
By analyzing GRANTs, it is possible to figure out whether the user has global privileges or if not the set of databases or wildcards the user has permissions. These databases or wildcards can then be uses with SHOW DATABASES LIKE to retrieve databases.
I implemented this on the master and this gives reasonable performance for non privileged users on the test server.
Good idea; maybe it's time to ask feedback about this version to the bug reporters.
-
Madhura Jayaratne -
Marc Delisle