OK, I am not 100% sure if the cause is path or pathauto module, but here it is.

My site has lots of aliases (>20,000) and has node_access & organic groups enabled.

I noticed that periodically the whole site locks up and dies. Investigation shows that MySQL executes a path-related query that goes on forever. This query locks a bunch of tables which means other requests to the site lock, bringing it to its knees. If the original (locking) query completes all these requests hit MySQL at the same time killing it totally.

I did a screengrab of the offending long query (attached) and looks like it counts aliases, taking so long because OG is enabled and puts all sort of things into WHERE clause. Then I grepped to see where url counts are used and found them only in path / pathauto modules.

Now the questions:

1) I see the counts are used to check if there is 0, 1 or more aliases. If SELECT COUNT(*) query is replaced by SELECT * with further count of results, would that help? By how much?

2) Could it be possible to address the problem by looking at what OG / node access does and changing it? I cannot even get the full query saved so do not know even where to get started?

3) Could you think of any other causes of this problem?

Please HELP! This disaster is happening on a production system!

CommentFileSizeAuthor
url_alias COUNT death.gif21.39 KBdkruglyak

Comments

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Category: bug » support
Priority: Critical » Normal

1) replacing select count(1) with select * would probably slow everything down further

2) try devel.module to see which queries are used

3) 20,000 aliases, a slow system, lots of different stuff could cause this, but yes path aliases (not pathauto per se) and og are two modules that are known to be potential slow-points for sites.

I suggest you create a test site based on this live one and try tweaking things to get better performance. One big boost would be finding the source of the large number of aliases and then reducing them. Or finding aliases which refer to nodes/users which may not exist (it's possible for certain kinds of cruft to slip by).

BTW - your post doesn't really allow me to help you. It's a "support request" not a bug and it's only critical for you because of the situation that you are in. Nothing here says "this is a critical flaw of pathauto" so I downgraded it to normal. This is about all the support that I'm willing/able to give on this issue since it's not clearly pathauto related.

dkruglyak’s picture

dkruglyak commented

Questions:

1) Are there any stats on SELECT COUNT vs SELECT * effectiveness?

2) I tried devel module, but it does not capture the queries fully. The SQL is too long. My screenshot shows them as well.

3) Sure, but this just means this needs to be fixed for Drupal to be a serious player on large-scale sites. We cannot reduce the number of aliases since we have lots of nodes and alias each one for search optimization.

The answer cannot be reducing the number of aliases. We need to make paths/OG really work with lots of them.

If you are not willing to help, where should I ask ???

fgm’s picture

fgm
Primary language French
Location Paris, France
commented

Well, if it is only user to theck whether there is only 0, 1 or more results (but can you explain how you identified this ?) it might be interesting to replace the first query by something like
SELECT pid FROM {url_alias} a INNER JOIN node [....] and placing it in a db_query($query, 0, 2), then counting the 0 to 2 results obtained to ensure whether there are 0, 1 or more results.

I feel this could be more efficient than a count query, but this needs to be measured.

dkruglyak’s picture

dkruglyak commented

I am so glad to be not the only one who thinks this is important issue...

RE: how I discovered the use of counts. I simply grepped the whole code tree for 'url_alias', then looked through the code to see where they are used in conjunction with COUNT query. Only two modules matched: path and pathauto. Then I traced the use of results in these files to see that they are only checked to be 0, 1 or >1.

The problem with fully reproducing this test is the SQL code is so long, devel module does not record the full string (truncates in the middle of where clause)! Same when I try to retrieve it from MySQL Admin process window (see screengrab)...

How could we set up a test case to do benchmarking as you are suggesting?

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Status: Active » Postponed (maintainer needs more info)

howto benchmark drupal: http://drupal.org/node/79237

and if devel won't capture the queries then you can temporariliy edit your database.mysql.inc file to log all queries.

I'm also updating the status. Since there's no specfic request for change and since there aren't any steps to consistently repeat the problem this issue "needs more info".

dkruglyak’s picture

dkruglyak commented
Title: url_alias counts bring death and destruction » url_alias COUNT and DISTINCT queries bring death and destruction
Category: support » feature
Status: Postponed (maintainer needs more info) » Active

OK, I found out how to get the queries. When a long-running query is killed apache log captures it "as is". Here are my findings:

1) COUNT and DISTINCT queries are equal offenders
2) All long queries include a LIKE clause that was being cut off by devel module
3) Offending queries repeat multiple times. Perhaps caused by search engine spiders

Specific queries are here (note how similar are COUNT and DISTINCT flavors):

SELECT COUNT(*)

FROM url_alias a 
	INNER JOIN node n ON a.src = CONCAT('node/', n.nid)  
	INNER JOIN node_access na ON na.nid = n.nid  

WHERE 	(na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR 
	(na.gid = 0 AND na.realm = 'og_public') OR 
	(na.gid = 0 AND na.realm = 'og_all'))) AND n.status = 1 AND a.dst LIKE 'blog/johndoe/2006/%'

Second one (different LIMITS show up):

SELECT DISTINCT(n.nid)

FROM url_alias a 
	INNER JOIN node n ON a.src = CONCAT('node/', n.nid)   
	INNER JOIN node_access na ON na.nid = n.nid  

WHERE 	(na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR 
	(na.gid = 0 AND na.realm = 'og_public') OR 
	(na.gid = 0 AND na.realm = 'og_all'))) AND n.status = 1 AND a.dst LIKE 'blog/johndoe/2006/%' 

ORDER BY n.sticky DESC, n.created DESC LIMIT 0, 10

Any optimization suggestions? Perhaps queries like that can be cached? Maybe like Result cache module does it?

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Title: url_alias COUNT and DISTINCT queries bring death and destruction » url_alias COUNT and DISTINCT (from index alias and listing pages) are very slow
Status: Active » Closed (won't fix)

I see now. It appears that you are using the index aliases and/or the listing page functionality.

I decided to remove those from 5.x partially for the exact reason that you found: they are slow!

http://drupal.org/node/91116

While I haven't actually committed that patch yet, the issue provides strategies for migration to new (more efficient and more reliable) solutions.

Also, it appears that you are using a node access module (compare your query to the original query on line 263 of pathauto_node.inc). Node access is another thing that will slow your site.

For the the DISTINCT query you list I believe comes from the call to theme_pager or one of those similar areas.

So, a move to views or a custom listing page would serve you well instead of using pathauto's listing page.

dkruglyak’s picture

dkruglyak commented
Component: Code » Documentation
Status: Closed (won't fix) » Active

Yes, I just tracked it down too and fixed by disabling their generation and wiping them from url_alias table...

DELETE FROM url_alias WHERE src LIKE 'pathauto%'

I still recommend a VERY STRONG documentation warning in 4.7, perhaps on the settings form.

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Status: Active » Fixed

Good point!

Note added to the project page: http://drupal.org/project/pathauto

If that seems insufficient please propose alternate text. I don't plan to add a warning to the settings page so please don't propose that.

dkruglyak’s picture

dkruglyak commented
Status: Fixed » Closed (fixed)

That's good enough. Thanks.

greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Status: Closed (fixed) » Fixed

dkruglyak - thanks.

In general issues are marked as "fixed" and then left there. There is an automated job that closes them after a while (2 weeks?). This is done to let them stay visible in the issue queue for a little while so people don't think they've idsappeared.

Anonymous’s picture

(not verified) commented
Status: Fixed » Closed (fixed)
greggles’s picture

greggles
he/him
Primary language English
Location Denver, Colorado, USA
commented
Title: url_alias COUNT and DISTINCT (from index alias and listing pages) are very slow » url_alias COUNT and DISTINCT (from index alias and listing pages) bring death and destruction

In hindsight, I like this title a little more.

dkruglyak’s picture

dkruglyak commented

LOL