usr/src/lib/libsqlite/tool/space_used.tcl

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