Configuring SQLite3 for best efficiency as a persistent cache

Question

I'm using SQLite3, via php's extension module, to create a persistent object cache for WordPress. It's intended for use on modestly sized sites that don't have access to redis or memcached. Why use this? To give smaller WordPress installations access to similar performance-enhancing tech as the bigger ones. Many php configurations at el-cheapo hosting services have the SQLite 3 extension in them. The tag line is "the persistent object cache for the rest of us". I'm here asking for a review, specifically, of the code that configures SQLite3. My repo is here.

php has what we can call the "fifty first dates" problem. It starts up anew to handle every page view and other incoming http/s request (unlike, for example, nodejs which handles requests from within a single long-lived process). So, without a persistent object cache php must load all sorts of data from the MariaDB or MySQL database before it can handle any page view. That's a ponderous process. Reading that same stuff from a cache is generally faster.

Characteristics of this SQLite3 application.

1. It contains no irreplaceable data; it's entirely populated from the database instance supporting WordPress.
2. It's a key-value store with an expiration time on some, but not all, objects.
3. The keys are case-sensitive ASCII TEXT strings. Most are shorter than 30 characters, but some run to 100 characters or so.
4. The values are BLOBs. They can be any size from a few bytes up. I've seen some lengths of 600KiB or so, but most are a few hundred bytes or less.
5. It's read-mostly.
6. It's write-infrequenty. But when writing, it's necessary to write immediately. Writes operations can't be batched up into multi-row transactions.
7. It's helpful to think of this cache as a way to memoize various operations.
8. It needs to work with SQLite versions 3.7 and up.

So, that's the context. The code works. Here's the initialization code, running in a php class derived from SQLite3. I hope somebody who's familiar with making SQLite3 efficient can point out mistakes.

This initialization code runs for every page view. Remember, "fifty first dates". We can't make assumptions about the state of the system.

/* set some initial pragma stuff */
$this->exec( 'PRAGMA synchronous = OFF' );
$this->exec( "PRAGMA encoding = 'UTF-8'" );
$this->exec( 'PRAGMA case_sensitive_like = true' );
$this->exec( 'PRAGMA journal_mode = MEMORY' );

/* the table */
$this->exec( 'BEGIN;' );
/* does our table exist?  */
$q = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND tbl_name = 'cache';";
$r = $this->querySingle( $q );
if ( 0 === $r ) {
  /* later versions of SQLite3 have clustered primary keys, "WITHOUT ROWID" */
  if ( version_compare( $this->sqlite_get_version(), '3.8.2' ) < 0 ) {
    $t = "CREATE TABLE IF NOT EXISTS cache (
            name TEXT NOT NULL COLLATE BINARY,
            value BLOB,
            expires INT
          );
         CREATE UNIQUE INDEX IF NOT EXISTS name ON cache (name);
         CREATE INDEX IF NOT EXISTS expires ON $tbl (expires);";
  } else {
    $t = "CREATE TABLE IF NOT EXISTS cache (
           name TEXT NOT NULL PRIMARY KEY COLLATE BINARY,
           value BLOB,
           expires INT
         ) WITHOUT ROWID;
         CREATE INDEX IF NOT EXISTS expires ON cache (expires);";
  }
  $this->exec( $t );
}
$this->exec( 'COMMIT;' );

My specific questions:

1. Are my PRAGMA directives suitable for this application?
2. In particular, does the journal_mode = MEMORY pragma make sense?
3. Did I miss any other useful PRAGMAs?
4. Are the table definitions suitable?
5. Can anything be done here to make this any faster or more time / space efficient?
6. Is there anything dumb that I should fix?

Answer 1

I'm afraid there has to be more context. What's lacking is some code showing how that function is being used.

But I am wondering if you've done any measuring of performance before and after your proposed solution. Can you safely assume that your code leads to a noticeable boost in performance and if so can it be quantified? Can you guarantee that it doesn't add extra overhead instead? I tend to be skeptical.

That is exactly the point of your module: performance, either in startup time or fetch time. Your assumptions need to be proven. Imagine you've been hired as a software consultant or you're trying to sell your code to someone. You will be asked to justify your work and it's good to show benchmarks.

Just because the SQLite database file is on the local file system does not mean that data retrieval will be faster than on a MySQL instance on the network, because the two systems are very different and there are so many factors that come into play.

Since you've mentioned MySQL, it has an in-memory storage engine with certain limitations, but still has some use cases. Why not store your cache data in such a table? (Although the default InnoDB engine should already provide good performance).

By the way the whole SQLite DB can be in-memory, if you are fine with volatility. Alternatively, the table can also be created in the TEMP schema.

There is little meaningful code to review right now, but why check the existence of table cache in sqlite_master if you're doing this: CREATE TABLE IF NOT EXISTS.

The last line is a COMMIT but outside the if loop, so there may be nothing to commit.

In particular, does the journal_mode = MEMORY pragma make sense?

What the doc tells us:

The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt.

So probably you don't care very much if you're storing cached results that can easily be reproduced.

The other PRAGMA is somewhat similar:

OFF (0) With synchronous OFF (0), SQLite continues without syncing as soon as it has handed data off to the operating system. If the application running SQLite crashes, the data will be safe, but the database might become corrupted if the operating system crashes or the computer loses power before that data has been written to the disk surface. On the other hand, commits can be orders of magnitude faster with synchronous OFF.

The decision very much depends on how reliably data writes should be, and whether you can afford the risk of corruption. Here you have chosen performance over data integrity.

However, your code could crash if the DB file has been corrupted: it will try to open a file that is present but unusable. If you don't handle the scenario, then it would be necessary to delete the file manually to unblock your application. As per Murphy's law, anticipate the things that could (and will) go wrong.

Can anything be done here to make this any faster or more time / space efficient?

I'm afraid there is no one size fits all answer here. Start by analyzing current performance. It depends a lot on the volume of data too. Also consider the other options already available, don't reinvent the wheel unless the available solutions are not satisfactory for your use case.

Answer 2

Not wanting to be suborn, but I'm not going to answer your questions here. There will be others who can do this.

That being said, I notice some very short variable names: $q, $r, $t and $tbl, which, I assume, stand for $query, $result, $table and $table?

Better names would have been: $checkCacheQuery, $cacheExists and $createCacheQuery, or something similar. Looking at your repo, I think you forgot to replace $tbl? It represents something like $cacheTableName.

It's been my long-held opinion that variable names should make sense and relate to what the content of the variable represent. You've failed to pick a good name, whenever a code-reader has to search through the code to find out what the content of variable could possibly represent.

There can be several reasons why people use one-letter variables. They might think fewer coding characters looks clean, or they might think it speeds up code execution. Both are fallacies. Code should be written to be easily understood, and PHP pre-processes code once into OPCodes after which identifiers are no longer used.

Link: Basics of Naming Conventions for PHP Developers

Note: The code in your question doesn't represent code that actually works, it's a small snippet out of a bigger piece of code. Here we're not afraid of larger chucks of code.