Working with Data and Cache Storage in Drupal

One common belief that I encounter among developers is that if they make Drupal their career, they are bound to get stuck with one technology and only the LAMP stack. I felt that way too, when I started out as a Drupal developer seven years ago. But the varied projects I worked on soon debunked that myth.

Yes, it is definitely true that the LAMP stack is not the only thing that you get to work on with when you are on Drupal. You need to be more than a simple PHP developer to explore the wild, wild ways of the Drupal world. A small glimpse into the database side of this amazing CMS platform will reveal this.

Drupal loves MySQL
MySQL is ideal as a primary database for Drupal, because the Drupal core is built on the assumption that it will be deployed on a finely tuned LAMP stack. Even the caching layer in Drupal stores its data in MySQL tables. Drupal’s database abstraction layer makes it easy for the underlying DB to be replaced with any other, provided that there is a driver implemented for it and a PHP extension that connects the PHP layer to the database engine. Drupal’s database abstraction layer uses the PDO’s (PHP data object’s) database API and hence derives much of its semantics and syntax from it. This layer also puts in a lot of security checks, best practices and optimisations. Hence, it must never be bypassed in any customisation efforts. Drupal’s database implementation also supports a master-slave configuration for higher performance.

A lot of hosting providers that support CMSs like Drupal go in for variants of MySQL, like Percona Server and MariaDB, as well as the enterprise version of Oracle MySQL to have additional features or performance benefits. Acquia Cloud uses the Percona Server as its database of choice.

So what if we need something other than MySQL? The options that Drupal provides as a part of its core are PostgreSQL and SQLite. Now why would we use these, if MySQL works really well, out-of-the-box? Anyway, here are some options.

PostgreSQL
PostgreSQL has a very good and complete ACID implementation, and various algorithms built in for complex SQL operations like JOINS, UNIONS and such. So, if your Drupal applications are heavy in terms of data structure, you could use PostgreSQL. If this database is part of a bigger system, where data is shared amongst applications like a BI tool, it would be good to consolidate to a database that supports all your applications, including Drupal. Support for PostgreSQL is part of Drupal 7 Core.

SQLite
This lightweight database extends its properties to your Drupal application. SQLite is a serverless single file database engine. It is a great option for small sites with low to medium traffic. The overheads of maintaining a MySQL or PostgreSQL server have been removed, and the infrastructure required to run the show is also less. Drupal Core provides SQLite as an option, as a part of its core features. This database is ideal for creating microsites, advertisements, internal information sites and such, in educational institutions, NGOs, etc.

MongoDB
The Drupal community has contributed an integration of Drupal’s database layer with MongoDB for special use cases, such as hiving off the cache tables to a flat file structure like what MongoDB has. MongoDB is also used for field storage, session, watchdog, queue and block, to enable faster reads and take advantage of the flat file architecture and features that it has to offer.

Proprietary databases
Oracle and Microsoft SQL Server are not part of Drupal Core, but developers have built connectors to support them as databases for Drupal. These are proprietary systems and not usually chosen by open source architects, but the beauty of open source projects like Drupal is that they allow integration with any system and easily fit into a larger multi-technology stack architecture.
To learn more about the Oracle DB, log on to https://www.drupal.org/project/oracle, and for Microsoft SQL Server, you can go to http://drupal.org/project/sqlsrv

Caching mechanisms
The Drupal community has contributed many modules that split the Drupal caching mechanism from MySQL and other database options to object caching mechanisms like Memcache and Redis. So, all the cache tables like cache, cache_page, cache_block and more are hived off to the external caching mechanism.

Memcache
Memcache is a high-performance, distributed memory object caching system. It is an in-memory key-value store for small chunks of arbitrary data (strings, objects, etc) from results of database calls, API calls, or page rendering. PECL (the PHP Extension Community Library) provides for a Memcache or Memcached library that can be used with the Drupal module ‘Memcache API and Integration’ (https://www.drupal.org/project/memcache).

The module supports multiple memcached servers connected to the Drupal site, thereby distributing the load of the cache bins.
An example of the configuration that would go into Drupal’s settings.php would be as below:

memcache_servers’ => array(
server1:port => cluster1,
server2:port => cluster2,
serverN:port => clusterN,
‘unix:///path/to/socket’ => clusterS
)

‘memcache_bins’ => array(
bin1 => cluster1,
bin2 => cluster2,
binN => clusterN,
binS => clusterS
)

As a part of Acquia Cloud Enterprise, in case of high complexity and memory requirements, a dedicated server is assigned to host just the memcached services, while the Apache2 Web server and the MySQL-Percona database server are dedicated to other instances.

Redis
Redis, too, is an advanced key-value cache and data store. It is often referred to as a data structure server. Drupal’s module to support Redis as the cache store has been gaining popularity (https://www.drupal.org/project/redis).
The Redis Drupal module uses the Predis PHP library or PHPRedis PHP extension to provide connectivity to a Redis instance.

Entity cache
The Entity cache module is another very popular add-on to a Drupal site, which puts Drupal’s core entities into cache via the cache API. This module, in combination with Memcache or Redis caching mechanisms, is potent. Core entities like Nodes, Taxonomy, Comments, Users and Files are offloaded from database queries and loaded into this Entity cache. One good thing is that, as of Drupal 8, this feature is being added to Drupal Core.

APC or Alternative PHP Cache
The APC module supports the Alternative PHP Cache (APC), which is a free and open source opcode cache for optimising PHP intermediate code. Along with the opcode cache, it also provides for a user cache for storing application data. APC can be used for cache data that does not change often and grows too big, to avoid fragmentation. The use of APC with other caching mechanisms can be combined by assigning which cache bin uses a particular cache cluster.

Boost
Boost is a file cache module that targets static page caching, especially in case of a Drupal site being hosted on a shared hosting provider. With respect to dedicated hosting, Varnish is a better option for page caching. Boost will cache and compress your HTML page, css, js, ajax and xml requests. It comes with an advanced cache expiration and regeneration logic that will surely speed up your site’s performance for anonymous traffic.

Authcache
But what about authenticated users? The Authcache module comes to the rescue by offering page caching for both anonymous as well as authenticated users. It also integrates with other caching mechanisms like Memcache, Redis, MongoDB or any others that provide an implementation of DrupalCacheInterface.