SQLite

Check-in [82aed271a4]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Additional fixes to the new fragmentation feature of sqlite3_analyzer. (CVS 3635)
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 82aed271a4a69e3dad6013b83cc7d492b7fed164
User & Date: drh 2007-02-13 01:38:31.000
Context
2007-02-13
01:41
Explanation of the "fragmentation" line is added to the help message at the end of the output of sqlite3_analyzer. (CVS 3636) (check-in: 31e5073c58 user: drh tags: trunk)
01:38
Additional fixes to the new fragmentation feature of sqlite3_analyzer. (CVS 3635) (check-in: 82aed271a4 user: drh tags: trunk)
2007-02-10
19:22
Changes to support fragmentation analysis in sqlite3_analyzer. (CVS 3634) (check-in: bd6bc3b8f0 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to tool/spaceanal.tcl.
︙︙
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241

        set pglist "$ci(page_no) $pglist"
      }
    }

    # Check the page list for fragmentation
    #
    foreach pg $pglist {
      if {($pg<$prev_pgno || $pg>$prev_pgno+$MAXGAP) && $prev_pgno>0} {
        incr gap_cnt
      }
      set prev_pgno $pg
    }
  }
  btree_close_cursor $csr









|








227
228
229
230
231
232
233
234
235
236
237
238
239
240
241

        set pglist "$ci(page_no) $pglist"
      }
    }

    # Check the page list for fragmentation
    #
    foreach pg $pglist {
      if {$pg!=$prev_pgno+1 && $prev_pgno>0} {
        incr gap_cnt
      }
      set prev_pgno $pg
    }
  }
  btree_close_cursor $csr
︙︙
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362

    # the page-related statistics $leaf_pages and $unused_leaf.
    #
    if {![info exists seen($ci(page_no))]} {
      set seen($ci(page_no)) 1
      incr leaf_pages
      incr unused_leaf $ci(page_freebytes)
      set pg $ci(page_no)
      if {$prev_pgno>0 && ($prev_pgno<$pg-$MAXGAP || $prev_pgno>$pg)} {
        incr gap_cnt
      }
      set prev_pgno $ci(page_no)
    }
  }
  btree_close_cursor $csr









|








348
349
350
351
352
353
354
355
356
357
358
359
360
361
362

    # the page-related statistics $leaf_pages and $unused_leaf.
    #
    if {![info exists seen($ci(page_no))]} {
      set seen($ci(page_no)) 1
      incr leaf_pages
      incr unused_leaf $ci(page_freebytes)
      set pg $ci(page_no)
      if {$prev_pgno>0 && $pg!=$prev_pgno+1} {
        incr gap_cnt
      }
      set prev_pgno $ci(page_no)
    }
  }
  btree_close_cursor $csr
︙︙
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494

  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]
  set fragmentation [percent $gap_cnt $total_pages {fragmentation}]
  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
      )
    "]








<








480
481
482
483
484
485
486

487
488
489
490
491
492
493

  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
      )
    "]
︙︙
508
509
510
511
512
513
514

515
516
517
518
519
520
521

  statline {Bytes of payload} $payload $payload_percent
  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 {$total_pages>1} {

    statline {Fragmentation} $fragmentation
  }
  statline {Maximum payload per entry} $mx_payload
  statline {Entries that use overflow} $ovfl_cnt $ovfl_cnt_percent
  if {$int_pages>0} {
    statline {Index pages used} $int_pages
  }








>








507
508
509
510
511
512
513
514
515
516
517
518
519
520
521

  statline {Bytes of payload} $payload $payload_percent
  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 {$total_pages>1} {
    set fragmentation [percent $gap_cnt [expr {$total_pages-1}] {fragmentation}]
    statline {Fragmentation} $fragmentation
  }
  statline {Maximum payload per entry} $mx_payload
  statline {Entries that use overflow} $ovfl_cnt $ovfl_cnt_percent
  if {$int_pages>0} {
    statline {Index pages used} $int_pages
  }
︙︙