2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # Run this TCL script using "testfixture" in order get a report that shows
5 # how much disk space is used by a particular data to actually store data
6 # versus how much space is unused.
9 # Get the name of the database to analyze
11 if {[llength $argv]!=1} {
12 puts stderr
"Usage: $argv0 database-name"
15 set file_to_analyze
[lindex $argv 0]
16 if {![file exists
$file_to_analyze]} {
17 puts stderr
"No such file: $file_to_analyze"
20 if {![file readable
$file_to_analyze]} {
21 puts stderr
"File is not readable: $file_to_analyze"
24 if {[file size
$file_to_analyze]<2048} {
25 puts stderr
"Empty or malformed database: $file_to_analyze"
31 sqlite db
[lindex $argv 0]
32 set DB
[btree_open
[lindex $argv 0]]
34 # In-memory database for collecting statistics
38 {CREATE TABLE space_used
(
39 name clob
, -- Name of a table or index in the database
file
40 tblname clob
, -- Name of associated table
41 is_index boolean
, -- TRUE
if it is an index
, false
for a table
42 nentry int
, -- Number of entries in the BTree
43 payload int
, -- Total amount of data stored in this table or index
44 mx_payload int
, -- Maximum payload size
45 n_ovfl int
, -- Number of entries that overflow
46 pri_pages int
, -- Number of primary pages used
47 ovfl_pages int
, -- Number of overflow pages used
48 pri_unused int
, -- Number of unused bytes on primary pages
49 ovfl_unused int
-- Number of unused bytes on overflow pages
53 # This query will be used to find the root page number for every index and
54 # table in the database.
57 SELECT name
, tbl_name
, type
, rootpage
58 FROM sqlite_master WHERE type IN
('table'
,'index'
)
60 SELECT 'sqlite_master'
, 'sqlite_master'
, 'table'
, 2
64 # Analyze every table in the database, one at a time.
66 foreach {name tblname type rootpage
} [db
eval $sql] {
67 puts stderr
"Analyzing $name..."
68 set cursor
[btree_cursor
$DB $rootpage 0]
69 set go
[btree_first
$cursor]
77 set pg_used
($rootpage) 1016
80 set payload
[btree_payload_size
$cursor]
82 set stat
[btree_cursor_dump
$cursor]
83 set pgno
[lindex $stat 0]
84 set freebytes
[lindex $stat 4]
85 set pg_used
($pgno) $freebytes
87 # if {[lindex $stat 8]==0} {error "overflow is empty with $payload"}
88 set n
[expr {($payload-236+1019)/1020}]
91 incr unused_ovfl
[expr {$n*1020+236-$payload}]
93 # if {[lindex $stat 8]!=0} {error "overflow not empty with $payload"}
95 if {$payload>$mx_size} {set mx_size
$payload}
96 set go
[btree_next
$cursor]
98 btree_close_cursor
$cursor
99 set n_primary
[llength [array names pg_used
]]
101 foreach x
[array names pg_used
] {incr unused_primary
$pg_used($x)}
102 regsub -all '
$name '' name
103 set sql
"INSERT INTO space_used VALUES('$name'"
104 regsub -all '
$tblname '' tblname
105 append sql
",'$tblname',[expr {$type=="index
"}],$n_entry"
106 append sql
",$size,$mx_size,$cnt_ovfl,"
107 append sql
"$n_primary,$n_overflow,$unused_primary,$unused_ovfl);"
111 # Generate a single line of output in the statistics section of the
114 proc statline
{title value
{extra
{}}} {
115 set len
[string length
$title]
116 set dots
[string range
{......................................
} $len end
]
117 set len
[string length
$value]
118 set sp2
[string range
{ } $len end
]
122 puts "$title$dots $value$sp2$extra"
125 # Generate a formatted percentage value for $num/$denom
127 proc percent
{num denom
} {
128 if {$denom==0.0} {return ""}
129 set v
[expr {$num*100.0/$denom}]
130 if {$v>1.0 && $v<99.0} {
131 return [format %4.1f
%% $v]
132 } elseif
{$v<0.1 ||
$v>99.9} {
133 return [format %6.3f
%% $v]
135 return [format %5.2f
%% $v]
139 # Generate a subreport that covers some subset of the database.
140 # the $where clause determines which subset to analyze.
142 proc subreport
{title where
} {
144 mem
eval "SELECT sum(nentry) AS nentry, \
145 sum(payload) AS payload, \
146 sum(CASE is_index WHEN 1 THEN 0 ELSE payload-4*nentry END) \
148 max(mx_payload) AS mx_payload, \
149 sum(n_ovfl) as n_ovfl, \
150 sum(pri_pages) AS pri_pages, \
151 sum(ovfl_pages) AS ovfl_pages, \
152 sum(pri_unused) AS pri_unused, \
153 sum(ovfl_unused) AS ovfl_unused \
154 FROM space_used WHERE $where" {} {set hit
1}
155 if {!$hit} {return 0}
157 set len
[string length
$title]
159 set stars
"***********************************"
161 set stars
[string range
$stars $len end
]
162 puts "*** $title $stars"
164 statline
"Percentage of total database" \
165 [percent
[expr {$pri_pages+$ovfl_pages}] $::file_pgcnt]
166 statline
"Number of entries" $nentry
167 set storage
[expr {($pri_pages+$ovfl_pages)*1024}]
168 statline
"Bytes of storage consumed" $storage
169 statline
"Bytes of payload" $payload [percent
$payload $storage]
170 statline
"Bytes of data" $data [percent
$data $storage]
171 set key
[expr {$payload-$data}]
172 statline
"Bytes of key" $key [percent
$key $storage]
173 set avgpay
[expr {$nentry>0?
$payload/$nentry:0}]
174 statline
"Average payload per entry" $avgpay
175 set avgunused
[expr {$nentry>0?
($pri_unused+$ovfl_unused)/$nentry:0}]
176 statline
"Average unused bytes per entry" $avgunused
177 statline
"Average fanout" \
178 [format %.2f
[expr {$pri_pages==0?
0:($nentry+0.0)/$pri_pages}]]
179 statline
"Maximum payload per entry" $mx_payload
180 statline
"Entries that use overflow" $n_ovfl [percent
$n_ovfl $nentry]
181 statline
"Total pages used" [set allpgs
[expr {$pri_pages+$ovfl_pages}]]
182 statline
"Primary pages used" $pri_pages ;# [percent $pri_pages $allpgs]
183 statline
"Overflow pages used" $ovfl_pages ;# [percent $ovfl_pages $allpgs]
184 statline
"Unused bytes on primary pages" $pri_unused \
185 [percent
$pri_unused [expr {$pri_pages*1024}]]
186 statline
"Unused bytes on overflow pages" $ovfl_unused \
187 [percent
$ovfl_unused [expr {$ovfl_pages*1024}]]
188 set allunused
[expr {$ovfl_unused+$pri_unused}]
189 statline
"Unused bytes on all pages" $allunused \
190 [percent
$allunused [expr {$allpgs*1024}]]
194 # Output summary statistics:
196 puts "/** Disk-Space Utilization Report For $file_to_analyze"
197 puts "*** As of [clock format [clock seconds] -format {%Y-%b-%d %H:%M:%S}]"
199 set fsize
[file size
[lindex $argv 0]]
200 set file_pgcnt
[expr {$fsize/1024}]
201 set usedcnt
[mem
eval {SELECT sum
(pri_pages
+ovfl_pages
) FROM space_used
}]
202 set freecnt
[expr {$file_pgcnt-$usedcnt-1}]
203 set freecnt2
[lindex [btree_get_meta
$DB] 0]
204 statline
{Pages in the whole
file (measured
)} $file_pgcnt
205 set file_pgcnt2
[expr {$usedcnt+$freecnt2+1}]
206 statline
{Pages in the whole
file (calculated
)} $file_pgcnt2
207 statline
{Pages that store data
} $usedcnt [percent
$usedcnt $file_pgcnt]
208 statline
{Pages on the freelist
(per header
)}\
209 $freecnt2 [percent
$freecnt2 $file_pgcnt]
210 statline
{Pages on the freelist
(calculated
)}\
211 $freecnt [percent
$freecnt $file_pgcnt]
212 statline
{Header pages
} 1 [percent
1 $file_pgcnt]
214 set ntable
[db
eval {SELECT count
(*)+1 FROM sqlite_master WHERE type
='table'
}]
215 statline
{Number of tables in the database
} $ntable
216 set nindex
[db
eval {SELECT count
(*) FROM sqlite_master WHERE type
='index'
}]
217 set autoindex
[db
eval {SELECT count
(*) FROM sqlite_master
218 WHERE type
='index' AND name LIKE '
(% autoindex
%)'
}]
219 set manindex
[expr {$nindex-$autoindex}]
220 statline
{Number of indices
} $nindex
221 statline
{Number of named indices
} $manindex [percent
$manindex $nindex]
222 statline
{Automatically generated indices
} $autoindex \
223 [percent
$autoindex $nindex]
225 set bytes_data
[mem
eval "SELECT sum(payload-4*nentry) FROM space_used
226 WHERE NOT is_index AND name!='sqlite_master'"]
227 set total_payload
[mem
eval "SELECT sum(payload) FROM space_used"]
228 statline
"Size of the file in bytes" $fsize
229 statline
"Bytes of payload stored" $total_payload \
230 [percent
$total_payload $fsize]
231 statline
"Bytes of user data stored" $bytes_data \
232 [percent
$bytes_data $fsize]
234 # Output table rankings
237 puts "*** Page counts for all tables with their indices ********************"
239 mem
eval {SELECT tblname
, count
(*) AS cnt
, sum
(pri_pages
+ovfl_pages
) AS size
240 FROM space_used GROUP BY tblname ORDER BY size DESC
, tblname
} {} {
241 statline
[string toupper
$tblname] $size [percent
$size $file_pgcnt]
247 subreport
{All tables and indices
} 1
249 subreport
{All tables
} {NOT is_index
}
251 subreport
{All indices
} {is_index
}
253 foreach tbl
[mem
eval {SELECT name FROM space_used WHERE NOT is_index
256 set name
[string toupper
$tbl]
257 set n
[mem
eval "SELECT count(*) FROM space_used WHERE tblname='$qn'"]
259 subreport
"Table $name and all its indices" "tblname='$qn'"
260 subreport
"Table $name w/o any indices" "name='$qn'"
261 subreport
"Indices of table $name" "tblname='$qn' AND is_index"
263 subreport
"Table $name" "name='$qn'"
267 # Output instructions on what the numbers above mean.
270 *** Definitions
******************************************************
272 Number of pages in the whole
file
274 The number of
1024-byte pages that go into forming the complete database
276 Pages that store data
278 The number of pages that store data
, either as primary B
*Tree pages or
279 as overflow pages. The number at the right is the data pages divided by
280 the total number of pages in the
file.
282 Pages on the freelist
284 The number of pages that are not currently in use but are reserved
for
285 future use. The percentage at the right is the number of freelist pages
286 divided by the total number of pages in the
file.
290 The number of pages of header overhead in the database. This value is
291 always
1. The percentage at the right is the number of header pages
292 divided by the total number of pages in the
file.
294 Number of tables in the database
296 The number of tables in the database
, including the SQLITE_MASTER table
297 used to store schema information.
301 The total number of indices in the database.
303 Number of named indices
305 The number of indices created using an explicit CREATE INDEX statement.
307 Automatically generated indices
309 The number of indices used to implement PRIMARY KEY or UNIQUE constraints
312 Size of the
file in bytes
314 The total amount of disk space used by the entire database files.
316 Bytes of payload stored
318 The total number of bytes of payload stored in the database. Payload
319 includes both key and data. The content of the SQLITE_MASTER table is
320 counted when computing this number. The percentage at the right shows
321 the payload divided by the total
file size.
323 Bytes of user data stored
325 The total number of bytes of data stored in the database
, not counting
326 the database schema information stored in the SQLITE_MASTER table. The
327 percentage at the right is the user data size divided by the total
file
330 Percentage of total database
332 The amount of the complete database
file that is devoted to storing
333 information described by this category.
337 The total number of B
*Tree key
/value pairs stored under this category.
339 Bytes of storage consumed
341 The total amount of disk space required to store all B
*Tree entries
342 under this category. The is the total number of pages used times
343 the pages size
(1024).
347 The amount of payload stored under this category. Payload is the sum
348 of keys and data. Each table
entry has
4 bytes of key and an arbitrary
349 amount of data. Each index
entry has
4 or more bytes of key and no
350 data. The percentage at the right is the bytes of payload divided by
351 the bytes of storage consumed.
355 The amount of data stored under this category. The data space reported
356 includes formatting information such as nul-terminators and field-lengths
357 that are stored with the data. The percentage at the right is the bytes
358 of data divided by bytes of storage consumed.
362 The sum of the sizes of all keys under this category. The percentage at
363 the right is the bytes of key divided by the bytes of storage consumed.
365 Average payload per
entry
367 The average amount of payload on each
entry. This is just the bytes of
368 payload divided by the number of entries.
370 Average unused bytes per
entry
372 The average amount of free space remaining on all pages under this
373 category on a per-entry basis. This is the number of unused bytes on
374 all pages divided by the number of entries.
376 Maximum payload per
entry
378 The largest payload size of any
entry.
380 Entries that use overflow
382 Up to
236 bytes of payload
for each
entry are stored directly in the
383 primary B
*Tree page. Any additional payload is stored on a linked
list
384 of overflow pages. This is the number of entries that exceed
236 bytes
385 in size. The value to the right is the number of entries that overflow
386 divided by the total number of entries.
390 This is the number of
1024 byte pages used to hold all information in
391 the current category. This is the sum of primary and overflow pages.
395 This is the number of primary B
*Tree pages used.
399 The total number of overflow pages used
for this category.
401 Unused bytes on primary pages
403 The total number of bytes of unused space on all primary pages. The
404 percentage at the right is the number of unused bytes divided by the
405 total number of bytes on primary pages.
407 Unused bytes on overflow pages
409 The total number of bytes of unused space on all overflow pages. The
410 percentage at the right is the number of unused bytes divided by the
411 total number of bytes on overflow pages.
413 Unused bytes on all pages
415 The total number of bytes of unused space on all primary and overflow
416 pages. The percentage at the right is the number of unused bytes
417 divided by the total number of bytes.
420 # Output the database
422 puts "**********************************************************************"
423 puts "The entire text of this report can be sourced into any SQL database"
424 puts "engine for further analysis. All of the text above is an SQL comment."
425 puts "The data used to generate this report follows:"
430 mem
eval {SELECT
* FROM space_used
} x
{
431 puts -nonewline "INSERT INTO space_used VALUES("
432 regsub '
$x(name
) '' qn
433 regsub '
$x(tblname
) '' qtn
434 puts -nonewline "'$qn','$qtn',"
435 puts -nonewline "$x(is_index),$x(nentry),$x(payload),$x(mx_payload),"
436 puts -nonewline "$x(n_ovfl),$x(pri_pages),$x(ovfl_pages),$x(pri_unused),"
437 puts "$x(ovfl_unused));"