[drupal-devel] [task] Performance issues with path aliases

Sun May 8 18:08:21 UTC 2005

Issue status update for http://drupal.org/node/22035

 Project:      Drupal
 Version:      cvs
 Component:    base system
 Category:     tasks
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  mikeryan
 Updated by:   puregin
 Status:       patch

I believe that it's felt that adding path aliases would improve the
Drupal documentation.  This may well add 400+ paths to the URL aliases
table on Drupal.org

puregin

Previous comments:
------------------------------------------------------------------------

May 4, 2005 - 17:10 : mikeryan

See Investigate use of conf_url_rewrite [1] for context...

The current core support for translating incoming path aliases to the
internal Drupal path (drupal_get_path_alias) and substituting aliases
when generating links to internal paths (drupal_get_normal_path) does
not scale well with many aliases. The ease with which pathauto enables
site administrators to generate large numbers of aliases exposes this
issue, but it is inherent in the core implementation, because
drupal_get_path_map reads in the entire url_alias table at bootstrap
time. I'd like to discuss ideas on how to improve the performance...

Most obviously, why not simply query the url_alias table as needed
instead of loading the whole table? In the incoming case, only a single
simple SELECT is necessary, which will always be more efficient than
reading the table. In the outgoing case, there might be some slight
performance advantage to caching the table with a small number of
aliases, but the disadvantage can become huge as the alias table grows.

One note - I've noticed that the src column in url_alias is not
indexed, I think adding an index should significantly help the
performance in the outgoing case if we were to do individual SELECTs.

Any other thoughts?
[1] http://drupal.org/node/21938

------------------------------------------------------------------------

May 4, 2005 - 18:02 : mikeryan

Attachment: http://drupal.org/files/issues/bootstrap.inc_2.patch (1.06 KB)

What the hell, I went ahead and gave it a shot... On my home system,
where I'm testing out 4.6.0, page loads have been taking several
seconds, which I attributed to the fact that it's an old computer and
I'm multi-tasking like crazy. I implemented my own suggestion, and now
pages load in about one second, it makes an incredible difference
(FWIW, my url_alias table has over 4000 rows).

Patches attached, go give it a try...

------------------------------------------------------------------------

May 4, 2005 - 18:02 : mikeryan

Attachment: http://drupal.org/files/issues/common.inc_11.patch (723 bytes)

One attachment per note, that's tedious...

------------------------------------------------------------------------

May 4, 2005 - 18:03 : mikeryan

Attachment: http://drupal.org/files/issues/path.module_0.patch (1.34 KB)

------------------------------------------------------------------------

May 4, 2005 - 18:03 : mikeryan

Attachment: http://drupal.org/files/issues/database.mysql_4.patch (553 bytes)

------------------------------------------------------------------------

May 4, 2005 - 18:04 : mikeryan

Attachment: http://drupal.org/files/issues/database.pgsql_2.patch (502 bytes)

------------------------------------------------------------------------

May 4, 2005 - 18:07 : mikeryan

Note: I just noticed that the database.*sql patches showed an extra diff
(not mine) to the location field in locales_source. I did my diffs
against a freshly-updated DRUPAL-4-6, and had made the edits against a
release from a few days ago... Those locales_source changes should
probably be removed from my patches before applying.

------------------------------------------------------------------------

May 7, 2005 - 15:13 : mikeryan

I upgraded Fenway Views [2] to Drupal 4.6.0 today, incorporating these
patches. Performance is very noticeably improved.
[2] http://fenway-views.com/

------------------------------------------------------------------------

May 8, 2005 - 05:31 : killes at www.drop.org

Attachment: http://drupal.org/files/issues/pathalias.patch (4.98 KB)

I've merged the patch into one. Much more convenient. I also changed it
to cvs as only there new features will be added.
Upgrade path needs to be added.

Mike, can you run some more tests? We are especially interested in
"hard" data ie numbers with possibly error bars. it would be
interesting to know how this patch affects sites that have only a few
path aliases vs those with a lot of them. Also, how pages with a lot of
node links (tracker) are affected.

It might be worthwhile to add static caching in drupal_get_normal_path
if it gets calle dmore than once for a link on one page view.

------------------------------------------------------------------------

May 8, 2005 - 09:21 : mikeryan

Thanks for merging the patches, I wasn't aware you could patch multiple
files at once. If this is accepted, of course, adding the index on src
should be incorporated into updates.inc.

In terms of hard data, I've never profiled PHP code - what tool(s) do
you use? I couldn't find anything referenced in the PHP manual...

re: pages with lots of links - on Fenway Views the big test is the
calendar [3], and the performance improvement seems even more dramatic
here. I don't know why - the url_alias table is read once per page, so
I would expect as you do that preloading the table would tend to look
better when you have a hundred internal links on the page. Maybe we're
both underestimating exactly how fast a simple MySQL query on an
indexed key can be (I'm using MySQL 4.0.20, BTW)...
[3] http://fenway-views.com/calendar

------------------------------------------------------------------------

May 8, 2005 - 09:26 : killes at www.drop.org

As we speak, Mathias is doing some tests. The tool of choice is usually
apache bench. If you have a lot of url aliases, the generated array
will be huge. Part of the improvement could be due to the fact that you
need less memory now.

------------------------------------------------------------------------

May 8, 2005 - 09:28 : mikeryan

Great, thanks!

------------------------------------------------------------------------

May 8, 2005 - 09:34 : Dries

On drupal.org it takes 800 ms or more to generate a page.  Of these 800
ms, only 4 ms is spent building the path alias map (incl. the SQL query
time which takes about 1 ms).  That is, building the map takes 0.5% of
the total time which is neglible.  In our url_alias table are only 321
aliases though.  How many aliases do you have?

What is more, when we build drupal.org's main page, we query this map
215 times.  I believe your patch would introduce 215 SQL queries ...

I'm afraid that if we'd apply your patch, we'd pay a serious
performance penalty (unless we have many more aliases).

Can you provide some numbers/measurements?

------------------------------------------------------------------------

May 8, 2005 - 09:37 : Dries

Note: my measurements did not take into account the time spent searching
the map.  On the main page, we search this array 215 times.