17c478bd9Sstevel@tonic-gate# Run this TCL script using "testfixture" in order get a report that shows 27c478bd9Sstevel@tonic-gate# how much disk space is used by a particular data to actually store data 37c478bd9Sstevel@tonic-gate# versus how much space is unused. 47c478bd9Sstevel@tonic-gate# 57c478bd9Sstevel@tonic-gate 67c478bd9Sstevel@tonic-gate# Get the name of the database to analyze 77c478bd9Sstevel@tonic-gate# 87c478bd9Sstevel@tonic-gateif {[llength $argv]!=1} { 97c478bd9Sstevel@tonic-gate puts stderr "Usage: $argv0 database-name" 107c478bd9Sstevel@tonic-gate exit 1 117c478bd9Sstevel@tonic-gate} 127c478bd9Sstevel@tonic-gateset file_to_analyze [lindex $argv 0] 137c478bd9Sstevel@tonic-gate 147c478bd9Sstevel@tonic-gate# Open the database 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gatesqlite db [lindex $argv 0] 177c478bd9Sstevel@tonic-gateset DB [btree_open [lindex $argv 0]] 187c478bd9Sstevel@tonic-gate 197c478bd9Sstevel@tonic-gate# Output the schema for the generated report 207c478bd9Sstevel@tonic-gate# 217c478bd9Sstevel@tonic-gateputs \ 227c478bd9Sstevel@tonic-gate{BEGIN; 237c478bd9Sstevel@tonic-gateCREATE TABLE space_used( 247c478bd9Sstevel@tonic-gate name clob, -- Name of a table or index in the database file 257c478bd9Sstevel@tonic-gate is_index boolean, -- TRUE if it is an index, false for a table 267c478bd9Sstevel@tonic-gate payload int, -- Total amount of data stored in this table or index 277c478bd9Sstevel@tonic-gate pri_pages int, -- Number of primary pages used 287c478bd9Sstevel@tonic-gate ovfl_pages int, -- Number of overflow pages used 297c478bd9Sstevel@tonic-gate pri_unused int, -- Number of unused bytes on primary pages 307c478bd9Sstevel@tonic-gate ovfl_unused int -- Number of unused bytes on overflow pages 317c478bd9Sstevel@tonic-gate);} 327c478bd9Sstevel@tonic-gate 337c478bd9Sstevel@tonic-gate# This query will be used to find the root page number for every index and 347c478bd9Sstevel@tonic-gate# table in the database. 357c478bd9Sstevel@tonic-gate# 367c478bd9Sstevel@tonic-gateset sql { 377c478bd9Sstevel@tonic-gate SELECT name, type, rootpage FROM sqlite_master 387c478bd9Sstevel@tonic-gate UNION ALL 397c478bd9Sstevel@tonic-gate SELECT 'sqlite_master', 'table', 2 407c478bd9Sstevel@tonic-gate ORDER BY 1 417c478bd9Sstevel@tonic-gate} 427c478bd9Sstevel@tonic-gate 437c478bd9Sstevel@tonic-gate# Initialize variables used for summary statistics. 447c478bd9Sstevel@tonic-gate# 457c478bd9Sstevel@tonic-gateset total_size 0 467c478bd9Sstevel@tonic-gateset total_primary 0 477c478bd9Sstevel@tonic-gateset total_overflow 0 487c478bd9Sstevel@tonic-gateset total_unused_primary 0 497c478bd9Sstevel@tonic-gateset total_unused_ovfl 0 507c478bd9Sstevel@tonic-gate 517c478bd9Sstevel@tonic-gate# Analyze every table in the database, one at a time. 527c478bd9Sstevel@tonic-gate# 537c478bd9Sstevel@tonic-gateforeach {name type rootpage} [db eval $sql] { 547c478bd9Sstevel@tonic-gate set cursor [btree_cursor $DB $rootpage 0] 557c478bd9Sstevel@tonic-gate set go [btree_first $cursor] 567c478bd9Sstevel@tonic-gate set size 0 577c478bd9Sstevel@tonic-gate catch {unset pg_used} 587c478bd9Sstevel@tonic-gate set unused_ovfl 0 597c478bd9Sstevel@tonic-gate set n_overflow 0 607c478bd9Sstevel@tonic-gate while {$go==0} { 617c478bd9Sstevel@tonic-gate set payload [btree_payload_size $cursor] 627c478bd9Sstevel@tonic-gate incr size $payload 637c478bd9Sstevel@tonic-gate set stat [btree_cursor_dump $cursor] 647c478bd9Sstevel@tonic-gate set pgno [lindex $stat 0] 657c478bd9Sstevel@tonic-gate set freebytes [lindex $stat 4] 667c478bd9Sstevel@tonic-gate set pg_used($pgno) $freebytes 677c478bd9Sstevel@tonic-gate if {$payload>238} { 687c478bd9Sstevel@tonic-gate set n [expr {($payload-238+1019)/1020}] 697c478bd9Sstevel@tonic-gate incr n_overflow $n 707c478bd9Sstevel@tonic-gate incr unused_ovfl [expr {$n*1020+238-$payload}] 717c478bd9Sstevel@tonic-gate } 727c478bd9Sstevel@tonic-gate set go [btree_next $cursor] 737c478bd9Sstevel@tonic-gate } 747c478bd9Sstevel@tonic-gate btree_close_cursor $cursor 757c478bd9Sstevel@tonic-gate set n_primary [llength [array names pg_used]] 767c478bd9Sstevel@tonic-gate set unused_primary 0 777c478bd9Sstevel@tonic-gate foreach x [array names pg_used] {incr unused_primary $pg_used($x)} 787c478bd9Sstevel@tonic-gate regsub -all ' $name '' name 797c478bd9Sstevel@tonic-gate puts -nonewline "INSERT INTO space_used VALUES('$name'" 807c478bd9Sstevel@tonic-gate puts -nonewline ",[expr {$type=="index"}]" 817c478bd9Sstevel@tonic-gate puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);" 827c478bd9Sstevel@tonic-gate incr total_size $size 837c478bd9Sstevel@tonic-gate incr total_primary $n_primary 847c478bd9Sstevel@tonic-gate incr total_overflow $n_overflow 857c478bd9Sstevel@tonic-gate incr total_unused_primary $unused_primary 867c478bd9Sstevel@tonic-gate incr total_unused_ovfl $unused_ovfl 877c478bd9Sstevel@tonic-gate} 887c478bd9Sstevel@tonic-gate 897c478bd9Sstevel@tonic-gate# Output summary statistics: 907c478bd9Sstevel@tonic-gate# 917c478bd9Sstevel@tonic-gateputs "-- Total payload size: $total_size" 927c478bd9Sstevel@tonic-gateputs "-- Total pages used: $total_primary primary and $total_overflow overflow" 937c478bd9Sstevel@tonic-gateset file_pgcnt [expr {[file size [lindex $argv 0]]/1024}] 947c478bd9Sstevel@tonic-gateputs -nonewline "-- Total unused bytes on primary pages: $total_unused_primary" 957c478bd9Sstevel@tonic-gateif {$total_primary>0} { 967c478bd9Sstevel@tonic-gate set upp [expr {$total_unused_primary/$total_primary}] 977c478bd9Sstevel@tonic-gate puts " (avg $upp bytes/page)" 987c478bd9Sstevel@tonic-gate} else { 997c478bd9Sstevel@tonic-gate puts "" 1007c478bd9Sstevel@tonic-gate} 1017c478bd9Sstevel@tonic-gateputs -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl" 1027c478bd9Sstevel@tonic-gateif {$total_overflow>0} { 1037c478bd9Sstevel@tonic-gate set upp [expr {$total_unused_ovfl/$total_overflow}] 1047c478bd9Sstevel@tonic-gate puts " (avg $upp bytes/page)" 1057c478bd9Sstevel@tonic-gate} else { 1067c478bd9Sstevel@tonic-gate puts "" 1077c478bd9Sstevel@tonic-gate} 1087c478bd9Sstevel@tonic-gateset n_free [expr {$file_pgcnt-$total_primary-$total_overflow}] 1097c478bd9Sstevel@tonic-gateif {$n_free>0} {incr n_free -1} 1107c478bd9Sstevel@tonic-gateputs "-- Total pages on freelist: $n_free" 1117c478bd9Sstevel@tonic-gateputs "COMMIT;" 112