On Sun, Nov 30, 2014 at 11:06 PM, Александр Гурьянов <caiiiy...@gmail.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
