On Oct 26, 2008, at 10:01 PM, Julian Bui wrote: > Hi Dr. Hipp, > > Thanks for responding (and without rudeness). > > I'm running my db from a java app within linux using the latest > version of sqlite released and the latest version of the xerial jdbc > driver. Again, I only index over a single column which holds a > double. > > I start with a clean database and insert rows and find out how big > the database is. > > ACTUAL RUNS: > 100 records = 3.0 KB > 1,000 records = 49 KB > 10,000 records = 2.5 MB > 100,000 records = 240 MB > > EXPECTED RESULTS - with 28 bytes per record estimate of actual data > (not including index): > 100 records = 100 * 28 = 2.73 KB (agrees with actual run) > 1000 records = 1000 * 28 = 27.34 KB (slipping away from actual run > size, but still within the same magnitude) > 10,000 records = 10,000 * 28 = 0.267 MB (disagrees with actual > result, is index size growing exponentially?) > 100,000 records = 100,000 * 28 = 2.67 MB (disagrees with actual > result, is index size growing exponentially?) > > I've attached the 100 and 10,000 record dbs. >
I ran the sqlite3_analyzer program against your db10000.db database file. And despite its name, it appears to contain 55002 entries, not 10,000 as claimed. The average size of each entry in the table is 26 bytes and each index entry consumes about 13 bytes. 55001*(26+13) is about 2.1MB which is not far from the actual database file size of 2.6MB. I think the growth in database size is probably occurring because you are inserting more values into the database than you think you are. The first few pages of the output of sqlite3_analyzer are attached. D. Richard Hipp [EMAIL PROTECTED] /** Disk-Space Utilization Report For db10000.db *** As of 2008-Oct-27 12:45:37 Page size in bytes.................... 1024 Pages in the whole file (measured).... 2597 Pages in the whole file (calculated).. 2597 Pages that store data................. 2597 100.0% Pages on the freelist (per header).... 0 0.0% Pages on the freelist (calculated).... 0 0.0% Pages of auto-vacuum overhead......... 0 0.0% Number of tables in the database...... 2 Number of indices..................... 1 Number of named indices............... 1 Automatically generated indices....... 0 Size of the file in bytes............. 2659328 Bytes of user payload stored.......... 1374633 51.7% *** Page counts for all tables with their indices ******************** CAN_MESSAGES.......................... 2596 99.961% SQLITE_MASTER......................... 1 0.039% *** All tables and indices ******************************************* Percentage of total database.......... 100.0% Number of entries..................... 110002 Bytes of storage consumed............. 2659328 Bytes of payload...................... 2001976 75.3% Average payload per entry............. 18.20 Average unused bytes per entry........ 1.27 Average fanout........................ 95.00 Fragmentation......................... 67.9% Maximum payload per entry............. 156 Entries that use overflow............. 0 0.0% Index pages used...................... 18 Primary pages used.................... 2579 Overflow pages used................... 0 Total pages used...................... 2597 Unused bytes on index pages........... 3430 18.6% Unused bytes on primary pages......... 136177 5.2% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 139607 5.2% *** All tables ******************************************************* Percentage of total database.......... 66.2% Number of entries..................... 55002 Bytes of storage consumed............. 1759232 Bytes of payload...................... 1374870 78.2% Average payload per entry............. 25.00 Average unused bytes per entry........ 0.77 Average fanout........................ 95.00 Fragmentation......................... 51.5% Maximum payload per entry............. 156 Entries that use overflow............. 0 0.0% Index pages used...................... 18 Primary pages used.................... 1700 Overflow pages used................... 0 Total pages used...................... 1718 Unused bytes on index pages........... 3430 18.6% Unused bytes on primary pages......... 38731 2.2% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 42161 2.4% *** All indices ****************************************************** Percentage of total database.......... 33.8% Number of entries..................... 55000 Bytes of storage consumed............. 900096 Bytes of payload...................... 627106 69.7% Average payload per entry............. 11.40 Average unused bytes per entry........ 1.77 Fragmentation......................... 100.0% Maximum payload per entry............. 12 Entries that use overflow............. 0 0.0% Primary pages used.................... 879 Overflow pages used................... 0 Total pages used...................... 879 Unused bytes on primary pages......... 97446 10.8% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 97446 10.8% _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
