============================================================================== "defragment.txt" CONTENTS (last updated 10 November 2011) ============================================================================== How to tell if the database for POPFile 1.1.x is fragmented How to tell if the database for POPFile 0.21.0 to 1.0.1 is fragmented Orphaned pages (only applies to POPFile 0.21.0 to 1.0.1 ?) How to defragment the SQLite (3.x or 2.x) database used by POPFile Download Links ============================================================================== How to tell if the database for POPFile 1.1.x is fragmented ============================================================================== The SQLite Database Analyser Utility's report shows many technical details, almost all of which mean nothing to me. However this report makes it easy to determine whether or not POPFile's SQLite database is fragmented. POPFile 1.1.0 (or later) uses a SQLite 3.x format database. The report created by the SQLite Database Analyser Utility includes fragmentation statistics: *** All tables and indices ******************************************* Fragmentation......................... 4.8% *** All tables ******************************************************* Fragmentation......................... 3.3% *** All indices ****************************************************** Fragmentation......................... 6.3% *** Table BUCKET_PARAMS and all its indices ************************** Fragmentation......................... 0.0% etc ============================================================================== How to tell if the database for POPFile 0.21.0 to 1.0.1 is fragmented ============================================================================== POPFile releases 0.21.0 (9 March 2004) to 1.0.1 (26 May 2008) used the older SQLite 2.x format for the database. These old databases are not compatible with software designed for use with the newer 3.x format databases. Unfortunately the 2.x version of the SQLite Database Analyser Utility does not report "Fragmentation" directly. However the report includes some information that can be used to estimate the fragmentation. The SQLite 2.x database file is organized as a series of pages. Normally there are three types of page in the database file: the header page, pages used to store the data and the "freelist" pages. When items are deleted from the database, for example when messages are deleted from the HISTORY page in the UI, the database does not shrink; instead, the pages which held the deleted data are added to the freelist so they can be reused later on. This makes the database file larger than it needs to be, but this can speed up subsequent database inserts. However eventually inserts and deletes can leave the database file structure fragmented and this slows down disk access to the database contents. When this happens the database can be defragmented to improve things. The first few lines of the "Disk-Space Utilization Report" produced by the SQLite Database Analyser indicate how much unused space is present in the database file. This can be used as a simple indication of fragmentation. The "Pages on the freelist (calculated)" entry in the analysis report contains a convenient percentage value which can be used as a very rough estimate of the amount of fragmentation: Example 1: A database which is not fragmented: Pages on the freelist (calculated).... 0 0.000% Example 2: A database which is slightly fragmented: Pages on the freelist (calculated).... 70 2.3% Having some free space in the database can speed up inserts so having a non-zero estimated fragmentation is not always a bad thing. It may be worth defragmenting the database when the estimated fragmentation value is "too high" but I have no idea what value to use for "too high" and it is possible that using this rule is not a very good idea. ============================================================================== Orphaned pages (only applies to POPFile 0.21.0 to 1.0.1 ?) ============================================================================== The SQLite 2.x database file is organized as a series of pages. Normally there are three types of page in the database file: the header page, pages used to store the data and the "freelist" pages. In some (rare?) cases the database file can contain some pages which do not belong to any of these three types of page. These "orphaned" pages are wasted space: they make the database file bigger than it should be and since they are not included in the "freelist" SQLite cannot reuse them. The POPFile SQLite Database Status Check utility's report will list any orphaned pages found, e.g. "Page 21830 is never used". The first few lines of the "Disk-Space Utilization Report" produced by the SQLite Database Analyser indicate how much unused space (freelist and orphaned pages) is present in the database file. Example 1: A database which does not contain orphaned pages: Pages in the whole file (measured).... 3084 Pages in the whole file (calculated).. 3084 Pages that store data................. 3013 97.7% Pages on the freelist (per header).... 70 2.3% Pages on the freelist (calculated).... 70 2.3% Header pages.......................... 1 0.032% The two "Pages in the whole file" values are identical. Example 2: A database which includes orphaned pages: Pages in the whole file (measured).... 34848 Pages in the whole file (calculated).. 34725 Pages that store data................. 33796 97.0% Pages on the freelist (per header).... 928 2.7% Pages on the freelist (calculated).... 1051 3.0% Header pages.......................... 1 0.003% The two "Pages in the whole file" values differ by 123, meaning that there are 123 "orphan" pages which cannot be used by SQLite to store data. In other words some space is being wasted. Defragmenting the database will reclaim this unused space. ============================================================================== How to defragment the SQLite (3.x or 2.x) database used by POPFile ============================================================================== The database can be defragmented by using the VACUUM command in the SQLite command-line utility. This command cleans the database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure. The Windows installer for POPFile creates a shortcut in POPFile's 'User Data' folder which makes it easy to do this. If you are not sure of the 'User Data' folder's location, the installer creates a shortcut which will display the location for you: Start -- Programs -- POPFile -- Support -- PFI Diagnostic utility (simple) Before you use the "Run SQLite utility" shortcut, it is recommended that you make a backup copy of the database file (the default filename is 'popfile.db') in case anything goes wrong! Double-clicking the "Run SQLite utility" shortcut in the 'User Data' folder will open a DOS-box like this: SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> Enter the command VACUUM followed by a semi-colon then press the ENTER key. You can use uppercase or lowercase for the VACUUM command. It may take a few seconds (or longer if the database is very big) but soon the "sqlite>" prompt should reappear, as shown here: SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> VACUUM; sqlite> Now enter the command .q (that is a full-stop followed by a lowercase letter Q) and press the ENTER key to exit from the utility and close the DOS-box: SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> VACUUM; sqlite> .q ============================================================================== Download Links ============================================================================== POPFile SQLite Database Analyser for Windows (approx 965 KB): http://www.sugelan.co.uk/popfile/download/test/pfidbanalyser.zip Official SQLite download page (for source and binaries for Windows & Linux): http://www.sqlite.org/download.html ============================================================================== Brian Smith My POPFile Utilities download page: http://www.sugelan.co.uk/popfile/utilities.html POPFile "Open Discussion" Forum: http://getpopfile.org/discussion/2 ============================================================================== (end)