Drupal 7: Async Pretfetch Database Cache Module

David G - DrupalWhile working on numerous projects it is often that I will hit bottlenecks here and there with Drupal and my site configuration. For example when using the Migrate module I had erroreously hit a DEADLOCK cache table issue in my local server. Other times I’ve wished some piece of the site loaded faster for authenticated users. Or, oddly sometimes I’ll get a semaphore Locked issue when moving a site. So, I’m always looking for new alternatives to hopefully make Drupal faster and circumvent these types of issues. A newer module in this space is the APDQC module, or “Asyncronous Prefetch Database Cache Module”.

I should clarify, Drupal works great — and I feel oftentimes the issues I encounter are not it’s fault persay but a misconfiguration of my system. I’ve not fully profiled my system in an idle state, and then attempted to profile it while Drupal is under heavy load. That said there are numerous times users complain about issues with Drupal that occur readily and are difficult to track down. Some examples include:

So, this module seeks to amend these issues through alternate configurations of underlying drupal settings, rarely even seen in the Drupal Admin UI. In the above posts you’ll see (oftentimes) the user MikeyTown2 reply with some configuration advice. While I don’t know him personally — I’ve seen him alot on StackExchange and was suprised to see he’s authoring the APDQC module!

The APDQC module currently does the following (from the project page):

  • Eliminates deadlocks & metadata locks for all cache tables.
  • Faster page load times for logged in and anonymous users due to cache prefetching.
  • Outputs query information to the devel query log. Will output prefetch info as well.
    Better handing of the minimum cache lifetime; does smarter garbage collection of the cache bins. Purges caches based on individual records’ timestamps instead of just using the expire column, the created column is used as well.
  • Changes the collation of cache tables to utf8_bin. Using the utf8_bin collation is faster and more accurate when matching cache ids since no unicode normalization is done to cache query conditions. D8 backport of (#2352207: Database cache backend does not treat cid as case sensitive).
  • Changes the semaphore table to use the MEMORY engine if using MySQL 5.5 or lower. This will speed up writes to the lock table for older versions of MySQL.
  • Writes to the watchdog table from the dblog module are done using an async query.
  • Session initialization is done with 1 query instead of 2. Session writes use async queries.

As with many caching configuration overrides to Drupal, this set of changes requires that you alter Drupal’s configuration using settings.php for your drupal site by appending the following to the end of the file:

$databases['default']['default']['init_commands']['isolation'] = "SET SESSION tx_isolation='READ-COMMITTED'";
$databases['default']['default']['init_commands']['lock_wait_timeout'] = "SET SESSION innodb_lock_wait_timeout = 20";
$databases['default']['default']['init_commands']['wait_timeout'] = "SET SESSION wait_timeout = 600";
$conf['cache_backends'][] = 'sites/all/modules/apdqc/apdqc.cache.inc';
$conf['cache_default_class'] = 'APDQCache';
$conf['lock_inc'] = 'sites/all/modules/apdqc/apdqc.lock.inc';
$conf['session_inc'] = 'sites/all/modules/apdqc/apdqc.session.inc';

Note that this module is designed to only work with MySQL at this time. Additionally, APDQC requires the MySQLD native driver for PHP. On my Ubuntu development server that is as simple as issueing sudo apt-get install php5-mysqlnd and restarting Apache.

This module has some configuration options on a APDQC configuration page, but of additional note are the settings that have no UI configuration and must be set additionally via settings.php:

// The following settings are not configurable from the admin
// UI and must be set in settings.php.
// In general they are settings that should not be changed.
// The current defaults are shown.

    // If TRUE run DELETE instead of TRUNCATE on all cache bins.
    $conf['cache_no_truncate'] = FALSE;

    // If FALSE do not prefetch the cache_bootstrap cache bin.
    $conf['cache_bootstrap_prefetch'] = TRUE;

    // If TRUE call apdqc_cache_clear_alter() & apdqc_cache_clear() on cc.
    $conf['apdqc_call_hook_on_clear'] = FALSE;

    // If TRUE use _apdqc_dblog_watchdog instead of dblog_watchdog.
    $conf['apdqc_dblog_watchdog'] = TRUE;

I can’t guarantee this module will streamline your Drupal install 1000%, but having used alternate cache backends in the past with Drupal I’m always excited about new options in this realm. And knowing/reading many of MikeyTown2’s numerous performance related threads on the net and viewing some of his presentation talks — he’s the guy to try and pull this off well!

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , | Posted under Drupal | RSS 2.0

Author Spotlight

David Gurba

I am a web programmer currently employed at UCSB. I have been developing web applications professionally for 8+ years now. For the last 5 years I’ve been actively developing websites primarily in PHP using Drupal. I have experience using LAMP and developing data driven websites for clients in aviation, higher education and e-commerce. If you’d like to contact me I can be reached at david.gurba@arvixe.com

Leave a Reply

Your email address will not be published. Required fields are marked *