1 # Run this TCL script using "testfixture" in order get a report that shows 2 # how much disk space is used by a particular data to actually store data 3 # versus how much space is unused. 4 # 5 6 # Get the name of the database to analyze 7 # 8 if {[llength $argv]!=1} { 9 puts stderr "Usage: $argv0 database-name" 10 exit 1 11 } 12 set file_to_analyze [lindex $argv 0] 13 14 # Open the database 15 # 16 sqlite db [lindex $argv 0] 17 set DB [btree_open [lindex $argv 0]] 18 19 # Output the schema for the generated report 20 # 21 puts \ 22 {BEGIN; 23 CREATE TABLE space_used( 24 name clob, -- Name of a table or index in the database file 25 is_index boolean, -- TRUE if it is an index, false for a table 26 payload int, -- Total amount of data stored in this table or index 27 pri_pages int, -- Number of primary pages used 28 ovfl_pages int, -- Number of overflow pages used 29 pri_unused int, -- Number of unused bytes on primary pages 30 ovfl_unused int -- Number of unused bytes on overflow pages 31 );} 32 33 # This query will be used to find the root page number for every index and 34 # table in the database. 35 # 36 set sql { 37 SELECT name, type, rootpage FROM sqlite_master 38 UNION ALL 39 SELECT 'sqlite_master', 'table', 2 40 ORDER BY 1 41 } 42 43 # Initialize variables used for summary statistics. 44 # 45 set total_size 0 46 set total_primary 0 47 set total_overflow 0 48 set total_unused_primary 0 49 set total_unused_ovfl 0 50 51 # Analyze every table in the database, one at a time. 52 # 53 foreach {name type rootpage} [db eval $sql] { 54 set cursor [btree_cursor $DB $rootpage 0] 55 set go [btree_first $cursor] 56 set size 0 57 catch {unset pg_used} 58 set unused_ovfl 0 59 set n_overflow 0 60 while {$go==0} { 61 set payload [btree_payload_size $cursor] 62 incr size $payload 63 set stat [btree_cursor_dump $cursor] 64 set pgno [lindex $stat 0] 65 set freebytes [lindex $stat 4] 66 set pg_used($pgno) $freebytes 67 if {$payload>238} { 68 set n [expr {($payload-238+1019)/1020}] 69 incr n_overflow $n 70 incr unused_ovfl [expr {$n*1020+238-$payload}] 71 } 72 set go [btree_next $cursor] 73 } 74 btree_close_cursor $cursor 75 set n_primary [llength [array names pg_used]] 76 set unused_primary 0 77 foreach x [array names pg_used] {incr unused_primary $pg_used($x)} 78 regsub -all ' $name '' name 79 puts -nonewline "INSERT INTO space_used VALUES('$name'" 80 puts -nonewline ",[expr {$type=="index"}]" 81 puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);" 82 incr total_size $size 83 incr total_primary $n_primary 84 incr total_overflow $n_overflow 85 incr total_unused_primary $unused_primary 86 incr total_unused_ovfl $unused_ovfl 87 } 88 89 # Output summary statistics: 90 # 91 puts "-- Total payload size: $total_size" 92 puts "-- Total pages used: $total_primary primary and $total_overflow overflow" 93 set file_pgcnt [expr {[file size [lindex $argv 0]]/1024}] 94 puts -nonewline "-- Total unused bytes on primary pages: $total_unused_primary" 95 if {$total_primary>0} { 96 set upp [expr {$total_unused_primary/$total_primary}] 97 puts " (avg $upp bytes/page)" 98 } else { 99 puts "" 100 } 101 puts -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl" 102 if {$total_overflow>0} { 103 set upp [expr {$total_unused_ovfl/$total_overflow}] 104 puts " (avg $upp bytes/page)" 105 } else { 106 puts "" 107 } 108 set n_free [expr {$file_pgcnt-$total_primary-$total_overflow}] 109 if {$n_free>0} {incr n_free -1} 110 puts "-- Total pages on freelist: $n_free" 111 puts "COMMIT;" 112
