SQLite

Check-in [43ad41efa9]
Login

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: 43ad41efa9e1fdd79a9804197a227491236495f14ed56c656224d6ce181703c1
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
Unified Diff Show Whitespace Changes Patch
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