Customizing SMF > SMF Coding Discussion

[WIP]SQLite3 Cache System

<< < (2/5) > >>

nend:
Updated to use transactions for all the put operations. Get operations are sort of tricky, don't think there is a transaction method. Transactions in SQLite are faster than doing the queries one at a time.

New code, I need to quit playing with this for a while, lol.
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);');

// Register transaction shutdown function to run on exit.
register_shutdown_function('sicache_trans');
}


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

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

if (!isset($sicache_trans))
$sicache_trans = '';

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() { // Transactions are faster with SQLite, we can't do it for read but at least for write and delete. :D

global $sicacheDB, $sicache_trans;

$sicacheDB->exec('BEGIN;'.$sicache_trans.'COMMIT;');
}
?>
--- End code ---

nend:
Ok, a question here to all SMF Guru's.

So I registered a shutdown function to run all db inserts and deletes in one big transaction.

My question is there any other registered shutdown functions in SMF?

I need to know this so I can know if flushing the buffer is a good idea in my shutdown function. The problem I can foresee if another shutdown function that got registered later than mine, needs access to the buffer and I already flushed it.

Flushing the buffer though IMHO would make it feel like the script speeded up even though it hasn't. Basically outputing the contents while the script is still doing its inserts and deletes in the SQLite DB. So if there is nothing to follow that would ouput to the buffer, then outputting the buffer and then processing sounds like a good idea.

nend:
Ok figured it out, SMF doesn't have any shutdown functions, however it has a ob callback function in QueryString.php called ob_sessrewrite().

Well found a bug, it doesn't seem to be related to SMF or the script. It looks to be a PHP bug. You can not access a SQLite3 database in a ob callback function. You can access a MySql database or write/read to a file but not use a SQLite3 database in the function.

If you try to access a SQLite3 database in the callback function PHP will return no error, not even in the error logs. The script will actually output nothing and the SQLite3 database can not be accessed.

Simple demonstration of the bug.

--- Code: ---<?php
ob_start('callbackfunc');
echo 'test test test test test test test test test ';
function callbackfunc($buffer) {
$DB = new SQLite3('db');
$DB->exec('CREATE TABLE test (one int, two text);');
$DB->exec('INSERT INTO test VALUES (10, \'some text\')');
return $buffer;
}
?>
--- End code ---
You can move the SQLite3 database queries outside of the callback function and all will work ok, but inside it will not.

This doesn't only serve a problem to my work in progress here, it also serves as a problem to SMF when using SQLite3 and a query is made from within the callback by say a mod. There are only a few mods that do this, most of them modify the output buffer like Pretty URLs and I am sure Simple SEF does it the same way.

These results where generated on a server running PHP 5.3 with SQLite3. Maybe they don't apply to earlier versions of PHP or other versions of SQLite. I however doubt the problem relies in SQLite but mainly with PHP 5.3 and/or possibly others and how they access SQLite.

nend:
SMF's ob callback was getting called after the shutdown function. Fixed by flushing of the buffer and turning off the output buffer, thus forcing SMF to do its end processing before the shutdown function executes the rest of its task. The script should take the same amount of time to run but may notice a speed increase because the buffer should be sent before the big insert and delete transaction.

Now using a more proper query for the get function. We are using querySingle instead of query then fetchArray, basically reduced down two lines to one.

Moved all deletes of expired ttls to the shutdown function.

Also the vacuum is fixed, which means we finally have a size management system. No longer databases growing to a gigantic size.

New code.

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

nend:
Hmm, no comments. Well for the sake of keeping my code changes organized here is another code change I wiped up real quick. It is sort of a workaround for PHP bug 62000. I don't like it because it isn't really optimal way, but there isn't any other choice then to load any caches that where set in the ob callback before it loads and prevent ob call back from accessing the database until its finished.  :-\

Turn off you cache to delete the file, apply the new code to sicache.php then turn on your cache to your desired level. Have to do this step because we have a new table for the ob cache.

--- 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); CREATE TABLE obcache (key text unique, value text, ttl int);');
if (!isset($sicache_trans))
$sicache_trans = '';
register_shutdown_function('sicache_trans');
}

function sicache_get($key) {

global $siOBcache;

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

global $sicacheDB, $sicacheData;

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 $siOBcache, $sicache_trans;

if(!isset($key) || !isset($value))
return;

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).'\', '.sqlite_escape_string(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).'\', '.sqlite_escape_string(time() + $ttl).');';
}
}

function sicache_trans() {

global $siOBcache, $sicacheDB, $sicache_trans;

//PHP Bug #62000 workaround, we need to get the whole ob cache.
//Basically a sloppy blind query, but what else can you do?
$query = @$sicacheDB->query('SELECT * FROM obcache');
if ($query != false) {
$siOBcache = array();
while ($data = $query->fetchArray()) {
$siOBcache[$data['key']] = $data['value'];
}
}
ob_end_flush();
if (!isset($sicacheDB))
sicache_ini();
@$sicacheDB->exec('BEGIN;DELETE FROM cache WHERE ttl < '.time().';DELETE FROM obcache WHERE ttl < '.time().';'.$sicache_trans.'COMMIT;VACUUM;');
}
?>
--- End code ---

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version