/ Check-in [83b2c27a]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:83b2c27a568cd67cd5162d513766b23ff9fc2227
User & Date: drh 2002-11-24 14:52:27
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: 9864a126 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: 83b2c27a 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: dd5396a7 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to main.mk.

208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
	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 
	$(BCC) -o temp temp.c
	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








<







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
16
17
18
19
20
21
22
23
...
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
**    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.19 2002/08/31 18:53:08 drh Exp $
*/
#include "sqliteInt.h"
#include "pager.h"
#include "btree.h"
#include "tcl.h"
#include <stdlib.h>
#include <string.h>
................................................................................
    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);
  free(zBuf);
  return SQLITE_OK;
}

/*
** Usage:   btree_cursor_dump ID
**
** Return eight integers containing information about the entry the







|







 







<







9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
...
818
819
820
821
822
823
824

825
826
827
828
829
830
831
**    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>
................................................................................
    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;"