1: # Run this TCL script using "testfixture" in order get a report that shows
2: # how much disk space is used by a particular data to actually store data
3: # versus how much space is unused.
4: #
5:
6: if {[catch {
7: # Get the name of the database to analyze
8: #
9: proc usage {} {
10: set argv0 [file rootname [file tail [info nameofexecutable]]]
11: puts stderr "Usage: $argv0 database-name"
12: exit 1
13: }
14: set file_to_analyze {}
15: set flags(-pageinfo) 0
16: set flags(-stats) 0
17: append argv {}
18: foreach arg $argv {
19: if {[regexp {^-+pageinfo$} $arg]} {
20: set flags(-pageinfo) 1
21: } elseif {[regexp {^-+stats$} $arg]} {
22: set flags(-stats) 1
23: } elseif {[regexp {^-} $arg]} {
24: puts stderr "Unknown option: $arg"
25: usage
26: } elseif {$file_to_analyze!=""} {
27: usage
28: } else {
29: set file_to_analyze $arg
30: }
31: }
32: if {$file_to_analyze==""} usage
33: if {![file exists $file_to_analyze]} {
34: puts stderr "No such file: $file_to_analyze"
35: exit 1
36: }
37: if {![file readable $file_to_analyze]} {
38: puts stderr "File is not readable: $file_to_analyze"
39: exit 1
40: }
41: set true_file_size [file size $file_to_analyze]
42: if {$true_file_size<512} {
43: puts stderr "Empty or malformed database: $file_to_analyze"
44: exit 1
45: }
46:
47: # Compute the total file size assuming test_multiplexor is being used.
48: # Assume that SQLITE_ENABLE_8_3_NAMES might be enabled
49: #
50: set extension [file extension $file_to_analyze]
51: set pattern $file_to_analyze
52: append pattern {[0-9][0-9]}
53: foreach f [glob -nocomplain $pattern] {
54: incr true_file_size [file size $f]
55: set extension {}
56: }
57: if {[string length $extension]>=2 && [string length $extension]<=4} {
58: set pattern [file rootname $file_to_analyze]
59: append pattern [string range $extension 0 1]
60: append pattern {[0-9][0-9]}
61: foreach f [glob -nocomplain $pattern] {
62: incr true_file_size [file size $f]
63: }
64: }
65:
66: # Open the database
67: #
68: sqlite3 db $file_to_analyze
69: register_dbstat_vtab db
70:
71: db eval {SELECT count(*) FROM sqlite_master}
72: set pageSize [expr {wide([db one {PRAGMA page_size}])}]
73:
74: if {$flags(-pageinfo)} {
75: db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
76: db eval {SELECT name, path, pageno FROM temp.stat ORDER BY pageno} {
77: puts "$pageno $name $path"
78: }
79: exit 0
80: }
81: if {$flags(-stats)} {
82: db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
83: puts "BEGIN;"
84: puts "CREATE TABLE stats("
85: puts " name STRING, /* Name of table or index */"
86: puts " path INTEGER, /* Path to page from root */"
87: puts " pageno INTEGER, /* Page number */"
88: puts " pagetype STRING, /* 'internal', 'leaf' or 'overflow' */"
89: puts " ncell INTEGER, /* Cells on page (0 for overflow) */"
90: puts " payload INTEGER, /* Bytes of payload on this page */"
91: puts " unused INTEGER, /* Bytes of unused space on this page */"
92: puts " mx_payload INTEGER, /* Largest payload size of all cells */"
93: puts " pgoffset INTEGER, /* Offset of page in file */"
94: puts " pgsize INTEGER /* Size of the page */"
95: puts ");"
96: db eval {SELECT quote(name) || ',' ||
97: quote(path) || ',' ||
98: quote(pageno) || ',' ||
99: quote(pagetype) || ',' ||
100: quote(ncell) || ',' ||
101: quote(payload) || ',' ||
102: quote(unused) || ',' ||
103: quote(mx_payload) || ',' ||
104: quote(pgoffset) || ',' ||
105: quote(pgsize) AS x FROM stat} {
106: puts "INSERT INTO stats VALUES($x);"
107: }
108: puts "COMMIT;"
109: exit 0
110: }
111:
112: # In-memory database for collecting statistics. This script loops through
113: # the tables and indices in the database being analyzed, adding a row for each
114: # to an in-memory database (for which the schema is shown below). It then
115: # queries the in-memory db to produce the space-analysis report.
116: #
117: sqlite3 mem :memory:
118: set tabledef {CREATE TABLE space_used(
119: name clob, -- Name of a table or index in the database file
120: tblname clob, -- Name of associated table
121: is_index boolean, -- TRUE if it is an index, false for a table
122: nentry int, -- Number of entries in the BTree
123: leaf_entries int, -- Number of leaf entries
124: payload int, -- Total amount of data stored in this table or index
125: ovfl_payload int, -- Total amount of data stored on overflow pages
126: ovfl_cnt int, -- Number of entries that use overflow
127: mx_payload int, -- Maximum payload size
128: int_pages int, -- Number of interior pages used
129: leaf_pages int, -- Number of leaf pages used
130: ovfl_pages int, -- Number of overflow pages used
131: int_unused int, -- Number of unused bytes on interior pages
132: leaf_unused int, -- Number of unused bytes on primary pages
133: ovfl_unused int, -- Number of unused bytes on overflow pages
134: gap_cnt int, -- Number of gaps in the page layout
135: compressed_size int -- Total bytes stored on disk
136: );}
137: mem eval $tabledef
138:
139: # Create a temporary "dbstat" virtual table.
140: #
141: db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat}
142: db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat
143: ORDER BY name, path}
144: db eval {DROP TABLE temp.stat}
145:
146: proc isleaf {pagetype is_index} {
147: return [expr {$pagetype == "leaf" || ($pagetype == "internal" && $is_index)}]
148: }
149: proc isoverflow {pagetype is_index} {
150: return [expr {$pagetype == "overflow"}]
151: }
152: proc isinternal {pagetype is_index} {
153: return [expr {$pagetype == "internal" && $is_index==0}]
154: }
155:
156: db func isleaf isleaf
157: db func isinternal isinternal
158: db func isoverflow isoverflow
159:
160: set isCompressed 0
161: set compressOverhead 0
162: set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
163: foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {
164:
165: set is_index [expr {$name!=$tblname}]
166: db eval {
167: SELECT
168: sum(ncell) AS nentry,
169: sum(isleaf(pagetype, $is_index) * ncell) AS leaf_entries,
170: sum(payload) AS payload,
171: sum(isoverflow(pagetype, $is_index) * payload) AS ovfl_payload,
172: sum(path LIKE '%+000000') AS ovfl_cnt,
173: max(mx_payload) AS mx_payload,
174: sum(isinternal(pagetype, $is_index)) AS int_pages,
175: sum(isleaf(pagetype, $is_index)) AS leaf_pages,
176: sum(isoverflow(pagetype, $is_index)) AS ovfl_pages,
177: sum(isinternal(pagetype, $is_index) * unused) AS int_unused,
178: sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused,
179: sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused,
180: sum(pgsize) AS compressed_size
181: FROM temp.dbstat WHERE name = $name
182: } break
183:
184: set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
185: set storage [expr {$total_pages*$pageSize}]
186: if {!$isCompressed && $storage>$compressed_size} {
187: set isCompressed 1
188: set compressOverhead 14
189: }
190:
191: # Column 'gap_cnt' is set to the number of non-contiguous entries in the
192: # list of pages visited if the b-tree structure is traversed in a top-down
193: # fashion (each node visited before its child-tree is passed). Any overflow
194: # chains present are traversed from start to finish before any child-tree
195: # is.
196: #
197: set gap_cnt 0
198: set pglist [db eval {
199: SELECT pageno FROM temp.dbstat WHERE name = $name ORDER BY rowid
200: }]
201: set prev [lindex $pglist 0]
202: foreach pgno [lrange $pglist 1 end] {
203: if {$pgno != $prev+1} {incr gap_cnt}
204: set prev $pgno
205: }
206:
207: mem eval {
208: INSERT INTO space_used VALUES(
209: $name,
210: $tblname,
211: $is_index,
212: $nentry,
213: $leaf_entries,
214: $payload,
215: $ovfl_payload,
216: $ovfl_cnt,
217: $mx_payload,
218: $int_pages,
219: $leaf_pages,
220: $ovfl_pages,
221: $int_unused,
222: $leaf_unused,
223: $ovfl_unused,
224: $gap_cnt,
225: $compressed_size
226: );
227: }
228: }
229:
230: proc integerify {real} {
231: if {[string is double -strict $real]} {
232: return [expr {wide($real)}]
233: } else {
234: return 0
235: }
236: }
237: mem function int integerify
238:
239: # Quote a string for use in an SQL query. Examples:
240: #
241: # [quote {hello world}] == {'hello world'}
242: # [quote {hello world's}] == {'hello world''s'}
243: #
244: proc quote {txt} {
245: regsub -all ' $txt '' q
246: return '$q'
247: }
248:
249: # Generate a single line of output in the statistics section of the
250: # report.
251: #
252: proc statline {title value {extra {}}} {
253: set len [string length $title]
254: set dots [string range {......................................} $len end]
255: set len [string length $value]
256: set sp2 [string range { } $len end]
257: if {$extra ne ""} {
258: set extra " $extra"
259: }
260: puts "$title$dots $value$sp2$extra"
261: }
262:
263: # Generate a formatted percentage value for $num/$denom
264: #
265: proc percent {num denom {of {}}} {
266: if {$denom==0.0} {return ""}
267: set v [expr {$num*100.0/$denom}]
268: set of {}
269: if {$v==100.0 || $v<0.001 || ($v>1.0 && $v<99.0)} {
270: return [format {%5.1f%% %s} $v $of]
271: } elseif {$v<0.1 || $v>99.9} {
272: return [format {%7.3f%% %s} $v $of]
273: } else {
274: return [format {%6.2f%% %s} $v $of]
275: }
276: }
277:
278: proc divide {num denom} {
279: if {$denom==0} {return 0.0}
280: return [format %.2f [expr double($num)/double($denom)]]
281: }
282:
283: # Generate a subreport that covers some subset of the database.
284: # the $where clause determines which subset to analyze.
285: #
286: proc subreport {title where} {
287: global pageSize file_pgcnt compressOverhead
288:
289: # Query the in-memory database for the sum of various statistics
290: # for the subset of tables/indices identified by the WHERE clause in
291: # $where. Note that even if the WHERE clause matches no rows, the
292: # following query returns exactly one row (because it is an aggregate).
293: #
294: # The results of the query are stored directly by SQLite into local
295: # variables (i.e. $nentry, $nleaf etc.).
296: #
297: mem eval "
298: SELECT
299: int(sum(nentry)) AS nentry,
300: int(sum(leaf_entries)) AS nleaf,
301: int(sum(payload)) AS payload,
302: int(sum(ovfl_payload)) AS ovfl_payload,
303: max(mx_payload) AS mx_payload,
304: int(sum(ovfl_cnt)) as ovfl_cnt,
305: int(sum(leaf_pages)) AS leaf_pages,
306: int(sum(int_pages)) AS int_pages,
307: int(sum(ovfl_pages)) AS ovfl_pages,
308: int(sum(leaf_unused)) AS leaf_unused,
309: int(sum(int_unused)) AS int_unused,
310: int(sum(ovfl_unused)) AS ovfl_unused,
311: int(sum(gap_cnt)) AS gap_cnt,
312: int(sum(compressed_size)) AS compressed_size
313: FROM space_used WHERE $where" {} {}
314:
315: # Output the sub-report title, nicely decorated with * characters.
316: #
317: puts ""
318: set len [string length $title]
319: set stars [string repeat * [expr 65-$len]]
320: puts "*** $title $stars"
321: puts ""
322:
323: # Calculate statistics and store the results in TCL variables, as follows:
324: #
325: # total_pages: Database pages consumed.
326: # total_pages_percent: Pages consumed as a percentage of the file.
327: # storage: Bytes consumed.
328: # payload_percent: Payload bytes used as a percentage of $storage.
329: # total_unused: Unused bytes on pages.
330: # avg_payload: Average payload per btree entry.
331: # avg_fanout: Average fanout for internal pages.
332: # avg_unused: Average unused bytes per btree entry.
333: # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
334: #
335: set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
336: set total_pages_percent [percent $total_pages $file_pgcnt]
337: set storage [expr {$total_pages*$pageSize}]
338: set payload_percent [percent $payload $storage {of storage consumed}]
339: set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
340: set avg_payload [divide $payload $nleaf]
341: set avg_unused [divide $total_unused $nleaf]
342: if {$int_pages>0} {
343: # TODO: Is this formula correct?
344: set nTab [mem eval "
345: SELECT count(*) FROM (
346: SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
347: )
348: "]
349: set avg_fanout [mem eval "
350: SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
351: WHERE $where AND is_index = 0
352: "]
353: set avg_fanout [format %.2f $avg_fanout]
354: }
355: set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]
356:
357: # Print out the sub-report statistics.
358: #
359: statline {Percentage of total database} $total_pages_percent
360: statline {Number of entries} $nleaf
361: statline {Bytes of storage consumed} $storage
362: if {$compressed_size!=$storage} {
363: set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
364: set pct [expr {$compressed_size*100.0/$storage}]
365: set pct [format {%5.1f%%} $pct]
366: statline {Bytes used after compression} $compressed_size $pct
367: }
368: statline {Bytes of payload} $payload $payload_percent
369: statline {Average payload per entry} $avg_payload
370: statline {Average unused bytes per entry} $avg_unused
371: if {[info exists avg_fanout]} {
372: statline {Average fanout} $avg_fanout
373: }
374: if {$total_pages>1} {
375: set fragmentation [percent $gap_cnt [expr {$total_pages-1}] {fragmentation}]
376: statline {Fragmentation} $fragmentation
377: }
378: statline {Maximum payload per entry} $mx_payload
379: statline {Entries that use overflow} $ovfl_cnt $ovfl_cnt_percent
380: if {$int_pages>0} {
381: statline {Index pages used} $int_pages
382: }
383: statline {Primary pages used} $leaf_pages
384: statline {Overflow pages used} $ovfl_pages
385: statline {Total pages used} $total_pages
386: if {$int_unused>0} {
387: set int_unused_percent [
388: percent $int_unused [expr {$int_pages*$pageSize}] {of index space}]
389: statline "Unused bytes on index pages" $int_unused $int_unused_percent
390: }
391: statline "Unused bytes on primary pages" $leaf_unused [
392: percent $leaf_unused [expr {$leaf_pages*$pageSize}] {of primary space}]
393: statline "Unused bytes on overflow pages" $ovfl_unused [
394: percent $ovfl_unused [expr {$ovfl_pages*$pageSize}] {of overflow space}]
395: statline "Unused bytes on all pages" $total_unused [
396: percent $total_unused $storage {of all space}]
397: return 1
398: }
399:
400: # Calculate the overhead in pages caused by auto-vacuum.
401: #
402: # This procedure calculates and returns the number of pages used by the
403: # auto-vacuum 'pointer-map'. If the database does not support auto-vacuum,
404: # then 0 is returned. The two arguments are the size of the database file in
405: # pages and the page size used by the database (in bytes).
406: proc autovacuum_overhead {filePages pageSize} {
407:
408: # Set $autovacuum to non-zero for databases that support auto-vacuum.
409: set autovacuum [db one {PRAGMA auto_vacuum}]
410:
411: # If the database is not an auto-vacuum database or the file consists
412: # of one page only then there is no overhead for auto-vacuum. Return zero.
413: if {0==$autovacuum || $filePages==1} {
414: return 0
415: }
416:
417: # The number of entries on each pointer map page. The layout of the
418: # database file is one pointer-map page, followed by $ptrsPerPage other
419: # pages, followed by a pointer-map page etc. The first pointer-map page
420: # is the second page of the file overall.
421: set ptrsPerPage [expr double($pageSize/5)]
422:
423: # Return the number of pointer map pages in the database.
424: return [expr wide(ceil( ($filePages-1.0)/($ptrsPerPage+1.0) ))]
425: }
426:
427:
428: # Calculate the summary statistics for the database and store the results
429: # in TCL variables. They are output below. Variables are as follows:
430: #
431: # pageSize: Size of each page in bytes.
432: # file_bytes: File size in bytes.
433: # file_pgcnt: Number of pages in the file.
434: # file_pgcnt2: Number of pages in the file (calculated).
435: # av_pgcnt: Pages consumed by the auto-vacuum pointer-map.
436: # av_percent: Percentage of the file consumed by auto-vacuum pointer-map.
437: # inuse_pgcnt: Data pages in the file.
438: # inuse_percent: Percentage of pages used to store data.
439: # free_pgcnt: Free pages calculated as (<total pages> - <in-use pages>)
440: # free_pgcnt2: Free pages in the file according to the file header.
441: # free_percent: Percentage of file consumed by free pages (calculated).
442: # free_percent2: Percentage of file consumed by free pages (header).
443: # ntable: Number of tables in the db.
444: # nindex: Number of indices in the db.
445: # nautoindex: Number of indices created automatically.
446: # nmanindex: Number of indices created manually.
447: # user_payload: Number of bytes of payload in table btrees
448: # (not including sqlite_master)
449: # user_percent: $user_payload as a percentage of total file size.
450:
451: ### The following, setting $file_bytes based on the actual size of the file
452: ### on disk, causes this tool to choke on zipvfs databases. So set it based
453: ### on the return of [PRAGMA page_count] instead.
454: if 0 {
455: set file_bytes [file size $file_to_analyze]
456: set file_pgcnt [expr {$file_bytes/$pageSize}]
457: }
458: set file_pgcnt [db one {PRAGMA page_count}]
459: set file_bytes [expr {$file_pgcnt * $pageSize}]
460:
461: set av_pgcnt [autovacuum_overhead $file_pgcnt $pageSize]
462: set av_percent [percent $av_pgcnt $file_pgcnt]
463:
464: set sql {SELECT sum(leaf_pages+int_pages+ovfl_pages) FROM space_used}
465: set inuse_pgcnt [expr wide([mem eval $sql])]
466: set inuse_percent [percent $inuse_pgcnt $file_pgcnt]
467:
468: set free_pgcnt [expr {$file_pgcnt-$inuse_pgcnt-$av_pgcnt}]
469: set free_percent [percent $free_pgcnt $file_pgcnt]
470: set free_pgcnt2 [db one {PRAGMA freelist_count}]
471: set free_percent2 [percent $free_pgcnt2 $file_pgcnt]
472:
473: set file_pgcnt2 [expr {$inuse_pgcnt+$free_pgcnt2+$av_pgcnt}]
474:
475: set ntable [db eval {SELECT count(*)+1 FROM sqlite_master WHERE type='table'}]
476: set nindex [db eval {SELECT count(*) FROM sqlite_master WHERE type='index'}]
477: set sql {SELECT count(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%'}
478: set nautoindex [db eval $sql]
479: set nmanindex [expr {$nindex-$nautoindex}]
480:
481: # set total_payload [mem eval "SELECT sum(payload) FROM space_used"]
482: set user_payload [mem one {SELECT int(sum(payload)) FROM space_used
483: WHERE NOT is_index AND name NOT LIKE 'sqlite_master'}]
484: set user_percent [percent $user_payload $file_bytes]
485:
486: # Output the summary statistics calculated above.
487: #
488: puts "/** Disk-Space Utilization Report For $file_to_analyze"
489: catch {
490: puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]"
491: }
492: puts ""
493: statline {Page size in bytes} $pageSize
494: statline {Pages in the whole file (measured)} $file_pgcnt
495: statline {Pages in the whole file (calculated)} $file_pgcnt2
496: statline {Pages that store data} $inuse_pgcnt $inuse_percent
497: statline {Pages on the freelist (per header)} $free_pgcnt2 $free_percent2
498: statline {Pages on the freelist (calculated)} $free_pgcnt $free_percent
499: statline {Pages of auto-vacuum overhead} $av_pgcnt $av_percent
500: statline {Number of tables in the database} $ntable
501: statline {Number of indices} $nindex
502: statline {Number of named indices} $nmanindex
503: statline {Automatically generated indices} $nautoindex
504: if {$isCompressed} {
505: statline {Size of uncompressed content in bytes} $file_bytes
506: set efficiency [percent $true_file_size $file_bytes]
507: statline {Size of compressed file on disk} $true_file_size $efficiency
508: } else {
509: statline {Size of the file in bytes} $file_bytes
510: }
511: statline {Bytes of user payload stored} $user_payload $user_percent
512:
513: # Output table rankings
514: #
515: puts ""
516: puts "*** Page counts for all tables with their indices ********************"
517: puts ""
518: mem eval {SELECT tblname, count(*) AS cnt,
519: int(sum(int_pages+leaf_pages+ovfl_pages)) AS size
520: FROM space_used GROUP BY tblname ORDER BY size+0 DESC, tblname} {} {
521: statline [string toupper $tblname] $size [percent $size $file_pgcnt]
522: }
523: if {$isCompressed} {
524: puts ""
525: puts "*** Bytes of disk space used after compression ***********************"
526: puts ""
527: set csum 0
528: mem eval {SELECT tblname,
529: int(sum(compressed_size)) +
530: $compressOverhead*sum(int_pages+leaf_pages+ovfl_pages)
531: AS csize
532: FROM space_used GROUP BY tblname ORDER BY csize+0 DESC, tblname} {} {
533: incr csum $csize
534: statline [string toupper $tblname] $csize [percent $csize $true_file_size]
535: }
536: set overhead [expr {$true_file_size - $csum}]
537: if {$overhead>0} {
538: statline {Header and free space} $overhead [percent $overhead $true_file_size]
539: }
540: }
541:
542: # Output subreports
543: #
544: if {$nindex>0} {
545: subreport {All tables and indices} 1
546: }
547: subreport {All tables} {NOT is_index}
548: if {$nindex>0} {
549: subreport {All indices} {is_index}
550: }
551: foreach tbl [mem eval {SELECT name FROM space_used WHERE NOT is_index
552: ORDER BY name}] {
553: regsub ' $tbl '' qn
554: set name [string toupper $tbl]
555: set n [mem eval "SELECT count(*) FROM space_used WHERE tblname='$qn'"]
556: if {$n>1} {
557: subreport "Table $name and all its indices" "tblname='$qn'"
558: subreport "Table $name w/o any indices" "name='$qn'"
559: subreport "Indices of table $name" "tblname='$qn' AND is_index"
560: } else {
561: subreport "Table $name" "name='$qn'"
562: }
563: }
564:
565: # Output instructions on what the numbers above mean.
566: #
567: puts {
568: *** Definitions ******************************************************
569:
570: Page size in bytes
571:
572: The number of bytes in a single page of the database file.
573: Usually 1024.
574:
575: Number of pages in the whole file
576: }
577: puts " The number of $pageSize-byte pages that go into forming the complete
578: database"
579: puts {
580: Pages that store data
581:
582: The number of pages that store data, either as primary B*Tree pages or
583: as overflow pages. The number at the right is the data pages divided by
584: the total number of pages in the file.
585:
586: Pages on the freelist
587:
588: The number of pages that are not currently in use but are reserved for
589: future use. The percentage at the right is the number of freelist pages
590: divided by the total number of pages in the file.
591:
592: Pages of auto-vacuum overhead
593:
594: The number of pages that store data used by the database to facilitate
595: auto-vacuum. This is zero for databases that do not support auto-vacuum.
596:
597: Number of tables in the database
598:
599: The number of tables in the database, including the SQLITE_MASTER table
600: used to store schema information.
601:
602: Number of indices
603:
604: The total number of indices in the database.
605:
606: Number of named indices
607:
608: The number of indices created using an explicit CREATE INDEX statement.
609:
610: Automatically generated indices
611:
612: The number of indices used to implement PRIMARY KEY or UNIQUE constraints
613: on tables.
614:
615: Size of the file in bytes
616:
617: The total amount of disk space used by the entire database files.
618:
619: Bytes of user payload stored
620:
621: The total number of bytes of user payload stored in the database. The
622: schema information in the SQLITE_MASTER table is not counted when
623: computing this number. The percentage at the right shows the payload
624: divided by the total file size.
625:
626: Percentage of total database
627:
628: The amount of the complete database file that is devoted to storing
629: information described by this category.
630:
631: Number of entries
632:
633: The total number of B-Tree key/value pairs stored under this category.
634:
635: Bytes of storage consumed
636:
637: The total amount of disk space required to store all B-Tree entries
638: under this category. The is the total number of pages used times
639: the pages size.
640:
641: Bytes of payload
642:
643: The amount of payload stored under this category. Payload is the data
644: part of table entries and the key part of index entries. The percentage
645: at the right is the bytes of payload divided by the bytes of storage
646: consumed.
647:
648: Average payload per entry
649:
650: The average amount of payload on each entry. This is just the bytes of
651: payload divided by the number of entries.
652:
653: Average unused bytes per entry
654:
655: The average amount of free space remaining on all pages under this
656: category on a per-entry basis. This is the number of unused bytes on
657: all pages divided by the number of entries.
658:
659: Fragmentation
660:
661: The percentage of pages in the table or index that are not
662: consecutive in the disk file. Many filesystems are optimized
663: for sequential file access so smaller fragmentation numbers
664: sometimes result in faster queries, especially for larger
665: database files that do not fit in the disk cache.
666:
667: Maximum payload per entry
668:
669: The largest payload size of any entry.
670:
671: Entries that use overflow
672:
673: The number of entries that user one or more overflow pages.
674:
675: Total pages used
676:
677: This is the number of pages used to hold all information in the current
678: category. This is the sum of index, primary, and overflow pages.
679:
680: Index pages used
681:
682: This is the number of pages in a table B-tree that hold only key (rowid)
683: information and no data.
684:
685: Primary pages used
686:
687: This is the number of B-tree pages that hold both key and data.
688:
689: Overflow pages used
690:
691: The total number of overflow pages used for this category.
692:
693: Unused bytes on index pages
694:
695: The total number of bytes of unused space on all index pages. The
696: percentage at the right is the number of unused bytes divided by the
697: total number of bytes on index pages.
698:
699: Unused bytes on primary pages
700:
701: The total number of bytes of unused space on all primary pages. The
702: percentage at the right is the number of unused bytes divided by the
703: total number of bytes on primary pages.
704:
705: Unused bytes on overflow pages
706:
707: The total number of bytes of unused space on all overflow pages. The
708: percentage at the right is the number of unused bytes divided by the
709: total number of bytes on overflow pages.
710:
711: Unused bytes on all pages
712:
713: The total number of bytes of unused space on all primary and overflow
714: pages. The percentage at the right is the number of unused bytes
715: divided by the total number of bytes.
716: }
717:
718: # Output a dump of the in-memory database. This can be used for more
719: # complex offline analysis.
720: #
721: puts "**********************************************************************"
722: puts "The entire text of this report can be sourced into any SQL database"
723: puts "engine for further analysis. All of the text above is an SQL comment."
724: puts "The data used to generate this report follows:"
725: puts "*/"
726: puts "BEGIN;"
727: puts $tabledef
728: unset -nocomplain x
729: mem eval {SELECT * FROM space_used} x {
730: puts -nonewline "INSERT INTO space_used VALUES"
731: set sep (
732: foreach col $x(*) {
733: set v $x($col)
734: if {$v=="" || ![string is double $v]} {set v [quote $v]}
735: puts -nonewline $sep$v
736: set sep ,
737: }
738: puts ");"
739: }
740: puts "COMMIT;"
741:
742: } err]} {
743: puts "ERROR: $err"
744: puts $errorInfo
745: exit 1
746: }
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to spaceanal.tcl CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool