Very bad performances with mySQL

[Raymond Bourges]

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.

[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

[Raymond Bourges]

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

[Florent Guillaume]

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   [phone]

_______________________________________________
ECM mailing list
ECM@...
http://lists.nuxeo.com/mailman/listinfo/ecm
To unsubscribe, go to http://lists.nuxeo.com/mailman/options/ecm