Jørn Wildt
Dealing with Zikula and other interesting stuff
Increase performance by ADODB cache
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
On sites with Pagesetter is easy to increase performance. I suggest turn on adodb cache.
In PS change execute (or limit) in pnuserapi.php file like this:
Change line:
$result = $dbconn->execute($sql);
to:
if ($pnconfig['adodbcache'] = '1') { $ADODB_CACHE_DIR = $pnconfig['adodbcachetemp'];
$result = $dbconn->cacheexecute($pnconfig['adodbcachetime'],$sql); } else
$result = $dbconn->execute($sql);
In config.php after:
$pnconfig['temp'] = '/tmp';
add lines:
$pnconfig['adodbcache'] = '1'; // turn on and of cache
$pnconfig['adodbcachetemp'] = '/tmp/adodb'; // place to write cache files
$pnconfig['adodbcachetime'] = '60'; // cache time in seconds
Remember to add below line at begin of each function with this hack:
global $pnconfig;
That method allow easy turn off adodb cache by $pnconfig['adodbcache'] = '0';
Above hack is only example, but works very well. Must only change all execute or limit commands in pnuserapi.php file. On my page increases pagerendertime three times.
edited by: arturm, Mar 05, 2007 - 04:03 PM
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
I've tryed your solution but when I display my site, all publication has disapear :-? and I've got these error
Code
modules/pagesetter/pnuserapi.php(954): "getPub" failed: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''SELECT pubTable.pg_id,\n\n
pubTable.pg_pid,\n\n ' at line 1 while executing: SELECT pubTable.pg_id,
pubTable.pg_pid, pubTable.pg_approvalState, pubTable.pg_online,
pubTable.pg_revision, pubTable.pg_topic, pn_topics.pn_topicname as
core_topic_name, pn_topics.pn_topictext as core_topic_text,
pn_topics.pn_topicimage as core_topic_image, pubTable.pg_showInMenu,
pubTable.pg_showInList, pubTable.pg_author, pubTable.pg_creator,
UNIX_TIMESTAMP(pubTable.pg_created) as pg_created,
pn_pagesetter_pubheader.pg_hitcount, UNIX_TIMESTAMP(pubTable.pg_lastUpdatedDate)
as pg_lastUpdatedDate, UNIX_TIMESTAMP(pubTable.pg_publishDate) as
pg_publishDate, UNIX_TIMESTAMP(pubTable.pg_expireDate) as pg_expireDate,
pubTable.pg_language , pg_field276, pg_field279, pg_field283, pg_field288,
pg_field280, pg_field277, pg_field285, pg_field281, pg_field286, pg_field357,
pg_field340, pg_field341, pg_field278 FROM pn_pagesetter_pubdata30 as pubTable
LEFT JOIN pn_topics ON pubTable.pg_topic = pn_topics.pn_topicid LEFT JOIN
pn_pagesetter_pubheader ON pubTable.pg_pid = pn_pagesetter_pubheader.pg_pid AND
pg_tid = 30 WHERE pubTable.pg_pid = 86 AND (pg_publishDate <= NOW() ||
pg_publishDate IS NULL)AND (NOW() < pg_expireDate || pg_expireDate IS NULL) AND
pg_online AND NOT pg_indepot
A question :
Quote
Change line:
$result = $dbconn->execute($sql);
to:
if ($pnconfig['adodbcache'] = '1') { $ADODB_CACHE_DIR = $pnconfig['adodbcachetemp'];
$result = $dbconn->cacheexecute($pnconfig['adodbcachetime'],$sql); } else
$result = $dbconn->execute($sql);
Did we have to change all lines where it's wrote
Code
$result = $dbconn->execute($sql);
or only for the first line where I find it ?
edited by: Starguezer, Nov 06, 2007 - 02:20 PM
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
Of corse ADODB cache is different kind of cache. It uses special kind of files, which stores answers from the MySql.
In config.php I added:
Code
$pnconfig['adodbcache'] = '1'; // 1 - turn on cache
$pnconfig['adodbcachetemp'] = '/tmp/pnTemp2/adodb'; // place to store cache
$pnconfig['adodbcachetime'] = '600'; // cache time in seconds
And in ENTIRE pnuserapi.php file I searched and replaced lines next to "$dbconn->execute" and "$dbconn->selectLimit". In example in pagesetter_userapi_getPubList function I changed:
Code
// Select one extra to see if more is available
{ if ($pnconfig['adodbcache'] = '1') { $ADODB_CACHE_DIR = $pnconfig['adodbcachetemp'];
$result = $dbconn->cacheselectLimit($pnconfig['adodbcachetime'],$sql, $noOfItems+1, $offsetItems); } else { $result = $dbconn->selectLimit($sql, $noOfItems+1, $offsetItems); } }
else
{ if ($pnconfig['adodbcache'] = '1') { $ADODB_CACHE_DIR = $pnconfig['adodbcachetemp'];
$result = $dbconn->cacheexecute($pnconfig['adodbcachetime'],$sql); } else { $result = $dbconn->execute($sql); } }
if ($dbconn->errorNo() != 0)
return pagesetterErrorApi(__FILE__, __LINE__, '"getPubList" failed: '
. $dbconn->errorMsg() . " while executing: $sql");
// Get workflow information in order to show state titles
Another example: in pagesetter_userapi_getPubSet function - simple modification:
Code
if ($pnconfig['adodbcache'] = '1') { $ADODB_CACHE_DIR = $pnconfig['adodbcachetemp'];
$result = $dbconn->cacheexecute($pnconfig['adodbcachetime'],$sql); } else { $result = $dbconn->execute($sql); }
And so on. I find 15 "= $dbconn->" strings in pnuserapi.php file and all of them should be modified if you want to cache all queries. Is one disadvantage of caching queries: changes on page will be delayed.
And remember to add:
Code
global $pnconfig;
line at begin of every function which uses ADODB cache. I don't know why we have to add this declaration, but it's work ;-) .
After turning on ADODB cache performance is increase from 3 to 10 times faster.
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
I've done it but I can't see real performance :s
- I've add line in config.php
- Modified pnuserapi.php file (I've made 7 modifications)
But when I go there : http://www.seriesparadise.com/ => Menu (orange bar) => S?ries => Fiches s?ries
It always take a lot of time to load :(
(~7 or 8 secondes !)
I've copy/past my pnuserapi.php file here :
http://www.seriesparadise.com/modules/pnuserapi.txt
If you can see mistakes...
Thank you very much for help me because since months my site is to slow...
edited by: Starguezer, Nov 06, 2007 - 03:31 PM
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
Did you modified all 15 places where is code with "$dbconn->execute" and "$dbconn->selectLimit" ?
And remember, first time ADODB have to send query to MySQL because cache was not created. When page will be open next time ADODB uses cache.
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
Ok, where to send file?
Edit:
1. In function pagesetter_userapi_incrementHitCount($args) I suggest remove cache, because it's a UPDATE query. It may be danger. Remove cache in all queries where write to database.
2. What you type in config.php?
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
You can send on mail deleted against spam ;)
1. I've correct it, thanks ;)
2.Code
$pnconfig['system'] = '0';
$pnconfig['prefix'] = 'pn';
$pnconfig['encoded'] = '0';
$pnconfig['pconnect'] = '0';
$pnconfig['temp'] = 'pnTemp';
$pnconfig['adodbcache'] = '1'; // turn on and of cache
$pnconfig['adodbcachetemp'] = 'pnTemp/adodb'; // place to write cache files
$pnconfig['adodbcachetime'] = '60'; // cache time in seconds
edited by: Starguezer, Nov 06, 2007 - 10:40 PM
-
- rank:
- 12
- registered:
- March 2009
- Status:
- offline
- last visit:
- Posts:
- 0
I think my problem is a little bit uncommun...
If you agree to help me, please add me on MSN, it will be easiest to speak ;)
mail deleted against spam ;)
Thanks for your help
edited by: Starguezer, Nov 06, 2007 - 10:40 PM