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

[Marc Delisle]

Madhura Jayaratne a écrit :
> On Wed, Nov 5, 2014 at 8:41 AM, Marc Delisle <marc at infomarc.info> wrote:
> 
>> 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.
>>
>> 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.


-- 
Marc Delisle (phpMyAdmin)