SQLite Cache

[nend]

Link to Mod

This is a experimental cache system that uses SQLite.

The cache system state is stable if you meet the minimum system requirements.

Be sure to meet all system requirements before installing. If you get any errors before you install then do not install it via the Package Manager, a MANUAL INSTALL IS REQUIRED. This mod has very strict requirements and not making sure your environment meets these requirements is taking a recoverable but inconvenient risk on your forum that I am not legally liable for.

The system works by replacing SMF's file cache with a SqLite database to store all caches. The benefits of this are very slim and in test with few queries, the system was slightly slower than file caching however after the first one or two cache calls the system is marginally faster than SMF's file cache system.

Benefits over SMF's file caching

• Longer key name support.
• No illegal key characters.
• One file in cache dir.
• Slight speed enhancements in a medium to heavily cached site. (May be slower on a lightly cached site.)

SQLite3 Cache 1.0.2 to 1.0.4 Minimum Requirements

• PHP 5.3.x
• SQLite 3

SQLite Cache 1.0.5 and Greater Minimum Requirements

• PHP 5 to 5.3.x
• SQLite 2 or 3
• Zlib Output Compression

Installation with lower or higher system requirements are not recommend and may render site inoperable.

License

Copyright (c) 2012, Russell Najar (SiberInc)
All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
Neither the name of the SiberInc nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Change Log
1.0
Initial Release

1.0.1
* Fixed bad path in installation script.
+ Transactions are for everything now. (Speed enhancement)
+ Turned off synchronous pragma. (Speed enhancement with possibility of corruption)

1.0.2
- Removed initialization call from main index.php
* Rewrite on OB side since we are using transactions. (May be for the worse or bad, example of possible mod affected Pretty URLs)
+ Set journal mode to memory. (Once again a corruption risk in the even of sudden power loss.)

1.0.3
+ Added sicache_clean(), replacement for SMF's clean_cache in Subs.php.

1.0.4
* Removed useless get queries on a new db cache.
* Removed useless get queries on SMF's cache clean command.
* Called ob buffer with error depressed. (Easier to ignore an error than take a performance penalty accommodating it.)
* In sicache_clean removed append on cache dump and overwritten the transaction.
* Moved main purge statement on expired caches to beginning of transaction.
* Removed unused globals.
+ Added support to detect a malformed database in sicache_trans and delete it in sicache_destroy(). (This fixes a bug where the cache would quit working if it got malformed.)

1.0.5 Unreleased
* Changed value table from text to blob.
+ Added support for ZLib compression of values. (Trying to decrease the SQLite file size.)
+ Added SQLite3 class which adds compatibility PHP 5 to 5.4 and SQLite 2 & 3. (Compression with UDF Binary encoding is preventing the mod right now from going any higher than 5.4)

1.0.5.1 Released, use with caution.
+ Added SQLite3 class for older PHP versions that do not support it and to add support for SQLite 2 when SQLite 3 is not installed.
* Renamed mod from SQLite3 Cache to SQLite Cache.

Info Pertaining to Releases &/or ChangeLog
One may notice above that allot of changes have the potential of data loss. Data loss however is no concern since the cache means for existence is temporary storage. If corruption of data occurs the database is purged and the system is returns to a brand new state waiting for input from SMF.

In case you killed your forum
This mod is stable but by not meeting system requirements and/or not following the proper instructions included in this documentation can lead to a inoperable forum. If you end up with a inoperable forum then comment out the following line in index.php to get it back and running.

Code Select Expand

require_once($sourcedir . '/SQLite3cache.php');

[Sudhakar Arjunan]

i understood this mod is the cache for sql.

Could you be more elaborate the system.

[Arantor]

It's one type of cache, that can be a speed enhancement for some types of forums where things like memcached are not available.

If you don't understand what it's doing, you probably shouldn't install it, assuming you meet the requirements.

If you don't know whether you meet the requirements, you definitely shouldn't install it.

[nend]

i understood this mod is the cache for sql.

Could you be more elaborate the system.

The cache system replaces the current file based cache of SMF. Once installed it is activated by turning on caching in the server settings of your forum.

I will update the description soon.

[Arantor]

You should probably add my disclaimer

[nend]

You should probably add my disclaimer

What's that?

[Arantor]

If you don't know whether you meet the requirements, you definitely shouldn't install it.

[Sudhakar Arjunan]

Thanks both of you. I need a clarity before i install !!!

Nend i would look for your notes. 

[nend]

Will be updating soon with a better version, only a few changes.

There was a SMF cache function hiding in Subs.php which caused a little bit of overhead. Since this cache system works differently its way of handling the situation was not optimal.

Just need a little bit of testing and I will release it.

[FrizzleFried]

I would be interested in hearing some real-world opinions of this mod and whether it does or does not improve speed, etc...

[nend]

Here is a comparison of how it is doing on one of my sites. Results may vary though depending on how SqLite is setup on the server. The first test on both of them is when the cache directory is empty. Then the last two in the test are when there is something in the cache.

SqLite3 Cache

Cache hits: 11: 0.02294s for 20333 bytes (
   get modSettings: 0.02179s - 0 bytes,
   put modSettings: 0.00027s - 12531 bytes,
   get user_settings-1: 0.00015s - 0 bytes,
   put user_settings-1: 0.00007s - 2235 bytes,
   get theme_settings-3:1: 0.00008s - 0 bytes,
   get theme_settings-3: 0.00006s - 0 bytes,
   put theme_settings-3:1: 0.00005s - 1595 bytes,
   get log_online-update: 0.00017s - 0 bytes,
   put log_online-update: 0.00004s - 13 bytes,
   get menu_buttons-1_4-english: 0.00016s - 0 bytes,
   put menu_buttons-1_4-english: 0.00010s - 3959 bytes
)

Cache hits: 5: 0.02837s for 20333 bytes (
   get modSettings: 0.02774s - 12531 bytes,
   get user_settings-1: 0.00019s - 2235 bytes,
   get theme_settings-3:1: 0.00008s - 1595 bytes,
   get log_online-update: 0.00015s - 13 bytes,
   get menu_buttons-1_4-english: 0.00021s - 3959 bytes
)

Cache hits: 4: 0.01440s for 20316 bytes (
   get modSettings: 0.01403s - 12531 bytes,
   get user_settings-1: 0.00013s - 2231 bytes,
   get theme_settings-3:1: 0.00008s - 1595 bytes,
   get menu_buttons-1_4-english: 0.00016s - 3959 bytes
)

SMF File Cache

Cache hits: 11: 0.04824s for 20333 bytes (
   get modSettings: 0.02356s - 0 bytes,
   put modSettings: 0.00885s - 12531 bytes,
   get user_settings-1: 0.00102s - 0 bytes,
   put user_settings-1: 0.00261s - 2235 bytes,
   get theme_settings-3:1: 0.00027s - 0 bytes,
   get theme_settings-3: 0.00045s - 0 bytes,
   put theme_settings-3:1: 0.00167s - 1595 bytes,
   get log_online-update: 0.00191s - 0 bytes,
   put log_online-update: 0.00122s - 13 bytes,
   get menu_buttons-1_4-english: 0.00185s - 0 bytes,
   put menu_buttons-1_4-english: 0.00483s - 3959 bytes
)

Cache hits: 10: 0.04453s for 22564 bytes (
   get modSettings: 0.01707s - 12531 bytes,
   get user_settings-1: 0.00509s - 2235 bytes,
   get theme_settings-3:1: 0.00229s - 1595 bytes,
   get log_online-update: 0.00463s - 13 bytes,
   put member_data-profile-1: 0.00122s - 0 bytes,
   put member_data-normal-1: 0.00071s - 0 bytes,
   put member_data-minimal-1: 0.00254s - 0 bytes,
   put user_settings-1: 0.00417s - 0 bytes,
   put user_settings-1: 0.00442s - 2231 bytes,
   get menu_buttons-1_4-english: 0.00239s - 3959 bytes
)

Cache hits: 12: 0.04272s for 22560 bytes (
   get modSettings: 0.00161s - 0 bytes,
   put modSettings: 0.00514s - 12531 bytes,
   get user_settings-1: 0.00422s - 2231 bytes,
   get theme_settings-3:1: 0.00910s - 1595 bytes,
   get log_online-update: 0.00953s - 0 bytes,
   put log_online-update: 0.00552s - 13 bytes,
   put member_data-profile-1: 0.00008s - 0 bytes,
   put member_data-normal-1: 0.00003s - 0 bytes,
   put member_data-minimal-1: 0.00003s - 0 bytes,
   put user_settings-1: 0.00099s - 0 bytes,
   put user_settings-1: 0.00372s - 2231 bytes,
   get menu_buttons-1_4-english: 0.00277s - 3959 bytes
)

If you notice the first cache hit on SqLite3 Cache is always the slowest. This is because the database is still being opened. This is one of the reasons I said after the first couple of hits to the cache the SqLite3 Cache is faster.

However don't look at the total times, compare the individual times in each page load to get a idea of which one is faster.

[nend]

Just surpassed myself again, you know when you think it couldn't get any faster and it does. 

I am debating on releasing this version. The new version requires an additional feature, I think it is in most PHP setups already though. Introducing ZLib compression in the SQLite3 Cache for SMF. 

[petgil.net]

Working on New Mods & Themes for SMF... Will update soon


petgil.com

[nend]

I am thinking the post above is SPAM.

In any matter I need some test done on the new version that uses compression. I am still deciding the route to go since if I release this as 1.0.5 then there will be one more extra requirement to get it working and that would be the ZLib.

Will here is the file, it is faster, I just need some testing done on it via other systems. Also please note uninstall any previous versions of the cache if you have any before installing this one. 

[tinoest]

I would suggest a small change to your code to check that the sqlite_ functions exist , the following should work.

You might want to warn them if they don't. Unless you already do before this and i've missed it. 

Code Select Expand

if(!class_exists('SQLite3') && class_exists('SQLiteDatabase')) {
    class SQLite3 {
          var $handle;
              function SQLite3($data) {
                      $this->handle = sqlite_open($data);
                          }
              public function exec($data) {
                      return sqlite_exec($this->handle, $data);
                          }
              public function querySingle($data, $type = false) {
                      if ($type == true) {$type = false;} else {$type = true;}
                              return sqlite_single_query($this->handle, $data, $type);
                          }   
              public function escapeString($data) {
                      return sqlite_escape_string($data);
                          }
              public function lastErrorCode() {
                      return sqlite_last_error();
                          }
            }             
}           



[nend]

It is better to assume in a performance critical application then to check. So basically saying it is up to the one that downloads the modification to see if they meet the minimum and maximum requirements.

Take for example the sqlite_foo() function is available from PHP 5 to 5.3.x and the SQLite3 class is available from 5.3 and above. Since we need UDF binary encoded data we have to use a function that is not supported in 5.4 and up. Hence the minimum and maximum requirements PHP 5 to 5.3.x. Since we know the sqlite_foo() functions exist in these versions there is no reason to check.