« Return to Thread: Very bad performances with mySQL
Re: Very bad performances with mySQL
by Raymond Bourges
::
Rate this Message:
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
> ECM@...
> http://lists.nuxeo.com/mailman/listinfo/ecm
> To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
>
[raymond_bourges.vcf]
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:Raymond.Bourges@...
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
ECM@...
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm
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]
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:Raymond.Bourges@...
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
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 |
