Hi,
I have some problems trying to truncate a db file to its "normal" size.
I use sqlite as a file format in a mostly append only mode.
Because I need to be prepared for high data rate, I chose to use chunk size
to avoid file fragmentation (e.g, I use a chunk size of 4MB, but sometimes
up to 100MB for blobs).
But very often data is very small, i.e. a few kb; therefore I need/want to
truncate it back to the "normal" size.
What is the correct way of doing it?
Note: I am using WAL mode during writing, and switching back on stop (when
I also want the truncation).
Another note: I did find a way to achieve this (in app code), but I'm not
sure if it's guaranteed. Therefore I'm not even writing how :), waiting for
better answers.
Using "PRAGMA auto_vacuum" and related does NOT solve anything, because the
file is just bigger; there are no pages in "freelist".
Vacuum is also not really an option; copying data is very expensive (can be
very large), when there is nothing really to do.
I might also have readers, so closing and manually truncating the file is
also not an option.
This is extract of summary from analyzer:
Page size in bytes.................... 8192
Pages in the whole file (measured).... 8
Pages in the whole file (calculated).. 8
Pages on the freelist (per header).... 0 0.0%
Size of the file in bytes............. 65536
At this point my file is 4MB, and there is no way to reduce it the required
size of 8 pages(see above).
Enabling debug and reading the source code, I found that pager_truncate is
never called because the line
if( pPager->dbSize!=pPager->dbFileSize ){
does not pass condition; both values at this point (I added some logging
lines) show 512 pages (= 4MB file / 8kB page size).
I understand that dbSize is initialized based on file size on open, and
never really shrinks (unless one really writes many pages, then delete
them)...
Further reading, I found a possible solution: Why shouldn't PRAGMA
incremental_vacuum truncate the file even if there is no free list???
I added this code to sqlite3BtreeIncrVacuum, before sqlite3BtreeLeave
(therefore working regardless of autoVacuum mode!):
//gc: truncate if needed!
> Pager *pPager = pBt->pPager;
> if(pPager->dbSize > pBt->nPage){
> if( pPager->eState>=PAGER_WRITER_CACHEMOD ||
> pager_open_journal(pPager) == SQLITE_OK ){
> sqlite3PagerTruncateImage(pPager, pBt->nPage);
> }
> }
> sqlite3BtreeLeave(p);
> return rc;
>
Is there any problem with this approach?? It looks to work as I want,
except it writes pages to journal before truncating.
This looks completely unnecessary as they contain no data; especially when
the chunk is 100MB (yes, I also have this case for blobs).
Maybe someone with more knowledge can solve it, this way or another.
Thanks for attention,
Gabriel
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
