Severe performance issue when installing xmlsitemap module [#245185]

I had a site that used gsitemap, and has 35,000+ users, and 127,000+ URL aliases.

When installing xmlsitemap, I specifically unchecked the xmlsitemap_user module.

However, after clicking submit on the admin/build/modules page, the page never came back.

Checking the server, I found this query running for 263 seconds, locking the users table and causing all other queries to be locked.

| Query | 263 | Copying to tmp table | INSERT INTO xmlsitemap_user (uid, last_changed, pid) SELECT u.uid, u.created, ua.pid FROM users |

The problem query is in xmlsitemap_user.install, which is:

INSERT INTO xmlsitemap_user (uid, last_changed, pid)
  SELECT u.uid, u.created, ua.pid FROM users u
  LEFT JOIN xmlsitemap_user xu ON xu.uid = u.uid
  LEFT JOIN url_alias ua ON ua.src = CONCAT('user/', u.uid);

The site them exhausted all the MySQL connections (370), and was hung.

So, I stopped Apache and aborted MySQL, and restarted it, and all was well after truncating the table above.

So there are two problems:

- When installing, it enables modules that it is specifically told not to install.

- The performance overhead of the above query is unacceptable. It uses LEFT JOIN on string fields which causes full table scans and temporary tables. A better approach is to batch the rebuild in cron somehow so the number of rows involved is not as large. Or warn about this feature for large sites.

Comments

Comment #1

RobRoy commented 23 April 2008 at 04:12

FYI, the users part is in the replace function, so if you had showusers set to TRUE in gsitemap.

...
if (variable_get('gsitemap_showusers', FALSE)) {
  $modules[] = 'xmlsitemap_user';
  variable_del('gsitemap_showusers');
}
drupal_install_modules($modules);
...

I'm doing a freshie so I don't have to worry, but we have nodes and users in the 100,000s so that would probably kill our servers too!

Comment #2

hass commented 1 May 2008 at 23:35

The main issue are the 35,000+ users, and 127,000+ URLs... this will cause 162,000 INSERTs what will really overwhelm nearly every server and you will run into a PHP timeout. In D5 you can only try to run cron as often as it took to fill the tables... after the tables are filled the module should work normal.

We could solve this in D6 with batch api.