sqlite3/tool/space_used.tcl

   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;"