Further to Richards comment the formula to determine when a record overflows into a page file is given in section 1.5 of the file format doc
http://www.sqlite.org/fileformat.html The relevant section states: The amount of payload that spills onto overflow pages also depends on the page type. For the following computations, let U be the usable size of a database page, the total page size less the reserved space at the end of each page. And let P be the payload size. Table B-Tree Leaf Cell: If the payload size P is less than or equal to U-35 then the entire payload is stored on the b-tree leaf page. Let M be ((U-12)*32/255)-23. If P is greater than U-35 then the number of byte stored on the b-tree leaf page is the smaller of M+((P-M)%(U-4)) and U-35. Note that number of bytes stored on the leaf page is never less than M. So in your case with a max blob size of 49870 bytes (which is > U-35) so as far as I see it all the blobs can fit onto a single page and acccording to that formula they should be. Certainly in my testing I used your schema (with rowids) and wrote a single blob of size 60000 bytes to a DB with a pagesize of 65536 bytes and the complete blob was stored in one page as expected (and as shown below). http://sandersonforensics.com/pics/largeblobhdr.jpg http://sandersonforensics.com/pics/largeblob.jpg You have truncated some of the data for DBs with page size > 512 - I assume you have determined that this is not relevant but without the info I can't draw any conclusions. I also assume that you started a new instance of sqlite between tests? Perhaps I could suggest that you do a similar test to mine just adding one (or a small handful) of blobs to the table and see if this sheds any light on what is going on. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite http://sandersonforensics.com/forum/content.php?168-Reconnoitre - VSC processing made easy On 1 December 2014 at 13:13, Richard Hipp <[email protected]> wrote: > On Sun, Nov 30, 2014 at 11:06 PM, Александр Гурьянов <[email protected]> > wrote: > >> Hi. Hi. I am writing an app that stores some blobs in sqlite3 database. My >> question is about disk space usage. >> For analyst i use sqlite_analyzer. >> >> My database is created with this script: >> >> PRAGMA page_size = 512; >> PRAGMA synchronous = OFF; >> PRAGMA journal_mode = MEMORY; >> begin; >> create table grid_blobs(grid_id integer, blob blob, PRIMARY KEY(grid_id)) >> WITHOUT ROWID; >> > > You'll have much better success here if you omit the WITHOUT ROWID. See > section 4.0 "When to use WITHOUT ROWID" at > https://www.sqlite.org/withoutrowid.html for additional information. > > See also: http://www.sqlite.org/affcase1.html#smaller > > > > >> insert into grid_blobs values(0, randomblob(70)); >> insert into grid_blobs values(1, randomblob(66)); >> insert into grid_blobs values(2, randomblob(82)); >> insert into grid_blobs values(3, randomblob(79)); >> insert into grid_blobs values(4, randomblob(67)); >> insert into grid_blobs values(5, randomblob(124)); >> insert into grid_blobs values(6, randomblob(119)); >> insert into grid_blobs values(7, randomblob(70)); >> insert into grid_blobs values(8, randomblob(82)); >> insert into grid_blobs values(9, randomblob(75)); >> --... (other entries) >> insert into grid_blobs values(2703, randomblob(698)); >> commit; >> >> There are 2703 entries with blob length from 31 bytes up to 49870 bytes >> (avg: 2297, median: 332). >> The output of sqlite_analyzer for this table is: >> >> sqlite3 test.db "PRAGMA page_size=512; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Percentage of total database...................... 99.984% >> Number of entries................................. 2437 >> Bytes of storage consumed......................... 3229184 >> Bytes of payload.................................. 2608484 80.8% >> Average payload per entry......................... 1070.37 >> Average unused bytes per entry.................... 235.34 >> Average fanout.................................... 8.00 >> Non-sequential pages.............................. 0 0.0% >> Maximum payload per entry......................... 49877 >> Entries that use overflow......................... 2321 95.2% >> Index pages used.................................. 35 >> Primary pages used................................ 268 >> Overflow pages used............................... 6004 >> Total pages used.................................. 6307 >> Unused bytes on index pages....................... 1774 9.9% >> Unused bytes on primary pages..................... 8836 6.4% >> Unused bytes on overflow pages.................... 562916 18.3% >> Unused bytes on all pages......................... 573526 17.8% >> >> sqlite3 test.db "PRAGMA page_size=1024; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 4679 10.6% >> Unused bytes on primary pages..................... 17233 5.7% >> Unused bytes on overflow pages.................... 933114 28.7% >> Unused bytes on all pages......................... 955026 26.5% >> >> sqlite3 test.db "PRAGMA page_size=2048; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 13738 14.6% >> Unused bytes on primary pages..................... 37882 6.6% >> Unused bytes on overflow pages.................... 1184110 37.0% >> Unused bytes on all pages......................... 1235730 31.9% >> >> sqlite3 test.db "PRAGMA page_size=4096; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 16017 12.6% >> Unused bytes on primary pages..................... 57645 6.3% >> Unused bytes on overflow pages.................... 1264444 43.2% >> Unused bytes on all pages......................... 1338106 33.7% >> >> sqlite3 test.db "PRAGMA page_size=8192; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 31248 18.2% >> Unused bytes on primary pages..................... 70063 5.5% >> Unused bytes on overflow pages.................... 1535111 54.6% >> Unused bytes on all pages......................... 1636422 38.4% >> >> sqlite3 test.db "PRAGMA page_size=16384; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 52549 24.7% >> Unused bytes on primary pages..................... 88974 5.1% >> Unused bytes on overflow pages.................... 1202291 59.7% >> Unused bytes on all pages......................... 1343814 33.9% >> >> sqlite3 test.db "PRAGMA page_size=32768; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 36957 18.8% >> Unused bytes on primary pages..................... 104610 5.0% >> Unused bytes on overflow pages.................... 1104895 70.2% >> Unused bytes on all pages......................... 1246462 32.2% >> >> sqlite3 test.db "PRAGMA page_size=65536; vacuum;" && >> ~/Downloads/sqlite3_analyzer test.db > analyze >> Unused bytes on index pages....................... 115861 44.2% >> Unused bytes on primary pages..................... 179123 7.2% >> Unused bytes on overflow pages.................... 427834 72.5% >> Unused bytes on all pages......................... 722818 21.6% >> >> As you can see there are initially 18% of unused space in overflow pages. >> As far as I understand it means that there are >> some blobs that do not fit into primary pages and these blobs are stored in >> overflow pages. But with increasing page size the unused space on overflow >> pages are also growth. Even when the page size is greater then max blob >> size (page_size = 65536, max blob = 49870) there are 72.5% of unused bytes >> on overflow pages. Why? >> >> _______________________________________________ >> sqlite-users mailing list >> [email protected] >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > -- > D. Richard Hipp > [email protected] > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
