Hello,
we are having problem with database that originated on computer of one
of our customers.
The database is used in WAL mode with auto_vacuum=1 and page_size=1024.
When running the "pragma incremental_vacuum(1);" command the WAL file
grows to 14Mb, while we would expect it to grow only to about 4Kb (1Kb
per page + some overhead). This causes the transaction to run for much
longer time than expected and eventually the WAL file grows to several
gigabytes when we try to run incremental_vacuum for 4096 pages.
Additional facts:
- The database was created with SQLite 3.7.5 running on Windows
- "pragma integrity_check;" reports ok
- Some free trunk list pages contain weird values in the "number of
entries" field
I have attached most of the dumps that I could create with the regular
SQLite tools (showdb dbheader, sqlite3_analyzer output, beginning of
the free list dump). I'm willing to send the whole database file
(~5Gb) to sqlite developers on request.
My questions are:
- Is is expected behavior that "pragma incremental_vacuum(1);" can
create 14Mb WAL file even though the page size is just 1Kb?
- If not, is it a know bug? If yes, in what circumstances?
- Is the free list corrupted? And if it is, how could this happen and
should "pragma integrity_check;" verify it?
Best regards,
Filip Navara
-- showdb mail_data.dat dbheader --
Pagesize: 1024
Available pages: 1..1389458
000: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00 SQLite format 3.
010: 04 00 02 02 00 40 20 20 00 00 00 2f 00 55 33 92 .....@ .../.U3.
020: 00 4b 47 99 00 54 53 34 00 00 00 01 00 00 00 04 .KG..TS4........
030: 00 00 00 00 00 00 00 04 00 00 00 01 00 00 00 00 ................
040: 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00 00 ................
050: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 2f .............../
060: 00 2d e2 1d 00 .-...
Decoded:
010: 04 00 1024 Database page size
012: 02 2 File format write version
013: 02 2 File format read version
014: 00 0 Reserved space at end of page
018: 00 00 00 2f 47 File change counter
01c: 00 55 33 92 5583762 Size of database in pages
020: 00 4b 47 99 4933529 Page number of first freelist page
024: 00 54 53 34 5526324 Number of freelist pages
028: 00 00 00 01 1 Schema cookie
02c: 00 00 00 04 4 Schema format version
030: 00 00 00 00 0 Default page cache size
034: 00 00 00 04 4 Largest auto-vac root page
038: 00 00 00 01 1 Text encoding
03c: 00 00 00 00 0 User version
040: 00 00 00 01 1 Incremental-vacuum mode
044: 00 00 00 00 0 meta[7]
048: 00 00 00 00 0 meta[8]
04c: 00 00 00 00 0 meta[9]
050: 00 00 00 00 0 meta[10]
054: 00 00 00 00 0 meta[11]
058: 00 00 00 00 0 meta[12]
05c: 00 00 00 2f 47 Change counter for version number
060: 00 2d e2 1d 3007005 SQLite version number
-- showdb mail_data.dat 4933529t (truncated) --
Pagesize: 1024
Available pages: 1..1389458
Decode of freelist trunk page 4933529:
000: 00 0b 47 9a 739226 Next freelist trunk page
004: b5 b7 bc b5 -1246249803 Number of entries on this page
Decode of freelist trunk page 739226:
000: 00 0b 47 9b 739227 Next freelist trunk page
004: 9f 65 86 9f -1620736353 Number of entries on this page
Decode of freelist trunk page 739227:
000: 00 0b 47 9c 739228 Next freelist trunk page
004: d9 ed ea d9 -638719271 Number of entries on this page
Decode of freelist trunk page 739228:
000: 00 0b 47 9d 739229 Next freelist trunk page
004: d9 ed ea d9 -638719271 Number of entries on this page
Decode of freelist trunk page 739229:
000: 00 0b 47 9e 739230 Next freelist trunk page
004: db f0 ed db -604967461 Number of entries on this page
Decode of freelist trunk page 739230:
000: 00 0b 47 9f 739231 Next freelist trunk page
004: db f0 ed db -604967461 Number of entries on this page
Decode of freelist trunk page 739231:
000: 00 0b 47 a1 739233 Next freelist trunk page
004: db f0 ed db -604967461 Number of entries on this page
Decode of freelist trunk page 739233:
000: 00 0b 47 a2 739234 Next freelist trunk page
004: d8 ec e9 d8 -655562280 Number of entries on this page
Decode of freelist trunk page 739234:
000: 00 0b 47 a3 739235 Next freelist trunk page
004: d8 ec e9 d8 -655562280 Number of entries on this page
...
Decode of freelist trunk page 739463:
000: 00 0b 48 88 739464 Next freelist trunk page
004: e9 d8 ec e9 -371659543 Number of entries on this page
Decode of freelist trunk page 739464:
000: 00 0b 47 8d 739213 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 739213:
000: 00 0b 46 93 738963 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 738963:
000: 00 0b 45 99 738713 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 738713:
000: 00 0b 44 9f 738463 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 738463:
000: 00 0b 43 a5 738213 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 738213:
000: 00 0b 42 aa 737962 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 737962:
000: 00 0b 41 b0 737712 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 737712:
000: 00 0b 40 b6 737462 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 737462:
000: 00 0b 3f bc 737212 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
Decode of freelist trunk page 737212:
000: 00 0b 3e c1 736961 Next freelist trunk page
004: 00 00 00 f8 248 Number of entries on this page
...
-- sqlite3_analyzer output --
/** Disk-Space Utilization Report For mail_data.dat
*** As of 2011-Apr-05 11:44:52
Page size in bytes.................... 1024
Pages in the whole file (measured).... 5583762
Pages in the whole file (calculated).. 5583761
Pages that store data................. 30199 0.54%
Pages on the freelist (per header).... 5526324 99.0%
Pages on the freelist (calculated).... 5526325 99.0%
Pages of auto-vacuum overhead......... 27238 0.49%
Number of tables in the database...... 2
Number of indices..................... 1
Number of named indices............... 0
Automatically generated indices....... 1
Size of the file in bytes............. 5717772288
Bytes of user payload stored.......... 16323306 0.29%
*** Page counts for all tables with their indices ********************
LOCALMAILCONTENTS..................... 30198 0.54%
SQLITE_MASTER......................... 1 0.0%
*** All tables and indices *******************************************
Percentage of total database.......... 0.54%
Number of entries..................... 109914
Bytes of storage consumed............. 30923776
Bytes of payload...................... 16987667 54.9%
Average payload per entry............. 154.55
Average unused bytes per entry........ 117.25
Average fanout........................ 98.00
Fragmentation......................... 73.3%
Maximum payload per entry............. 5730
Entries that use overflow............. 324 0.29%
Index pages used...................... 292
Primary pages used.................... 29501
Overflow pages used................... 406
Total pages used...................... 30199
Unused bytes on index pages........... 40084 13.4%
Unused bytes on primary pages......... 12839816 42.5%
Unused bytes on overflow pages........ 7814 1.9%
Unused bytes on all pages............. 12887714 41.7%
*** All tables *******************************************************
Percentage of total database.......... 0.52%
Number of entries..................... 54958
Bytes of storage consumed............. 29961216
Bytes of payload...................... 16323898 54.5%
Average payload per entry............. 297.02
Average unused bytes per entry........ 232.27
Average fanout........................ 98.00
Fragmentation......................... 72.4%
Maximum payload per entry............. 5730
Entries that use overflow............. 324 0.59%
Index pages used...................... 292
Primary pages used.................... 28561
Overflow pages used................... 406
Total pages used...................... 29259
Unused bytes on index pages........... 40084 13.4%
Unused bytes on primary pages......... 12717169 43.5%
Unused bytes on overflow pages........ 7814 1.9%
Unused bytes on all pages............. 12765067 42.6%
*** All indices ******************************************************
Percentage of total database.......... 0.017%
Number of entries..................... 54956
Bytes of storage consumed............. 962560
Bytes of payload...................... 663769 69.0%
Average payload per entry............. 12.08
Average unused bytes per entry........ 2.23
Fragmentation......................... 99.89%
Maximum payload per entry............. 15
Entries that use overflow............. 0 0.0%
Primary pages used.................... 940
Overflow pages used................... 0
Total pages used...................... 940
Unused bytes on primary pages......... 122647 12.7%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 122647 12.7%
*** Table LOCALMAILCONTENTS and all its indices **********************
Percentage of total database.......... 0.54%
Number of entries..................... 109912
Bytes of storage consumed............. 30922752
Bytes of payload...................... 16987075 54.9%
Average payload per entry............. 154.55
Average unused bytes per entry........ 117.25
Average fanout........................ 98.00
Fragmentation......................... 73.3%
Maximum payload per entry............. 5730
Entries that use overflow............. 324 0.29%
Index pages used...................... 292
Primary pages used.................... 29500
Overflow pages used................... 406
Total pages used...................... 30198
Unused bytes on index pages........... 40084 13.4%
Unused bytes on primary pages......... 12839501 42.5%
Unused bytes on overflow pages........ 7814 1.9%
Unused bytes on all pages............. 12887399 41.7%
*** Table LOCALMAILCONTENTS w/o any indices **************************
Percentage of total database.......... 0.52%
Number of entries..................... 54956
Bytes of storage consumed............. 29960192
Bytes of payload...................... 16323306 54.5%
Average payload per entry............. 297.03
Average unused bytes per entry........ 232.27
Average fanout........................ 98.00
Fragmentation......................... 72.4%
Maximum payload per entry............. 5730
Entries that use overflow............. 324 0.59%
Index pages used...................... 292
Primary pages used.................... 28560
Overflow pages used................... 406
Total pages used...................... 29258
Unused bytes on index pages........... 40084 13.4%
Unused bytes on primary pages......... 12716854 43.5%
Unused bytes on overflow pages........ 7814 1.9%
Unused bytes on all pages............. 12764752 42.6%
*** Indices of table LOCALMAILCONTENTS *******************************
Percentage of total database.......... 0.017%
Number of entries..................... 54956
Bytes of storage consumed............. 962560
Bytes of payload...................... 663769 69.0%
Average payload per entry............. 12.08
Average unused bytes per entry........ 2.23
Fragmentation......................... 99.89%
Maximum payload per entry............. 15
Entries that use overflow............. 0 0.0%
Primary pages used.................... 940
Overflow pages used................... 0
Total pages used...................... 940
Unused bytes on primary pages......... 122647 12.7%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 122647 12.7%
*** Table SQLITE_MASTER **********************************************
Percentage of total database.......... 0.0%
Number of entries..................... 2
Bytes of storage consumed............. 1024
Bytes of payload...................... 592 57.8%
Average payload per entry............. 296.00
Average unused bytes per entry........ 157.50
Maximum payload per entry............. 527
Entries that use overflow............. 0 0.0%
Primary pages used.................... 1
Overflow pages used................... 0
Total pages used...................... 1
Unused bytes on primary pages......... 315 30.8%
Unused bytes on overflow pages........ 0
Unused bytes on all pages............. 315 30.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 1024-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_MASTER table
used to store schema information.
Number of indices
The total number of indices in the database.
Number of named indices
The number of indices created using an explicit CREATE INDEX statement.
Automatically generated 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_MASTER 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.
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.
Fragmentation
The percentage of pages in the table or index that are not
consecutive in the disk file. Many filesystems are optimized
for sequential file access so smaller fragmentation numbers
sometimes result in faster queries, especially for larger
database files that do not fit in the disk cache.
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
nentry int, -- Number of entries in the BTree
leaf_entries int, -- Number of leaf entries
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
);
INSERT INTO space_used
VALUES('sqlite_master','sqlite_master',0,2,2,592,0,0,527,0,1,0,0,315,0,0);
INSERT INTO space_used
VALUES('LocalMailContents','LocalMailContents',0,83515,54956,16323306,406306,324,5730,292,28560,406,40084,12716854,7814,21186);
INSERT INTO space_used
VALUES('sqlite_autoindex_LocalMailContents_1','LocalMailContents',1,54956,54956,663769,0,0,15,0,940,0,0,122647,0,938);
COMMIT;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
