Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Space analyzer: Format integer results as integers. Also fix the calculation of average-fanout. (CVS 2304) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
52e473b94694cd0991257427d57515ec |
User & Date: | danielk1977 2005-02-01 10:36:40.000 |
Context
2005-02-01
| ||
15:57 | Increment the version number to 3.1.1beta. (CVS 2305) (check-in: 076f9868a4 user: drh tags: trunk) | |
10:36 | Space analyzer: Format integer results as integers. Also fix the calculation of average-fanout. (CVS 2304) (check-in: 52e473b946 user: danielk1977 tags: trunk) | |
10:35 | Have debugging code handle sqliteMalloc(n) where n<0 in the same way as production. (CVS 2303) (check-in: ab85e1d012 user: danielk1977 tags: trunk) | |
Changes
Changes to tool/spaceanal.tcl.
︙ | ︙ | |||
51 52 53 54 55 56 57 58 59 60 61 62 63 64 | ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int -- Number of unused bytes on overflow pages );} mem eval $tabledef # Quote a string for use in an SQL query. Examples: # # [quote {hello world}] == {'hello world'} # [quote {hello world's}] == {'hello world''s'} # proc quote {txt} { regsub -all ' $txt '' q | > > > > > | 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 | ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int -- Number of unused bytes on overflow pages );} mem eval $tabledef proc integerify {real} { return [expr int($real)] } mem function int integerify # Quote a string for use in an SQL query. Examples: # # [quote {hello world}] == {'hello world'} # [quote {hello world's}] == {'hello world''s'} # proc quote {txt} { regsub -all ' $txt '' q |
︙ | ︙ | |||
374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 | return [format {%5.1f%% %s} $v $of] } elseif {$v<0.1 || $v>99.9} { return [format {%7.3f%% %s} $v $of] } else { return [format {%6.2f%% %s} $v $of] } } # Generate a subreport that covers some subset of the database. # the $where clause determines which subset to analyze. # proc subreport {title where} { global pageSize file_pgcnt # Query the in-memory database for the sum of various statistics # for the subset of tables/indices identified by the WHERE clause in # $where. Note that even if the WHERE clause matches no rows, the # following query returns exactly one row (because it is an aggregate). # # The results of the query are stored directly by SQLite into local # variables (i.e. $nentry, $nleaf etc.). # mem eval " SELECT | > > > > > | | | | | | | | | | | | 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 | return [format {%5.1f%% %s} $v $of] } elseif {$v<0.1 || $v>99.9} { return [format {%7.3f%% %s} $v $of] } else { return [format {%6.2f%% %s} $v $of] } } proc divide {num denom} { if {$denom==0} {return 0.0} return [format %.2f [expr double($num)/double($denom)]] } # Generate a subreport that covers some subset of the database. # the $where clause determines which subset to analyze. # proc subreport {title where} { global pageSize file_pgcnt # Query the in-memory database for the sum of various statistics # for the subset of tables/indices identified by the WHERE clause in # $where. Note that even if the WHERE clause matches no rows, the # following query returns exactly one row (because it is an aggregate). # # The results of the query are stored directly by SQLite into local # variables (i.e. $nentry, $nleaf etc.). # mem eval " SELECT int(sum(nentry)) AS nentry, int(sum(leaf_entries)) AS nleaf, int(sum(payload)) AS payload, int(sum(ovfl_payload)) AS ovfl_payload, max(mx_payload) AS mx_payload, int(sum(ovfl_cnt)) as ovfl_cnt, int(sum(leaf_pages)) AS leaf_pages, int(sum(int_pages)) AS int_pages, int(sum(ovfl_pages)) AS ovfl_pages, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused FROM space_used WHERE $where" {} {} # Output the sub-report title, nicely decorated with * characters. # puts "" set len [string length $title] set stars [string repeat * [expr 65-$len]] |
︙ | ︙ | |||
430 431 432 433 434 435 436 | # ovfl_cnt_percent: Percentage of btree entries that use overflow pages. # set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] set total_pages_percent [percent $total_pages $file_pgcnt] set storage [expr {$total_pages*$pageSize}] set payload_percent [percent $payload $storage {of storage consumed}] set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}] | | | > > > > > > > > > | | 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 | # ovfl_cnt_percent: Percentage of btree entries that use overflow pages. # set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}] set total_pages_percent [percent $total_pages $file_pgcnt] set storage [expr {$total_pages*$pageSize}] set payload_percent [percent $payload $storage {of storage consumed}] set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}] set avg_payload [divide $payload $nleaf] set avg_unused [divide $total_unused $nleaf] if {$int_pages>0} { # TODO: Is this formula correct? set nTab [mem eval " SELECT count(*) FROM ( SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0 ) "] set avg_fanout [mem eval " SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used WHERE $where AND is_index = 0 "] set avg_fanout [format %.2f $avg_fanout] } set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nleaf |
︙ | ︙ | |||
532 533 534 535 536 537 538 | set file_bytes [file size $file_to_analyze] set file_pgcnt [expr {$file_bytes/$pageSize}] set av_pgcnt [autovacuum_overhead $file_pgcnt $pageSize] set av_percent [percent $av_pgcnt $file_pgcnt] set sql {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used} | | | | 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 | set file_bytes [file size $file_to_analyze] set file_pgcnt [expr {$file_bytes/$pageSize}] set av_pgcnt [autovacuum_overhead $file_pgcnt $pageSize] set av_percent [percent $av_pgcnt $file_pgcnt] set sql {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used} set inuse_pgcnt [expr int([mem eval $sql])] set inuse_percent [percent $inuse_pgcnt $file_pgcnt] set free_pgcnt [expr $file_pgcnt-$inuse_pgcnt-$av_pgcnt] set free_percent [percent $free_pgcnt $file_pgcnt] set free_pgcnt2 [lindex [btree_get_meta $DB] 0] set free_percent2 [percent $free_pgcnt2 $file_pgcnt] set file_pgcnt2 [expr {$inuse_pgcnt+$free_pgcnt2+$av_pgcnt}] set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}] set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}] set sql {SELECT count(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'} set nautoindex [db eval $sql] set nmanindex [expr {$nindex-$nautoindex}] # set total_payload [mem eval "SELECT sum(payload) FROM space_used"] set user_payload [mem one {SELECT int(sum(payload)) FROM space_used WHERE NOT is_index AND name NOT LIKE 'sqlite_master'}] set user_percent [percent $user_payload $file_bytes] # Output the summary statistics calculated above. # puts "/** Disk-Space Utilization Report For $file_to_analyze" puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]" |
︙ | ︙ | |||
578 579 580 581 582 583 584 | # Output table rankings # puts "" puts "*** Page counts for all tables with their indices ********************" puts "" mem eval {SELECT tblname, count(*) AS cnt, | | | 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 | # Output table rankings # puts "" puts "*** Page counts for all tables with their indices ********************" puts "" mem eval {SELECT tblname, count(*) AS cnt, int(sum(int_pages+leaf_pages+ovfl_pages)) AS size FROM space_used GROUP BY tblname ORDER BY size DESC, tblname} {} { statline [string toupper $tblname] $size [percent $size $file_pgcnt] } # Output subreports # if {$nindex>0} { |
︙ | ︙ |