Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add the "space_used.tcl" script to the tools directory. This script is used to measure how much disk space is used by each table and index of a database. (CVS 789) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
83b2c27a568cd67cd5162d513766b23f |
User & Date: | drh 2002-11-24 14:52:27.000 |
Context
2002-12-01
| ||
02:00 | Fixed large file support under Linux. I'm unable to test under Windows. Ticket #191. (CVS 790) (check-in: 9864a1265b user: drh tags: trunk) | |
2002-11-24
| ||
14:52 | Add the "space_used.tcl" script to the tools directory. This script is used to measure how much disk space is used by each table and index of a database. (CVS 789) (check-in: 83b2c27a56 user: drh tags: trunk) | |
2002-11-20
| ||
11:55 | Automatically determine if pointers are 4 or 8 bytes in size during the compilation process. Ticket #190. (CVS 788) (check-in: dd5396a73a user: drh tags: trunk) | |
Changes
Changes to main.mk.
︙ | ︙ | |||
208 209 210 211 212 213 214 | echo '"#define SQLITE_PTR_SZ %d\n",sizeof(char*));' >>temp.c echo 'exit(0);}' >>temp.c $(BCC) -o temp temp.c ./temp >config.h rm -f temp.c temp sqlite.h: $(TOP)/src/sqlite.h.in | < | 208 209 210 211 212 213 214 215 216 217 218 219 220 221 | echo '"#define SQLITE_PTR_SZ %d\n",sizeof(char*));' >>temp.c echo 'exit(0);}' >>temp.c $(BCC) -o temp temp.c ./temp >config.h rm -f temp.c temp sqlite.h: $(TOP)/src/sqlite.h.in sed -e s/--VERS--/`cat ${TOP}/VERSION`/ \ -e s/--ENCODING--/$(ENCODING)/ \ $(TOP)/src/sqlite.h.in >sqlite.h tokenize.o: $(TOP)/src/tokenize.c $(HDR) $(TCCX) -c $(TOP)/src/tokenize.c |
︙ | ︙ |
Changes to src/test3.c.
︙ | ︙ | |||
9 10 11 12 13 14 15 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the btree.c module in SQLite. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** | | | 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ** May you share freely, never taking more than you give. ** ************************************************************************* ** Code for testing the btree.c module in SQLite. This code ** is not included in the SQLite library. It is used for automated ** testing of the SQLite library. ** ** $Id: test3.c,v 1.20 2002/11/24 14:52:27 drh Exp $ */ #include "sqliteInt.h" #include "pager.h" #include "btree.h" #include "tcl.h" #include <stdlib.h> #include <string.h> |
︙ | ︙ | |||
818 819 820 821 822 823 824 | return TCL_ERROR; } if( Tcl_GetInt(interp, argv[1], (int*)&pCur) ) return TCL_ERROR; sqliteBtreeKeySize(pCur, &n1); sqliteBtreeDataSize(pCur, &n2); sprintf(zBuf, "%d", n1+n2); Tcl_AppendResult(interp, zBuf, 0); | < | 818 819 820 821 822 823 824 825 826 827 828 829 830 831 | return TCL_ERROR; } if( Tcl_GetInt(interp, argv[1], (int*)&pCur) ) return TCL_ERROR; sqliteBtreeKeySize(pCur, &n1); sqliteBtreeDataSize(pCur, &n2); sprintf(zBuf, "%d", n1+n2); Tcl_AppendResult(interp, zBuf, 0); return SQLITE_OK; } /* ** Usage: btree_cursor_dump ID ** ** Return eight integers containing information about the entry the |
︙ | ︙ |
Added tool/space_used.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 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 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 | # Run this TCL script using "testfixture" in order get a report that shows # how much disk space is used by a particular data to actually store data # versus how much space is unused. # # Get the name of the database to analyze # if {[llength $argv]!=1} { puts stderr "Usage: $argv0 database-name" exit 1 } set file_to_analyze [lindex $argv 0] # Open the database # sqlite db [lindex $argv 0] set DB [btree_open [lindex $argv 0]] # Output the schema for the generated report # puts \ {BEGIN; CREATE TABLE space_used( name clob, -- Name of a table or index in the database file is_index boolean, -- TRUE if it is an index, false for a table payload int, -- Total amount of data stored in this table or index pri_pages int, -- Number of primary pages used ovfl_pages int, -- Number of overflow pages used pri_unused int, -- Number of unused bytes on primary pages ovfl_unused int -- Number of unused bytes on overflow pages );} # This query will be used to find the root page number for every index and # table in the database. # set sql { SELECT name, type, rootpage FROM sqlite_master UNION ALL SELECT 'sqlite_master', 'table', 2 ORDER BY 1 } # Initialize variables used for summary statistics. # set total_size 0 set total_primary 0 set total_overflow 0 set total_unused_primary 0 set total_unused_ovfl 0 # Analyze every table in the database, one at a time. # foreach {name type rootpage} [db eval $sql] { set cursor [btree_cursor $DB $rootpage 0] set go [btree_first $cursor] set size 0 catch {unset pg_used} set unused_ovfl 0 set n_overflow 0 while {$go==0} { set payload [btree_payload_size $cursor] incr size $payload set stat [btree_cursor_dump $cursor] set pgno [lindex $stat 0] set freebytes [lindex $stat 4] set pg_used($pgno) $freebytes if {$payload>238} { set n [expr {($payload-238+1019)/1020}] incr n_overflow $n incr unused_ovfl [expr {$n*1020+238-$payload}] } set go [btree_next $cursor] } btree_close_cursor $cursor set n_primary [llength [array names pg_used]] set unused_primary 0 foreach x [array names pg_used] {incr unused_primary $pg_used($x)} regsub -all ' $name '' name puts -nonewline "INSERT INTO space_used VALUES('$name'" puts -nonewline ",[expr {$type=="index"}]" puts ",$size,$n_primary,$n_overflow,$unused_primary,$unused_ovfl);" incr total_size $size incr total_primary $n_primary incr total_overflow $n_overflow incr total_unused_primary $unused_primary incr total_unused_ovfl $unused_ovfl } # Output summary statistics: # puts "-- Total payload size: $total_size" puts "-- Total pages used: $total_primary primary and $total_overflow overflow" set file_pgcnt [expr {[file size [lindex $argv 0]]/1024}] puts -nonewline "-- Total unused bytes on primary pages: $total_unused_primary" if {$total_primary>0} { set upp [expr {$total_unused_primary/$total_primary}] puts " (avg $upp bytes/page)" } else { puts "" } puts -nonewline "-- Total unused bytes on overflow pages: $total_unused_ovfl" if {$total_overflow>0} { set upp [expr {$total_unused_ovfl/$total_overflow}] puts " (avg $upp bytes/page)" } else { puts "" } set n_free [expr {$file_pgcnt-$total_primary-$total_overflow}] if {$n_free>0} {incr n_free -1} puts "-- Total pages on freelist: $n_free" puts "COMMIT;" |