Return to space_used.tcl CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool

sqlite3

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