« Return to Thread: Very bad performances with mySQL
Re: Very bad performances with mySQL
by Florent Guillaume
::
Rate this Message:
This is one of the many reasons we don't recommend MySQL in
production: its query planner is not very intelligent, and like in
this example you have to guide it by hand to make it do what you want,
where in normal databases (PostgreSQL) it would estimate (or be told)
the cost of the NX_ACCESS_ALLOWED and execute in the outer part of the
joins, not the inner part as you're witnessing.
Doing a subselect to force ordering is a hack that may work here, but
will produce very bad results in other situations. The planner should
know best what to do.
In the future we may add configuration options to do that. In the
meantime, you may want to request through your Nuxeo Support a
specialized version of QueryMaker for your use case.
Florent
On 30 Jun 2009, at 11:09, Raymond Bourges wrote:
> I have some explanations about my problem.
>
> With NXQL used in dashboard I have this kind of SQL in my mySQL :
>
> SELECT DISTINCT `_nxhier`.`id`
> FROM `hierarchy` `_nxhier`
> LEFT JOIN `proxies` ON `proxies`.`id` = `_nxhier`.`id`
> JOIN `hierarchy` ON (`hierarchy`.`id` = `_nxhier`.`id` OR
> `hierarchy`.`id` = `proxies`.`targetid`)
> WHERE `hierarchy`.`primarytype` IN ('search_results', 'Document',
> 'Picture', 'Domain', 'Comment', 'Workspace', 'TemplateRoot',
> 'QueryNav', 'File', 'HiddenFolder', 'Section', 'UserWorkspacesRoot',
> 'Note', 'SectionRoot', 'Folder', 'CommentRoot', 'PictureBook',
> 'AdvancedSearch', 'OrderedFolder', 'WorkspaceRoot')
> AND NX_ACCESS_ALLOWED(`_nxhier`.`id`, 'pers_HARPUR1_dept_57SI|
> pers_HARPUR1_dept_957|pers_ORI-OAI_admins|pers_ORI-
> OAI_validateurs_techniques|members|bourges|Everyone', 'Browse|Read|
> ReadProperties|ReadRemove|ReadWrite|Everything');
>
> MySQL use NX_ACCESS_ALLOWED for each row BEFORE doing JOIN so
> NX_ACCESS_ALLOWED is used a lot :-(
>
> This SQL have an execution time of 6 seconds.
>
> If I transform this SQL as :
>
> select id from (
> SELECT DISTINCT `_nxhier`.`id`
> FROM `hierarchy` `_nxhier`
> LEFT JOIN `proxies` ON `proxies`.`id` = `_nxhier`.`id`
> JOIN `hierarchy` ON (`hierarchy`.`id` = `_nxhier`.`id` OR
> `hierarchy`.`id` = `proxies`.`targetid`)
> WHERE `hierarchy`.`primarytype` IN ('search_results', 'Document',
> 'Picture', 'Domain', 'Comment', 'Workspace', 'TemplateRoot',
> 'QueryNav', 'File', 'HiddenFolder', 'Section', 'UserWorkspacesRoot',
> 'Note', 'SectionRoot', 'Folder', 'CommentRoot', 'PictureBook',
> 'AdvancedSearch', 'OrderedFolder', 'WorkspaceRoot') ) as select1
> where NX_ACCESS_ALLOWED(`select1`.`id`, 'pers_HARPUR1_dept_57SI|
> pers_HARPUR1_dept_957|pers_ORI-OAI_admins|pers_ORI-
> OAI_validateurs_techniques|members|bourges|Everyone', 'Browse|Read|
> ReadProperties|ReadRemove|ReadWrite|Everything');
>
> This SQL have an execution time of 0.04 seconds !!!
>
> In order to change the nuxeo generated SQL the solution seems to
> adapt QueryMaker class.
>
> But this implementation is not mySQL specific.
>
> How to do?
>
> This modification is required (and urgent in my case) for mySQL
> support with more that 10 documents ;-).
>
> Thanks
>
> Raymond Bourges a écrit :
>> Hi,
>>
>> In my nuxeo 5.2 I have 168 entries in my hierarchy table.
>>
>> When I have access to guest dashboard I have 72,211 calls to
>> NX_ACCESS_ALLOWED function (I have this information with some debug
>> code add to NX_ACCESS_ALLOWED function) !
>>
>> Of course my mySQL server use a lot of CPU (because of
>> NX_ACCESS_ALLOWED complexity) and page makes a long time to display
>> (up to 5 minutes for 250 documents in a other developer's computer).
>>
>> Why how many calls to NX_ACCESS_ALLOWED?
>>
>> Strangely Postgresql seems to be more efficient. I haven't
>> information about number of calls to NX_ACCESS_ALLOWED function
>> with PG but I think that Postgresql have a better cache mechanism.
>>
>> About this, this is my mysql cache configuration :
>> SHOW VARIABLES LIKE 'query%';
>> query_alloc_block_size 8192
>> query_cache_limit 2097152
>> query_cache_min_res_unit 4096
>> query_cache_size 134217728
>> query_cache_type ON
>> query_cache_wlock_invalidate OFF
>> query_prealloc_size 8192
>>
>> Thanks.
>> _______________________________________________
>> ECM mailing list
>> ECM@...
>> http://lists.nuxeo.com/mailman/listinfo/ecm
>> To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
>>
>
> <raymond_bourges.vcf>_______________________________________________
> ECM mailing list
> ECM@...
> http://lists.nuxeo.com/mailman/listinfo/ecm
> To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
--
Florent Guillaume, Head of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87
_______________________________________________
ECM mailing list
ECM@...
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
production: its query planner is not very intelligent, and like in
this example you have to guide it by hand to make it do what you want,
where in normal databases (PostgreSQL) it would estimate (or be told)
the cost of the NX_ACCESS_ALLOWED and execute in the outer part of the
joins, not the inner part as you're witnessing.
Doing a subselect to force ordering is a hack that may work here, but
will produce very bad results in other situations. The planner should
know best what to do.
In the future we may add configuration options to do that. In the
meantime, you may want to request through your Nuxeo Support a
specialized version of QueryMaker for your use case.
Florent
On 30 Jun 2009, at 11:09, Raymond Bourges wrote:
> I have some explanations about my problem.
>
> With NXQL used in dashboard I have this kind of SQL in my mySQL :
>
> SELECT DISTINCT `_nxhier`.`id`
> FROM `hierarchy` `_nxhier`
> LEFT JOIN `proxies` ON `proxies`.`id` = `_nxhier`.`id`
> JOIN `hierarchy` ON (`hierarchy`.`id` = `_nxhier`.`id` OR
> `hierarchy`.`id` = `proxies`.`targetid`)
> WHERE `hierarchy`.`primarytype` IN ('search_results', 'Document',
> 'Picture', 'Domain', 'Comment', 'Workspace', 'TemplateRoot',
> 'QueryNav', 'File', 'HiddenFolder', 'Section', 'UserWorkspacesRoot',
> 'Note', 'SectionRoot', 'Folder', 'CommentRoot', 'PictureBook',
> 'AdvancedSearch', 'OrderedFolder', 'WorkspaceRoot')
> AND NX_ACCESS_ALLOWED(`_nxhier`.`id`, 'pers_HARPUR1_dept_57SI|
> pers_HARPUR1_dept_957|pers_ORI-OAI_admins|pers_ORI-
> OAI_validateurs_techniques|members|bourges|Everyone', 'Browse|Read|
> ReadProperties|ReadRemove|ReadWrite|Everything');
>
> MySQL use NX_ACCESS_ALLOWED for each row BEFORE doing JOIN so
> NX_ACCESS_ALLOWED is used a lot :-(
>
> This SQL have an execution time of 6 seconds.
>
> If I transform this SQL as :
>
> select id from (
> SELECT DISTINCT `_nxhier`.`id`
> FROM `hierarchy` `_nxhier`
> LEFT JOIN `proxies` ON `proxies`.`id` = `_nxhier`.`id`
> JOIN `hierarchy` ON (`hierarchy`.`id` = `_nxhier`.`id` OR
> `hierarchy`.`id` = `proxies`.`targetid`)
> WHERE `hierarchy`.`primarytype` IN ('search_results', 'Document',
> 'Picture', 'Domain', 'Comment', 'Workspace', 'TemplateRoot',
> 'QueryNav', 'File', 'HiddenFolder', 'Section', 'UserWorkspacesRoot',
> 'Note', 'SectionRoot', 'Folder', 'CommentRoot', 'PictureBook',
> 'AdvancedSearch', 'OrderedFolder', 'WorkspaceRoot') ) as select1
> where NX_ACCESS_ALLOWED(`select1`.`id`, 'pers_HARPUR1_dept_57SI|
> pers_HARPUR1_dept_957|pers_ORI-OAI_admins|pers_ORI-
> OAI_validateurs_techniques|members|bourges|Everyone', 'Browse|Read|
> ReadProperties|ReadRemove|ReadWrite|Everything');
>
> This SQL have an execution time of 0.04 seconds !!!
>
> In order to change the nuxeo generated SQL the solution seems to
> adapt QueryMaker class.
>
> But this implementation is not mySQL specific.
>
> How to do?
>
> This modification is required (and urgent in my case) for mySQL
> support with more that 10 documents ;-).
>
> Thanks
>
> Raymond Bourges a écrit :
>> Hi,
>>
>> In my nuxeo 5.2 I have 168 entries in my hierarchy table.
>>
>> When I have access to guest dashboard I have 72,211 calls to
>> NX_ACCESS_ALLOWED function (I have this information with some debug
>> code add to NX_ACCESS_ALLOWED function) !
>>
>> Of course my mySQL server use a lot of CPU (because of
>> NX_ACCESS_ALLOWED complexity) and page makes a long time to display
>> (up to 5 minutes for 250 documents in a other developer's computer).
>>
>> Why how many calls to NX_ACCESS_ALLOWED?
>>
>> Strangely Postgresql seems to be more efficient. I haven't
>> information about number of calls to NX_ACCESS_ALLOWED function
>> with PG but I think that Postgresql have a better cache mechanism.
>>
>> About this, this is my mysql cache configuration :
>> SHOW VARIABLES LIKE 'query%';
>> query_alloc_block_size 8192
>> query_cache_limit 2097152
>> query_cache_min_res_unit 4096
>> query_cache_size 134217728
>> query_cache_type ON
>> query_cache_wlock_invalidate OFF
>> query_prealloc_size 8192
>>
>> Thanks.
>> _______________________________________________
>> ECM mailing list
>> ECM@...
>> http://lists.nuxeo.com/mailman/listinfo/ecm
>> To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
>>
>
> <raymond_bourges.vcf>_______________________________________________
> ECM mailing list
> ECM@...
> http://lists.nuxeo.com/mailman/listinfo/ecm
> To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
--
Florent Guillaume, Head of R&D, Nuxeo
Open Source, Java EE based, Enterprise Content Management (ECM)
http://www.nuxeo.com http://www.nuxeo.org +33 1 40 33 79 87
_______________________________________________
ECM mailing list
ECM@...
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
« Return to Thread: Very bad performances with mySQL
| Free embeddable forum powered by Nabble | Forum Help |
