1

I'm attempting to debug a site with intermittent slowness, and I've managed to track it down to a long series of file_usage queries which appear to be coming from a search_index rebuild.

SELECT base.fid AS fid, base.uid AS uid, base.filename AS filename, base.uri AS uri, base.filemime AS filemime, base.filesize AS filesize, base.status AS status, base.timestamp AS timestamp
FROM 
file_managed base
WHERE  (base.fid IN  ('50545'))

And

SELECT f.module AS module, f.type AS type, f.id AS id, f.count AS count
FROM 
file_usage f
WHERE  (fid = '22638') AND (count > '0')

These queries appear to be triggered on random page loads, and they continue until they are killed, or the page times out.

I thought it might have something to do with rebuilding the search index, so I've added

$conf['search_cron_limit'] = '0';

To settings.php to no avail. There are a whole lot of modules on this site, so I don't really know where to start.

  • try running cron a different way, from drush probably ? i think one of the tasks is checking for unused files. you should be able to switch it off, though – mojzis Mar 26 '13 at 1:22
  • @mojzis - So cron is only running from drush once every 4 hours. I thought it was being triggered from cron before, but I'm pretty sure it's only running at that interval. – Cthos Mar 26 '13 at 1:25
  • @mojzis - Plus, it doesn't run every cron run. – Cthos Mar 26 '13 at 1:26
  • @mojzis - But there is something in system_cron that is looking for files to prune....wow, that might be it. – Cthos Mar 26 '13 at 1:48
  • @mojzis - So I found it. You're totally right, it is being called from system_cron. If you want to turn that into an answer I'd be happy to accept. – Cthos Mar 26 '13 at 2:02
1

As it turns out, in the system_cron was ultimately causing the query storm. The particular bit of offending code was:

$result = db_query('SELECT fid FROM {file_managed} WHERE status <> :permanent AND timestamp < :timestamp', array(
    ':permanent' => FILE_STATUS_PERMANENT,
    ':timestamp' => REQUEST_TIME - DRUPAL_MAXIMUM_TEMP_FILE_AGE,
  ));
  foreach ($result as $row) {
    if ($file = file_load($row->fid)) {

As it turns out, there were about 200k files which matched that first query. It would then loop through them all and check to see if they had any references remaining. Since it had to do this check that may times, it was hammering the database and causing a decent amount of slowdowns. Additionally, it wouldn't find anything to prune since all of those files are still presently in use.

The solution in my case was to simply set the status field to 1 on all of the offending records, since they weren't going to change any time soon (these are product images whcih don't get updated often). The full consequences of that are unknown to me, but it seemed like a reasonable fix.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.