"SELECT dst FROM drupal_url_alias WHERE src =..." is called a *lot*

yes, one of the reasons I prematurely upgraded to 4.7 was because what i had read about the old caching behavior.

But "it's faster than before" doesn't seem like a very compelling answer... I'm talking about what it's doing now. Bug's filed against CVS -- it's not necessarily going to be solved in 4.7 branch.

Let me explain motivation first: I got a big spike of hits on my site, and I read comments about it which led me to believe that my site had succumbed to the "slashdot effect" and basically gone down during the spike. I was sleeping, and wasn't able to instrument while the spike happened, so I'm looking for possible culprits.

Let me be clear that I'm not sure how much of a performance problem this was under load. However, I was surprised that one page view was generating such a large amount of queries. My pages were previously running about 5-10 queries per page (about 3 or 4 of them in what would be equivalent to blocks) before I switched to Drupal.

I was hoping someone would have a better solution, but here are my thoughts: Most of the links on a page are the same links from page to page. They appear in blocks, which are relatively static; they appear in the header or footer, which are often completely static. Assume we can model a drupal site like this:

* For each page view, print N fixed links from a "fixed pool" and M links at random.
* The "fixed pool" changes slowly (say once every 10 minutes) as different blocks change content. A similar assumption is already encoded in the way blocks are cached for anonymous users.

A further note: on my site, N is a sizeable fraction of the links on most pages, so optimizing for the N links would cut down the number of queries dramatically. Hopefully back into the 10s instead of 100s.

Given that model, it would make sense to split the link pool into two conceptual parts: The part that is in the fixed pool, and the part that is not.

Question: how do you quickly determine which links are in the fixed pool and which ones aren't.

Proposal: use drupal's existing cache mechanism to cache the "fixed pool", by storing a cache of size N+epsilon (epsilon some small number to help increase cache hits) under a single key in the cache table.

Initially, at random, the cache is filled with N random entries.

Every page view loads the cache and tries to use that to resolve url aliases before resorting to a db query.

Every 100 page loads (or one pageload every 10 minutes or something) will then, blind to any global state, use some cheap operation to update the entries in the cache based on the links that were on that page load. With the right update algorithm, the cache would grow to contain all the links that were on all pages, as well as most of the links that were common to many pages.

http://drupal.org/files/issues/cache-url-alias.patch

Comments from 47832 (which has been marked as a duplicate of this bug):

On a site with a large menu there can be many url_alias queries. Now, at 1ms or less per query, individually they aren't a big deal, but even modest sites quickly get 30-40 of them on every page hit, and I can envision scenarios where you might see hundreds of them.

My solution is to create a cache that keeps the ones that get hit a lot, without burdening the system for the ones that get hit less often.

This patch is, at the moment, incomplete, but I wanted to get some community evaluation before I put the niceties on it.

It needs:

* Documentation of the functions that were added
* a form field in system_settings to control the cache size

This patch caches the url_alias map, and additionally keeps the last time a given alias was hit. It writes the cache on page exit; when it does so, if the number of items in the cache exceeds the threshold (defaulting to 500 items), it prunes items least recently hit first. This means that items that appear commonly on menus, that are hit often, will remain; items that are hit only on some nodes will quickly get pruned off.

On my test site this has the effect of shaving about 70ms off of every page view -- this is very significant on light pages, and of minor but still important significance on heavy pages.

I'd love to see a few people try this out and see what they thing, and ensure that my implementation is correct.
Updates
#1 submitted by merlinofchaos on February 4, 2006 - 00:48

Oh yes, one addition:

The system needs to update the cache (or invalidate it) when path aliases are modified; that also is missing from this patch.
#2 submitted by Dries on February 4, 2006 - 10:03

Code style nit: we try not to glue words together (lasthit) and try not to abbreviate words.

Did you benchmark this?

That's huge savings per webpage hit on a busy site. I imagine the savings would be even better on a site where the database was busy.

I was poking through the performance of theme('page') today, and thought these visualizations of my page.tpl.php run might be interesting. The main block is the call to _phptemplate_default. I couldn't go any higher than that, since it was called using reflection rather than directly, and the combination of tools I'm using doesn't seem to be able to follow that indirect call. Note that half the time spent is in l(). This is on a completely unused database.

2 more levels down the call stack. The call above db_query is drupal_lookup_path. One thing that surprised me (I won't bother uploading the image) is that about 1/4 of the time used up by db_query calls were taken up by variable_get()

I tested my main page on my home machine using 'ab -c 10 -n 100' and got a mean time per request speed-up of a bit more than 12%

With the patch

Concurrency Level: 10
Time taken for tests: 53.511949 seconds
Complete requests: 100
Failed requests: 94
(Connect: 0, Length: 94, Exceptions: 0)
Write errors: 0
Total transferred: 1725501 bytes
HTML transferred: 1698701 bytes
Requests per second: 1.87 [#/sec] (mean)
Time per request: 5351.195 [ms] (mean)
Time per request: 535.119 [ms] (mean, across all concurrent requests)
Transfer rate: 31.49 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.3 0 2
Processing: 1500 5326 1185.6 5320 7892
Waiting: 529 1929 937.4 1756 4828
Total: 1500 5326 1185.6 5320 7892

Percentage of the requests served within a certain time (ms)
50% 5320
66% 5920
75% 6152
80% 6369
90% 6862
95% 7196
98% 7315
99% 7892
100% 7892 (longest request)

Without the patch

Concurrency Level: 10
Time taken for tests: 60.978320 seconds
Complete requests: 100
Failed requests: 92
(Connect: 0, Length: 92, Exceptions: 0)
Write errors: 0
Total transferred: 1726379 bytes
HTML transferred: 1699579 bytes
Requests per second: 1.64 [#/sec] (mean)
Time per request: 6097.832 [ms] (mean)
Time per request: 609.783 [ms] (mean, across all concurrent requests)
Transfer rate: 27.63 [Kbytes/sec] received

Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 8 29.1 0 138
Processing: 2223 6063 1457.4 5930 10936
Waiting: 742 2525 879.9 2566 4203
Total: 2223 6072 1463.3 5930 10936

Percentage of the requests served within a certain time (ms)
50% 5930
66% 6415
75% 6827
80% 7125
90% 8163
95% 8794
98% 9793
99% 10936
100% 10936 (longest request)

That seems pretty normal -- the whole curve shifts faster, but less than one standard deviation faster.

Like people from different countries being different heights, but perhaps less than one standard deviation different.