eZ Community » Forums » Setup & design » Caching - Still having many...
expandshrink

Caching - Still having many MySQL-Queries

Caching - Still having many MySQL-Queries

Wednesday 27 January 2010 10:09:36 pm - 10 replies

Hello,

beside activating the Caching of ezPublish (View-Cache, Template-Cache, Template-Compile, Translation-Cache) I am trying to use cache-blocks in Templates to increase performance by especially decreasing the number of MySQL-Queries. This is working fine for several pages, but for some of them the number of Queries can not be reduced.

Even if I use a pagelayout which does not contain a single line and enable debug the MySQL-Queries are still very high, on one page I even have over 240 MySQL-Queries.

Does anyone have any ideas?

Thanks,
Alex

Modified on Wednesday 27 January 2010 10:10:58 pm by Alex Yes

Wednesday 27 January 2010 10:39:26 pm

can you send the sql queries.

Thursday 28 January 2010 11:08:03 am

Hi,

Can you also post how you manage cache in the module result ? For simple pages, you don't need to use cache blocks thanks to the view cache.

Thursday 28 January 2010 11:55:27 am

The cache is not flushed, the queries still occur after the first request of the page.
Below are the queries from one page with an empty pagelayout. I have shortened the queries so that it is more clearly.

SELECT data, user_id, user_hash, expiration_time FROM ezsession WHERE session_key='...'
SELECT id, name, locale, disabled FROM   ezcontent_language ORDER BY name ASC
SELECT e0.id AS e0_id, ... FROM ezurlalias_ml e0, ezurlalias_ml e1, ezurlalias_ml e2, ezurlalias_ml e3, ezurlalias_ml e4 WHERE e0.parent = 0 AND ...  LIMIT 0, 1
 
Module start 'layout'
SELECT e0.id AS e0_id, ... FROM ezurlalias_ml e0, ezurlalias_ml e1 WHERE e0.parent = 0 AND (e0.lang_mask & 3 > 0) ... LIMIT 0, 1
 
Module start 'content'
SELECT path_string FROM   ezcontentobject_tree WHERE  node_id='102'
 
SELECT DISTINCT e0.id AS e0_id, e0.parent AS e0_parent, ... FROM ezurlalias_ml e0 WHERE e0.parent = 0 AND (e0.lang_mask & 3 > 0) ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, ... FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 98 AND ...
SELECT DISTINCT e0.id AS e0_id, ... FROM ezurlalias_ml e0, ezurlalias_ml e1 WHERE e0.parent = 0 AND (e0.lang_mask & 3 > 0) ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, ... FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 274848 AND ...
BEGIN WORK
SELECT id, main, memento_key, main_key, memento_data FROM ezoperation_memento WHERE memento_key='...' AND main='1'
SELECT id, main, memento_key, main_key, memento_data FROM ezoperation_memento WHERE memento_key='...' AND main='0'
SELECT id, module_name, function_name, connect_type, workflow_id, name FROM eztrigger WHERE  name='pre_read' AND module_name='content' AND function_name='read'
SELECT ezcontentobject.*, ezcontentobject_tree.*, ... FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 102 ...
COMMIT
SELECT ezcontentobject.*, ezcontentobject_tree.*, ... FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 102 ...
SELECT id, name, navigation_part_identifier, locale FROM   ezsection WHERE  id='1'
SELECT remote_id FROM ezcontentobject WHERE id = '102'
SELECT id, parent, lang_mask, text, action FROM ezurlalias_ml WHERE ( ezurlalias_ml.lang_mask & 3 > 0 ) ...
SELECT contentobject_state_id, group_id FROM ezcobj_state_link, ezcobj_state WHERE ezcobj_state.id=ezcobj_state_link.contentobject_state_id ...
SELECT l.contentobject_state_id, ... FROM ezcobj_state_link l, ezcobj_state s, ezcobj_state_group g WHERE l.contentobject_id=102 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*,... FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 93 AND ...
SELECT id, version, serialized_name_list, ... FROM ezcontentclass WHERE  id='23' AND version='0' ORDER BY version ASC LIMIT 0, 2
SELECT ezcontentobject.*, ezcontentobject_tree.*, ... FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE  ... ORDER BY path_string
SELECT id, parent, lang_mask, text, action FROM ezurlalias_ml WHERE ( ezurlalias_ml.lang_mask & 3 > 0 ) AND ...
SELECT name, content_translation FROM ezcontentobject_name WHERE contentobject_id = '102' ...
SELECT name, content_translation FROM ezcontentobject_name WHERE contentobject_id = '102' ...
SELECT ezcontentobject_attribute.*, ... FROM ezcontentobject_attribute, ezcontentclass_attribute, ezcontentobject_version WHERE ... 
SELECT ezcontentobject.*, ... FROM ezcontentobject, ezcontentclass WHERE ezcontentobject.id='102' ...
SELECT id, serialized_name_list, version, contentclass_id, ... FROM  ezcontentclass_attribute WHERE  id='236' AND version='0'
SELECT * FROM ezm_pool, ezcontentobject_tree WHERE ezm_pool.block_id='...' ... ORDER BY ezm_pool.priority DESC
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 277715 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 277711 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 277704 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 277589 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 277549 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 248 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 102 AND ...
SELECT id, parent, lang_mask, text, action FROM ezurlalias_ml WHERE ( ezurlalias_ml.lang_mask & 3 > 0 ) AND ...
SELECT contentobject_state_id, group_id FROM ezcobj_state_link, ezcobj_state WHERE ezcobj_state.id=ezcobj_state_link.contentobject_state_id AND ...
SELECT l.contentobject_state_id, ... FROM ezcobj_state_link l, ezcobj_state s, ezcobj_state_group g WHERE l.contentobject_id=286981 AND ...
SELECT id, parent, lang_mask, text, action FROM ezurlalias_ml WHERE ( ezurlalias_ml.lang_mask & 3 > 0 ) AND ...
SELECT remote_id FROM ezcontentobject WHERE id = '286981'
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 277715 AND ...
SELECT ezcontentobject.*, ezcontentobject_tree.*, FROM ezcontentobject_tree, ezcontentobject, ezcontentclass, ezcontentobject_name WHERE node_id = 248 AND ...

Thursday 28 January 2010 12:02:17 pm

Can you also post how you manage cache in the module result ? For simple pages, you don't need to use cache blocks thanks to the view cache.

Yes, for simple pages I use the view-cache. Is the module result the output of the template which prints out the content? If yes, there I am using cache-block's with various parameters like:

<span class="line">{cache-block ignore_content_expiry}</span>
...
<span class="line"></span><span class="line">{/cache-block}
</span>

But if I have an empty pagelayout it should not matter if I have a cache-block here or not and there are still many queries.

Thursday 28 January 2010 12:35:53 pm

i'm a bit confused by what you say.

With an empty pagelayout + viewcache enabled, you still get these SQL queries ? Then something is wrong with your viewcache.

There is an easy way to check if a view has been loaded from ViewCache: enable DebugSettings.DebugOutput + TemplateSettings.ShowUsedTemplates, and check in the list of used templates if node/view/full.tpl is listed. If it is, your view hasn't been loaded from viewcache.

Thursday 28 January 2010 12:57:52 pm

With an empty pagelayout + viewcache enabled, you still get these SQL queries ? Then something is wrong with your viewcache.

There is an easy way to check if a view has been loaded from ViewCache: enable DebugSettings.DebugOutput + TemplateSettings.ShowUsedTemplates, and check in the list of used templates if node/view/full.tpl is listed. If it is, your view hasn't been loaded from viewcache.

This is what I get:

- ini_load                
Load cache: 16
- Mysql Total                
Mysqli_queries: 46
Looping result: 38
- TS translator                
TS init: 3
TS cache load: 3
TS context load: 3
- Template Total: 3
Template load: 3
Template processing: 3
Template load and register function: 2
- states                
state_id_array: 1
state_identifier_array: 2
- override                
Cache load: 7
- Sytem overhead                
Fetch class attribute name: 3
Fetch class attribute can translate value: 1
- class_abstraction                
Instantiating content class attribute: 3
- XML                
Image XML parsing: 1
- General                
dbfile: 12
String conversion: 4
 
Requested Template            Template
node/view/full.tpl            full/frontpage.tpl    extension/mydesign/design/mydesign/override/templates/full/frontpage.tpl
content/datatype/view/ezpage.tpl    <No override>    extension/ezflow/design/standard/templates/content/datatype/view/ezpage.tpl
zone/frontpage.tpl            <No override>    extension/mydesign/design/mydesign/templates/zone/frontpage.tpl
content/datatype/view/ezimage.tpl    <No override>    extension/mydesign/design/mydesign/override/templates/content/datatype/view/ezimage.tpl
content/datatype/view/ezurl.tpl        <No override>    extension/mydesign/design/mydesign/templates/content/datatype/view/ezurl.tpl
blanktest_pagelayout.tpl        <No override>    extension/mydesign/design/mydesign/templates/blanktest_pagelayout.tpl
setup/debug_toolbar.tpl            <No override>    design/standard/templates/setup/debug_toolbar.tpl

So node/view/full.tpl is listed. I have tried it with pages which work fine with caching, then they only blanktest_pagelayout & debug_toolbar.tpl listed. Do you know the reason, why some pages are not loaded from the viewcache?

btw, I am using ez Flow. I don't know it this matters.

Thanks,
Alex

Modified on Thursday 28 January 2010 1:16:00 pm by Alex Yes

Thursday 28 January 2010 2:04:07 pm

Well, you first need to check your INI settings, obviously happy.gif Emoticon

Look for ContentSettings.ViewCaching in your siteaccess' site.ini and in override.

Friday 29 January 2010 11:34:17 am

I checked this, ViewCaching is enabled in every site.ini:

site.ini
override/site.ini.append.php
siteaccess/*/site.ini.append.php

Also as said some pages are cached some not. Any other clues?

Thanks,
Alex

Modified on Friday 29 January 2010 11:34:44 am by Alex Yes

Friday 29 January 2010 12:27:12 pm

Would by any chance any of your templates contain something with cache_ttl in it ?

Thursday 18 March 2010 4:21:48 pm

Would by any chance any of your templates contain something with cache_ttl in it ?

That was finally the reason ... if there is a single template with a cache_ttl=0 used in the page the whole page does not use the view-cache.

The problem is that the debug-mode does not show all templates used, therefor it was hard to find!

expandshrink

You must be logged in to post messages in this topic!

36 542 Users on board!

Forums menu

Proudly Developed with from