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