1$Id: README,v 1.8 2006/11/23 11:37:56 ivoras Exp $ 2 3SQL Cache Daemon 4**************** 5 6sqlcached is a memory cache engine similar in basic idea to memcached, 7but instead of limiting itself to key-value pairs, it offers full SQL 8access to the memory database. It's written in C and uses SQLite as 9its backend / memory database. 10 11Primary purpose of sqlcached is for environments such as web 12applications where memory caching gives huge speed improvements, but 13where data is too complex to be represented by simple key-value pairs. 14It also differs from memcached in that the primary mode of access is 15by (faster) unix sockets instead of TCP (which is also supported). 16 17Client libraries for C, PHP and Python languages are planned. 18 19Motivation 20========== 21 22- `memcached` is awesome, but doesn't offer any fine key granularity. 23- Need a cache with multiple keys and advanced expiry (to expire a subset 24 of keys on demand) 25 26Implementation 27============== 28 29- Use SQLite :memory: database to implement advanced cache mechanism 30- Thus, get full DB support: multiple tables, fields, indexes 31- Unfortunately, the best way to use a full DB is via SQL 32 33SQLite locking problem 34---------------------- 35 36Even in its regular use, SQLite doesn't allow multiple simultaneous 37updates on the database (by multiple simultaneous clients). When using 38memory databases, no sharing between multiple processes or threads 39is even possible. Thus, all access to the database needs to be 40serialised. SQL Cache Daemon is thus a single threaded process which 41multiplexes between client connections using poll(2). 42 43Since the goal is to replace memcached with something offering more 44functionality, and memcached is fine the way it is (single-threaded), 45it is believed that a single-threaded sqlcached will perform adequate. 46 47On transactions 48--------------- 49 50Since all requests are serialised onto one sqlite connection, real 51transactions cannot be implemented. Thus, commands "BEGIN", "COMMIT" 52and "ROLLBACK" are not allowed. All statements are executed in 53immediate/auto-commit mode. 54 55Performance expectations 56------------------------ 57 58Due to its complexity, SQLite is in general much slower than 59memcached, but real world measurements are telling that the 60comparative slowdown is somewhere at the order of 25% or less. Since 61memcached only offers very simple key-value operations, compared to 62the full SQL available in the SQLite engine, this slowdown is 63mostly insignificant. 64 65Some performance optimisations sqlcached uses: 66 67- Support for unix domain sockets eliminates TCP overhead 68- Communication between clients and the server is as simple 69 as possible and without small block reading/writing ops (to allow 70 simple clients using buffered fgets()) 71- poll() based single-threaded network multiplexing 72 73NB: SQLite and memcached do not allow concurrent access! While 74network multiplexing is done concurrently for many connections, 75each SQL query will execute on its own, blocking all other 76queries. On the other hand, each query gets 100% CPU time. This 77is the same method memcached uses. 78(In other words: keep SQL queries passed to sqlcached fast) 79 80 81Implementation details 82---------------------- 83 84Implemented on and for FreeBSD 6.x. The only external dependancy is for 85SQLite 2.8 (libsqlite). Porting to other platforms will be 86considered based on user feedback, if any. 87 88I will also accept compatibility patches for other OSes, but only if 89they don't introduce more than two #ifdef directives per .c or .h 90file (use #include directives for more complex things). 91 92 93Portability 94=========== 95 96SQLCacheD is created and maintained on FreeBSD. It's been tested 97to compile and work (at least for the basic operations) on Fedora 98Core 6, where you'll need the sqlite2-devel package to compile it. 99You should ignore missing declarations for vasprintf and asprintf 100as they won't impact the final executable. 101 102 103Configuration 104============= 105 106At first, the goal was to make an XML config file. At second thought, 107this was scrapped and all configuration is done via command line 108arguments. 109 110Of note is the schema file, loaded at startup, which serves to create 111(and possibly populate) the initial database. If no schema file is 112specified, the following table will be created: 113 114 CREATE TABLE cache ( 115 key varchar not null, 116 value varchar not null, 117 time integer not null, 118 primary key(key) 119 ); 120 CREATE INDEX cache_time ON cache(time); 121 122NOTE: Clients must be familiar with the database schema to use 123sqlcached. This default schema is enough to simulate basic 124behaviour of memcached. 125 126NOTE: All tables must have a "time" field to allow data expiry 127(garbage collection). 128 129Another file which may be used by sqlcached is the "expiry file", 130which describes how to delete data from the table (expiry criteria) 131If no expiry is performed, accumulated data may grow too large 132for the system to handle (and it will steal resources from 133other applications). 134 135Here's an example of the expiry file: 136 137 100 138 cache 60 200 0 139 140First line in this file specifies how often is data expiry function 141called, expressed as a number of SQL commands executed. The default 142value (100) means data will be checked for expiry every 100 SQL 143operations. 144 145The remaining lines in the file are in the format: 146 147 table_name time_to_live nr_ops nr_records 148 149...where... 150 151 * table_name is the name of the table to perform expiry on 152 (e.g. "cache") 153 * time_to_live is the maximum time records are allowed to exist 154 (i.e. records older than this much seconds will be deleted) 155 * nr_ops is the maximum number of ops records are allow to exist 156 (i.e. records that were created more than nr_ops SQL operations 157 ago will be deleted) 158 * nr_records is the maximum number of records in the table 159 (oldest records will be deleted to maintain record count; this 160 is the most expensive expiry method and it's recommended that 161 it be used with a high delays between expiry function calls) 162 163Any criteria whose value is 0 is ignored. In the given example 164of the criteria file, records will be deleted if older than 60 165seconds or 200 SQL ops (record count will not be checked). 166 167Discussion 168========== 169 170Q: How is sqlcached different from MySQL and its memory-based tables? 171A: It saves you from having to use and maintain a separate database 172 engine (well, unless you're already using MySQL). It's also more 173 light-weight. Also, MySQL doesn't offer automatic schema creation and 174 data expiry. On the other hand, MySQL should offer concurrent 175 access to the data. 176 177Q: Why use SQLite 2.8 instead of 3.x? 178A: Because of this: 179 http://article.gmane.org/gmane.comp.db.sqlite.general/19002 180 (in short: SQLite 3.x is very slow for this kind of use) 181 182