[WIP]SQLite3 Cache System

[CircleDock]

@ Circledock, vacuum has been working in the recent code. On the previous code I forgot to call vacuum;. Also if you use PDO it is going to use SQLite 2, which is OK also.

On our HostGator VPS, we've got SQLite 3 as a PDO; HostGator also supplies SQLite 2 but not with a PDO driver. For SQLite 3, we have to use the PDO version. The version of PHP supplied is v5.2.17.

However one fairly serious problem has emerged which I think may be caused by sqlite_escape_string() not being supported on our server. That means I've had to recode the DELETEs and INSERTS in sicache_put() and execute those immediately using prepared statements. Oh joy! Although this means I can't build $sicache_trans it does have the (dubious) advantage of there being less to do in sicache_trans() - the transactions don't need to be processed and therefore the table is released faster.

My latest source is:

Code Select Expand


<?php
//---------------------------------------------------------------
// SMF Caching using SQLite3 database.
//
// Original code by: nend
// Modified by: CircleDock
//
// Changes:
//      * Modified code for PHP5 PDO implementation of SQLite
//        especially sichache_ini()
//      * Changed "key" to "ckey" to avoid keyword collision.
//      * Removed second parameter from query statement in
//        sichache_get() -- parameter was "true" but caused
//        multipe errors.
//      * Recoded around "sqlite_escape_string()" by using prepared
//        statements.
//---------------------------------------------------------------

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans, $cachedir, $sicache_time;
   $dbName = $cachedir . '/cache.sdb';
   $connectStr = 'sqlite:' . $dbName;

// Open the connection
//    ob_implicit_flush();
   $sicacheDB = new PDO($connectStr);  

// If new database, then create the two tables needed
// otherwise clean-up the database and reduce its disk size
if (filesize($dbName) == 0) {
$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (ckey TEXT UNIQUE PRIMARY KEY, value TEXT, ttl INTEGER); CREATE TABLE obcache (ckey TEXT UNIQUE PRIMARY KEY, value TEXT, ttl INTEGER);');
}
   else
       $sicacheDB->exec('BEGIN;VACUUM;COMMIT;');
       
if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}
register_shutdown_function('sicache_trans');
}

// Function "sqlite_secape_string()" may be missing in PDO,
// but should be in the underlying class.
if (!function_exists('sqlite_escape_string'))
{
   function sqlite_escape_string( $string ){
       return SQLite3::escapeString($string);
   }
}

function sicache_get($key) {

global $siOBcache, $sicache_time, $sicache_trans;

if(!isset($key))
return;
if(isset($siOBcache[$key]))
return $siOBcache[$key];
if(isset($siOBcache))
return;

global $sicacheDB;

if(!isset($sicacheDB)) {
sicache_ini();
}

   $stmt = $sicacheDB->prepare("SELECT * FROM cache WHERE ckey = :keyval AND ttl > :ttlval LIMIT 1;");
   $stmt->bindValue(':keyval', $key, PDO::PARAM_STR);
   $stmt->bindValue(':ttlval', $sicache_time, PDO::PARAM_INT);
   if ($stmt->execute() !== false) {
       $row = $stmt->fetch(PDO::FETCH_ASSOC);
       return $row['value'];
// $query = $sicacheDB->query('SELECT * FROM cache WHERE ckey = \''.sqlite_escape_string($key).'\' AND ttl > '.$sicache_time.' LIMIT 1');
// if ($query != false) {
//        $row = $query->fetch(PDO::FETCH_ASSOC);
// return $row['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $siOBcache, $sicache_trans, $sicache_time, $sicacheDB;

if(!isset($key))
return;

if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}

   $expire = $sicache_time + $ttl;
   if (isset($siOBcache))
   {
       if ($value === null)
       {
           $stmt = $sicacheDB->prepare("BEGIN; DELETE FROM obcache where ckey = :ckeyval; COMMIT;");
           $stmt->bindValue(':ckeyval', $key, PDO::PARAM_STR);
       }
       else
       {
           $stmt = $sicacheDB->prepare("BEGIN; INSERT INTO obcache (ckey, valuem ttl) VALUES (:ckeyval, :valueval, :ttlval);COMMIT;");
           $stmt->bindValue(':ckeyval', $key, PDO::PARAM_STR);
           $stmt->bindValue(':valueval', $value, PDO::PARAM_STR);
           $stmt->bindValue(':ttlval', $expire, PDO::PARAM_INT);
       }
       $stmt->execute();
       return;
   }
   
   if ($value === null)
   {
       $stmt = $sicacheDB->prepare("BEGIN; DELETE FROM cache WHERE ckey = :ckeyval; COMMIT;");
       $stmt->bindValue(':ckeyval', $key, PDO::PARAM_STR);
   }
   else
   {
       $stmt = $sicacheDB->prepare("BEGIN; INSERT INTO cache (ckey, value, ttl) VALUES (:ckeyval, :valueval, :ttlval);COMMIT;");
       $stmt->bindValue(':ckeyval', $key, PDO::PARAM_STR);
       $stmt->bindValue(':valueval', $value, PDO::PARAM_STR);
       $stmt->bindValue(':ttlval', $expire, PDO::PARAM_INT);
   }
   $stmt->execute();
/*
if (isset($siOBcache)) {
if ($value === null) {
$sicache_trans.= 'DELETE FROM obcache WHERE ckey = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO obcache (ckey, value, ttl) VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
return;
}

if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE ckey = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache (ckey, value, ttl) VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
*/    
}

function sicache_trans() {

global $siOBcache, $sicacheDB, $sicache_trans, $sicache_time;

   $stmt = $sicacheDB->prepare("SELECT * FROM obcache");
   if ($stmt->execute())
   {
       $siOBcache = array();
while ($data = $stmt->fetch(PDO::FETCH_ASSOC))
       {
if ($data['ttl'] > $sicache_time)
           {
$siOBcache[$data['ckey']] = $data['value'];
           }
       }
   }
/*    
$query = $sicacheDB->query('SELECT * FROM obcache');
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetch(PDO::FETCH_ASSOC)) { // was PDO:FETCH_ASSOC
if ($data['ttl'] > $sicache_time) {
$siOBcache[$data['ckey']] = $data['value'];
}
}
}
*/
   
//    try {
       if (ob_get_level() > 0)
          ob_end_flush();
//    }
   
//    catch (GlobalError $e) {
//        echo $e->getMessage();
//    }
   
if (!isset($sicacheDB)) {
sicache_ini();
}
   
   $stmt = $sicacheDB->prepare("BEGIN; DELETE FROM cache WHERE ttl < :ttlval; DELETE FROM obcache WHERE ttl < :ttlvala; COMMIT; PRAGMA incremental_vacuum;");
   $stmt->bindValue(':ttlval', $sicache_time, PDO::PARAM_INT);
   $stmt->bindValue(':ttlvala', $sicache_time, PDO::PARAM_INT);
   $stmt->execute();
// @$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.$sicache_time.';DELETE FROM obcache WHERE ttl < '.$sicache_time.';'.$sicache_trans.' COMMIT;PRAGMA incremental_vacuum;');
}
?>

I've had to use bindValue rather than bindParam since the latter requires values for the string parameters $key and $value.

As for performance gains, It is a little difficult to explain because mainly there looks to be none. The initial connection is actually slower than the file cache, which means that the first few cache calls will be slower, however anything after will be faster usually. If you benchmark it you will either get results faster, slower or about equal.

It is certainly slower for the initial connection but then appears to be fairly speedy. Even if performance gains are minimal, that is offset by there being no illegal file names used. I've noted that there were often around 1000 files in the cache folder at any one time - split fairly evenly by SMF and Bad Behavior. I've modified BB to use the SQLite cache and that part is definitely faster as BB needs to do fewer checks on the data it reads from the cache.

One suggestion though, I had to figure this out also. What you want in SQLite is to lock that database file the least amount time possible. Basically anything that writes to the DB file, "INSERT;DELETE;UPDATE;VACUUM" should be called in a transaction to minimize file locking.

Yep, I thought of that too!

There is one line that's causing a problem on the production server but not on my local server. In function sicache_trans()

Code Select Expand


ob_end_flush();

It's causing errors on that server and a read of the PHP docs suggests the call may actually not be necessary.

Edit:
ob_end_flush is necessary otherwise the MySQL database isn't updated with any changes. However:

Code Select Expand


if (ob_get_level() > 0)
    ob_end_flush();

seems to fix that problem.

Yeah, notice that error a while back and forgot to fix it. It is caused by the buffer being empty, doesn't happen to much but it does happen.

The code around this area is a workaround for a bug in PHP, if I don't flush the buffer and there is a function in the callback that uses the cache then SMF will crash with no output. I hate the bug and even hate my own workaround because it is a blind query. All SQLite database operations crash in the output buffer callback.

Interestingly the ob_get_level() test is not required on my local server - which is running PHP 5.4 - but is required on the production server (PHP 5.2.17).

[nend]

Here is my current code, a little different from the posted.

Code Select Expand

<?php

if (!defined('SMF'))
die('Hacking attempt...');

function sicache_ini() {

global $sicacheDB, $sicache_trans, $cachedir, $sicache_time;

$sicacheDB = new SQLite3($cachedir.'/cache');
if (filesize($cachedir.'/cache') == 0) {
@$sicacheDB->exec('CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);CREATE INDEX ttls ON cache(ttl);CREATE INDEX obttls ON obcache(ttl);');
}
if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}
register_shutdown_function('sicache_trans');
}

function sicache_get($key) {

global $siOBcache, $sicache_time, $sicache_trans;

if(!isset($key))
return;
if(isset($siOBcache[$key]))
return $siOBcache[$key];
if(isset($siOBcache))
return;

global $sicacheDB;

if(!isset($sicacheDB)) {
sicache_ini();
}

$query = @$sicacheDB->querySingle('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' AND ttl > '.$sicache_time.' LIMIT 1', true);
if ($query != false) {
return $query['value'];
}
}

function sicache_put($key, $value, $ttl = 120) {

global $siOBcache, $sicache_trans, $sicache_time;

if(!isset($key))
return;

if (!isset($sicache_trans)) {
$sicache_trans = '';
$sicache_time = time();
}

if (isset($siOBcache)) {
if ($value === null) {
$sicache_trans.= 'DELETE FROM obcache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO obcache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
return;
}

if ($value === null) {
$sicache_trans.= 'DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\';';
} else {
$sicache_trans.= 'INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.($sicache_time + $ttl).');';
}
}

function sicache_trans() {

global $siOBcache, $sicacheDB, $sicache_trans, $sicache_time;

$query = @$sicacheDB->query('SELECT * FROM obcache AND ttl > '.$sicache_time);
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetchArray()) {
$siOBcache[$data['key']] = $data['value'];
}
}

if (ob_get_level() > 0) {
ob_end_flush();
}

if (!isset($sicacheDB)) {
sicache_ini();
}

@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.$sicache_time.';DELETE FROM obcache WHERE ttl < '.$sicache_time.';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>

What I want to do maybe in the future, I say maybe because I am unsure it will have any performance boost, is to store all request that where made by a cache call in a array.

Say you make a cache call but the ttl is outdated, well that will be stored in the array as.

Code Select Expand


[keyblah] => array(
     "key" => "keyblah",
     "data" => null,
     "ttl" => null
);

I am thinking to do it this way just in case there is another request for the key to save time. Also when a INSERT is made for that exact key then the information is updated and it will be applied as a UPDATE.

Code Select Expand


[keyblah] => array(
     "key" => "keyblah",
     "data" => "Some data here",
     "ttl" => 60
);

This will change it from a DELETE key then INSERT key to only one UPDATE.

However I am unsure of the performance gain if any, mainly because on a commit I am thinking maybe SQLite sorts this stuff out on its own. 

However one fairly serious problem has emerged which I think may be caused by sqlite_escape_string() not being supported on our server. That means I've had to recode the DELETEs and INSERTS in sicache_put() and execute those immediately using prepared statements. Oh joy! Although this means I can't build $sicache_trans it does have the (dubious) advantage of there being less to do in sicache_trans() - the transactions don't need to be processed and therefore the table is released faster.

sqlite_escape_string was introduced in PHP 5.3 I believe, so that explains it.

[CircleDock]

The SQLite PDO doesn't seem to support transactions at all ...