Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the sqlite3_analyzer.exe utility so that it computes and shows the number of bytes of metadata on btree pages and per table and index entry. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
43ad41efa9e1fdd79a9804197a227491 |
User & Date: | drh 2017-06-15 00:52:03.263 |
Context
2017-06-15
| ||
12:21 | In the command-line shell, enhance the ".schema" command show that it shows the schema for ATTACH-ed databases in addition to "main". (check-in: 48e086284a user: drh tags: trunk) | |
00:52 | Enhance the sqlite3_analyzer.exe utility so that it computes and shows the number of bytes of metadata on btree pages and per table and index entry. (check-in: 43ad41efa9 user: drh tags: trunk) | |
2017-06-13
| ||
16:52 | Fix sqlite3rbu_close() so that the pzErrmsg parameter may be passed NULL. (check-in: c433672dd8 user: dan tags: trunk) | |
Changes
Changes to tool/spaceanal.tcl.
︙ | ︙ | |||
420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 | # total_pages_percent: Pages consumed as a percentage of the file. # storage: Bytes consumed. # payload_percent: Payload bytes used as a percentage of $storage. # total_unused: Unused bytes on pages. # avg_payload: Average payload per btree entry. # avg_fanout: Average fanout for internal pages. # avg_unused: Average unused bytes per btree entry. # 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 $nentry] set avg_unused [divide $total_unused $nentry] 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 ) "] | > > > > > | 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 | # total_pages_percent: Pages consumed as a percentage of the file. # storage: Bytes consumed. # payload_percent: Payload bytes used as a percentage of $storage. # total_unused: Unused bytes on pages. # avg_payload: Average payload per btree entry. # avg_fanout: Average fanout for internal pages. # avg_unused: Average unused bytes per btree entry. # avg_meta: Average metadata overhead per entry. # 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 $nentry] set avg_unused [divide $total_unused $nentry] set total_meta [expr {$storage - $payload - $total_unused}] set total_meta [expr {$total_meta + 4*($ovfl_pages - $ovfl_cnt)}] set meta_percent [percent $total_meta $storage {of metadata}] set avg_meta [divide $total_meta $nentry] 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 ) "] |
︙ | ︙ | |||
456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 | if {$compressed_size!=$storage} { set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}] set pct [expr {$compressed_size*100.0/$storage}] set pct [format {%5.1f%%} $pct] statline {Bytes used after compression} $compressed_size $pct } statline {Bytes of payload} $payload $payload_percent if {$cnt==1} {statline {B-tree depth} $depth} statline {Average payload per entry} $avg_payload statline {Average unused bytes per entry} $avg_unused if {[info exists avg_fanout]} { statline {Average fanout} $avg_fanout } if {$showFrag && $total_pages>1} { set fragmentation [percent $gap_cnt [expr {$total_pages-1}]] statline {Non-sequential pages} $gap_cnt $fragmentation } | > > | 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 | if {$compressed_size!=$storage} { set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}] set pct [expr {$compressed_size*100.0/$storage}] set pct [format {%5.1f%%} $pct] statline {Bytes used after compression} $compressed_size $pct } statline {Bytes of payload} $payload $payload_percent statline {Bytes of metadata} $total_meta $meta_percent if {$cnt==1} {statline {B-tree depth} $depth} statline {Average payload per entry} $avg_payload statline {Average unused bytes per entry} $avg_unused statline {Average metadata per entry} $avg_meta if {[info exists avg_fanout]} { statline {Average fanout} $avg_fanout } if {$showFrag && $total_pages>1} { set fragmentation [percent $gap_cnt [expr {$total_pages-1}]] statline {Non-sequential pages} $gap_cnt $fragmentation } |
︙ | ︙ | |||
753 754 755 756 757 758 759 760 761 762 763 764 765 766 | Bytes of payload The amount of payload stored under this category. Payload is the data part of table entries and the key part of index entries. The percentage at the right is the bytes of payload divided by the bytes of storage consumed. Average payload per entry The average amount of payload on each entry. This is just the bytes of payload divided by the number of entries. Average unused bytes per entry | > > > > > > > > > | 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 | Bytes of payload The amount of payload stored under this category. Payload is the data part of table entries and the key part of index entries. The percentage at the right is the bytes of payload divided by the bytes of storage consumed. Bytes of metadata The amount of formatting and structural information stored on for the table or index. Metadata includes the btree page header, the cell pointer array, the size field for each cell, the left child pointer or non-leaf cells, the overflow pointers for overflow cells, and the rowid value for rowid table cells. In other words, metadata is everything that is not unused space and that is not content. Average payload per entry The average amount of payload on each entry. This is just the bytes of payload divided by the number of entries. Average unused bytes per entry |
︙ | ︙ |