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.

eZ debug

Timing: Jan 18 2025 02:10:09
Script start
Timing: Jan 18 2025 02:10:09
Module start 'layout'
Timing: Jan 18 2025 02:10:09
Module start 'content'
Timing: Jan 18 2025 02:10:10
Module end 'content'
Timing: Jan 18 2025 02:10:10
Script end

Main resources:

Total runtime0.7020 sec
Peak memory usage4,096.0000 KB
Database Queries85

Timing points:

CheckpointStart (sec)Duration (sec)Memory at start (KB)Memory used (KB)
Script start 0.00000.0066 589.1563152.6406
Module start 'layout' 0.00660.0025 741.796939.4766
Module start 'content' 0.00910.6913 781.2734738.8828
Module end 'content' 0.70040.0016 1,520.156328.1250
Script end 0.7020  1,548.2813 

Time accumulators:

 Accumulator Duration (sec) Duration (%) Count Average (sec)
Ini load
Load cache0.00330.4693160.0002
Check MTime0.00150.2079160.0001
Mysql Total
Database connection0.00130.182410.0013
Mysqli_queries0.619288.1985850.0073
Looping result0.00080.1096830.0000
Template Total0.671095.620.3355
Template load0.00250.349720.0012
Template processing0.668695.233020.3343
Template load and register function0.00010.021010.0001
states
state_id_array0.00040.057610.0004
state_identifier_array0.00090.126020.0004
Override
Cache load0.00220.3068600.0000
Sytem overhead
Fetch class attribute can translate value0.00060.082940.0001
Fetch class attribute name0.00100.1444130.0001
XML
Image XML parsing0.00300.422540.0007
class_abstraction
Instantiating content class attribute0.00000.0034150.0000
General
dbfile0.00330.4632220.0001
String conversion0.00000.001040.0000
Note: percentages do not add up to 100% because some accumulators overlap

Templates used to render the page:

UsageRequested templateTemplateTemplate loadedEditOverride
1node/view/full.tplfull/forum_topic.tplextension/sevenx/design/simple/override/templates/full/forum_topic.tplEdit templateOverride template
11content/datatype/view/ezxmltext.tpl<No override>extension/community_design/design/suncana/templates/content/datatype/view/ezxmltext.tplEdit templateOverride template
20content/datatype/view/ezxmltags/paragraph.tpl<No override>extension/ezwebin/design/ezwebin/templates/content/datatype/view/ezxmltags/paragraph.tplEdit templateOverride template
5content/datatype/view/ezxmltags/line.tpl<No override>design/standard/templates/content/datatype/view/ezxmltags/line.tplEdit templateOverride template
4content/datatype/view/ezimage.tpl<No override>extension/sevenx/design/simple/templates/content/datatype/view/ezimage.tplEdit templateOverride template
3content/datatype/view/ezxmltags/literal.tpl<No override>extension/community/design/standard/templates/content/datatype/view/ezxmltags/literal.tplEdit templateOverride template
3content/datatype/view/ezxmltags/quote.tpldatatype/ezxmltext/quote.tplextension/ezwebin/design/ezwebin/override/templates/datatype/ezxmltext/quote.tplEdit templateOverride template
1print_pagelayout.tpl<No override>extension/community/design/community/templates/print_pagelayout.tplEdit templateOverride template
 Number of times templates used: 48
 Number of unique templates used: 8

Time used to render debug report: 0.0002 secs