| OLD | NEW |
|---|
| (Empty) |
| 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 if {[catch { | |
| 7 # Get the name of the database to analyze | |
| 8 # | |
| 9 proc usage {} { | |
| 10 set argv0 [file rootname [file tail [info nameofexecutable]]] | |
| 11 puts stderr "Usage: $argv0 database-name" | |
| 12 exit 1 | |
| 13 } | |
| 14 set file_to_analyze {} | |
| 15 set flags(-pageinfo) 0 | |
| 16 set flags(-stats) 0 | |
| 17 append argv {} | |
| 18 foreach arg $argv { | |
| 19 if {[regexp {^-+pageinfo$} $arg]} { | |
| 20 set flags(-pageinfo) 1 | |
| 21 } elseif {[regexp {^-+stats$} $arg]} { | |
| 22 set flags(-stats) 1 | |
| 23 } elseif {[regexp {^-} $arg]} { | |
| 24 puts stderr "Unknown option: $arg" | |
| 25 usage | |
| 26 } elseif {$file_to_analyze!=""} { | |
| 27 usage | |
| 28 } else { | |
| 29 set file_to_analyze $arg | |
| 30 } | |
| 31 } | |
| 32 if {$file_to_analyze==""} usage | |
| 33 set root_filename $file_to_analyze | |
| 34 regexp {^file:(//)?([^?]*)} $file_to_analyze all x1 root_filename | |
| 35 if {![file exists $root_filename]} { | |
| 36 puts stderr "No such file: $root_filename" | |
| 37 exit 1 | |
| 38 } | |
| 39 if {![file readable $root_filename]} { | |
| 40 puts stderr "File is not readable: $root_filename" | |
| 41 exit 1 | |
| 42 } | |
| 43 set true_file_size [file size $root_filename] | |
| 44 if {$true_file_size<512} { | |
| 45 puts stderr "Empty or malformed database: $root_filename" | |
| 46 exit 1 | |
| 47 } | |
| 48 | |
| 49 # Compute the total file size assuming test_multiplexor is being used. | |
| 50 # Assume that SQLITE_ENABLE_8_3_NAMES might be enabled | |
| 51 # | |
| 52 set extension [file extension $root_filename] | |
| 53 set pattern $root_filename | |
| 54 append pattern {[0-3][0-9][0-9]} | |
| 55 foreach f [glob -nocomplain $pattern] { | |
| 56 incr true_file_size [file size $f] | |
| 57 set extension {} | |
| 58 } | |
| 59 if {[string length $extension]>=2 && [string length $extension]<=4} { | |
| 60 set pattern [file rootname $root_filename] | |
| 61 append pattern {.[0-3][0-9][0-9]} | |
| 62 foreach f [glob -nocomplain $pattern] { | |
| 63 incr true_file_size [file size $f] | |
| 64 } | |
| 65 } | |
| 66 | |
| 67 # Open the database | |
| 68 # | |
| 69 if {[catch {sqlite3 db $file_to_analyze -uri 1} msg]} { | |
| 70 puts stderr "error trying to open $file_to_analyze: $msg" | |
| 71 exit 1 | |
| 72 } | |
| 73 register_dbstat_vtab db | |
| 74 | |
| 75 db eval {SELECT count(*) FROM sqlite_master} | |
| 76 set pageSize [expr {wide([db one {PRAGMA page_size}])}] | |
| 77 | |
| 78 if {$flags(-pageinfo)} { | |
| 79 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} | |
| 80 db eval {SELECT name, path, pageno FROM temp.stat ORDER BY pageno} { | |
| 81 puts "$pageno $name $path" | |
| 82 } | |
| 83 exit 0 | |
| 84 } | |
| 85 if {$flags(-stats)} { | |
| 86 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} | |
| 87 puts "BEGIN;" | |
| 88 puts "CREATE TABLE stats(" | |
| 89 puts " name STRING, /* Name of table or index */" | |
| 90 puts " path INTEGER, /* Path to page from root */" | |
| 91 puts " pageno INTEGER, /* Page number */" | |
| 92 puts " pagetype STRING, /* 'internal', 'leaf' or 'overflow' */" | |
| 93 puts " ncell INTEGER, /* Cells on page (0 for overflow) */" | |
| 94 puts " payload INTEGER, /* Bytes of payload on this page */" | |
| 95 puts " unused INTEGER, /* Bytes of unused space on this page */" | |
| 96 puts " mx_payload INTEGER, /* Largest payload size of all cells */" | |
| 97 puts " pgoffset INTEGER, /* Offset of page in file */" | |
| 98 puts " pgsize INTEGER /* Size of the page */" | |
| 99 puts ");" | |
| 100 db eval {SELECT quote(name) || ',' || | |
| 101 quote(path) || ',' || | |
| 102 quote(pageno) || ',' || | |
| 103 quote(pagetype) || ',' || | |
| 104 quote(ncell) || ',' || | |
| 105 quote(payload) || ',' || | |
| 106 quote(unused) || ',' || | |
| 107 quote(mx_payload) || ',' || | |
| 108 quote(pgoffset) || ',' || | |
| 109 quote(pgsize) AS x FROM stat} { | |
| 110 puts "INSERT INTO stats VALUES($x);" | |
| 111 } | |
| 112 puts "COMMIT;" | |
| 113 exit 0 | |
| 114 } | |
| 115 | |
| 116 # In-memory database for collecting statistics. This script loops through | |
| 117 # the tables and indices in the database being analyzed, adding a row for each | |
| 118 # to an in-memory database (for which the schema is shown below). It then | |
| 119 # queries the in-memory db to produce the space-analysis report. | |
| 120 # | |
| 121 sqlite3 mem :memory: | |
| 122 set tabledef {CREATE TABLE space_used( | |
| 123 name clob, -- Name of a table or index in the database file | |
| 124 tblname clob, -- Name of associated table | |
| 125 is_index boolean, -- TRUE if it is an index, false for a table | |
| 126 nentry int, -- Number of entries in the BTree | |
| 127 leaf_entries int, -- Number of leaf entries | |
| 128 payload int, -- Total amount of data stored in this table or index | |
| 129 ovfl_payload int, -- Total amount of data stored on overflow pages | |
| 130 ovfl_cnt int, -- Number of entries that use overflow | |
| 131 mx_payload int, -- Maximum payload size | |
| 132 int_pages int, -- Number of interior pages used | |
| 133 leaf_pages int, -- Number of leaf pages used | |
| 134 ovfl_pages int, -- Number of overflow pages used | |
| 135 int_unused int, -- Number of unused bytes on interior pages | |
| 136 leaf_unused int, -- Number of unused bytes on primary pages | |
| 137 ovfl_unused int, -- Number of unused bytes on overflow pages | |
| 138 gap_cnt int, -- Number of gaps in the page layout | |
| 139 compressed_size int -- Total bytes stored on disk | |
| 140 );} | |
| 141 mem eval $tabledef | |
| 142 | |
| 143 # Create a temporary "dbstat" virtual table. | |
| 144 # | |
| 145 db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} | |
| 146 db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat | |
| 147 ORDER BY name, path} | |
| 148 db eval {DROP TABLE temp.stat} | |
| 149 | |
| 150 proc isleaf {pagetype is_index} { | |
| 151 return [expr {$pagetype == "leaf" || ($pagetype == "internal" && $is_index)}] | |
| 152 } | |
| 153 proc isoverflow {pagetype is_index} { | |
| 154 return [expr {$pagetype == "overflow"}] | |
| 155 } | |
| 156 proc isinternal {pagetype is_index} { | |
| 157 return [expr {$pagetype == "internal" && $is_index==0}] | |
| 158 } | |
| 159 | |
| 160 db func isleaf isleaf | |
| 161 db func isinternal isinternal | |
| 162 db func isoverflow isoverflow | |
| 163 | |
| 164 set isCompressed 0 | |
| 165 set compressOverhead 0 | |
| 166 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } | |
| 167 foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { | |
| 168 | |
| 169 set is_index [expr {$name!=$tblname}] | |
| 170 db eval { | |
| 171 SELECT | |
| 172 sum(ncell) AS nentry, | |
| 173 sum(isleaf(pagetype, $is_index) * ncell) AS leaf_entries, | |
| 174 sum(payload) AS payload, | |
| 175 sum(isoverflow(pagetype, $is_index) * payload) AS ovfl_payload, | |
| 176 sum(path LIKE '%+000000') AS ovfl_cnt, | |
| 177 max(mx_payload) AS mx_payload, | |
| 178 sum(isinternal(pagetype, $is_index)) AS int_pages, | |
| 179 sum(isleaf(pagetype, $is_index)) AS leaf_pages, | |
| 180 sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, | |
| 181 sum(isinternal(pagetype, $is_index) * unused) AS int_unused, | |
| 182 sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, | |
| 183 sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused, | |
| 184 sum(pgsize) AS compressed_size | |
| 185 FROM temp.dbstat WHERE name = $name | |
| 186 } break | |
| 187 | |
| 188 set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] | |
| 189 set storage [expr {$total_pages*$pageSize}] | |
| 190 if {!$isCompressed && $storage>$compressed_size} { | |
| 191 set isCompressed 1 | |
| 192 set compressOverhead 14 | |
| 193 } | |
| 194 | |
| 195 # Column 'gap_cnt' is set to the number of non-contiguous entries in the | |
| 196 # list of pages visited if the b-tree structure is traversed in a top-down | |
| 197 # fashion (each node visited before its child-tree is passed). Any overflow | |
| 198 # chains present are traversed from start to finish before any child-tree | |
| 199 # is. | |
| 200 # | |
| 201 set gap_cnt 0 | |
| 202 set prev 0 | |
| 203 db eval { | |
| 204 SELECT pageno, pagetype FROM temp.dbstat | |
| 205 WHERE name=$name | |
| 206 ORDER BY pageno | |
| 207 } { | |
| 208 if {$prev>0 && $pagetype=="leaf" && $pageno!=$prev+1} { | |
| 209 incr gap_cnt | |
| 210 } | |
| 211 set prev $pageno | |
| 212 } | |
| 213 mem eval { | |
| 214 INSERT INTO space_used VALUES( | |
| 215 $name, | |
| 216 $tblname, | |
| 217 $is_index, | |
| 218 $nentry, | |
| 219 $leaf_entries, | |
| 220 $payload, | |
| 221 $ovfl_payload, | |
| 222 $ovfl_cnt, | |
| 223 $mx_payload, | |
| 224 $int_pages, | |
| 225 $leaf_pages, | |
| 226 $ovfl_pages, | |
| 227 $int_unused, | |
| 228 $leaf_unused, | |
| 229 $ovfl_unused, | |
| 230 $gap_cnt, | |
| 231 $compressed_size | |
| 232 ); | |
| 233 } | |
| 234 } | |
| 235 | |
| 236 proc integerify {real} { | |
| 237 if {[string is double -strict $real]} { | |
| 238 return [expr {wide($real)}] | |
| 239 } else { | |
| 240 return 0 | |
| 241 } | |
| 242 } | |
| 243 mem function int integerify | |
| 244 | |
| 245 # Quote a string for use in an SQL query. Examples: | |
| 246 # | |
| 247 # [quote {hello world}] == {'hello world'} | |
| 248 # [quote {hello world's}] == {'hello world''s'} | |
| 249 # | |
| 250 proc quote {txt} { | |
| 251 return [string map {' ''} $txt] | |
| 252 } | |
| 253 | |
| 254 # Output a title line | |
| 255 # | |
| 256 proc titleline {title} { | |
| 257 if {$title==""} { | |
| 258 puts [string repeat * 79] | |
| 259 } else { | |
| 260 set len [string length $title] | |
| 261 set stars [string repeat * [expr 79-$len-5]] | |
| 262 puts "*** $title $stars" | |
| 263 } | |
| 264 } | |
| 265 | |
| 266 # Generate a single line of output in the statistics section of the | |
| 267 # report. | |
| 268 # | |
| 269 proc statline {title value {extra {}}} { | |
| 270 set len [string length $title] | |
| 271 set dots [string repeat . [expr 50-$len]] | |
| 272 set len [string length $value] | |
| 273 set sp2 [string range { } $len end] | |
| 274 if {$extra ne ""} { | |
| 275 set extra " $extra" | |
| 276 } | |
| 277 puts "$title$dots $value$sp2$extra" | |
| 278 } | |
| 279 | |
| 280 # Generate a formatted percentage value for $num/$denom | |
| 281 # | |
| 282 proc percent {num denom {of {}}} { | |
| 283 if {$denom==0.0} {return ""} | |
| 284 set v [expr {$num*100.0/$denom}] | |
| 285 set of {} | |
| 286 if {$v==100.0 || $v<0.001 || ($v>1.0 && $v<99.0)} { | |
| 287 return [format {%5.1f%% %s} $v $of] | |
| 288 } elseif {$v<0.1 || $v>99.9} { | |
| 289 return [format {%7.3f%% %s} $v $of] | |
| 290 } else { | |
| 291 return [format {%6.2f%% %s} $v $of] | |
| 292 } | |
| 293 } | |
| 294 | |
| 295 proc divide {num denom} { | |
| 296 if {$denom==0} {return 0.0} | |
| 297 return [format %.2f [expr double($num)/double($denom)]] | |
| 298 } | |
| 299 | |
| 300 # Generate a subreport that covers some subset of the database. | |
| 301 # the $where clause determines which subset to analyze. | |
| 302 # | |
| 303 proc subreport {title where showFrag} { | |
| 304 global pageSize file_pgcnt compressOverhead | |
| 305 | |
| 306 # Query the in-memory database for the sum of various statistics | |
| 307 # for the subset of tables/indices identified by the WHERE clause in | |
| 308 # $where. Note that even if the WHERE clause matches no rows, the | |
| 309 # following query returns exactly one row (because it is an aggregate). | |
| 310 # | |
| 311 # The results of the query are stored directly by SQLite into local | |
| 312 # variables (i.e. $nentry, $nleaf etc.). | |
| 313 # | |
| 314 mem eval " | |
| 315 SELECT | |
| 316 int(sum(nentry)) AS nentry, | |
| 317 int(sum(leaf_entries)) AS nleaf, | |
| 318 int(sum(payload)) AS payload, | |
| 319 int(sum(ovfl_payload)) AS ovfl_payload, | |
| 320 max(mx_payload) AS mx_payload, | |
| 321 int(sum(ovfl_cnt)) as ovfl_cnt, | |
| 322 int(sum(leaf_pages)) AS leaf_pages, | |
| 323 int(sum(int_pages)) AS int_pages, | |
| 324 int(sum(ovfl_pages)) AS ovfl_pages, | |
| 325 int(sum(leaf_unused)) AS leaf_unused, | |
| 326 int(sum(int_unused)) AS int_unused, | |
| 327 int(sum(ovfl_unused)) AS ovfl_unused, | |
| 328 int(sum(gap_cnt)) AS gap_cnt, | |
| 329 int(sum(compressed_size)) AS compressed_size | |
| 330 FROM space_used WHERE $where" {} {} | |
| 331 | |
| 332 # Output the sub-report title, nicely decorated with * characters. | |
| 333 # | |
| 334 puts "" | |
| 335 titleline $title | |
| 336 puts "" | |
| 337 | |
| 338 # Calculate statistics and store the results in TCL variables, as follows: | |
| 339 # | |
| 340 # total_pages: Database pages consumed. | |
| 341 # total_pages_percent: Pages consumed as a percentage of the file. | |
| 342 # storage: Bytes consumed. | |
| 343 # payload_percent: Payload bytes used as a percentage of $storage. | |
| 344 # total_unused: Unused bytes on pages. | |
| 345 # avg_payload: Average payload per btree entry. | |
| 346 # avg_fanout: Average fanout for internal pages. | |
| 347 # avg_unused: Average unused bytes per btree entry. | |
| 348 # ovfl_cnt_percent: Percentage of btree entries that use overflow pages. | |
| 349 # | |
| 350 set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] | |
| 351 set total_pages_percent [percent $total_pages $file_pgcnt] | |
| 352 set storage [expr {$total_pages*$pageSize}] | |
| 353 set payload_percent [percent $payload $storage {of storage consumed}] | |
| 354 set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}] | |
| 355 set avg_payload [divide $payload $nleaf] | |
| 356 set avg_unused [divide $total_unused $nleaf] | |
| 357 if {$int_pages>0} { | |
| 358 # TODO: Is this formula correct? | |
| 359 set nTab [mem eval " | |
| 360 SELECT count(*) FROM ( | |
| 361 SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0 | |
| 362 ) | |
| 363 "] | |
| 364 set avg_fanout [mem eval " | |
| 365 SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used | |
| 366 WHERE $where AND is_index = 0 | |
| 367 "] | |
| 368 set avg_fanout [format %.2f $avg_fanout] | |
| 369 } | |
| 370 set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] | |
| 371 | |
| 372 # Print out the sub-report statistics. | |
| 373 # | |
| 374 statline {Percentage of total database} $total_pages_percent | |
| 375 statline {Number of entries} $nleaf | |
| 376 statline {Bytes of storage consumed} $storage | |
| 377 if {$compressed_size!=$storage} { | |
| 378 set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}] | |
| 379 set pct [expr {$compressed_size*100.0/$storage}] | |
| 380 set pct [format {%5.1f%%} $pct] | |
| 381 statline {Bytes used after compression} $compressed_size $pct | |
| 382 } | |
| 383 statline {Bytes of payload} $payload $payload_percent | |
| 384 statline {Average payload per entry} $avg_payload | |
| 385 statline {Average unused bytes per entry} $avg_unused | |
| 386 if {[info exists avg_fanout]} { | |
| 387 statline {Average fanout} $avg_fanout | |
| 388 } | |
| 389 if {$showFrag && $total_pages>1} { | |
| 390 set fragmentation [percent $gap_cnt [expr {$total_pages-1}]] | |
| 391 statline {Non-sequential pages} $gap_cnt $fragmentation | |
| 392 } | |
| 393 statline {Maximum payload per entry} $mx_payload | |
| 394 statline {Entries that use overflow} $ovfl_cnt $ovfl_cnt_percent | |
| 395 if {$int_pages>0} { | |
| 396 statline {Index pages used} $int_pages | |
| 397 } | |
| 398 statline {Primary pages used} $leaf_pages | |
| 399 statline {Overflow pages used} $ovfl_pages | |
| 400 statline {Total pages used} $total_pages | |
| 401 if {$int_unused>0} { | |
| 402 set int_unused_percent [ | |
| 403 percent $int_unused [expr {$int_pages*$pageSize}] {of index space}] | |
| 404 statline "Unused bytes on index pages" $int_unused $int_unused_percent | |
| 405 } | |
| 406 statline "Unused bytes on primary pages" $leaf_unused [ | |
| 407 percent $leaf_unused [expr {$leaf_pages*$pageSize}] {of primary space}] | |
| 408 statline "Unused bytes on overflow pages" $ovfl_unused [ | |
| 409 percent $ovfl_unused [expr {$ovfl_pages*$pageSize}] {of overflow space}] | |
| 410 statline "Unused bytes on all pages" $total_unused [ | |
| 411 percent $total_unused $storage {of all space}] | |
| 412 return 1 | |
| 413 } | |
| 414 | |
| 415 # Calculate the overhead in pages caused by auto-vacuum. | |
| 416 # | |
| 417 # This procedure calculates and returns the number of pages used by the | |
| 418 # auto-vacuum 'pointer-map'. If the database does not support auto-vacuum, | |
| 419 # then 0 is returned. The two arguments are the size of the database file in | |
| 420 # pages and the page size used by the database (in bytes). | |
| 421 proc autovacuum_overhead {filePages pageSize} { | |
| 422 | |
| 423 # Set $autovacuum to non-zero for databases that support auto-vacuum. | |
| 424 set autovacuum [db one {PRAGMA auto_vacuum}] | |
| 425 | |
| 426 # If the database is not an auto-vacuum database or the file consists | |
| 427 # of one page only then there is no overhead for auto-vacuum. Return zero. | |
| 428 if {0==$autovacuum || $filePages==1} { | |
| 429 return 0 | |
| 430 } | |
| 431 | |
| 432 # The number of entries on each pointer map page. The layout of the | |
| 433 # database file is one pointer-map page, followed by $ptrsPerPage other | |
| 434 # pages, followed by a pointer-map page etc. The first pointer-map page | |
| 435 # is the second page of the file overall. | |
| 436 set ptrsPerPage [expr double($pageSize/5)] | |
| 437 | |
| 438 # Return the number of pointer map pages in the database. | |
| 439 return [expr wide(ceil( ($filePages-1.0)/($ptrsPerPage+1.0) ))] | |
| 440 } | |
| 441 | |
| 442 | |
| 443 # Calculate the summary statistics for the database and store the results | |
| 444 # in TCL variables. They are output below. Variables are as follows: | |
| 445 # | |
| 446 # pageSize: Size of each page in bytes. | |
| 447 # file_bytes: File size in bytes. | |
| 448 # file_pgcnt: Number of pages in the file. | |
| 449 # file_pgcnt2: Number of pages in the file (calculated). | |
| 450 # av_pgcnt: Pages consumed by the auto-vacuum pointer-map. | |
| 451 # av_percent: Percentage of the file consumed by auto-vacuum pointer-map. | |
| 452 # inuse_pgcnt: Data pages in the file. | |
| 453 # inuse_percent: Percentage of pages used to store data. | |
| 454 # free_pgcnt: Free pages calculated as (<total pages> - <in-use pages>) | |
| 455 # free_pgcnt2: Free pages in the file according to the file header. | |
| 456 # free_percent: Percentage of file consumed by free pages (calculated). | |
| 457 # free_percent2: Percentage of file consumed by free pages (header). | |
| 458 # ntable: Number of tables in the db. | |
| 459 # nindex: Number of indices in the db. | |
| 460 # nautoindex: Number of indices created automatically. | |
| 461 # nmanindex: Number of indices created manually. | |
| 462 # user_payload: Number of bytes of payload in table btrees | |
| 463 # (not including sqlite_master) | |
| 464 # user_percent: $user_payload as a percentage of total file size. | |
| 465 | |
| 466 ### The following, setting $file_bytes based on the actual size of the file | |
| 467 ### on disk, causes this tool to choke on zipvfs databases. So set it based | |
| 468 ### on the return of [PRAGMA page_count] instead. | |
| 469 if 0 { | |
| 470 set file_bytes [file size $file_to_analyze] | |
| 471 set file_pgcnt [expr {$file_bytes/$pageSize}] | |
| 472 } | |
| 473 set file_pgcnt [db one {PRAGMA page_count}] | |
| 474 set file_bytes [expr {$file_pgcnt * $pageSize}] | |
| 475 | |
| 476 set av_pgcnt [autovacuum_overhead $file_pgcnt $pageSize] | |
| 477 set av_percent [percent $av_pgcnt $file_pgcnt] | |
| 478 | |
| 479 set sql {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used} | |
| 480 set inuse_pgcnt [expr wide([mem eval $sql])] | |
| 481 set inuse_percent [percent $inuse_pgcnt $file_pgcnt] | |
| 482 | |
| 483 set free_pgcnt [expr {$file_pgcnt-$inuse_pgcnt-$av_pgcnt}] | |
| 484 set free_percent [percent $free_pgcnt $file_pgcnt] | |
| 485 set free_pgcnt2 [db one {PRAGMA freelist_count}] | |
| 486 set free_percent2 [percent $free_pgcnt2 $file_pgcnt] | |
| 487 | |
| 488 set file_pgcnt2 [expr {$inuse_pgcnt+$free_pgcnt2+$av_pgcnt}] | |
| 489 | |
| 490 set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}] | |
| 491 set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}] | |
| 492 set sql {SELECT count(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'} | |
| 493 set nautoindex [db eval $sql] | |
| 494 set nmanindex [expr {$nindex-$nautoindex}] | |
| 495 | |
| 496 # set total_payload [mem eval "SELECT sum(payload) FROM space_used"] | |
| 497 set user_payload [mem one {SELECT int(sum(payload)) FROM space_used | |
| 498 WHERE NOT is_index AND name NOT LIKE 'sqlite_master'}] | |
| 499 set user_percent [percent $user_payload $file_bytes] | |
| 500 | |
| 501 # Output the summary statistics calculated above. | |
| 502 # | |
| 503 puts "/** Disk-Space Utilization Report For $root_filename" | |
| 504 puts "" | |
| 505 statline {Page size in bytes} $pageSize | |
| 506 statline {Pages in the whole file (measured)} $file_pgcnt | |
| 507 statline {Pages in the whole file (calculated)} $file_pgcnt2 | |
| 508 statline {Pages that store data} $inuse_pgcnt $inuse_percent | |
| 509 statline {Pages on the freelist (per header)} $free_pgcnt2 $free_percent2 | |
| 510 statline {Pages on the freelist (calculated)} $free_pgcnt $free_percent | |
| 511 statline {Pages of auto-vacuum overhead} $av_pgcnt $av_percent | |
| 512 statline {Number of tables in the database} $ntable | |
| 513 statline {Number of indices} $nindex | |
| 514 statline {Number of defined indices} $nmanindex | |
| 515 statline {Number of implied indices} $nautoindex | |
| 516 if {$isCompressed} { | |
| 517 statline {Size of uncompressed content in bytes} $file_bytes | |
| 518 set efficiency [percent $true_file_size $file_bytes] | |
| 519 statline {Size of compressed file on disk} $true_file_size $efficiency | |
| 520 } else { | |
| 521 statline {Size of the file in bytes} $file_bytes | |
| 522 } | |
| 523 statline {Bytes of user payload stored} $user_payload $user_percent | |
| 524 | |
| 525 # Output table rankings | |
| 526 # | |
| 527 puts "" | |
| 528 titleline "Page counts for all tables with their indices" | |
| 529 puts "" | |
| 530 mem eval {SELECT tblname, count(*) AS cnt, | |
| 531 int(sum(int_pages+leaf_pages+ovfl_pages)) AS size | |
| 532 FROM space_used GROUP BY tblname ORDER BY size+0 DESC, tblname} {} { | |
| 533 statline [string toupper $tblname] $size [percent $size $file_pgcnt] | |
| 534 } | |
| 535 puts "" | |
| 536 titleline "Page counts for all tables and indices separately" | |
| 537 puts "" | |
| 538 mem eval { | |
| 539 SELECT | |
| 540 upper(name) AS nm, | |
| 541 int(int_pages+leaf_pages+ovfl_pages) AS size | |
| 542 FROM space_used | |
| 543 ORDER BY size+0 DESC, name} {} { | |
| 544 statline $nm $size [percent $size $file_pgcnt] | |
| 545 } | |
| 546 if {$isCompressed} { | |
| 547 puts "" | |
| 548 titleline "Bytes of disk space used after compression" | |
| 549 puts "" | |
| 550 set csum 0 | |
| 551 mem eval {SELECT tblname, | |
| 552 int(sum(compressed_size)) + | |
| 553 $compressOverhead*sum(int_pages+leaf_pages+ovfl_pages) | |
| 554 AS csize | |
| 555 FROM space_used GROUP BY tblname ORDER BY csize+0 DESC, tblname} {} { | |
| 556 incr csum $csize | |
| 557 statline [string toupper $tblname] $csize [percent $csize $true_file_size] | |
| 558 } | |
| 559 set overhead [expr {$true_file_size - $csum}] | |
| 560 if {$overhead>0} { | |
| 561 statline {Header and free space} $overhead [percent $overhead $true_file_siz
e] | |
| 562 } | |
| 563 } | |
| 564 | |
| 565 # Output subreports | |
| 566 # | |
| 567 if {$nindex>0} { | |
| 568 subreport {All tables and indices} 1 0 | |
| 569 } | |
| 570 subreport {All tables} {NOT is_index} 0 | |
| 571 if {$nindex>0} { | |
| 572 subreport {All indices} {is_index} 0 | |
| 573 } | |
| 574 foreach tbl [mem eval {SELECT DISTINCT tblname name FROM space_used | |
| 575 ORDER BY name}] { | |
| 576 set qn [quote $tbl] | |
| 577 set name [string toupper $tbl] | |
| 578 set n [mem eval {SELECT count(*) FROM space_used WHERE tblname=$tbl}] | |
| 579 if {$n>1} { | |
| 580 set idxlist [mem eval "SELECT name FROM space_used | |
| 581 WHERE tblname='$qn' AND is_index | |
| 582 ORDER BY 1"] | |
| 583 subreport "Table $name and all its indices" "tblname='$qn'" 0 | |
| 584 subreport "Table $name w/o any indices" "name='$qn'" 1 | |
| 585 if {[llength $idxlist]>1} { | |
| 586 subreport "Indices of table $name" "tblname='$qn' AND is_index" 0 | |
| 587 } | |
| 588 foreach idx $idxlist { | |
| 589 set qidx [quote $idx] | |
| 590 subreport "Index [string toupper $idx] of table $name" "name='$qidx'" 1 | |
| 591 } | |
| 592 } else { | |
| 593 subreport "Table $name" "name='$qn'" 1 | |
| 594 } | |
| 595 } | |
| 596 | |
| 597 # Output instructions on what the numbers above mean. | |
| 598 # | |
| 599 puts "" | |
| 600 titleline Definitions | |
| 601 puts { | |
| 602 Page size in bytes | |
| 603 | |
| 604 The number of bytes in a single page of the database file. | |
| 605 Usually 1024. | |
| 606 | |
| 607 Number of pages in the whole file | |
| 608 } | |
| 609 puts " The number of $pageSize-byte pages that go into forming the complete | |
| 610 database" | |
| 611 puts { | |
| 612 Pages that store data | |
| 613 | |
| 614 The number of pages that store data, either as primary B*Tree pages or | |
| 615 as overflow pages. The number at the right is the data pages divided by | |
| 616 the total number of pages in the file. | |
| 617 | |
| 618 Pages on the freelist | |
| 619 | |
| 620 The number of pages that are not currently in use but are reserved for | |
| 621 future use. The percentage at the right is the number of freelist pages | |
| 622 divided by the total number of pages in the file. | |
| 623 | |
| 624 Pages of auto-vacuum overhead | |
| 625 | |
| 626 The number of pages that store data used by the database to facilitate | |
| 627 auto-vacuum. This is zero for databases that do not support auto-vacuum. | |
| 628 | |
| 629 Number of tables in the database | |
| 630 | |
| 631 The number of tables in the database, including the SQLITE_MASTER table | |
| 632 used to store schema information. | |
| 633 | |
| 634 Number of indices | |
| 635 | |
| 636 The total number of indices in the database. | |
| 637 | |
| 638 Number of defined indices | |
| 639 | |
| 640 The number of indices created using an explicit CREATE INDEX statement. | |
| 641 | |
| 642 Number of implied indices | |
| 643 | |
| 644 The number of indices used to implement PRIMARY KEY or UNIQUE constraints | |
| 645 on tables. | |
| 646 | |
| 647 Size of the file in bytes | |
| 648 | |
| 649 The total amount of disk space used by the entire database files. | |
| 650 | |
| 651 Bytes of user payload stored | |
| 652 | |
| 653 The total number of bytes of user payload stored in the database. The | |
| 654 schema information in the SQLITE_MASTER table is not counted when | |
| 655 computing this number. The percentage at the right shows the payload | |
| 656 divided by the total file size. | |
| 657 | |
| 658 Percentage of total database | |
| 659 | |
| 660 The amount of the complete database file that is devoted to storing | |
| 661 information described by this category. | |
| 662 | |
| 663 Number of entries | |
| 664 | |
| 665 The total number of B-Tree key/value pairs stored under this category. | |
| 666 | |
| 667 Bytes of storage consumed | |
| 668 | |
| 669 The total amount of disk space required to store all B-Tree entries | |
| 670 under this category. The is the total number of pages used times | |
| 671 the pages size. | |
| 672 | |
| 673 Bytes of payload | |
| 674 | |
| 675 The amount of payload stored under this category. Payload is the data | |
| 676 part of table entries and the key part of index entries. The percentage | |
| 677 at the right is the bytes of payload divided by the bytes of storage | |
| 678 consumed. | |
| 679 | |
| 680 Average payload per entry | |
| 681 | |
| 682 The average amount of payload on each entry. This is just the bytes of | |
| 683 payload divided by the number of entries. | |
| 684 | |
| 685 Average unused bytes per entry | |
| 686 | |
| 687 The average amount of free space remaining on all pages under this | |
| 688 category on a per-entry basis. This is the number of unused bytes on | |
| 689 all pages divided by the number of entries. | |
| 690 | |
| 691 Non-sequential pages | |
| 692 | |
| 693 The number of pages in the table or index that are out of sequence. | |
| 694 Many filesystems are optimized for sequential file access so a small | |
| 695 number of non-sequential pages might result in faster queries, | |
| 696 especially for larger database files that do not fit in the disk cache. | |
| 697 Note that after running VACUUM, the root page of each table or index is | |
| 698 at the beginning of the database file and all other pages are in a | |
| 699 separate part of the database file, resulting in a single non- | |
| 700 sequential page. | |
| 701 | |
| 702 Maximum payload per entry | |
| 703 | |
| 704 The largest payload size of any entry. | |
| 705 | |
| 706 Entries that use overflow | |
| 707 | |
| 708 The number of entries that user one or more overflow pages. | |
| 709 | |
| 710 Total pages used | |
| 711 | |
| 712 This is the number of pages used to hold all information in the current | |
| 713 category. This is the sum of index, primary, and overflow pages. | |
| 714 | |
| 715 Index pages used | |
| 716 | |
| 717 This is the number of pages in a table B-tree that hold only key (rowid) | |
| 718 information and no data. | |
| 719 | |
| 720 Primary pages used | |
| 721 | |
| 722 This is the number of B-tree pages that hold both key and data. | |
| 723 | |
| 724 Overflow pages used | |
| 725 | |
| 726 The total number of overflow pages used for this category. | |
| 727 | |
| 728 Unused bytes on index pages | |
| 729 | |
| 730 The total number of bytes of unused space on all index pages. The | |
| 731 percentage at the right is the number of unused bytes divided by the | |
| 732 total number of bytes on index pages. | |
| 733 | |
| 734 Unused bytes on primary pages | |
| 735 | |
| 736 The total number of bytes of unused space on all primary pages. The | |
| 737 percentage at the right is the number of unused bytes divided by the | |
| 738 total number of bytes on primary pages. | |
| 739 | |
| 740 Unused bytes on overflow pages | |
| 741 | |
| 742 The total number of bytes of unused space on all overflow pages. The | |
| 743 percentage at the right is the number of unused bytes divided by the | |
| 744 total number of bytes on overflow pages. | |
| 745 | |
| 746 Unused bytes on all pages | |
| 747 | |
| 748 The total number of bytes of unused space on all primary and overflow | |
| 749 pages. The percentage at the right is the number of unused bytes | |
| 750 divided by the total number of bytes. | |
| 751 } | |
| 752 | |
| 753 # Output a dump of the in-memory database. This can be used for more | |
| 754 # complex offline analysis. | |
| 755 # | |
| 756 titleline {} | |
| 757 puts "The entire text of this report can be sourced into any SQL database" | |
| 758 puts "engine for further analysis. All of the text above is an SQL comment." | |
| 759 puts "The data used to generate this report follows:" | |
| 760 puts "*/" | |
| 761 puts "BEGIN;" | |
| 762 puts $tabledef | |
| 763 unset -nocomplain x | |
| 764 mem eval {SELECT * FROM space_used} x { | |
| 765 puts -nonewline "INSERT INTO space_used VALUES" | |
| 766 set sep ( | |
| 767 foreach col $x(*) { | |
| 768 set v $x($col) | |
| 769 if {$v=="" || ![string is double $v]} {set v '[quote $v]'} | |
| 770 puts -nonewline $sep$v | |
| 771 set sep , | |
| 772 } | |
| 773 puts ");" | |
| 774 } | |
| 775 puts "COMMIT;" | |
| 776 | |
| 777 } err]} { | |
| 778 puts "ERROR: $err" | |
| 779 puts $errorInfo | |
| 780 exit 1 | |
| 781 } | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
