/ Check-in [43ad41ef]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 43ad41efa9e1fdd79a9804197a227491236495f14ed56c656224d6ce181703c1
User & Date: drh 2017-06-15 00:52:03
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: 48e08628 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: 43ad41ef user: drh tags: trunk
2017-06-13
16:52
Fix sqlite3rbu_close() so that the pzErrmsg parameter may be passed NULL. check-in: c433672d user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to tool/spaceanal.tcl.

   420    420     # total_pages_percent: Pages consumed as a percentage of the file.
   421    421     # storage: Bytes consumed.
   422    422     # payload_percent: Payload bytes used as a percentage of $storage.
   423    423     # total_unused: Unused bytes on pages.
   424    424     # avg_payload: Average payload per btree entry.
   425    425     # avg_fanout: Average fanout for internal pages.
   426    426     # avg_unused: Average unused bytes per btree entry.
          427  +  # avg_meta: Average metadata overhead per entry.
   427    428     # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
   428    429     #
   429    430     set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
   430    431     set total_pages_percent [percent $total_pages $file_pgcnt]
   431    432     set storage [expr {$total_pages*$pageSize}]
   432    433     set payload_percent [percent $payload $storage {of storage consumed}]
   433    434     set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
   434    435     set avg_payload [divide $payload $nentry]
   435    436     set avg_unused [divide $total_unused $nentry]
          437  +  set total_meta [expr {$storage - $payload - $total_unused}]
          438  +  set total_meta [expr {$total_meta + 4*($ovfl_pages - $ovfl_cnt)}]
          439  +  set meta_percent [percent $total_meta $storage {of metadata}]
          440  +  set avg_meta [divide $total_meta $nentry]
   436    441     if {$int_pages>0} {
   437    442       # TODO: Is this formula correct?
   438    443       set nTab [mem eval "
   439    444         SELECT count(*) FROM (
   440    445             SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
   441    446         )
   442    447       "]
................................................................................
   456    461     if {$compressed_size!=$storage} {
   457    462       set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
   458    463       set pct [expr {$compressed_size*100.0/$storage}]
   459    464       set pct [format {%5.1f%%} $pct]
   460    465       statline {Bytes used after compression} $compressed_size $pct
   461    466     }
   462    467     statline {Bytes of payload} $payload $payload_percent
          468  +  statline {Bytes of metadata} $total_meta $meta_percent
   463    469     if {$cnt==1} {statline {B-tree depth} $depth}
   464    470     statline {Average payload per entry} $avg_payload
   465    471     statline {Average unused bytes per entry} $avg_unused
          472  +  statline {Average metadata per entry} $avg_meta
   466    473     if {[info exists avg_fanout]} {
   467    474       statline {Average fanout} $avg_fanout
   468    475     }
   469    476     if {$showFrag && $total_pages>1} {
   470    477       set fragmentation [percent $gap_cnt [expr {$total_pages-1}]]
   471    478       statline {Non-sequential pages} $gap_cnt $fragmentation
   472    479     }
................................................................................
   753    760   Bytes of payload
   754    761   
   755    762       The amount of payload stored under this category.  Payload is the data
   756    763       part of table entries and the key part of index entries.  The percentage
   757    764       at the right is the bytes of payload divided by the bytes of storage 
   758    765       consumed.
   759    766   
          767  +Bytes of metadata
          768  +
          769  +    The amount of formatting and structural information stored on for the
          770  +    table or index.  Metadata includes the btree page header, the cell pointer
          771  +    array, the size field for each cell, the left child pointer or non-leaf
          772  +    cells, the overflow pointers for overflow cells, and the rowid value for
          773  +    rowid table cells.  In other words, metadata is everything that is not
          774  +    unused space and that is not content.
          775  +
   760    776   Average payload per entry
   761    777   
   762    778       The average amount of payload on each entry.  This is just the bytes of
   763    779       payload divided by the number of entries.
   764    780   
   765    781   Average unused bytes per entry
   766    782