I left the list some days and find this subject discussed and finished!!
Happy New Year 2016 to every colister and sqlite developer.
I add my list and a tiny explanation.
- Lock tables/indexes on cache (make them unevictable)
When a query use a big size table, other tables/index used in the query or
other queries tables/indexes are dropped from sqlite cache. Lock content on
cache could boost those queries that "fights" with a big size table. Mark a
table as non cacheable could do the same in my use case.
As a working example I c&p from own code, a sqlite filesystem that stores file
content as blob or as external file:
CREATE TABLE `file` ( \
`fid` INTEGER, \
`size` INTEGER NOT NULL, \
`permission` INTEGER NOT NULL DEFAULT 700, \
`lock` INTEGER NOT NULL DEFAULT 0, \
`blob_id` INTEGER NOT NULL, \
`owner_id` INTEGER NOT NULL, \
`group_id` INTEGER NOT NULL, \
`name` TEXT NOT NULL, \
`external_path` TEXT NOT NULL, \
PRIMARY KEY(fid) \
);
CREATE TABLE `fsblob` ( \
`bid` INTEGER, \
`size` INTEGER NOT NULL, \
`fid` INTEGER NOT NULL, \
`compressed` INTEGER NOT NULL DEFAULT 0, \
`content` BLOB, \
PRIMARY KEY(bid) \
); \
\
Each time a user access a file, the "file" table (and others like "directory",
"users") is evicted from cache (fsblob table fills cache) and needs to be
reread and reparsed from disk for the next query. The problem is worse when
multiple files are accessed. Making the cache bigger works upto some filesize,
but locking the "important" tables on cache is the correct (I think) fix. As a
workaround, I used multiple databases, one with fs metadata and attach others
with file content (fsblob tables), but dropped it because other tecnical
problems.
- Access table row by fileposition
In a read only scenario (no writer and/or no wal mode), read and parse the row
content if we know it's absolute database file position or we know page number
plus page position offset.
- Lock constant tables on file (stick table rows to page and/or fileposition)
This way, the file position of a row is fixed or easily calculated and can
create/use hash indexes. Now I'm using a virtual table derived from Dr. Hipps
csv virtual table years ago that creates a inmemory hash index of an external
csv file, but it implies use of own sqlite3 version with virtual code and an
additional file to database.
Additionally, if we can lock constant tables (defined as is by the developer)
at the begining of the database file, mmap feature can cache constant/static
data from those tables. In my app, it implies file, user, group, fs and other
tiny tables (changes are rare) could be mmaped.
- Vacuum/backup reorder tables in database file
If we can tell vacuum or backup the order we want the tables be on the database
file, on some scenarios (like mine;) ) moving tables to the begining (constant
data) or the end (timestamp log growing table, f.ex.) could speed up queries
- Vacuum/backup adds free pages for table/index grow
As far as I know, vacuum and backup write database tables, indexes and metadata
without free pages. Adding free pages at the end of the pages that store a
table or index, and reserve those pages for use of that table or index could
hold back internal fragmentation.
There are others ideas but... hey 2017 is closer ;)
Thanks to Dr. Richard Hipps, Dan Kennedy and Joe Mistachkin for their
exceptional work, and other colisters for their time and help.
--- ---
Eduardo Morras <emorrasg at yahoo.es>
