-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcommon_drawl_report
377 lines (298 loc) · 17.6 KB
/
common_drawl_report
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
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
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
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
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
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
/** Disk-Space Utilization Report For common_drawl.db
Page size in bytes................................ 4096
Pages in the whole file (measured)................ 743906
Pages in the whole file (calculated).............. 743906
Pages that store data............................. 743905 100.000%
Pages on the freelist (per header)................ 0 0.0%
Pages on the freelist (calculated)................ 1 0.0%
Pages of auto-vacuum overhead..................... 0 0.0%
Number of tables in the database.................. 2
Number of indices................................. 1
Number of defined indices......................... 0
Number of implied indices......................... 1
Size of the file in bytes......................... 3047038976
Bytes of user payload stored...................... 2661397998 87.3%
*** Page counts for all tables with their indices *****************************
EMBEDDINGS........................................ 743904 100.000%
SQLITE_SCHEMA..................................... 1 0.0%
*** Page counts for all tables and indices separately *************************
EMBEDDINGS........................................ 733823 98.6%
SQLITE_AUTOINDEX_EMBEDDINGS_1..................... 10081 1.4%
SQLITE_SCHEMA..................................... 1 0.0%
*** All tables and indices ****************************************************
Percentage of total database...................... 100.000%
Number of entries................................. 4391794
Bytes of storage consumed......................... 3047034880
Bytes of payload.................................. 2692079832 88.4%
Bytes of metadata................................. 34655431 1.1%
Average payload per entry......................... 612.98
Average unused bytes per entry.................... 72.93
Average metadata per entry........................ 7.89
Average fanout.................................... 389.00
Maximum payload per entry......................... 2210
Entries that use overflow......................... 9 0.0%
Index pages used.................................. 1912
Primary pages used................................ 741984
Overflow pages used............................... 9
Total pages used.................................. 743905
Unused bytes on index pages....................... 976256 12.5%
Unused bytes on primary pages..................... 319291203 10.5%
Unused bytes on overflow pages.................... 32158 87.2%
Unused bytes on all pages......................... 320299617 10.5%
*** All tables ****************************************************************
Percentage of total database...................... 98.6%
Number of entries................................. 2195898
Bytes of storage consumed......................... 3005743104
Bytes of payload.................................. 2661398137 88.5%
Bytes of metadata................................. 27946752 0.93%
Average payload per entry......................... 1211.99
Average unused bytes per entry.................... 144.09
Average metadata per entry........................ 12.73
Average fanout.................................... 396.00
Maximum payload per entry......................... 2210
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1853
Primary pages used................................ 731971
Overflow pages used............................... 0
Total pages used.................................. 733824
Unused bytes on index pages....................... 952519 12.5%
Unused bytes on primary pages..................... 315445696 10.5%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 316398215 10.5%
*** All indices ***************************************************************
Percentage of total database...................... 1.4%
Number of entries................................. 2195896
Bytes of storage consumed......................... 41291776
Bytes of payload.................................. 30681695 74.3%
Bytes of metadata................................. 6708679 16.2%
B-tree depth...................................... 3
Average payload per entry......................... 13.97
Average unused bytes per entry.................... 1.78
Average metadata per entry........................ 3.06
Average fanout.................................... 170.00
Maximum payload per entry......................... 1012
Entries that use overflow......................... 9 0.0%
Index pages used.................................. 59
Primary pages used................................ 10013
Overflow pages used............................... 9
Total pages used.................................. 10081
Unused bytes on index pages....................... 23737 9.8%
Unused bytes on primary pages..................... 3845507 9.4%
Unused bytes on overflow pages.................... 32158 87.2%
Unused bytes on all pages......................... 3901402 9.4%
*** Table EMBEDDINGS and all its indices **************************************
Percentage of total database...................... 100.000%
Number of entries................................. 4391792
Bytes of storage consumed......................... 3047030784
Bytes of payload.................................. 2692079693 88.4%
Bytes of metadata................................. 34655315 1.1%
Average payload per entry......................... 612.98
Average unused bytes per entry.................... 72.93
Average metadata per entry........................ 7.89
Average fanout.................................... 389.00
Maximum payload per entry......................... 2210
Entries that use overflow......................... 9 0.0%
Index pages used.................................. 1912
Primary pages used................................ 741983
Overflow pages used............................... 9
Total pages used.................................. 743904
Unused bytes on index pages....................... 976256 12.5%
Unused bytes on primary pages..................... 319287362 10.5%
Unused bytes on overflow pages.................... 32158 87.2%
Unused bytes on all pages......................... 320295776 10.5%
*** Table EMBEDDINGS w/o any indices ******************************************
Percentage of total database...................... 98.6%
Number of entries................................. 2195896
Bytes of storage consumed......................... 3005739008
Bytes of payload.................................. 2661397998 88.5%
Bytes of metadata................................. 27946636 0.93%
B-tree depth...................................... 4
Average payload per entry......................... 1211.99
Average unused bytes per entry.................... 144.08
Average metadata per entry........................ 12.73
Average fanout.................................... 396.00
Non-sequential pages.............................. 9251 1.3%
Maximum payload per entry......................... 2210
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1853
Primary pages used................................ 731970
Overflow pages used............................... 0
Total pages used.................................. 733823
Unused bytes on index pages....................... 952519 12.5%
Unused bytes on primary pages..................... 315441855 10.5%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 316394374 10.5%
*** Index SQLITE_AUTOINDEX_EMBEDDINGS_1 of table EMBEDDINGS *******************
Percentage of total database...................... 1.4%
Number of entries................................. 2195896
Bytes of storage consumed......................... 41291776
Bytes of payload.................................. 30681695 74.3%
Bytes of metadata................................. 6708679 16.2%
B-tree depth...................................... 3
Average payload per entry......................... 13.97
Average unused bytes per entry.................... 1.78
Average metadata per entry........................ 3.06
Average fanout.................................... 170.00
Non-sequential pages.............................. 9262 91.9%
Maximum payload per entry......................... 1012
Entries that use overflow......................... 9 0.0%
Index pages used.................................. 59
Primary pages used................................ 10013
Overflow pages used............................... 9
Total pages used.................................. 10081
Unused bytes on index pages....................... 23737 9.8%
Unused bytes on primary pages..................... 3845507 9.4%
Unused bytes on overflow pages.................... 32158 87.2%
Unused bytes on all pages......................... 3901402 9.4%
*** Table SQLITE_SCHEMA *******************************************************
Percentage of total database...................... 0.0%
Number of entries................................. 2
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 139 3.4%
Bytes of metadata................................. 116 2.8%
B-tree depth...................................... 1
Average payload per entry......................... 69.50
Average unused bytes per entry.................... 1920.50
Average metadata per entry........................ 58.00
Maximum payload per entry......................... 88
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 3841 93.8%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 3841 93.8%
*** Definitions ***************************************************************
Page size in bytes
The number of bytes in a single page of the database file.
Usually 1024.
Number of pages in the whole file
The number of 4096-byte pages that go into forming the complete
database
Pages that store data
The number of pages that store data, either as primary B*Tree pages or
as overflow pages. The number at the right is the data pages divided by
the total number of pages in the file.
Pages on the freelist
The number of pages that are not currently in use but are reserved for
future use. The percentage at the right is the number of freelist pages
divided by the total number of pages in the file.
Pages of auto-vacuum overhead
The number of pages that store data used by the database to facilitate
auto-vacuum. This is zero for databases that do not support auto-vacuum.
Number of tables in the database
The number of tables in the database, including the SQLITE_SCHEMA table
used to store schema information.
Number of indices
The total number of indices in the database.
Number of defined indices
The number of indices created using an explicit CREATE INDEX statement.
Number of implied indices
The number of indices used to implement PRIMARY KEY or UNIQUE constraints
on tables.
Size of the file in bytes
The total amount of disk space used by the entire database files.
Bytes of user payload stored
The total number of bytes of user payload stored in the database. The
schema information in the SQLITE_SCHEMA table is not counted when
computing this number. The percentage at the right shows the payload
divided by the total file size.
Percentage of total database
The amount of the complete database file that is devoted to storing
information described by this category.
Number of entries
The total number of B-Tree key/value pairs stored under this category.
Bytes of storage consumed
The total amount of disk space required to store all B-Tree entries
under this category. The is the total number of pages used times
the pages size.
Bytes of payload
The amount of payload stored under this category. Payload is the data
part of table entries and the key part of index entries. The percentage
at the right is the bytes of payload divided by the bytes of storage
consumed.
Bytes of metadata
The amount of formatting and structural information stored in the
table or index. Metadata includes the btree page header, the cell pointer
array, the size field for each cell, the left child pointer or non-leaf
cells, the overflow pointers for overflow cells, and the rowid value for
rowid table cells. In other words, metadata is everything that is neither
unused space nor content. The record header in the payload is counted as
content, not metadata.
Average payload per entry
The average amount of payload on each entry. This is just the bytes of
payload divided by the number of entries.
Average unused bytes per entry
The average amount of free space remaining on all pages under this
category on a per-entry basis. This is the number of unused bytes on
all pages divided by the number of entries.
Non-sequential pages
The number of pages in the table or index that are out of sequence.
Many filesystems are optimized for sequential file access so a small
number of non-sequential pages might result in faster queries,
especially for larger database files that do not fit in the disk cache.
Note that after running VACUUM, the root page of each table or index is
at the beginning of the database file and all other pages are in a
separate part of the database file, resulting in a single non-
sequential page.
Maximum payload per entry
The largest payload size of any entry.
Entries that use overflow
The number of entries that user one or more overflow pages.
Total pages used
This is the number of pages used to hold all information in the current
category. This is the sum of index, primary, and overflow pages.
Index pages used
This is the number of pages in a table B-tree that hold only key (rowid)
information and no data.
Primary pages used
This is the number of B-tree pages that hold both key and data.
Overflow pages used
The total number of overflow pages used for this category.
Unused bytes on index pages
The total number of bytes of unused space on all index pages. The
percentage at the right is the number of unused bytes divided by the
total number of bytes on index pages.
Unused bytes on primary pages
The total number of bytes of unused space on all primary pages. The
percentage at the right is the number of unused bytes divided by the
total number of bytes on primary pages.
Unused bytes on overflow pages
The total number of bytes of unused space on all overflow pages. The
percentage at the right is the number of unused bytes divided by the
total number of bytes on overflow pages.
Unused bytes on all pages
The total number of bytes of unused space on all primary and overflow
pages. The percentage at the right is the number of unused bytes
divided by the total number of bytes.
*******************************************************************************
The entire text of this report can be sourced into any SQL database
engine for further analysis. All of the text above is an SQL comment.
The data used to generate this report follows:
*/
BEGIN;
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
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
);
INSERT INTO space_used VALUES('sqlite_schema','sqlite_schema',0,0,2,2,1,139,0,0,88,0,1,0,0,3841,0,0,4096);
INSERT INTO space_used VALUES('embeddings','embeddings',0,0,2927865,2195896,4,2661397998,0,0,2210,1853,731970,0,952519,315441855,0,9251,3005739008);
INSERT INTO space_used VALUES('sqlite_autoindex_embeddings_1','embeddings',1,0,2195896,2185884,3,30681695,4670,9,1012,59,10013,9,23737,3845507,32158,9262,41291776);
COMMIT