Because of my poor english , i will cut the crap.
Maybe someone have been post the same disscussion before , i post here for advices to hack drupal core too.....
RESULT ::reduce from 77-28 selects . BY using this method on taxonomy_get_term reduce my site form 77 selects to 43 ,and copy the same method to taxonomy_get_term , will then reduce from 43 to 28 , if you’re using pathauto and path and a lot terms , it’s a lot more useful.
1.after using cacherouter and trun on drupal normal cache , my site still have at least 77 mysql-selects per page when login.
2.using devel , i found a lot of drupal_lookup_path and taxonomy_get_term
3.learning ideas from http://drupal.org/node/192448 and http://www.lullabot.com/articles/a_beginners_guide_to_caching_data , i use “PHP static “ plus “drupal cache_set” to reduce the selects.
GO to drupal_lookup_path firstly
4.locate include/path.inc , found :
$alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src = '%s' AND language IN('%s', '') ORDER BY language DESC, pid DESC", $path, $path_language));change it to
////////hack start
$usecache=TRUE;
if($usecache){
static $cachedb;
if(!isset($cachedb)){
//if(!function_exists("cache_get")){print "i am not exist";}else{drupal_set_message("i am exist");}
$ca=cache_get("d_lookuppath");
if($ca){
$cachedb=$ca->data;
if(!is_array($cachedb)){
$cachedb=array();
}
else{
}
}else{
$cachedb=array();
}
}
$key=$path."-".$path_language;
if(isset($cachedb[$key])){
$alias=$cachedb[$key];
}else{
$alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src = '%s' AND language IN('%s', '') ORDER BY language DESC, pid DESC", $path, $path_language));
$cachedb[$key]=$alias;
cache_set("d_lookuppath",$cachedb);
}
}else{
$alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src = '%s' AND language IN('%s', '') ORDER BY language DESC, pid DESC", $path, $path_language));
}
///////hack endyou should read the Chinese version here if you want to know clearer , but i think i have declear the function clear.
Go to taxonomy_get_term
using the same function/method and change just a few codes which mainly drupal-cache_set's cid:
1.locate modules/taxonomy/taxonomy.module , function taxonomy_get_term
2.change it to ：
function taxonomy_get_term($tid, $reset = FALSE) {
static $terms = array();
if ($reset) {
$terms = array();
}
///////////////////
//hack start
//hack method copy from path.inc or view http://www.trackself.com/archives/432.html
$usecache=TRUE;
if($usecache){
static $cachedb;
if(!isset($cachedb)){
$ca=cache_get("tax_get_term");
if($ca){
$cachedb=$ca->data;
if(!is_array($cachedb)){
$cachedb=array();
}
}else{
$cachedb=array();
}
}
$key=$tid;
if(isset($cachedb[$key])){
$terms[$tid]=$cachedb[$key];
}else{
$terms[$tid] = db_fetch_object(db_query('SELECT * FROM {term_data} WHERE tid = %d', $tid));
$cachedb[$key]=$terms[$tid];
//actually , is better to set this line to another place . like index.php is a litter better
cache_set("tax_get_term",$cachedb);
}
}else{
//origin code
if (!isset($terms[$tid])) {
$terms[$tid] = db_fetch_object(db_query('SELECT * FROM {term_data} WHERE tid = %d', $tid));
}
}
//hack end
///////////////////
/*
//origin code
if (!isset($terms[$tid])) {
$terms[$tid] = db_fetch_object(db_query('SELECT * FROM {term_data} WHERE tid = %d', $tid));
}
*/
return $terms[$tid];
}conclution
by using this method , i think we could reduce any repeat mysql-select in large site.
By seems that a lot of people didn't like the idea of core hack , so do you have any ideas to avoid hacking the core?looking for advices.
Hope helps.
my enviroments:bluehost+apache+php+mysql+xcache+cacherouter+(views+cck+panels+pathauto)
Comments
Path Cache Module
http://drupal.org/project/pathcache
http://drupalcode.org/viewvc/drupal/contributions/modules/pathcache/path...
<?php
function drupal_lookup_path($action, $path = '', $path_language = '') {
global $language;
// $map is an array with language keys, holding arrays of Drupal paths to alias relations
static $map = array(), $no_src = array(), $count;
$path_language = $path_language ? $path_language : $language->language;
// PATCH GOES HERE
// Use $count to avoid looking up paths in subsequent calls if there simply are no aliases
if (!isset($count)) {
$count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
}
if ($action == 'wipe') {
$map = array();
$no_src = array();
$count = NULL;
}
elseif ($count > 0 && $path != '') {
if ($action == 'alias') {
if (isset($map[$path_language][$path])) {
return $map[$path_language][$path];
}
// Get the most fitting result falling back with alias without language
$alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src = '%s' AND language IN('%s', '') ORDER BY language DESC, pid DESC", $path, $path_language));
$map[$path_language][$path] = $alias;
return $alias;
}
// Check $no_src for this $path in case we've already determined that there
// isn't a path that has this alias
elseif ($action == 'source' && !isset($no_src[$path_language][$path])) {
// Look for the value $path within the cached $map
$src = '';
if (!isset($map[$path_language]) || !($src = array_search($path, $map[$path_language]))) {
// Get the most fitting result falling back with alias without language
if ($src = db_result(db_query("SELECT src FROM {url_alias} WHERE dst = '%s' AND language IN('%s', '') ORDER BY language DESC, pid DESC", $path, $path_language))) {
$map[$path_language][$src] = $path;
}
else {
// We can't record anything into $map because we do not have a valid
// index and there is no need because we have not learned anything
// about any Drupal path. Thus cache to $no_src.
$no_src[$path_language][$path] = TRUE;
}
}
return $src;
}
}
return FALSE;
}
?>
Caching terms is a smart idea since they do not change very often. What happens when they do change though? The cache needs to be flushed.
cache_clear_all("term-cache-c
cache_clear_all("term-cache-cid","cache");//simply create a php and using this api will do , and if would like to do it daily , using cron or other method.
http://drupal.org/project/pathcache
about this module , i installed before i tried to hack core , but found it not work for me , maybe i had misconfig something.
and the code you provide above is just using "PHP-STATIC" method to avoid dumplicated path on a single view , so there will be still a lot of selects repeated after the user's second click.
Thanks for nice hack, I used
Thanks for nice hack, I used only the pathcache one and it reduced my sql queries from 81 to 50.
You can use a nodeapi hook to flush path cache on node change(which means the path was changed):
<?php/**
* clear cache for pathcache hack on noode change
*/
function MODULENAME_nodeapi(&$node, $op) {
switch ($op) {
case 'insert':
case 'delete':
case 'update':
cache_clear_all("d_lookuppath", "cache");
break;
}
}
?>
What about the query cache?
You may have succeeded in reducing the number of queries but how much time have you saved? Use the devel module to see the timings. It is likely that these queries would be cached in the MySQL query cache (RAM) and will be coming back in the blink of an eye - milliseconds. Adding a cache in the application layer is just stopping the cache in the DB layer from working and I would guess that you would be saving very little time at all by caching this way. As mikeytown2 says, url_alias and term_data tables probably will be changing relatively infrequently and so the MySQL cache should remain intact and will not need to be rebuilt all that often. MySQL does you the favour of invalidating the cache if the tables do change so you don't need to worry about that.
If you do try to handle it in the application layer, the end result is code which is much longer and difficult to understand - premature optimization. I would try to steer clear of this complexity if possible.
During work on my uriverse.com site, which has 10M nodes and url_aliases, like you I did run into problems with all of the lookups done on node and url_alias. This was probably one of the biggest challenges I faced. These two tables are hit a lot when retrieving lists for views etc. In my case it could be hit 50+ times a page load and so was important for it to work fast. I didn't have the luxury of enough RAM for a big MySQL query cache and so just had to ensure that the url_alias and node ids indexes were loaded into the key buffer. For me it was preferable to let the DB do the caching/indexing rather than handing that over to data structures in PHP. The end result was that the DB still got 50+ queries but it could handle it OK. ie. even without query buffer, an index in RAM is satisfactory even on big sites. Add Boost/Varnish/etc into the mix and you end up with a site that is fast for anonymous and acceptable for logged in users.
In your case the query cache could probably handle the requests for you assuming that you have enough RAM to store common queries. Take a look at the timings and see what you think.
Managing Director
Morpht
thank you murrayw . Adding a
thank you murrayw .
using devel , this method reduce my site's query time from 50ms to under 20ms in different pages views, i do not have a huge site to test.
yes , i did know that a mysql update query will clear mysql cache thus will slow down drupal's next db query.
But the cache is just updated when there is a neverseen select.
if you still have doubt , you counld add a preg_match method to filter just some kind of mysql-select could update the cache thus will be better
My tried is :
1.clear all cache
2.A user John click the index.php , then there will be cache updated , then John click a node/1 page , then the cache is updateed because node/1 drupal_lookup_path is different to index.php , then John click node/2 , this will not have a sql update because the cache contain the result , and the other user Kim click index.php and this will not update the mysql too
<
blockquote>
If you do try to handle it in the application layer, the end result is code which is much longer and difficult to understand - premature optimization. I would try to steer clear of this complexity if possible.
<
blockquote>
Actuall if there is possible i would like to install mysql-proxy instead of hacking to code , bluehost's mysql is just too slow to bear , so i have to reduce the mysql query.
10M nodes^^, i did not have 10 million nodes sites but i did have a sites with 500,000 nodes , it was a FTP-SEARCH sites on my University (using views hacking to optimize selects) . People search a lot on this site and i have to reduce the queries per page to 10-20 per page , if it still have 50+ select per select , this server will down soon. ( of course i used xcahce and so on cache,and i check the cpu , all comsuming thing is mysql).
anoter websites of mine is about bittorrent using on my University too , i have to using the same method to hack core to less db query because there is too many people visit the tracker on the same time(althought there is just one mysql-select-query per page view)
actually , i think that if i just using "CCK-modules" plus some cache modules (and i just use views to create sql for me only on the above two sites) , the slow things will always be mysql.
bluehost limit RAM to 128M , seems , and my server's ip on school could not be visited outside of the school , if it can , i will surely leave it to a mysql server or mysql-proxy not just mysql
1.if all alias table record
1.if all alias table record is small and will not change often ,then this method is great
2.if the alias table is huge , then using "preg_match($query)" to split the queries to use different cache (cache_set("cid"))would helps
3.on the other hand , seems that taxonomy will not always be updated , so it's a safe hack , i think.
4.if there are always some same mysql-selects-query, but always some different ones , use preg_match($query) to determin which of them should use dbcache and the other using just "PHP-STATIC"
actually , i don't think there is a good way to cure all perfomance issues , hack using the same method but different function, do you think so?
Interesting
Hi haojiang,
Interesting results. I wouldn't have guessed that the difference would be as large as 50ms to 20ms. If MySQL is your bottleneck then that is a significant difference. A lot of what I was saying was predicated on url_alias not changing that much. This was the case with my large site. Also, it has a lot less traffic than yours so I could handle slower pages the first time through.
One thing I mentioned before but not sure that you picked up on. If the DB is the bottleneck then maybe you could take strain off it by using page caching (Boost, Varnish, standard page cache). If your users are anonymous then there are big gains here - it could be the win you are looking for. Using one of these methods would mean that PHP/MySQL/CacheRouter wouldn't be hit at all. Boost has been my friend here for sure - especially as it dumps cached content to disk which is great if you have 500K nodes to worry about caching.
Just as an aside, as this isn't my area of expertise and I'm not really sure what the new DB stuff in D7 contains... in my past examinations of ORMs on other systems I have noticed that they only do a SELECT for a row (object) if it isn't stored in the ORM's cache. If Drupal followed such a pattern then it could optimize lookups across all tables, not just the hacks we are talking about. Of course people would need to do all their C(R)UD through an object interface to make sure cache could be invalidated for a single object/row. Then we all would get the 50ms to 20ms speedup and no worries about tables which updated frequently such as node and url_alias :) How beautiful would that be?
Managing Director
Morpht
"Boost has been my friend
"Boost has been my friend here for sure - especially as it dumps cached content to disk which is great if you have 500K nodes to worry about caching."
THX murrayw , i loved boost too , i am learning/digging its code right now this moment .
I am looking a method how to use boost module scriptly although it already support a great administraor form page.
支持支持！很高兴看到国内drupal高手现身！
支持支持！很高兴看到国内drupal高手现身！