Caching - Still having many MySQL-Queries

Author Message

Alex Yes

Wednesday 27 January 2010 1:09:36 pm

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

stephane couzinier

Wednesday 27 January 2010 1:39:26 pm

can you send the sql queries.

don't flush the cache

Nicolas Lescure

Thursday 28 January 2010 2: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.

Alex Yes

Thursday 28 January 2010 2: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 ...

Alex Yes

Thursday 28 January 2010 3:02:17 am

"

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.

Bertrand Dunogier

Thursday 28 January 2010 3:35:53 am

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.

Bertrand Dunogier
eZ Systems Engineering, Lyon
http://twitter.com/bdunogier
http://gplus.to/BertrandDunogier

Alex Yes

Thursday 28 January 2010 3:57:52 am

"

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

Bertrand Dunogier

Thursday 28 January 2010 5:04:07 am

Well, you first need to check your INI settings, obviously :-)

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

Bertrand Dunogier
eZ Systems Engineering, Lyon
http://twitter.com/bdunogier
http://gplus.to/BertrandDunogier

Alex Yes

Friday 29 January 2010 2: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

Bertrand Dunogier

Friday 29 January 2010 3:27:12 am

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

Bertrand Dunogier
eZ Systems Engineering, Lyon
http://twitter.com/bdunogier
http://gplus.to/BertrandDunogier

Alex Yes

Thursday 18 March 2010 8:21:48 am

"

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!

Powered by eZ Publish™ CMS Open Source Web Content Management. Copyright © 1999-2014 eZ Systems AS (except where otherwise noted). All rights reserved.