SQLite Analyser results, I only included the eight largest tables the others are all tiny:
/** Disk-Space Utilization Report For test.db *** As of 2008-Aug-19 16:10:41 Page size in bytes.................... 1024 Pages in the whole file (measured).... 9176 Pages in the whole file (calculated).. 9176 Pages that store data................. 9176 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...... 35 Number of indices..................... 18 Number of named indices............... 18 Automatically generated indices....... 0 Size of the file in bytes............. 9396224 Bytes of user payload stored.......... 7243721 77.1% *** Page counts for all tables with their indices ******************** TABLE_1............................... 5045 55.0% TABLE_2............................... 1615 17.6% TABLE_3............................... 853 9.3% TABLE_4............................... 733 8.0% TABLE_5............................... 285 3.1% TABLE_6............................... 215 2.3% TABLE_7............................... 187 2.0% TABLE_8............................... 84 0.92% TABLE_9............................... 38 0.41% SQLITE_MASTER......................... 29 0.32% TABLE_10.............................. 21 0.23% TABLE_11.............................. 17 0.19% TABLE_12.............................. 14 0.15% TABLE_13.............................. 7 0.076% TABLE_14.............................. 4 0.044% TABLE_15.............................. 4 0.044% TABLE_16.............................. 2 0.022% TABLE_17.............................. 2 0.022% TABLE_18.............................. 2 0.022% TABLE_19.............................. 2 0.022% TABLE_20.............................. 2 0.022% TABLE_21.............................. 2 0.022% TABLE_22.............................. 1 0.011% TABLE_23.............................. 1 0.011% TABLE_24.............................. 1 0.011% SQLITE_STAT1.......................... 1 0.011% TABLE_25.............................. 1 0.011% TABLE_26.............................. 1 0.011% TABLE_27.............................. 1 0.011% TABLE_28.............................. 1 0.011% TABLE_29.............................. 1 0.011% TABLE_30.............................. 1 0.011% TABLE_31.............................. 1 0.011% TABLE_32.............................. 1 0.011% TABLE_33............................... 1 0.011% *** All tables and indices ******************************************* Percentage of total database.......... 100.0% Number of entries..................... 112673 Bytes of storage consumed............. 9396224 Bytes of payload...................... 7701449 82.0% Average payload per entry............. 68.35 Average unused bytes per entry........ 9.50 Average fanout........................ 92.00 Fragmentation......................... 5.3% Maximum payload per entry............. 2587 Entries that use overflow............. 10 0.009% Index pages used...................... 91 Primary pages used.................... 9070 Overflow pages used................... 15 Total pages used...................... 9176 Unused bytes on index pages........... 25313 27.2% Unused bytes on primary pages......... 1045045 11.3% Unused bytes on overflow pages........ 518 3.4% Unused bytes on all pages............. 1070876 11.4% *** All tables ******************************************************* Percentage of total database.......... 92.6% Number of entries..................... 63999 Bytes of storage consumed............. 8704000 Bytes of payload...................... 7267915 83.5% Average payload per entry............. 113.56 Average unused bytes per entry........ 15.10 Average fanout........................ 92.00 Fragmentation......................... 3.0% Maximum payload per entry............. 2587 Entries that use overflow............. 10 0.016% Index pages used...................... 91 Primary pages used.................... 8394 Overflow pages used................... 15 Total pages used...................... 8500 Unused bytes on index pages........... 25313 27.2% Unused bytes on primary pages......... 940417 10.9% Unused bytes on overflow pages........ 518 3.4% Unused bytes on all pages............. 966248 11.1% *** All indices ****************************************************** Percentage of total database.......... 7.4% Number of entries..................... 48674 Bytes of storage consumed............. 692224 Bytes of payload...................... 433534 62.6% Average payload per entry............. 8.91 Average unused bytes per entry........ 2.15 Fragmentation......................... 33.9% Maximum payload per entry............. 12 Entries that use overflow............. 0 0.0% Primary pages used.................... 676 Overflow pages used................... 0 Total pages used...................... 676 Unused bytes on primary pages......... 104628 15.1% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 104628 15.1% *** Table TABLE_1 and all its indices ******************************** Percentage of total database.......... 55.0% Number of entries..................... 28932 Bytes of storage consumed............. 5166080 Bytes of payload...................... 4167680 80.7% Average payload per entry............. 144.05 Average unused bytes per entry........ 27.26 Average fanout........................ 110.00 Fragmentation......................... 2.7% Maximum payload per entry............. 333 Entries that use overflow............. 0 0.0% Index pages used...................... 44 Primary pages used.................... 5001 Overflow pages used................... 0 Total pages used...................... 5045 Unused bytes on index pages........... 6002 13.3% Unused bytes on primary pages......... 782557 15.3% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 788559 15.3% *** Table TABLE_1 w/o any indices ************************************ Percentage of total database.......... 53.0% Number of entries..................... 14466 Bytes of storage consumed............. 4982784 Bytes of payload...................... 4053558 81.4% Average payload per entry............. 280.21 Average unused bytes per entry........ 52.88 Average fanout........................ 110.00 Fragmentation......................... 2.6% Maximum payload per entry............. 333 Entries that use overflow............. 0 0.0% Index pages used...................... 44 Primary pages used.................... 4822 Overflow pages used................... 0 Total pages used...................... 4866 Unused bytes on index pages........... 6002 13.3% Unused bytes on primary pages......... 758925 15.4% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 764927 15.4% *** Indices of table TABLE_1 ***************************************** Percentage of total database.......... 2.0% Number of entries..................... 14466 Bytes of storage consumed............. 183296 Bytes of payload...................... 114122 62.3% Average payload per entry............. 7.89 Average unused bytes per entry........ 1.63 Fragmentation......................... 5.6% Maximum payload per entry............. 8 Entries that use overflow............. 0 0.0% Primary pages used.................... 179 Overflow pages used................... 0 Total pages used...................... 179 Unused bytes on primary pages......... 23632 12.9% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 23632 12.9% *** Table TABLE_2 and all its indices ************************ Percentage of total database.......... 17.6% Number of entries..................... 30794 Bytes of storage consumed............. 1653760 Bytes of payload...................... 1406810 85.1% Average payload per entry............. 45.68 Average unused bytes per entry........ 3.22 Average fanout........................ 105.00 Fragmentation......................... 13.8% Maximum payload per entry............. 127 Entries that use overflow............. 0 0.0% Index pages used...................... 13 Primary pages used.................... 1602 Overflow pages used................... 0 Total pages used...................... 1615 Unused bytes on index pages........... 2342 17.6% Unused bytes on primary pages......... 96785 5.9% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 99127 6.0% *** Table TABLE_2 w/o any indices **************************** Percentage of total database.......... 14.9% Number of entries..................... 15397 Bytes of storage consumed............. 1399808 Bytes of payload...................... 1238317 88.5% Average payload per entry............. 80.43 Average unused bytes per entry........ 4.08 Average fanout........................ 105.00 Fragmentation......................... 2.6% Maximum payload per entry............. 127 Entries that use overflow............. 0 0.0% Index pages used...................... 13 Primary pages used.................... 1354 Overflow pages used................... 0 Total pages used...................... 1367 Unused bytes on index pages........... 2342 17.6% Unused bytes on primary pages......... 60489 4.4% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 62831 4.5% *** Indices of table TABLE_2 ********************************* Percentage of total database.......... 2.7% Number of entries..................... 15397 Bytes of storage consumed............. 253952 Bytes of payload...................... 168493 66.3% Average payload per entry............. 10.94 Average unused bytes per entry........ 2.36 Fragmentation......................... 75.7% Maximum payload per entry............. 12 Entries that use overflow............. 0 0.0% Primary pages used.................... 248 Overflow pages used................... 0 Total pages used...................... 248 Unused bytes on primary pages......... 36296 14.3% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 36296 14.3% *** Table TABLE_3 ************************************************* Percentage of total database.......... 9.3% Number of entries..................... 11863 Bytes of storage consumed............. 873472 Bytes of payload...................... 743138 85.1% Average payload per entry............. 62.64 Average unused bytes per entry........ 4.74 Average fanout........................ 94.00 Fragmentation......................... 2.8% Maximum payload per entry............. 1005 Entries that use overflow............. 1 0.008% Index pages used...................... 9 Primary pages used.................... 843 Overflow pages used................... 1 Total pages used...................... 853 Unused bytes on index pages........... 2381 25.8% Unused bytes on primary pages......... 53764 6.2% Unused bytes on overflow pages........ 118 11.5% Unused bytes on all pages............. 56263 6.4% *** Table TABLE_4 and all its indices **************************** Percentage of total database.......... 8.0% Number of entries..................... 28932 Bytes of storage consumed............. 750592 Bytes of payload...................... 586950 78.2% Average payload per entry............. 20.29 Average unused bytes per entry........ 1.28 Average fanout........................ 92.00 Fragmentation......................... 4.0% Maximum payload per entry............. 76 Entries that use overflow............. 0 0.0% Index pages used...................... 6 Primary pages used.................... 727 Overflow pages used................... 0 Total pages used...................... 733 Unused bytes on index pages........... 1700 27.7% Unused bytes on primary pages......... 35369 4.8% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 37069 4.9% *** Table TABLE_4 w/o any indices ******************************** Percentage of total database.......... 6.0% Number of entries..................... 14466 Bytes of storage consumed............. 567296 Bytes of payload...................... 472828 83.3% Average payload per entry............. 32.69 Average unused bytes per entry........ 0.93 Average fanout........................ 92.00 Fragmentation......................... 2.5% Maximum payload per entry............. 76 Entries that use overflow............. 0 0.0% Index pages used...................... 6 Primary pages used.................... 548 Overflow pages used................... 0 Total pages used...................... 554 Unused bytes on index pages........... 1700 27.7% Unused bytes on primary pages......... 11737 2.1% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 13437 2.4% *** Indices of table TABLE_4 ************************************* Percentage of total database.......... 2.0% Number of entries..................... 14466 Bytes of storage consumed............. 183296 Bytes of payload...................... 114122 62.3% Average payload per entry............. 7.89 Average unused bytes per entry........ 1.63 Fragmentation......................... 8.4% Maximum payload per entry............. 8 Entries that use overflow............. 0 0.0% Primary pages used.................... 179 Overflow pages used................... 0 Total pages used...................... 179 Unused bytes on primary pages......... 23632 12.9% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 23632 12.9% *** Table TABLE_5 ************************************************* Percentage of total database.......... 3.1% Number of entries..................... 3343 Bytes of storage consumed............. 291840 Bytes of payload...................... 257040 88.1% Average payload per entry............. 76.89 Average unused bytes per entry........ 4.09 Average fanout........................ 71.00 Fragmentation......................... 2.8% Maximum payload per entry............. 116 Entries that use overflow............. 0 0.0% Index pages used...................... 4 Primary pages used.................... 281 Overflow pages used................... 0 Total pages used...................... 285 Unused bytes on index pages........... 1817 44.4% Unused bytes on primary pages......... 11868 4.1% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 13685 4.7% *** Table TABLE_6 and all its indices ***************************** Percentage of total database.......... 2.3% Number of entries..................... 1220 Bytes of storage consumed............. 220160 Bytes of payload...................... 204763 93.0% Average payload per entry............. 167.84 Average unused bytes per entry........ 5.49 Average fanout........................ 68.00 Fragmentation......................... 3.3% Maximum payload per entry............. 333 Entries that use overflow............. 0 0.0% Index pages used...................... 3 Primary pages used.................... 212 Overflow pages used................... 0 Total pages used...................... 215 Unused bytes on index pages........... 1454 47.3% Unused bytes on primary pages......... 5238 2.4% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 6692 3.0% *** Table TABLE_6 w/o any indices ********************************* Percentage of total database.......... 2.3% Number of entries..................... 610 Bytes of storage consumed............. 211968 Bytes of payload...................... 200747 94.7% Average payload per entry............. 329.09 Average unused bytes per entry........ 7.28 Average fanout........................ 68.00 Fragmentation......................... 2.4% Maximum payload per entry............. 333 Entries that use overflow............. 0 0.0% Index pages used...................... 3 Primary pages used.................... 204 Overflow pages used................... 0 Total pages used...................... 207 Unused bytes on index pages........... 1454 47.3% Unused bytes on primary pages......... 2984 1.4% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 4438 2.1% *** Indices of table TABLE_6 ************************************** Percentage of total database.......... 0.087% Number of entries..................... 610 Bytes of storage consumed............. 8192 Bytes of payload...................... 4016 49.0% Average payload per entry............. 6.58 Average unused bytes per entry........ 3.70 Fragmentation......................... 28.6% Maximum payload per entry............. 7 Entries that use overflow............. 0 0.0% Primary pages used.................... 8 Overflow pages used................... 0 Total pages used...................... 8 Unused bytes on primary pages......... 2254 27.5% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 2254 27.5% *** Table TABLE_7 and all its indices ******************************* Percentage of total database.......... 2.0% Number of entries..................... 3130 Bytes of storage consumed............. 191488 Bytes of payload...................... 162833 85.0% Average payload per entry............. 52.02 Average unused bytes per entry........ 4.28 Average fanout........................ 53.00 Fragmentation......................... 5.4% Maximum payload per entry............. 114 Entries that use overflow............. 0 0.0% Index pages used...................... 3 Primary pages used.................... 184 Overflow pages used................... 0 Total pages used...................... 187 Unused bytes on index pages........... 1785 58.1% Unused bytes on primary pages......... 11622 6.2% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 13407 7.0% *** Table TABLE_7 w/o any indices *********************************** Percentage of total database.......... 1.8% Number of entries..................... 1565 Bytes of storage consumed............. 165888 Bytes of payload...................... 145644 87.8% Average payload per entry............. 93.06 Average unused bytes per entry........ 6.38 Average fanout........................ 53.00 Fragmentation......................... 3.1% Maximum payload per entry............. 114 Entries that use overflow............. 0 0.0% Index pages used...................... 3 Primary pages used.................... 159 Overflow pages used................... 0 Total pages used...................... 162 Unused bytes on index pages........... 1785 58.1% Unused bytes on primary pages......... 8202 5.0% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 9987 6.0% *** Indices of table TABLE_7 **************************************** Percentage of total database.......... 0.27% Number of entries..................... 1565 Bytes of storage consumed............. 25600 Bytes of payload...................... 17189 67.1% Average payload per entry............. 10.98 Average unused bytes per entry........ 2.19 Fragmentation......................... 20.8% Maximum payload per entry............. 12 Entries that use overflow............. 0 0.0% Primary pages used.................... 25 Overflow pages used................... 0 Total pages used...................... 25 Unused bytes on primary pages......... 3420 13.4% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 3420 13.4% *** Table TABLE_8 and all its indices ********************************** Percentage of total database.......... 0.92% Number of entries..................... 1156 Bytes of storage consumed............. 86016 Bytes of payload...................... 73579 85.5% Average payload per entry............. 63.65 Average unused bytes per entry........ 5.71 Average fanout........................ 76.00 Fragmentation......................... 3.6% Maximum payload per entry............. 138 Entries that use overflow............. 0 0.0% Index pages used...................... 1 Primary pages used.................... 83 Overflow pages used................... 0 Total pages used...................... 84 Unused bytes on index pages........... 428 41.8% Unused bytes on primary pages......... 6173 7.3% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 6601 7.7% *** Table TABLE_8 w/o any indices ************************************** Percentage of total database.......... 0.84% Number of entries..................... 578 Bytes of storage consumed............. 78848 Bytes of payload...................... 69616 88.3% Average payload per entry............. 120.44 Average unused bytes per entry........ 9.01 Average fanout........................ 76.00 Fragmentation......................... 1.3% Maximum payload per entry............. 138 Entries that use overflow............. 0 0.0% Index pages used...................... 1 Primary pages used.................... 76 Overflow pages used................... 0 Total pages used...................... 77 Unused bytes on index pages........... 428 41.8% Unused bytes on primary pages......... 4782 6.1% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 5210 6.6% *** Indices of table TABLE_8 ******************************************* Percentage of total database.......... 0.076% Number of entries..................... 578 Bytes of storage consumed............. 7168 Bytes of payload...................... 3963 55.3% Average payload per entry............. 6.86 Average unused bytes per entry........ 2.41 Fragmentation......................... 33.3% Maximum payload per entry............. 8 Entries that use overflow............. 0 0.0% Primary pages used.................... 7 Overflow pages used................... 0 Total pages used...................... 7 Unused bytes on primary pages......... 1391 19.4% Unused bytes on overflow pages........ 0 Unused bytes on all pages............. 1391 19.4% ************************************************************************ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp Sent: Tuesday, August 19, 2008 3:43 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Reducing SQLite Memory footprint(!) On Aug 19, 2008, at 6:24 PM, Brown, Daniel wrote: > Good afternoon list, > > Are there any known techniques for reducing SQLite's memory footprint? Construct your database into a file (say "test.db"). Then run the sqlite3_analyzer utility (available for download on the SQLite website) over that file and post the results. The sqlite3_analyzer utility will give us additional information that might suggest ways of reducing the size of the database file. See also http://www.hwaci.com/sw/sqlite/prosupport.html#compress D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
