By kwinto on

I have 247 queries for front page and almost half of them (102) queries from function drupal_lookup_path. These queries consume about 30% of time of all database time. I believe this is very much and it's very inefficient for performance. In my case url_alias table contains only 5 records and I for now I really need all them.
Could be it better for first call of the function to cache ALL records of the table? I believe that it would much more efficient for resources consumption and performance.

⋅ Categories: Drupal 4.7.x

Comments

BioALIEN’s picture

BioALIEN commented

I've used aliasing on 250+ nodes as a test and the number of queries per page went crazy. The culprit is drupal_lookup_path(). I believe this is under discussion so a solution will be engineered for Drupal6 and hopefully back-ported down the road.

Here's a citation to point you in the right direction:
http://drupal.org/node/88022#comment-161124

------------------------------
BioALIEN
Buy/Sell/Trade with other webmasters: WebMasterTrader.com

dewolfe001’s picture

dewolfe001 commented

I found the same problems on my 5.1. install when I ran DEVEL and saw that the principal drag was drupal_lookup_path. I recoded my copy so that it leaned on the session object instead of these frequent trips to the database:

function drupal_lookup_path($action, $path = '') {
  // $map keys are Drupal paths and the values are the corresponding aliases
  static $map = array(), $no_src = array();
  static $count;

  // 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();
  }
  elseif ($count > 0 && $path != '') {
    if ($action == 'alias') {
      if (isset($map[$path])) {
        return $map[$path];
      }
 	  // 7/5/2007 - Mike - hacking core to implant session storage
	  elseif (isset($_SESSION['map'][$path])) {
	    $map[$path] = $_SESSION['map'][$path];
		return $map[$path];
	  }
      $alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src = '%s'", $path));
      $map[$path] = $alias;
	  $_SESSION['map'][$path] = $map[$path];
      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])) {
      // Look for the value $path within the cached $map
      if (!$src = array_search($path, $map)) {
        if ($src = db_result(db_query("SELECT src FROM {url_alias} WHERE dst = '%s'", $path))) {
          $map[$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] = TRUE;
        }
      }
      return $src;
    }
  }

  return FALSE;
}

noteworthy was my addition of :

  // 7/5/2007 - Mike - hacking core to implant session storage
	  elseif (isset($_SESSION['map'][$path])) {
	    $map[$path] = $_SESSION['map'][$path];
		return $map[$path];
	  }

and 

	  $_SESSION['map'][$path] = $map[$path];
gaborh’s picture

gaborh commented

If you happen to use this core hack from Mike in Drupal 5.x, I would suggest to additionally insert the following line:

if ($action == 'wipe') {
$map = array();
$no_src = array();
unset($_SESSION['map']); // hacking core to clear session storage
}

Otherwise drupal_clear_path_cache() will not work correctly, and it can cause problems.

sinasalek’s picture

sinasalek commented

Thanks guys, it's perfect. 50 less queries!!

sina.salek.ws
Feel freedom with open source softwares

sina.salek.ws, Software Manager & Lead developer
Feel freedom with open source softwares

sinmao’s picture

sinmao commented

Hi Gaborh,

I only 5 url_aliases, but 200+ drupal_clear_path() function calls. So what does this do exactly? Can I tell it not to lookup url_aliases unless it's those 5? Thx.

wuf31’s picture

wuf31 commented

Hi all, Just a thought, shouldn't we clear all user $_SESSION vars ?
Ain't it by this line, we just clear one user session variable ??

EtradingGallery

-=- Jhon -=-

jeb-1’s picture

jeb-1 commented

i'm glad i'm not the only one that was horrified at how many db calls are needed for simple pages.

any ideas if the menu.module is also going to be reworked? There are a lot of db calls just to create a silly little navigation bar.

BioALIEN’s picture

BioALIEN commented

As a matter of fact, menu.module has already been engineered and ready to go for Drupal 6 ;)
------------------------------
BioALIEN
iScene Interactive :: iScene.eu

dharamgollapudi’s picture

dharamgollapudi commented

subscribing....

2c’s picture

2c commented

The advanced cache reduces multiple db lookup queries into one query:

http://drupal.org/project/advcache

Check out the path_cache.patch

treksler’s picture

treksler commented

Hey

- does anyone know what is the status of Drupal urlalias caching?
- has any of this made it into D5 D6 D7?
- where do we go from here? is there an active issue/ patch to test?
- i.e. which one of these possible solutions should i be testing?

References
http://drupal.org/node/102311
http://drupal.org/node/106559
http://drupal.org/node/100301

sillygwailo’s picture

sillygwailo
Location Toronto, ON
commented

#106559: drupal_lookup_path() optimization - skip looking up certain paths in drupal_lookup_path() has made it into D7. A backport of that issue for D6 is available in Pressflow.

#100301: Page level path alias caching was marked as duplicate of the above issue.

(Username formerly my full name, Richard Eriksson.)

drubage’s picture

drubage commented

Hey all,

Just made these mods and holy crap the speed seems to have significantly improved. Way less DB calls from drupal_lookup_path(). I changed my drupal_lookup_path() in path.inc to the one above posted by dewolfe001. Then I updated my drupal_clear_path_cache() to look like this:

function drupal_clear_path_cache() {
  if ($action == 'wipe') {
	$map = array();
	$no_src = array();
	unset($_SESSION['map']); // hacking core to clear session storage
  }
  drupal_lookup_path('wipe');
}

Is that the correct modification of drupal_clear_path_cache()? Is there really nothing else to do? Is it that easy to reduce DB calls this much so quickly and painlessly? Why wouldn't this be in Drupal 5 core releases, makes no sense to me it's so simple it seems like something must be wrong.

-Drew

vacilando’s picture

vacilando commented

Any news on this? Is this the correct solution that reduces the flood of drupal_lookup_path calls?

—
Vacilando services | blog @vacilandois