Tons of file_usage queries

Question

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.

Answer 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.