Customizing SMF > SMF Coding Discussion

[WIP]SQLite3 Cache System

<< < (3/5) > >>

emanuele:
Nice work! :D

I was testing it in SMF, what I noticed is that the:

--- Code: ---@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');
--- End code ---
slows down the cache a lot:
Cache hits: 5: 0.05661s for 14216 bytes
while removing it:
Cache hits: 5: 0.00092s for 14216 bytes
that's on board index, and a couple of refreshes.
For reference this is with the file cache:
Cache hits: 5: 0.00088s for 14216 bytes
Of course there is *a lot* of variability, but the first is definitely higher that the other two. ;)

Something like that should help:

--- Code: --- static $fileexists;

if (!isset($fileexists))
$fileexists = file_exists('./cache/cache');

$sicacheDB = new SQLite3('./cache/cache');
if (!$fileexists)
@$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');

--- End code ---

Additionally, for security it would be better if the name of the database is random (you can save it in Settings.php).

nend:
On previous testing I was getting the same results, because I was manually calling the setup function.

I wonder why that query is so slow.  :-\

The only thing though is one time with SQLite I got DB corruption, The DB will exist just with 0 bytes and the slow query sets up the tables on corruption. There is a better way to do this though, allot faster instead of running the query all the time and suppressing the error like in my code. You have demonstrated one way, just need to check for 0 byte databases, the sign of corruption.

Allot of the performance of SQLite really depends on the machine and its settings. The file cache is the same way, different machine settings can lead to different performance outputs. I like your results, basically the same performance as the file cache, 0.00004s difference. There is one bonus to this system, freedom from the file limitations of the file system like file names and characters. However the server must have SQLite3 in order to use it.

emanuele:

--- Quote from: nend on May 15, 2012, 10:37:46 AM ---On previous testing I was getting the same results, because I was manually calling the setup function.

I wonder why that query is so slow.  :-\
--- End quote ---
"PRAGMA auto_vacuum = 2; "
Seems to be the main problem, removing it it becomes as faster as without the entire query.


--- Quote from: nend on May 15, 2012, 10:37:46 AM ---The only thing though is one time with SQLite I got DB corruption, The DB will exist just with 0 bytes and the slow query sets up the tables on corruption. There is a better way to do this though, allot faster instead of running the query all the time and suppressing the error like in my code. You have demonstrated one way, just need to check for 0 byte databases, the sign of corruption.
--- End quote ---
If you have to check it every once in a while, you can use the same "trick" SMF uses to check if the cache directory is writable (check in Subs.php, search for $securityFiles and you will find the place).
Maybe you can do the same with the PRAGMA and the VACUUM.


--- Quote from: nend on May 15, 2012, 10:37:46 AM ---Allot of the performance of SQLite really depends on the machine and its settings. The file cache is the same way, different machine settings can lead to different performance outputs. I like your results, basically the same performance as the file cache, 0.00004s difference. There is one bonus to this system, freedom from the file limitations of the file system like file names and characters. However the server must have SQLite3 in order to use it.

--- End quote ---
That difference is not significant (the result should be something like +/- 20%), they are basically the same and that is good!

nend:
Thanks for the input, will have to give a few of those suggestions a try.

Did a few optimizations, really just taking off feathers of weight off the system. Maybe a bad expression there but the optimizations I done aren't going to show unless the cache gets around 200 request and still not even a second. I don't think there is much more I can do on this end to give it any extra power. The system is at full speed, not going to get any faster I think.

I tried moving the db connection to get loaded first, thinking it might give the db more time to load. All I did was create the illusion the system was going faster, SMF still had the same page speeds. If anyone wishes to test this on any further you can call sicache_ini() after including the sicache.php file in index.php.

The first request though is always going to be the slowest, the DB is still loading, as the request go down the line they get faster and faster. Main reason is that sicache_ini() which loads the database gets called on the first request. One could call this function earlier in SMF, but it seems to have no benefits like I stated.

From what I read the "PRAGMA" has to be called before the tables get set up. If it isn't then there is a change auto_vacuum will not work. I'll research more if the PRAGMA is actually required there. Auto vacuum though is a nice feature that would keep the database size down.

Also I decided to use filesize() on sicache_ini() to check the db condition, if 0 is returned then the database has to be set up again. It seems to be working pretty good.

Well set out to try to make a cache system that may be better or worse than the file cache. Ended up with something that is sometimes better or sometimes worse.  :-\

Here is my latest code.

--- Code: ---<?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('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text unique, value text, ttl int); CREATE TABLE obcache (key text unique, value text, ttl int);');
}
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');
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetchArray()) {
if ($data['ttl'] > $sicache_time) {
$siOBcache[$data['key']] = $data['value'];
}
}
}

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;');
}
?>
--- End code ---

CircleDock:
I couldn't get your code to run as-is on either my local server nor on a production server and therefore had to convert it to use the PDO interface. All my changes are documented.

As you can see, I've also slightly modified the Pragmas and now the vacuuming works a treat.


--- Code: ---<?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
//        multiple errors.
//---------------------------------------------------------------

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
    $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('VACUUM;');
        
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();
}

$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;

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 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;

$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'];
}
}
}

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;PRAGMA incremental_vacuum;');
}
?>

--- End code ---

I've yet to benchmark it but on both the servers where it is installed, it does appear to be faster.

Mark

Navigation

[0] Message Index

[#] Next page

[*] Previous page