Hi,
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
begin:vcard
fn:Raymond Bourges
n:Bourges;Raymond
org;quoted-printable:Universit=C3=A9 de Rennes 1;CRI
adr:;;Campus de Beaulieu;Rennes;;35042;France
email;internet:[email protected]
title;quoted-printable:Ing=C3=A9nieur Informatique
tel;work:+33 (0)2.23.23.71.32
note:Mon agenda : http://agenda.univ-rennes1.fr/?calid=bourges&security=1
x-mozilla-html:TRUE
url:http://blogperso.univ-rennes1.fr/raymond.bourges/
version:2.1
end:vcard
_______________________________________________
ECM mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
