Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enhance the stat VFS to report out the total size of all pages used by a table, even if the ZIPVFS compression backend is in play. Update the sqlite3_analyzer logic to use these new outputs. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
19b8eaaf70db82d401d33beb7fd36045 |
User & Date: | drh 2011-09-28 00:50:14.068 |
Context
2011-09-28
| ||
01:10 | In the shell, allow arbitrary table names on the ".import" command. Ticket [d1d84037b90a449]. (check-in: f4dd32d30e user: drh tags: trunk) | |
00:50 | Enhance the stat VFS to report out the total size of all pages used by a table, even if the ZIPVFS compression backend is in play. Update the sqlite3_analyzer logic to use these new outputs. (check-in: 19b8eaaf70 user: drh tags: trunk) | |
2011-09-27
| ||
13:40 | Changes to sqlite3_analyzer to try to avoid integer overflow problems when linking against older versions of TCL. (check-in: 8c846311a0 user: drh tags: trunk) | |
Changes
Changes to src/test_stat.c.
︙ | ︙ | |||
60 61 62 63 64 65 66 | " name STRING, /* Name of table or index */" \ " path INTEGER, /* Path to page from root */" \ " pageno INTEGER, /* Page number */" \ " pagetype STRING, /* 'internal', 'leaf' or 'overflow' */" \ " ncell INTEGER, /* Cells on page (0 for overflow) */" \ " payload INTEGER, /* Bytes of payload on this page */" \ " unused INTEGER, /* Bytes of unused space on this page */" \ | | < | < < | < < < < < < | 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | " name STRING, /* Name of table or index */" \ " path INTEGER, /* Path to page from root */" \ " pageno INTEGER, /* Page number */" \ " pagetype STRING, /* 'internal', 'leaf' or 'overflow' */" \ " ncell INTEGER, /* Cells on page (0 for overflow) */" \ " payload INTEGER, /* Bytes of payload on this page */" \ " unused INTEGER, /* Bytes of unused space on this page */" \ " mx_payload INTEGER, /* Largest payload size of all cells */" \ " pgoffset INTEGER, /* Offset of page in file */" \ " pgsize INTEGER /* Size of the page */" \ ");" typedef struct StatTable StatTable; typedef struct StatCursor StatCursor; typedef struct StatPage StatPage; typedef struct StatCell StatCell; |
︙ | ︙ | |||
122 123 124 125 126 127 128 129 130 131 132 133 134 135 | char *zPath; /* Value of 'path' column */ u32 iPageno; /* Value of 'pageno' column */ char *zPagetype; /* Value of 'pagetype' column */ int nCell; /* Value of 'ncell' column */ int nPayload; /* Value of 'payload' column */ int nUnused; /* Value of 'unused' column */ int nMxPayload; /* Value of 'mx_payload' column */ }; struct StatTable { sqlite3_vtab base; sqlite3 *db; }; | > > | 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 | char *zPath; /* Value of 'path' column */ u32 iPageno; /* Value of 'pageno' column */ char *zPagetype; /* Value of 'pagetype' column */ int nCell; /* Value of 'ncell' column */ int nPayload; /* Value of 'payload' column */ int nUnused; /* Value of 'unused' column */ int nMxPayload; /* Value of 'mx_payload' column */ i64 iOffset; /* Value of 'pgOffset' column */ int szPage; /* Value of 'pgSize' column */ }; struct StatTable { sqlite3_vtab base; sqlite3 *db; }; |
︙ | ︙ | |||
279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 | } static int statDecodePage(Btree *pBt, StatPage *p){ int nUnused; int iOff; int nHdr; int isLeaf; u8 *aData = sqlite3PagerGetData(p->pPg); u8 *aHdr = &aData[p->iPgno==1 ? 100 : 0]; p->flags = aHdr[0]; p->nCell = get2byte(&aHdr[3]); p->nMxPayload = 0; isLeaf = (p->flags==0x0A || p->flags==0x0D); nHdr = 12 - isLeaf*4 + (p->iPgno==1)*100; nUnused = get2byte(&aHdr[5]) - nHdr - 2*p->nCell; nUnused += (int)aHdr[7]; iOff = get2byte(&aHdr[1]); while( iOff ){ nUnused += get2byte(&aData[iOff+2]); iOff = get2byte(&aData[iOff]); } p->nUnused = nUnused; p->iRightChildPg = isLeaf ? 0 : sqlite3Get4byte(&aHdr[8]); if( p->nCell ){ int i; /* Used to iterate through cells */ | > > | | 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 | } static int statDecodePage(Btree *pBt, StatPage *p){ int nUnused; int iOff; int nHdr; int isLeaf; int szPage; u8 *aData = sqlite3PagerGetData(p->pPg); u8 *aHdr = &aData[p->iPgno==1 ? 100 : 0]; p->flags = aHdr[0]; p->nCell = get2byte(&aHdr[3]); p->nMxPayload = 0; isLeaf = (p->flags==0x0A || p->flags==0x0D); nHdr = 12 - isLeaf*4 + (p->iPgno==1)*100; nUnused = get2byte(&aHdr[5]) - nHdr - 2*p->nCell; nUnused += (int)aHdr[7]; iOff = get2byte(&aHdr[1]); while( iOff ){ nUnused += get2byte(&aData[iOff+2]); iOff = get2byte(&aData[iOff]); } p->nUnused = nUnused; p->iRightChildPg = isLeaf ? 0 : sqlite3Get4byte(&aHdr[8]); szPage = sqlite3BtreeGetPageSize(pBt); if( p->nCell ){ int i; /* Used to iterate through cells */ int nUsable = szPage - sqlite3BtreeGetReserve(pBt); p->aCell = sqlite3_malloc((p->nCell+1) * sizeof(StatCell)); memset(p->aCell, 0, (p->nCell+1) * sizeof(StatCell)); for(i=0; i<p->nCell; i++){ StatCell *pCell = &p->aCell[i]; |
︙ | ︙ | |||
447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 | /* Populate the StatCursor fields with the values to be returned ** by the xColumn() and xRowid() methods. */ if( rc==SQLITE_OK ){ int i; StatPage *p = &pCsr->aPage[pCsr->iPage]; pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0); pCsr->iPageno = p->iPgno; statDecodePage(pBt, p); switch( p->flags ){ case 0x05: /* table internal */ case 0x02: /* index internal */ pCsr->zPagetype = "internal"; break; case 0x0D: /* table leaf */ | > > > > > > > > > > > > > > > > | 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 | /* Populate the StatCursor fields with the values to be returned ** by the xColumn() and xRowid() methods. */ if( rc==SQLITE_OK ){ int i; sqlite3_file *fd; sqlite3_int64 x[2]; StatPage *p = &pCsr->aPage[pCsr->iPage]; pCsr->zName = (char *)sqlite3_column_text(pCsr->pStmt, 0); pCsr->iPageno = p->iPgno; statDecodePage(pBt, p); /* The default page size and offset */ pCsr->szPage = sqlite3BtreeGetPageSize(pBt); pCsr->iOffset = pCsr->szPage * (p->iPgno - 1); /* If connected to a ZIPVFS backend, override the page size and ** offset with actual values obtained from ZIPVFS. */ fd = sqlite3PagerFile(pPager); x[0] = p->iPgno; if( sqlite3OsFileControl(fd, 230440, &x)==SQLITE_OK ){ pCsr->iOffset = x[0]; pCsr->szPage = x[1]; } switch( p->flags ){ case 0x05: /* table internal */ case 0x02: /* index internal */ pCsr->zPagetype = "internal"; break; case 0x0D: /* table leaf */ |
︙ | ︙ | |||
527 528 529 530 531 532 533 534 535 536 537 538 539 540 | break; case 6: /* unused */ sqlite3_result_int(ctx, pCsr->nUnused); break; case 7: /* mx_payload */ sqlite3_result_int(ctx, pCsr->nMxPayload); break; } return SQLITE_OK; } static int statRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ StatCursor *pCsr = (StatCursor *)pCursor; *pRowid = pCsr->iPageno; | > > > > > > | 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 | break; case 6: /* unused */ sqlite3_result_int(ctx, pCsr->nUnused); break; case 7: /* mx_payload */ sqlite3_result_int(ctx, pCsr->nMxPayload); break; case 8: /* pgoffset */ sqlite3_result_int64(ctx, pCsr->iOffset); break; case 9: /* pgsize */ sqlite3_result_int(ctx, pCsr->szPage); break; } return SQLITE_OK; } static int statRowid(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ StatCursor *pCsr = (StatCursor *)pCursor; *pRowid = pCsr->iPageno; |
︙ | ︙ | |||
603 604 605 606 607 608 609 | } int SqlitetestStat_Init(Tcl_Interp *interp){ Tcl_CreateObjCommand(interp, "register_dbstat_vtab", test_dbstat, 0, 0); return TCL_OK; } #endif /* if defined(SQLITE_TEST) || TCLSH==2 */ | < | 620 621 622 623 624 625 626 | } int SqlitetestStat_Init(Tcl_Interp *interp){ Tcl_CreateObjCommand(interp, "register_dbstat_vtab", test_dbstat, 0, 0); return TCL_OK; } #endif /* if defined(SQLITE_TEST) || TCLSH==2 */ |
Changes to test/stat.test.
︙ | ︙ | |||
32 33 34 35 36 37 38 | SELECT * FROM stat; } {} ifcapable wal { do_execsql_test stat-0.1 { PRAGMA journal_mode = WAL; PRAGMA journal_mode = delete; | > | > | > | > | > | | 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | SELECT * FROM stat; } {} ifcapable wal { do_execsql_test stat-0.1 { PRAGMA journal_mode = WAL; PRAGMA journal_mode = delete; SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat; } {wal delete sqlite_master / 1 leaf 0 0 916 0} } do_test stat-1.0 { execsql { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(b); INSERT INTO t1(rowid, a, b) VALUES(2, 2, 3); INSERT INTO t1(rowid, a, b) VALUES(3, 4, 5); } } {} do_test stat-1.1 { execsql { SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name = 't1'; } } {t1 / 2 leaf 2 10 998 5} do_test stat-1.2 { execsql { SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name = 'i1'; } } {i1 / 3 leaf 2 10 1000 5} do_test stat-1.3 { execsql { SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name = 'sqlite_master'; } } {sqlite_master / 1 leaf 2 77 831 40} do_test stat-1.4 { execsql { DROP TABLE t1; } } {} do_execsql_test stat-2.1 { CREATE TABLE t3(a PRIMARY KEY, b); INSERT INTO t3(rowid, a, b) VALUES(2, a_string(111), a_string(222)); INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; INSERT INTO t3 SELECT a_string(110+rowid), a_string(221+rowid) FROM t3; SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name != 'sqlite_master'; } [list \ sqlite_autoindex_t3_1 / 3 internal 3 368 623 125 \ sqlite_autoindex_t3_1 /000/ 8 leaf 8 946 46 123 \ sqlite_autoindex_t3_1 /001/ 9 leaf 8 988 2 131 \ sqlite_autoindex_t3_1 /002/ 15 leaf 7 857 137 132 \ sqlite_autoindex_t3_1 /003/ 20 leaf 6 739 257 129 \ t3 / 2 internal 15 0 907 0 \ |
︙ | ︙ | |||
104 105 106 107 108 109 110 | ] do_execsql_test stat-2.2 { DROP TABLE t3 } {} do_execsql_test stat-3.1 { CREATE TABLE t4(x); CREATE INDEX i4 ON t4(x); INSERT INTO t4(rowid, x) VALUES(2, a_string(7777)); | > | | 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 | ] do_execsql_test stat-2.2 { DROP TABLE t3 } {} do_execsql_test stat-3.1 { CREATE TABLE t4(x); CREATE INDEX i4 ON t4(x); INSERT INTO t4(rowid, x) VALUES(2, a_string(7777)); SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name != 'sqlite_master'; } [list \ i4 / 3 leaf 1 103 905 7782 \ i4 /000+000000 9 overflow 0 1020 0 0 \ i4 /000+000001 10 overflow 0 1020 0 0 \ i4 /000+000002 11 overflow 0 1020 0 0 \ i4 /000+000003 12 overflow 0 1020 0 0 \ i4 /000+000004 13 overflow 0 1020 0 0 \ |
︙ | ︙ | |||
128 129 130 131 132 133 134 | t4 /000+000005 18 overflow 0 1020 0 0 \ t4 /000+000006 17 overflow 0 1020 0 0 \ ] do_execsql_test stat-4.1 { CREATE TABLE t5(x); CREATE INDEX i5 ON t5(x); | > | > | | 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 | t4 /000+000005 18 overflow 0 1020 0 0 \ t4 /000+000006 17 overflow 0 1020 0 0 \ ] do_execsql_test stat-4.1 { CREATE TABLE t5(x); CREATE INDEX i5 ON t5(x); SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name = 't5' OR name = 'i5'; } [list \ i5 / 5 leaf 0 0 1016 0 \ t5 / 4 leaf 0 0 1016 0 \ ] db close forcedelete test.db sqlite3 db test.db register_dbstat_vtab db breakpoint do_execsql_test stat-5.1 { PRAGMA auto_vacuum = OFF; CREATE VIRTUAL TABLE temp.stat USING dbstat; CREATE TABLE t1(x); INSERT INTO t1 VALUES(zeroblob(1513)); INSERT INTO t1 VALUES(zeroblob(1514)); SELECT name, path, pageno, pagetype, ncell, payload, unused, mx_payload FROM stat WHERE name = 't1'; } [list \ t1 / 2 leaf 2 993 5 1517 \ t1 /000+000000 3 overflow 0 1020 0 0 \ t1 /001+000000 4 overflow 0 1020 0 0 \ ] finish_test |
Changes to tool/spaceanal.tcl.
︙ | ︙ | |||
55 56 57 58 59 60 61 | mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int, -- Number of unused bytes on overflow pages | | > | 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | mx_payload int, -- Maximum payload size int_pages int, -- Number of interior pages used leaf_pages int, -- Number of leaf pages used ovfl_pages int, -- Number of overflow pages used int_unused int, -- Number of unused bytes on interior pages leaf_unused int, -- Number of unused bytes on primary pages ovfl_unused int, -- Number of unused bytes on overflow pages gap_cnt int, -- Number of gaps in the page layout compressed_size int -- Total bytes stored on disk );} mem eval $tabledef # Create a temporary "dbstat" virtual table. # db eval {CREATE VIRTUAL TABLE temp.stat USING dbstat} db eval {CREATE TEMP TABLE dbstat AS SELECT * FROM temp.stat |
︙ | ︙ | |||
97 98 99 100 101 102 103 | sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, sum(isinternal(pagetype, $is_index)) AS int_pages, sum(isleaf(pagetype, $is_index)) AS leaf_pages, sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, sum(isinternal(pagetype, $is_index) * unused) AS int_unused, sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, | | > | 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 | sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, sum(isinternal(pagetype, $is_index)) AS int_pages, sum(isleaf(pagetype, $is_index)) AS leaf_pages, sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, sum(isinternal(pagetype, $is_index) * unused) AS int_unused, sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused, sum(pgsize) AS compressed_size FROM temp.dbstat WHERE name = $name } break # Column 'gap_cnt' is set to the number of non-contiguous entries in the # list of pages visited if the b-tree structure is traversed in a top-down # fashion (each node visited before its child-tree is passed). Any overflow # chains present are traversed from start to finish before any child-tree |
︙ | ︙ | |||
134 135 136 137 138 139 140 | $mx_payload, $int_pages, $leaf_pages, $ovfl_pages, $int_unused, $leaf_unused, $ovfl_unused, | | > | 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | $mx_payload, $int_pages, $leaf_pages, $ovfl_pages, $int_unused, $leaf_unused, $ovfl_unused, $gap_cnt, $compressed_size ); } } proc integerify {real} { if {[string is double -strict $real]} { return [expr {wide($real)}] |
︙ | ︙ | |||
220 221 222 223 224 225 226 | 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, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused, | | > | 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | 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, int(sum(leaf_unused)) AS leaf_unused, int(sum(int_unused)) AS int_unused, int(sum(ovfl_unused)) AS ovfl_unused, int(sum(gap_cnt)) AS gap_cnt, int(sum(compressed_size)) AS compressed_size FROM space_used WHERE $where" {} {} # Output the sub-report title, nicely decorated with * characters. # puts "" set len [string length $title] set stars [string repeat * [expr 65-$len]] |
︙ | ︙ | |||
270 271 272 273 274 275 276 277 278 279 280 281 282 283 | set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nleaf statline {Bytes of storage consumed} $storage 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} { | > > > > > | 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 | set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}] # Print out the sub-report statistics. # statline {Percentage of total database} $total_pages_percent statline {Number of entries} $nleaf statline {Bytes of storage consumed} $storage if {$compressed_size!=$storage} { set pct [expr {$compressed_size*100.0/$storage}] set pct [format {%5.1f%%} $pct] statline {Bytes used after compression} $compressed_size $pct } 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} { |
︙ | ︙ |