[Phpmyadmin-devel] Performance improvements for servers having large number of databases

[Marc Delisle]

Madhura Jayaratne a écrit :
> On Mon, Nov 3, 2014 at 10:31 AM, Madhura Jayaratne <madhura.cj at gmail.com>
> wrote:
> 
>>
>> On Mon, Nov 3, 2014 at 10:08 AM, Marc Delisle <marc at 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_FirstLevelNavigationItems
>>>> [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.


-- 
Marc Delisle (phpMyAdmin)