4

I noticed that some of the queries to retrieve data from cache tables is really slow (> 100ms). For example:

SELECT cid, data, created, expire, serialized FROM cache_views WHERE cid IN ('views_data:en')

SELECT cid, data, created, expire, serialized FROM cache_field WHERE cid IN ('field_info_fields')

SELECT cid, data, created, expire, serialized FROM cache_bootstrap WHERE cid IN ('system_list')

SELECT cid, data, created, expire, serialized FROM cache WHERE cid IN ('entity_info:en')

Most of those tables only have 10-100 records total. Why would they be so slow?

1
  • using a Redis cache fixed the problem for me. Memcached would probably another good solution – uwe Aug 26 '12 at 7:18
4

I don't know this for a fact but I think it's because the serialised objects contained in the data column are pretty large.

I just checked a production site of mine and the data column for views_data:en contains a serialised string that is 572824 characters long. I kid you not! Imagine loading that on each page request (fortunately that doesn't happen in my case).

The main cache table on the same website is currently at 1.4MB, with just 16 records in it.

On another, less-than-a-week-old dev site, the cache_form table is at 6.3MB, with just 142 records.

I know 1.4MB and 6.3MB aren't large amounts of data as such, but if you take into account the fact that it has to be processed and passed from MySQL to PHP I think that's where you'll find the bottleneck.

4
  • good point, my views_data:en views definition is 550kb as well. Apparently views is designed to load the complete base table and field definitions with every single request, see drupal.org/node/1421844. But they are trying to change it to only load the ones required for the current view. – uwe May 26 '12 at 4:53
  • Yikes that's very in-efficient. But I get what merlinofchaos is saying, it's a tricky one to weigh up. Sounds like the lazy-loading approach would be the best way forward – Clive May 26 '12 at 9:48
  • I wonder if compressing the json would improve it a bit? – uwe May 26 '12 at 15:59
  • It definitely might do, but then you've got to factor in the CPU time to perform the compression/decompression so it's difficult to say. It'd be a very interesting benchmark to take – Clive May 26 '12 at 16:19
1

I just wanted to point out also _field_info_collate_fields() memory usage, which affects memory usage and performance when you have hundreds or thousands of fields/field instances. With Entities everywhere, Profile2 module, Drupal Commerce, etc., many sites now have more than a few hundred field instances, and this quickly becomes a bottleneck. One of my sites has more than 6MB of serialized data in the field_info_fields row for cache_field.

Some sites with lots of fields/instances, views, etc. may encounter similar issues (from WSODes when clearing caches (from a PHP memory exhausted error) to slow page loads when caches are not yet built).

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.