OLD | NEW |
1 # Run this TCL script using "testfixture" in order get a report that shows | 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 | 2 # how much disk space is used by a particular data to actually store data |
3 # versus how much space is unused. | 3 # versus how much space is unused. |
4 # | 4 # |
5 | 5 |
6 if {[catch { | 6 if {[catch { |
7 | 7 |
8 # Argument $tname is the name of a table within the database opened by | 8 # Argument $tname is the name of a table within the database opened by |
9 # database handle [db]. Return true if it is a WITHOUT ROWID table, or | 9 # database handle [db]. Return true if it is a WITHOUT ROWID table, or |
10 # false otherwise. | 10 # false otherwise. |
11 # | 11 # |
12 proc is_without_rowid {tname} { | 12 proc is_without_rowid {tname} { |
13 set t [string map {' ''} $tname] | 13 set t [string map {' ''} $tname] |
14 db eval "PRAGMA index_list = '$t'" o { | 14 db eval "PRAGMA index_list = '$t'" o { |
15 if {$o(origin) == "pk"} { | 15 if {$o(origin) == "pk"} { |
16 set n $o(name) | 16 set n $o(name) |
17 if {0==[db one { SELECT count(*) FROM sqlite_master WHERE name=$n }]} { | 17 if {0==[db one { SELECT count(*) FROM sqlite_master WHERE name=$n }]} { |
18 return 1 | 18 return 1 |
19 } | 19 } |
20 } | 20 } |
21 } | 21 } |
22 return 0 | 22 return 0 |
23 } | 23 } |
24 | 24 |
| 25 # Read and run TCL commands from standard input. Used to implement |
| 26 # the --tclsh option. |
| 27 # |
| 28 proc tclsh {} { |
| 29 set line {} |
| 30 while {![eof stdin]} { |
| 31 if {$line!=""} { |
| 32 puts -nonewline "> " |
| 33 } else { |
| 34 puts -nonewline "% " |
| 35 } |
| 36 flush stdout |
| 37 append line [gets stdin] |
| 38 if {[info complete $line]} { |
| 39 if {[catch {uplevel #0 $line} result]} { |
| 40 puts stderr "Error: $result" |
| 41 } elseif {$result!=""} { |
| 42 puts $result |
| 43 } |
| 44 set line {} |
| 45 } else { |
| 46 append line \n |
| 47 } |
| 48 } |
| 49 } |
| 50 |
| 51 |
25 # Get the name of the database to analyze | 52 # Get the name of the database to analyze |
26 # | 53 # |
27 proc usage {} { | 54 proc usage {} { |
28 set argv0 [file rootname [file tail [info nameofexecutable]]] | 55 set argv0 [file rootname [file tail [info nameofexecutable]]] |
29 puts stderr "Usage: $argv0 ?--pageinfo? ?--stats? database-filename" | 56 puts stderr "Usage: $argv0 ?--pageinfo? ?--stats? database-filename" |
30 puts stderr { | 57 puts stderr { |
31 Analyze the SQLite3 database file specified by the "database-filename" | 58 Analyze the SQLite3 database file specified by the "database-filename" |
32 argument and output a report detailing size and storage efficiency | 59 argument and output a report detailing size and storage efficiency |
33 information for the database and its constituent tables and indexes. | 60 information for the database and its constituent tables and indexes. |
34 | 61 |
35 Options: | 62 Options: |
36 | 63 |
37 --stats Output SQL text that creates a new database containing | 64 --pageinfo Show how each page of the database-file is used |
38 statistics about the database that was analyzed | |
39 | 65 |
40 --pageinfo Show how each page of the database-file is used | 66 --stats Output SQL text that creates a new database containing |
| 67 statistics about the database that was analyzed |
| 68 |
| 69 --tclsh Run the built-in TCL interpreter interactively (for debugging) |
| 70 |
| 71 --version Show the version number of SQLite |
41 } | 72 } |
42 exit 1 | 73 exit 1 |
43 } | 74 } |
44 set file_to_analyze {} | 75 set file_to_analyze {} |
45 set flags(-pageinfo) 0 | 76 set flags(-pageinfo) 0 |
46 set flags(-stats) 0 | 77 set flags(-stats) 0 |
| 78 set flags(-debug) 0 |
47 append argv {} | 79 append argv {} |
48 foreach arg $argv { | 80 foreach arg $argv { |
49 if {[regexp {^-+pageinfo$} $arg]} { | 81 if {[regexp {^-+pageinfo$} $arg]} { |
50 set flags(-pageinfo) 1 | 82 set flags(-pageinfo) 1 |
51 } elseif {[regexp {^-+stats$} $arg]} { | 83 } elseif {[regexp {^-+stats$} $arg]} { |
52 set flags(-stats) 1 | 84 set flags(-stats) 1 |
| 85 } elseif {[regexp {^-+debug$} $arg]} { |
| 86 set flags(-debug) 1 |
| 87 } elseif {[regexp {^-+tclsh$} $arg]} { |
| 88 tclsh |
| 89 exit 0 |
| 90 } elseif {[regexp {^-+version$} $arg]} { |
| 91 sqlite3 mem :memory: |
| 92 puts [mem one {SELECT sqlite_version()||' '||sqlite_source_id()}] |
| 93 mem close |
| 94 exit 0 |
53 } elseif {[regexp {^-} $arg]} { | 95 } elseif {[regexp {^-} $arg]} { |
54 puts stderr "Unknown option: $arg" | 96 puts stderr "Unknown option: $arg" |
55 usage | 97 usage |
56 } elseif {$file_to_analyze!=""} { | 98 } elseif {$file_to_analyze!=""} { |
57 usage | 99 usage |
58 } else { | 100 } else { |
59 set file_to_analyze $arg | 101 set file_to_analyze $arg |
60 } | 102 } |
61 } | 103 } |
62 if {$file_to_analyze==""} usage | 104 if {$file_to_analyze==""} usage |
(...skipping 30 matching lines...) Expand all Loading... |
93 incr true_file_size [file size $f] | 135 incr true_file_size [file size $f] |
94 } | 136 } |
95 } | 137 } |
96 | 138 |
97 # Open the database | 139 # Open the database |
98 # | 140 # |
99 if {[catch {sqlite3 db $file_to_analyze -uri 1} msg]} { | 141 if {[catch {sqlite3 db $file_to_analyze -uri 1} msg]} { |
100 puts stderr "error trying to open $file_to_analyze: $msg" | 142 puts stderr "error trying to open $file_to_analyze: $msg" |
101 exit 1 | 143 exit 1 |
102 } | 144 } |
| 145 if {$flags(-debug)} { |
| 146 proc dbtrace {txt} {puts $txt; flush stdout;} |
| 147 db trace ::dbtrace |
| 148 } |
103 | 149 |
104 db eval {SELECT count(*) FROM sqlite_master} | 150 db eval {SELECT count(*) FROM sqlite_master} |
105 set pageSize [expr {wide([db one {PRAGMA page_size}])}] | 151 set pageSize [expr {wide([db one {PRAGMA page_size}])}] |
106 | 152 |
107 if {$flags(-pageinfo)} { | 153 if {$flags(-pageinfo)} { |
108 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} | 154 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} |
109 db eval {SELECT name, path, pageno FROM temp.stat ORDER BY pageno} { | 155 db eval {SELECT name, path, pageno FROM temp.stat ORDER BY pageno} { |
110 puts "$pageno $name $path" | 156 puts "$pageno $name $path" |
111 } | 157 } |
112 exit 0 | 158 exit 0 |
(...skipping 22 matching lines...) Expand all Loading... |
135 quote(unused) || ',' || | 181 quote(unused) || ',' || |
136 quote(mx_payload) || ',' || | 182 quote(mx_payload) || ',' || |
137 quote(pgoffset) || ',' || | 183 quote(pgoffset) || ',' || |
138 quote(pgsize) AS x FROM stat} { | 184 quote(pgsize) AS x FROM stat} { |
139 puts "INSERT INTO stats VALUES($x);" | 185 puts "INSERT INTO stats VALUES($x);" |
140 } | 186 } |
141 puts "COMMIT;" | 187 puts "COMMIT;" |
142 exit 0 | 188 exit 0 |
143 } | 189 } |
144 | 190 |
| 191 |
145 # In-memory database for collecting statistics. This script loops through | 192 # In-memory database for collecting statistics. This script loops through |
146 # the tables and indices in the database being analyzed, adding a row for each | 193 # the tables and indices in the database being analyzed, adding a row for each |
147 # to an in-memory database (for which the schema is shown below). It then | 194 # to an in-memory database (for which the schema is shown below). It then |
148 # queries the in-memory db to produce the space-analysis report. | 195 # queries the in-memory db to produce the space-analysis report. |
149 # | 196 # |
150 sqlite3 mem :memory: | 197 sqlite3 mem :memory: |
| 198 if {$flags(-debug)} { |
| 199 proc dbtrace {txt} {puts $txt; flush stdout;} |
| 200 mem trace ::dbtrace |
| 201 } |
151 set tabledef {CREATE TABLE space_used( | 202 set tabledef {CREATE TABLE space_used( |
152 name clob, -- Name of a table or index in the database file | 203 name clob, -- Name of a table or index in the database file |
153 tblname clob, -- Name of associated table | 204 tblname clob, -- Name of associated table |
154 is_index boolean, -- TRUE if it is an index, false for a table | 205 is_index boolean, -- TRUE if it is an index, false for a table |
| 206 is_without_rowid boolean, -- TRUE if WITHOUT ROWID table |
155 nentry int, -- Number of entries in the BTree | 207 nentry int, -- Number of entries in the BTree |
156 leaf_entries int, -- Number of leaf entries | 208 leaf_entries int, -- Number of leaf entries |
157 depth int, -- Depth of the b-tree | 209 depth int, -- Depth of the b-tree |
158 payload int, -- Total amount of data stored in this table or index | 210 payload int, -- Total amount of data stored in this table or index |
159 ovfl_payload int, -- Total amount of data stored on overflow pages | 211 ovfl_payload int, -- Total amount of data stored on overflow pages |
160 ovfl_cnt int, -- Number of entries that use overflow | 212 ovfl_cnt int, -- Number of entries that use overflow |
161 mx_payload int, -- Maximum payload size | 213 mx_payload int, -- Maximum payload size |
162 int_pages int, -- Number of interior pages used | 214 int_pages int, -- Number of interior pages used |
163 leaf_pages int, -- Number of leaf pages used | 215 leaf_pages int, -- Number of leaf pages used |
164 ovfl_pages int, -- Number of overflow pages used | 216 ovfl_pages int, -- Number of overflow pages used |
(...skipping 12 matching lines...) Expand all Loading... |
177 ORDER BY name, path} | 229 ORDER BY name, path} |
178 db eval {DROP TABLE temp.stat} | 230 db eval {DROP TABLE temp.stat} |
179 | 231 |
180 set isCompressed 0 | 232 set isCompressed 0 |
181 set compressOverhead 0 | 233 set compressOverhead 0 |
182 set depth 0 | 234 set depth 0 |
183 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } | 235 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } |
184 foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { | 236 foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { |
185 | 237 |
186 set is_index [expr {$name!=$tblname}] | 238 set is_index [expr {$name!=$tblname}] |
187 set idx_btree [expr {$is_index || [is_without_rowid $name]}] | 239 set is_without_rowid [is_without_rowid $name] |
188 db eval { | 240 db eval { |
189 SELECT | 241 SELECT |
190 sum(ncell) AS nentry, | 242 sum(ncell) AS nentry, |
191 sum((pagetype=='leaf')*ncell) AS leaf_entries, | 243 sum((pagetype=='leaf')*ncell) AS leaf_entries, |
192 sum(payload) AS payload, | 244 sum(payload) AS payload, |
193 sum((pagetype=='overflow') * payload) AS ovfl_payload, | 245 sum((pagetype=='overflow') * payload) AS ovfl_payload, |
194 sum(path LIKE '%+000000') AS ovfl_cnt, | 246 sum(path LIKE '%+000000') AS ovfl_cnt, |
195 max(mx_payload) AS mx_payload, | 247 max(mx_payload) AS mx_payload, |
196 sum(pagetype=='internal') AS int_pages, | 248 sum(pagetype=='internal') AS int_pages, |
197 sum(pagetype=='leaf') AS leaf_pages, | 249 sum(pagetype=='leaf') AS leaf_pages, |
(...skipping 30 matching lines...) Expand all Loading... |
228 if {$prev>0 && $pagetype=="leaf" && $pageno!=$prev+1} { | 280 if {$prev>0 && $pagetype=="leaf" && $pageno!=$prev+1} { |
229 incr gap_cnt | 281 incr gap_cnt |
230 } | 282 } |
231 set prev $pageno | 283 set prev $pageno |
232 } | 284 } |
233 mem eval { | 285 mem eval { |
234 INSERT INTO space_used VALUES( | 286 INSERT INTO space_used VALUES( |
235 $name, | 287 $name, |
236 $tblname, | 288 $tblname, |
237 $is_index, | 289 $is_index, |
| 290 $is_without_rowid, |
238 $nentry, | 291 $nentry, |
239 $leaf_entries, | 292 $leaf_entries, |
240 $depth, | 293 $depth, |
241 $payload, | 294 $payload, |
242 $ovfl_payload, | 295 $ovfl_payload, |
243 $ovfl_cnt, | 296 $ovfl_cnt, |
244 $mx_payload, | 297 $mx_payload, |
245 $int_pages, | 298 $int_pages, |
246 $leaf_pages, | 299 $leaf_pages, |
247 $ovfl_pages, | 300 $ovfl_pages, |
(...skipping 75 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
323 # | 376 # |
324 proc subreport {title where showFrag} { | 377 proc subreport {title where showFrag} { |
325 global pageSize file_pgcnt compressOverhead | 378 global pageSize file_pgcnt compressOverhead |
326 | 379 |
327 # Query the in-memory database for the sum of various statistics | 380 # Query the in-memory database for the sum of various statistics |
328 # for the subset of tables/indices identified by the WHERE clause in | 381 # for the subset of tables/indices identified by the WHERE clause in |
329 # $where. Note that even if the WHERE clause matches no rows, the | 382 # $where. Note that even if the WHERE clause matches no rows, the |
330 # following query returns exactly one row (because it is an aggregate). | 383 # following query returns exactly one row (because it is an aggregate). |
331 # | 384 # |
332 # The results of the query are stored directly by SQLite into local | 385 # The results of the query are stored directly by SQLite into local |
333 # variables (i.e. $nentry, $nleaf etc.). | 386 # variables (i.e. $nentry, $payload etc.). |
334 # | 387 # |
335 mem eval " | 388 mem eval " |
336 SELECT | 389 SELECT |
337 int(sum(nentry)) AS nentry, | 390 int(sum( |
338 int(sum(leaf_entries)) AS nleaf, | 391 CASE WHEN (is_without_rowid OR is_index) THEN nentry |
| 392 ELSE leaf_entries |
| 393 END |
| 394 )) AS nentry, |
339 int(sum(payload)) AS payload, | 395 int(sum(payload)) AS payload, |
340 int(sum(ovfl_payload)) AS ovfl_payload, | 396 int(sum(ovfl_payload)) AS ovfl_payload, |
341 max(mx_payload) AS mx_payload, | 397 max(mx_payload) AS mx_payload, |
342 int(sum(ovfl_cnt)) as ovfl_cnt, | 398 int(sum(ovfl_cnt)) as ovfl_cnt, |
343 int(sum(leaf_pages)) AS leaf_pages, | 399 int(sum(leaf_pages)) AS leaf_pages, |
344 int(sum(int_pages)) AS int_pages, | 400 int(sum(int_pages)) AS int_pages, |
345 int(sum(ovfl_pages)) AS ovfl_pages, | 401 int(sum(ovfl_pages)) AS ovfl_pages, |
346 int(sum(leaf_unused)) AS leaf_unused, | 402 int(sum(leaf_unused)) AS leaf_unused, |
347 int(sum(int_unused)) AS int_unused, | 403 int(sum(int_unused)) AS int_unused, |
348 int(sum(ovfl_unused)) AS ovfl_unused, | 404 int(sum(ovfl_unused)) AS ovfl_unused, |
(...skipping 19 matching lines...) Expand all Loading... |
368 # avg_payload: Average payload per btree entry. | 424 # avg_payload: Average payload per btree entry. |
369 # avg_fanout: Average fanout for internal pages. | 425 # avg_fanout: Average fanout for internal pages. |
370 # avg_unused: Average unused bytes per btree entry. | 426 # avg_unused: Average unused bytes per btree entry. |
371 # ovfl_cnt_percent: Percentage of btree entries that use overflow pages. | 427 # ovfl_cnt_percent: Percentage of btree entries that use overflow pages. |
372 # | 428 # |
373 set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] | 429 set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] |
374 set total_pages_percent [percent $total_pages $file_pgcnt] | 430 set total_pages_percent [percent $total_pages $file_pgcnt] |
375 set storage [expr {$total_pages*$pageSize}] | 431 set storage [expr {$total_pages*$pageSize}] |
376 set payload_percent [percent $payload $storage {of storage consumed}] | 432 set payload_percent [percent $payload $storage {of storage consumed}] |
377 set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}] | 433 set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}] |
378 set avg_payload [divide $payload $nleaf] | 434 set avg_payload [divide $payload $nentry] |
379 set avg_unused [divide $total_unused $nleaf] | 435 set avg_unused [divide $total_unused $nentry] |
380 if {$int_pages>0} { | 436 if {$int_pages>0} { |
381 # TODO: Is this formula correct? | 437 # TODO: Is this formula correct? |
382 set nTab [mem eval " | 438 set nTab [mem eval " |
383 SELECT count(*) FROM ( | 439 SELECT count(*) FROM ( |
384 SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0 | 440 SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0 |
385 ) | 441 ) |
386 "] | 442 "] |
387 set avg_fanout [mem eval " | 443 set avg_fanout [mem eval " |
388 SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used | 444 SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used |
389 WHERE $where | 445 WHERE $where |
390 "] | 446 "] |
391 set avg_fanout [format %.2f $avg_fanout] | 447 set avg_fanout [format %.2f $avg_fanout] |
392 } | 448 } |
393 set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] | 449 set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}] |
394 | 450 |
395 # Print out the sub-report statistics. | 451 # Print out the sub-report statistics. |
396 # | 452 # |
397 statline {Percentage of total database} $total_pages_percent | 453 statline {Percentage of total database} $total_pages_percent |
398 statline {Number of entries} $nleaf | 454 statline {Number of entries} $nentry |
399 statline {Bytes of storage consumed} $storage | 455 statline {Bytes of storage consumed} $storage |
400 if {$compressed_size!=$storage} { | 456 if {$compressed_size!=$storage} { |
401 set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}] | 457 set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}] |
402 set pct [expr {$compressed_size*100.0/$storage}] | 458 set pct [expr {$compressed_size*100.0/$storage}] |
403 set pct [format {%5.1f%%} $pct] | 459 set pct [format {%5.1f%%} $pct] |
404 statline {Bytes used after compression} $compressed_size $pct | 460 statline {Bytes used after compression} $compressed_size $pct |
405 } | 461 } |
406 statline {Bytes of payload} $payload $payload_percent | 462 statline {Bytes of payload} $payload $payload_percent |
407 if {$cnt==1} {statline {B-tree depth} $depth} | 463 if {$cnt==1} {statline {B-tree depth} $depth} |
408 statline {Average payload per entry} $avg_payload | 464 statline {Average payload per entry} $avg_payload |
(...skipping 387 matching lines...) Expand 10 before | Expand all | Expand 10 after Loading... |
796 } | 852 } |
797 puts ");" | 853 puts ");" |
798 } | 854 } |
799 puts "COMMIT;" | 855 puts "COMMIT;" |
800 | 856 |
801 } err]} { | 857 } err]} { |
802 puts "ERROR: $err" | 858 puts "ERROR: $err" |
803 puts $errorInfo | 859 puts $errorInfo |
804 exit 1 | 860 exit 1 |
805 } | 861 } |
OLD | NEW |