/ Check-in [82aed271]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 82aed271a4a69e3dad6013b83cc7d492b7fed164
User & Date: drh 2007-02-13 01:38:31
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: 31e5073c user: drh tags: trunk
01:38
Additional fixes to the new fragmentation feature of sqlite3_analyzer. (CVS 3635) check-in: 82aed271 user: drh tags: trunk
2007-02-10
19:22
Changes to support fragmentation analysis in sqlite3_analyzer. (CVS 3634) check-in: bd6bc3b8 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to tool/spaceanal.tcl.

227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
...
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
...
508
509
510
511
512
513
514

515
516
517
518
519
520
521
        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

................................................................................
    # 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

................................................................................
  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
      )
    "]
................................................................................
  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
  }







|







 







|







 







<







 







>







227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
...
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
...
480
481
482
483
484
485
486

487
488
489
490
491
492
493
...
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
        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

................................................................................
    # 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

................................................................................
  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
      )
    "]
................................................................................
  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
  }