summaryrefslogtreecommitdiff
path: root/tool/space_used.tcl
diff options
context:
space:
mode:
authorHans-Christoph Steiner <hans@eds.org>2012-03-30 20:42:12 -0400
committerHans-Christoph Steiner <hans@eds.org>2012-03-30 20:42:12 -0400
commit7bb481fda9ecb134804b49c2ce77ca28f7eea583 (patch)
tree31b520b9914d3e2453968abe375f2c102772c3dc /tool/space_used.tcl
Imported Upstream version 2.0.3
Diffstat (limited to 'tool/space_used.tcl')
-rw-r--r--tool/space_used.tcl111
1 files changed, 111 insertions, 0 deletions
diff --git a/tool/space_used.tcl b/tool/space_used.tcl
new file mode 100644
index 0000000..2044aa3
--- /dev/null
+++ b/tool/space_used.tcl
@@ -0,0 +1,111 @@
+# Run this TCL script using "testfixture" in order get a report that shows
+# how much disk space is used by a particular data to actually store data
+# versus how much space is unused.
+#
+
+# Get the name of the database to analyze
+#
+if {[llength $argv]!=1} {
+ puts stderr "Usage: $argv0 database-name"
+ exit 1
+}
+set file_to_analyze [lindex $argv 0]
+
+# Open the database
+#
+sqlite db [lindex $argv 0]
+set DB [btree_open [lindex $argv 0]]
+
+# Output the schema for the generated report
+#
+puts \
+{BEGIN;
+CREATE TABLE space_used(
+ name clob, -- Name of a table or index in the database file
+ is_index boolean, -- TRUE if it is an index, false for a table
+ payload int, -- Total amount of data stored in this table or index
+ pri_pages int, -- Number of primary pages used
+ ovfl_pages int, -- Number of overflow pages used
+ pri_unused int, -- Number of unused bytes on primary pages
+ ovfl_unused int -- Number of unused bytes on overflow pages
+);}
+
+# This query will be used to find the root page number for every index and
+# table in the database.
+#
+set sql {
+ SELECT name, type, rootpage FROM sqlite_master
+ UNION ALL
+ SELECT 'sqlite_master', 'table', 2
+ ORDER BY 1
+}
+
+# Initialize variables used for summary statistics.
+#
+set total_size 0
+set total_primary 0
+set total_overflow 0
+set total_unused_primary 0
+set total_unused_ovfl 0
+
+# Analyze every table in the database, one at a time.
+#
+foreach {name type rootpage} [db eval $sql] {
+ set cursor [btree_cursor $DB $rootpage 0]
+ set go [btree_first $cursor]
+ set size 0
+ catch {unset pg_used}
+ set unused_ovfl 0
+ set n_overflow 0
+ while {$go==0} {
+ set payload [btree_payload_size $cursor]
+ incr size $payload
+ set stat [btree_cursor_dump $cursor]
+ set pgno [lindex $stat 0]
+ set freebytes [lindex $stat 4]
+ set pg_used($pgno) $freebytes
+ if {$payload>238} {
+ set n [expr {($payload-238+1019)/1020}]
+ incr n_overflow $n
+ incr unused_ovfl [expr {$n*1020+238-$payload}]
+ }
+ set go [btree_next $cursor]
+ }
+ btree_close_cursor $cursor
+ set n_primary [llength [array names pg_used]]
+ set unused_primary 0
+ foreach x [array names pg_used] {incr unused_primary $pg_used($x)}
+ regsub -all ' $name '' name
+ puts -nonewline "INSERT INTO space_used VALUES('$name'"
+ puts -nonewline ",[expr {$type=="index"}]"
+ puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);"
+ incr total_size $size
+ incr total_primary $n_primary
+ incr total_overflow $n_overflow
+ incr total_unused_primary $unused_primary
+ incr total_unused_ovfl $unused_ovfl
+}
+
+# Output summary statistics:
+#
+puts "-- Total payload size: $total_size"
+puts "-- Total pages used: $total_primary primary and $total_overflow overflow"
+set file_pgcnt [expr {[file size [lindex $argv 0]]/1024}]
+puts -nonewline "-- Total unused bytes on primary pages: $total_unused_primary"
+if {$total_primary>0} {
+ set upp [expr {$total_unused_primary/$total_primary}]
+ puts " (avg $upp bytes/page)"
+} else {
+ puts ""
+}
+puts -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl"
+if {$total_overflow>0} {
+ set upp [expr {$total_unused_ovfl/$total_overflow}]
+ puts " (avg $upp bytes/page)"
+} else {
+ puts ""
+}
+set n_free [expr {$file_pgcnt-$total_primary-$total_overflow}]
+if {$n_free>0} {incr n_free -1}
+puts "-- Total pages on freelist: $n_free"
+puts "COMMIT;"