Customizing SMF > SMF Coding Discussion

[WIP]SQLite3 Cache System

(1/5) > >>

nend:
Requirements

* PHP with SQLite3
Incompatible With

* Mods that save caches in SMF's ob callback function. (PHP Bug #62000)
Information
Will this is a work in progress and is the first time ever I touched SQLite. Hopefully didn't miss up too bad on the code, going to have the SQLite experts shout, "No, that isn't the way" or "Your crazy". However the system looks promising after a few test runs with my code, which like I said may not be perfect.

One problem that I have with the code, since I am no SQLite expert, I can't get vacuum to work properly so the file isn't able to shrink.  :-\

What I am trying to do though is find a alternative to the file caching mechanism in SMF which is currently bugged by illegal file name characters and file name limit exceeding. This system in some runs I seems equal to file caching, in some instances better and in some a little worse. So my conclusion is that it has about the same performance as file caching minus the invalid/max character limits.

Installation
First off I am going to show what I did, then I expect everyone's input. Lets make this a community project because I am not making a mod for it but I am allowing anyone to distribute my findings as they see fit, including making mods, lol.

First off edit your sources.

Load.php
Find

--- Code: --- // Zend Platform/ZPS/etc.
elseif (function_exists('output_cache_put'))
output_cache_put($key, $value);
elseif (function_exists('xcache_set') && ini_get('xcache.var_size') > 0)
{
if ($value === null)
xcache_unset($key);
else
xcache_set($key, $value, $ttl);
}
--- End code ---
Add After

--- Code: --- // SI Cache
elseif (function_exists('sicache_put'))
{
sicache_put($key, $value, $ttl);
}
--- End code ---

Find

--- Code: --- // Zend's pricey stuff.
elseif (function_exists('output_cache_get'))
$value = output_cache_get($key, $ttl);
elseif (function_exists('xcache_get') && ini_get('xcache.var_size') > 0)
$value = xcache_get($key);
--- End code ---
Add After

--- Code: --- elseif (function_exists('sicache_get'))
$value = sicache_get($key);
--- End code ---

Then in the main index.php
Find

--- Code: ---// And important includes.
require_once($sourcedir . '/QueryString.php');
require_once($sourcedir . '/Subs.php');
require_once($sourcedir . '/Errors.php');
require_once($sourcedir . '/Load.php');
require_once($sourcedir . '/Security.php');
--- End code ---
Add After

--- Code: ---require_once($sourcedir . '/sicache.php');
--- End code ---

Then create a file name it "sicache.php" put the contents that I am going to post below and upload it to your Sources.

--- Code: ---<?php

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

function sicache_ini() {

global $sicacheDB, $sicache_trans;

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

function sicache_get($key) {

global $sicacheDB, $sicacheData;

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

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

global $sicacheDB, $sicache_trans;

if(!isset($key) || !isset($value))
return;
if (!isset($sicacheDB))
sicache_ini();
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).'\', '.sqlite_escape_string(time() + $ttl).');';
}
}

function sicache_trans() {

global $sicacheDB, $sicache_trans;

ob_end_flush(); // We need to be the last on the block, flush and let SMF do its ob callback stuff.
@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.time().';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>
--- End code ---

To activate just turn on caching. Please test levels 1 to 3. ;)

The cache file will be saved to your cache folder.

Benchmarks

--- Quote ---Carrying out 100 iterations

Short String
File Cache Put: 0.91465306282 seconds
File Cache Get: 0.727028846741 seconds
File Cache Del: 0.538260936737 seconds
SQLite3 Cache Put: 0.169445037842 seconds
SQLite3 Cache Get: 0.505801916122 seconds
SQLite3 Cache Del: 0.000108003616333 seconds

Medium String
File Cache Put: 0.744477033615 seconds
File Cache Get: 0.524791002274 seconds
File Cache Del: 0.123141050339 seconds
SQLite3 Cache Put: 0.000550985336304 seconds
SQLite3 Cache Get: 0.401298999786 seconds
SQLite3 Cache Del: 0.000107049942017 seconds

Long String
File Cache Put: 0.72679400444 seconds
File Cache Get: 0.562448978424 seconds
File Cache Del: 0.173590898514 seconds
SQLite3 Cache Put: 0.00059700012207 seconds
SQLite3 Cache Get: 0.556365013123 seconds
SQLite3 Cache Del: 0.0001060962677 seconds
--- End quote ---
As you can see Put & Del are faster than Get. This is because Put and Del can be put into transactions which are allot faster than single queries. There are penalties though, especially on database connection, but these don't seem as bad as file cache for some reason.

Also I had to do 100 request because some results for SQLite where coming up with 0 second results or the E-5 ending.

*updated benchmarks.

Kryzen:
Nice! :D I've never really understood how cache really works, but your code looks promising :)

vbgamer45:

--- Quote from: 医生唱片骑师 on May 08, 2012, 03:00:46 AM ---Nice! :D I've never really understood how cache really works, but your code looks promising :)

--- End quote ---
The goal is to reduce sql queries which take more time and if the data does not change often it is better to cache it in memory/files/or in this case sql lite.

nend:
Looking at it though, I would guess it would all have to depend on your setup. SQLite queries on my host are like 10x faster than MySql queries.

I have been trying to comprehend the documentation, my code has changed quite a bit because of it.

From what I gather if a SQLite DB is hit hard enough it basically becomes a memory driven DB until the queries calm down again. The documentation says the recommended cache is around 2mb. So if your DB is running at less then possibly the DB will stick in memory until things calm back down again. My DB I have been playing around with has been under 200kb's. This is however based on my comprehension of the documentation, we need a SQLite expert here to confirm this.  ???

There is also a option for a memory database through SQLite, but that would not work in this case, it is destroyed when the script disconnects.

nend:
Updated to SQLite 3 branch, old code uses SQLite2

sicache.php

--- Code: ---<?php

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

function sicache_ini() {

global $sicacheDB;

$sicacheDB = new SQLite3('./cache/cache');
if (@$sicacheDB->exec('DELETE FROM cache WHERE ttl < '.time()) != 1)
$sicacheDB->exec('PRAGMA auto_vacuum = 2; CREATE TABLE cache (key text, value text, ttl int);');
}


function sicache_get($key) {

global $sicacheDB;

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

$query = @$sicacheDB->query('SELECT * FROM cache WHERE key = \''.sqlite_escape_string($key).'\' LIMIT 1');
if ($query != false) {
$data = $query->fetchArray();
return $data['value'];
}
}

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

global $sicacheDB;

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

if ($value === null) {
@$sicacheDB->exec('DELETE FROM cache WHERE key = \''.sqlite_escape_string($key).'\'');
} else {
@$sicacheDB->exec('INSERT INTO cache VALUES (\''.sqlite_escape_string($key).'\', \''.sqlite_escape_string($value).'\', '.sqlite_escape_string(time() + $ttl).')');
}
}
 
?>
--- End code ---

Navigation

[0] Message Index

[#] Next page

Go to full version