space_used.tcl revision 2
2N/A
2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A
2N/A# Run this TCL script using "testfixture" in order get a report that shows
2N/A# how much disk space is used by a particular data to actually store data
2N/A# versus how much space is unused.
2N/A#
2N/A
2N/A# Get the name of the database to analyze
2N/A#
2N/Aif {[llength $argv]!=1} {
2N/A puts stderr "Usage: $argv0 database-name"
2N/A exit 1
2N/A}
2N/Aset file_to_analyze [lindex $argv 0]
2N/A
2N/A# Open the database
2N/A#
2N/Asqlite db [lindex $argv 0]
2N/Aset DB [btree_open [lindex $argv 0]]
2N/A
2N/A# Output the schema for the generated report
2N/A#
2N/Aputs \
2N/A{BEGIN;
2N/ACREATE TABLE space_used(
2N/A name clob, -- Name of a table or index in the database file
2N/A is_index boolean, -- TRUE if it is an index, false for a table
2N/A payload int, -- Total amount of data stored in this table or index
2N/A pri_pages int, -- Number of primary pages used
2N/A ovfl_pages int, -- Number of overflow pages used
2N/A pri_unused int, -- Number of unused bytes on primary pages
2N/A ovfl_unused int -- Number of unused bytes on overflow pages
2N/A);}
2N/A
2N/A# This query will be used to find the root page number for every index and
2N/A# table in the database.
2N/A#
2N/Aset sql {
2N/A SELECT name, type, rootpage FROM sqlite_master
2N/A UNION ALL
2N/A SELECT 'sqlite_master', 'table', 2
2N/A ORDER BY 1
2N/A}
2N/A
2N/A# Initialize variables used for summary statistics.
2N/A#
2N/Aset total_size 0
2N/Aset total_primary 0
2N/Aset total_overflow 0
2N/Aset total_unused_primary 0
2N/Aset total_unused_ovfl 0
2N/A
2N/A# Analyze every table in the database, one at a time.
2N/A#
2N/Aforeach {name type rootpage} [db eval $sql] {
2N/A set cursor [btree_cursor $DB $rootpage 0]
2N/A set go [btree_first $cursor]
2N/A set size 0
2N/A catch {unset pg_used}
2N/A set unused_ovfl 0
2N/A set n_overflow 0
2N/A while {$go==0} {
2N/A set payload [btree_payload_size $cursor]
2N/A incr size $payload
2N/A set stat [btree_cursor_dump $cursor]
2N/A set pgno [lindex $stat 0]
2N/A set freebytes [lindex $stat 4]
2N/A set pg_used($pgno) $freebytes
2N/A if {$payload>238} {
2N/A set n [expr {($payload-238+1019)/1020}]
2N/A incr n_overflow $n
2N/A incr unused_ovfl [expr {$n*1020+238-$payload}]
2N/A }
2N/A set go [btree_next $cursor]
2N/A }
2N/A btree_close_cursor $cursor
2N/A set n_primary [llength [array names pg_used]]
2N/A set unused_primary 0
2N/A foreach x [array names pg_used] {incr unused_primary $pg_used($x)}
2N/A regsub -all ' $name '' name
2N/A puts -nonewline "INSERT INTO space_used VALUES('$name'"
2N/A puts -nonewline ",[expr {$type=="index"}]"
2N/A puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);"
2N/A incr total_size $size
2N/A incr total_primary $n_primary
2N/A incr total_overflow $n_overflow
2N/A incr total_unused_primary $unused_primary
2N/A incr total_unused_ovfl $unused_ovfl
2N/A}
2N/A
2N/A# Output summary statistics:
2N/A#
2N/Aputs "-- Total payload size: $total_size"
2N/Aputs "-- Total pages used: $total_primary primary and $total_overflow overflow"
2N/Aset file_pgcnt [expr {[file size [lindex $argv 0]]/1024}]
2N/Aputs -nonewline "-- Total unused bytes on primary pages: $total_unused_primary"
2N/Aif {$total_primary>0} {
2N/A set upp [expr {$total_unused_primary/$total_primary}]
2N/A puts " (avg $upp bytes/page)"
2N/A} else {
2N/A puts ""
2N/A}
2N/Aputs -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl"
2N/Aif {$total_overflow>0} {
2N/A set upp [expr {$total_unused_ovfl/$total_overflow}]
2N/A puts " (avg $upp bytes/page)"
2N/A} else {
2N/A puts ""
2N/A}
2N/Aset n_free [expr {$file_pgcnt-$total_primary-$total_overflow}]
2N/Aif {$n_free>0} {incr n_free -1}
2N/Aputs "-- Total pages on freelist: $n_free"
2N/Aputs "COMMIT;"