Dave Smylie
Dave Smylie

Reputation: 2693

Rails QueryCache enabled, but not working

We have a Rails 4.2 app running on an Oracle backend, with memcached/Dalli as the cache store.

QueryCache does not seem to be working for repeated SQL within the same request. Per the documentation:

Query caching is a Rails feature that caches the result set returned by each query. If Rails encounters the same query again for that request, it will use the cached result set as opposed to running the query against the database again.

Configuration in application/production.rb sets the cache store to Dalli, and toggles query_cache to true within production:

application.rb: config.cache_store  = :dalli_store, 'localhost:11211', { namespace: 'BASE', down_retry_delay: 120 }

environments/production.rb:  config.action_controller.perform_caching = true

Running memcached in verbose mode, I can see all the fetches and sets and misses from our explicit low level caching (ie Rails.cache.set), but no evidence of any query caching. (Though I'm not 100% sure if the query cache should reach memcached or if it's just held in memory for the duration of the request). Given I can see other caching activity within memcached, I'm fairly sure the connection/setup between memcached and the rails is OK.

I believe it is not being cached for two reasons:

  1. No selects with the production log (in debug mode) show the CACHE prefix - just identical SELECT's with ever-so-slightly differing XX.XXms timestamps; and,
  2. Enabling database auditing with Oracle shows dozens of identical SELECTS per request (ie identical SELECT query conditions with the same case and same bind parameters)

One suggestion I have seen is to look at the Middleware to confirm that QueryCache is present, which it seems to be:

dave@test:~/code/app1$ rake middleware
use #<ActiveSupport::Cache::Strategy::LocalCache::Middleware>
use ActiveRecord::QueryCache

Another suggestion was to check (and set) ActiveRecord::Base.connection.query_cache_enabled explicitely. I have put that in a before_filter for the method I'm currently trying to optimise, and it reports itself as being enabled, but still no caching:

  def enable_qcache
     ActiveRecord::Base.connection.enable_query_cache!
     logger.error "IS CACHE ENABLED:  #{ ActiveRecord::Base.connection.query_cache_enabled}"
  end

   production.log: IS CACHE ENABLED:  true

Here is an example of the identical selects within the same request from the log:

  Policy Load (12.2ms)  SELECT * FROM (SELECT  "POLICY".* FROM "POLICY" WHERE "POLICY"."POLICY_ID" = :a1  ORDER BY policy_original_number DESC, policy_id DESC ) WHERE ROWNUM <= 1  [["policy_id", 459723071]]
  Policy Load (11.5ms)  SELECT * FROM (SELECT  "POLICY".* FROM "POLICY" WHERE "POLICY"."POLICY_ID" = :a1  ORDER BY policy_original_number DESC, policy_id DESC ) WHERE ROWNUM <= 1  [["policy_id", 459723071]]
  Policy Load (9.0ms)   SELECT * FROM (SELECT  "POLICY".* FROM "POLICY" WHERE "POLICY"."POLICY_ID" = :a1  ORDER BY policy_original_number DESC, policy_id DESC ) WHERE ROWNUM <= 1  [["policy_id", 459723071]]

Upvotes: 2

Views: 1261

Answers (0)

Related Questions