Ez 3.8.X doesn't work with big database

Thursday 28 September 2006 9:41:42 am

Hi all,

I've tryed installing all 3.8 ez from 3.8.0 to 3.8.4 but i alwais get the same result:
Everything works without errors but page load time ridiculous, sometimes even timeout. I found the cause is a mysql query:

SELECT ezcontentobject.*,
ezcontentobject_tree.*,
ezcontentclass.name as class_name,
ezcontentclass.identifier as class_identifier,
ezcontentobject_name.name as name,  ezcontentobject_name.real_translation
FROM
ezcontentobject_tree,
ezcontentobject,ezcontentclass, 
ezcontentobject_name
WHERE
path_string like '/1/2/10570/%' and  depth = 3  and
ezcontentclass.version=0 AND
ezcontentobject_tree.contentobject_id = ezcontentobject.id  AND
ezcontentclass.id = ezcontentobject.contentclass_id AND
ezcontentobject_tree.contentobject_id = ezcontentobject_name.contentobject_id and
ezcontentobject_tree.contentobject_version = ezcontentobject_name.content_version and
( ezcontentobject_name.language_id & ezcontentobject.language_mask > 0 AND
 ( (   ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 16 ) >> 3 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 8 ) >> 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 256 ) >> 5 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 128 ) >> 3 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 64 ) >> 1 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 4 ) << 4 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 2 ) << 6 )
+ ( ( ( ezcontentobject.language_mask - ( ezcontentobject.language_mask & ezcontentobject_name.language_id ) ) & 32 ) << 3 )
<
( ezcontentobject_name.language_id & 1 )
+ ( ( ezcontentobject_name.language_id & 16 ) >> 3 )
+ ( ( ezcontentobject_name.language_id & 8 ) >> 1 )
+ ( ( ezcontentobject_name.language_id & 256 ) >> 5 )
+ ( ( ezcontentobject_name.language_id & 128 ) >> 3 )
+ ( ( ezcontentobject_name.language_id & 64 ) >> 1 )
+ ( ( ezcontentobject_name.language_id & 4 ) << 4 )
+ ( ( ezcontentobject_name.language_id & 2 ) << 6 )
+ ( ( ezcontentobject_name.language_id & 32 ) << 3 )
)
AND ezcontentobject_tree.is_invisible = 0
AND ((ezcontentobject.contentclass_id in (1, 11, 12, 19, 2, 20, 21, 32, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 46, 48, 49, 5, 50) AND ezcontentobject.section_id in (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 3, 7, 8, 9)) OR (ezcontentobject.contentclass_id in (1, 11, 19, 2, 20, 21, 32, 34, 35, 36, 37, 38, 39, 4, 40, 41, 42, 43, 44, 46, 48, 49, 5, 50) AND ezcontentobject.section_id in (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 3, 7, 9)) OR (ezcontentobject.contentclass_id in (1, 16, 19, 2, 20, 21, 32, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44) AND ezcontentobject.section_id in (1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 7, 9)))
AND
ezcontentobject.language_mask & 511 > 0
ORDER BY  path_string ASC

Is it possible to make a less heavy query or any other thing? With a big database the site becomes unusable.

Thanks

Friday 29 September 2006 4:28:00 am

Thanks for the answer,

It happends on my code and also on admin code. I have about 200k objects.

With sql debug on i found that all the slow queries (the ones witch takes more than 1 sec to execute) has a where like

path_string like '/1/2/1297/%' and  depth = 3  and 

Not always '=' , also '>=', '<=' and not the same depth but always the same where.

Not all the queries with this where go to "Copying to tmp table" status, probably because of the query cache or any other mysql cache.

Any help would be apreciated

Luis

Friday 29 September 2006 5:03:17 am

Hi,

I tuned database yesterday following the indications on http://ez.no/community/articles/tuning_mysql_for_ez_publish but that didn't fix the problem.
I think the problem comes from using the operator like with a string with wildcards, i'll check with mysql forums. I'm also looking if it performs better changing the order of the where.
Should i post it as a bug? sugestion?

Thanks

Friday 29 September 2006 5:49:19 am

Hi

I've added a bug report about this. Please see: http://ez.no/bugs/view/9091
You can try the index I've listed there, and see if they improve the performance.

Best regards
Kåre

Kåre Høvik

Saturday 30 September 2006 2:17:48 am

Kore, thanks for posting the new indexes. We applied them as well ;)
Just a note: Can you have a look at the ezcontentobject.language_mask comparisons in the WHEREs, so this could be optimized for single lingual sites?

Marco
http://www.hyperroad-design.com