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
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
<raymond_bourges.vcf>_______________________________________________
ECM mailing list
[email protected]
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
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
