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; 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
