Hi Kees, On Apr 12, 2009, at 5:27 AM, Kees Nuyt wrote:
> PRAGMA freelist_count; tells you how many pages are free. > If there are many free pages, you may have a reason to > vacuum. It doesn't tell anything about the average > percentage of payload in database pages, which would be > another reason to vacuum. > For a full analysis, you'd have to run the sqlite3_analyzer > program, or incorporate part of its code in your > application. I just tried sqlite3_analyzer. It's amazing the amount of information that it displays! Excellent. One question, when I run the command I see that the fragmentation in "All tables" is greater than "All tables and indices". How can that be? The sum of all tables and their indices doesn't add up (10.6% + 7.5% ≠ 9.6%): > *** All tables and indices ******************************************* > > Percentage of total database.......... 100.0% > Number of entries..................... 79581 > Bytes of storage consumed............. 6328320 > Bytes of payload...................... 5148625 81.4% > Average payload per entry............. 64.70 > Average unused bytes per entry........ 10.88 > Average fanout........................ 254.00 > Fragmentation......................... 9.6% > Maximum payload per entry............. 5406 > Entries that use overflow............. 1 0.001% > Index pages used...................... 4 > Primary pages used.................... 1540 > Overflow pages used................... 1 > Total pages used...................... 1545 > Unused bytes on index pages........... 7470 45.6% > Unused bytes on primary pages......... 858287 13.6% > Unused bytes on overflow pages........ 0 0.0% > Unused bytes on all pages............. 865757 13.7% > > *** All tables ******************************************************* > > Percentage of total database.......... 66.4% > Number of entries..................... 16838 > Bytes of storage consumed............. 4202496 > Bytes of payload...................... 3522875 83.8% > Average payload per entry............. 209.22 > Average unused bytes per entry........ 33.26 > Average fanout........................ 254.00 > Fragmentation......................... 10.6% > Maximum payload per entry............. 5406 > Entries that use overflow............. 1 0.006% > Index pages used...................... 4 > Primary pages used.................... 1021 > Overflow pages used................... 1 > Total pages used...................... 1026 > Unused bytes on index pages........... 7470 45.6% > Unused bytes on primary pages......... 552618 13.2% > Unused bytes on overflow pages........ 0 0.0% > Unused bytes on all pages............. 560088 13.3% > > *** All indices ****************************************************** > > Percentage of total database.......... 33.6% > Number of entries..................... 62743 > Bytes of storage consumed............. 2125824 > Bytes of payload...................... 1625750 76.5% > Average payload per entry............. 25.91 > Average unused bytes per entry........ 4.87 > Fragmentation......................... 7.5% > Maximum payload per entry............. 48 > Entries that use overflow............. 0 0.0% > Primary pages used.................... 519 > Overflow pages used................... 0 > Total pages used...................... 519 > Unused bytes on primary pages......... 305669 14.4% > Unused bytes on overflow pages........ 0 > Unused bytes on all pages............. 305669 14.4% How does sqlite3_analyzer determine the fragmentation percentage of All tables/All tables + indices? This is the information I was looking for. Where can I obtain the relevant code where this fragmentation is calculated? I know I could run sqlite3_analyzer and parse the results, but incorporating the calculation in my code would be faster. Thanks Kees, -- Tito _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
