Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a bug in sqlite_analyzer causing it report (slightly) incorrect values for the number of entries in indexes or WITHOUT ROWID tables. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
dc37750d4e87d0c529785adceeebd838 |
User & Date: | dan 2016-05-06 15:16:02.765 |
Context
2016-05-06
| ||
16:30 | Fix an error in main.mk causing testfixture to use individual fts5 source code files instead of fts5.c. (check-in: 933a1c818c user: dan tags: trunk) | |
15:16 | Fix a bug in sqlite_analyzer causing it report (slightly) incorrect values for the number of entries in indexes or WITHOUT ROWID tables. (check-in: dc37750d4e user: dan tags: trunk) | |
2016-05-05
| ||
11:53 | Renumber internal constants in the printf() implemention for a small performance improvement. (check-in: 69d11447f4 user: drh tags: trunk) | |
Changes
Changes to tool/spaceanal.tcl.
︙ | ︙ | |||
148 149 150 151 152 153 154 155 156 157 158 159 160 161 | # queries the in-memory db to produce the space-analysis report. # sqlite3 mem :memory: set tabledef {CREATE TABLE space_used( name clob, -- Name of a table or index in the database file tblname clob, -- Name of associated table is_index boolean, -- TRUE if it is an index, false for a table nentry int, -- Number of entries in the BTree leaf_entries int, -- Number of leaf entries depth int, -- Depth of the b-tree payload int, -- Total amount of data stored in this table or index ovfl_payload int, -- Total amount of data stored on overflow pages ovfl_cnt int, -- Number of entries that use overflow mx_payload int, -- Maximum payload size | > | 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 | # queries the in-memory db to produce the space-analysis report. # sqlite3 mem :memory: set tabledef {CREATE TABLE space_used( name clob, -- Name of a table or index in the database file tblname clob, -- Name of associated table is_index boolean, -- TRUE if it is an index, false for a table is_without_rowid boolean, -- TRUE if WITHOUT ROWID table nentry int, -- Number of entries in the BTree leaf_entries int, -- Number of leaf entries depth int, -- Depth of the b-tree payload int, -- Total amount of data stored in this table or index ovfl_payload int, -- Total amount of data stored on overflow pages ovfl_cnt int, -- Number of entries that use overflow mx_payload int, -- Maximum payload size |
︙ | ︙ | |||
180 181 182 183 184 185 186 | set isCompressed 0 set compressOverhead 0 set depth 0 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { set is_index [expr {$name!=$tblname}] | | | 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 | set isCompressed 0 set compressOverhead 0 set depth 0 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { set is_index [expr {$name!=$tblname}] set is_without_rowid [is_without_rowid $name] db eval { SELECT sum(ncell) AS nentry, sum((pagetype=='leaf')*ncell) AS leaf_entries, sum(payload) AS payload, sum((pagetype=='overflow') * payload) AS ovfl_payload, sum(path LIKE '%+000000') AS ovfl_cnt, |
︙ | ︙ | |||
231 232 233 234 235 236 237 238 239 240 241 242 243 244 | set prev $pageno } mem eval { INSERT INTO space_used VALUES( $name, $tblname, $is_index, $nentry, $leaf_entries, $depth, $payload, $ovfl_payload, $ovfl_cnt, $mx_payload, | > | 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 | set prev $pageno } mem eval { INSERT INTO space_used VALUES( $name, $tblname, $is_index, $is_without_rowid, $nentry, $leaf_entries, $depth, $payload, $ovfl_payload, $ovfl_cnt, $mx_payload, |
︙ | ︙ | |||
326 327 328 329 330 331 332 | # Query the in-memory database for the sum of various statistics # for the subset of tables/indices identified by the WHERE clause in # $where. Note that even if the WHERE clause matches no rows, the # following query returns exactly one row (because it is an aggregate). # # The results of the query are stored directly by SQLite into local | | | > | > > | 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 | # Query the in-memory database for the sum of various statistics # for the subset of tables/indices identified by the WHERE clause in # $where. Note that even if the WHERE clause matches no rows, the # following query returns exactly one row (because it is an aggregate). # # The results of the query are stored directly by SQLite into local # variables (i.e. $nentry, $payload etc.). # mem eval " SELECT int(sum( CASE WHEN (is_without_rowid OR is_index) THEN nentry ELSE leaf_entries END )) AS nentry, int(sum(payload)) AS payload, int(sum(ovfl_payload)) AS ovfl_payload, max(mx_payload) AS mx_payload, int(sum(ovfl_cnt)) as ovfl_cnt, int(sum(leaf_pages)) AS leaf_pages, int(sum(int_pages)) AS int_pages, int(sum(ovfl_pages)) AS ovfl_pages, |
︙ | ︙ | |||
371 372 373 374 375 376 377 | # 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}] | | | | | | 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 | # 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 ) "] set avg_fanout [mem eval " SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used WHERE $where "] set avg_fanout [format %.2f $avg_fanout] } set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nentry statline {Bytes of storage consumed} $storage 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 } |
︙ | ︙ |