Growing state.db-wal file #4044
Comments
|
I don't think this is the same thing. All of your database pages are in use; it appears that you have 84k rows in your database, with almost none of them compacted (row count and current_rev are only about 50 off),
What exactly do you have running on here? Can you attach the k3s server logs? Also, the output of this query might be interesting: SELECT COUNT(*), name FROM kine GROUP BY name ORDER BY COUNT(*) DESC LIMIT 50; |
|
I will note that the compact query that prunes old rows runs in a database transaction that has a 5 second timeout, to avoid locking the database. If this embedded device is particularly low on CPU power or disk IO, it's possible that the compaction isn't able to occur in a timely manner. If this is happening, you should see errors in the logs. |
|
Thanks for the quick response! I'll follow-up with requested logs on Monday |
|
Curious to see what you turn up. |
|
Also get this issue on K3OS (v1.20.7+k3s1). Unfortunately on K3OS it is difficult to run sqlite binary on the OS. Probably missing dependencies. ls /var/lib/rancher/k3s/server/db -alh
total 24G
drwx------ 2 root root 4.0K Sep 17 09:39 .
drwxr-xr-x 7 root root 4.0K Sep 17 09:39 ..
-rw-r--r-- 1 root root 0 Sep 17 09:39 joined-a0416b1bb1e9
-rw-r--r-- 1 root root 143M Sep 21 13:38 state.db
-rw-r--r-- 1 root root 48M Sep 21 13:38 state.db-shm
-rw-r--r-- 1 root root 24G Sep 21 13:38 state.db-wal |
|
I have made the following workaround for anyone on K3OS or a minimum / cloud OS |
|
@andrewwebber that's appreciated, but can you collect some of the requested information so that we can figure out what's causing this? The standalone sqlite binaries available on https://www.sqlite.org/download.html should work. |
|
@bradtopol happy to help ldd /tmp/sqlite-tools-linux-x86-3360000/sqlite3
linux-gate.so.1 (0xf7f36000)
libdl.so.2 => /usr/lib32/libdl.so.2 (0xf7f09000)
libm.so.6 => /usr/lib32/libm.so.6 (0xf7e3c000)
libc.so.6 => /usr/lib32/libc.so.6 (0xf7c43000)
/lib/ld-linux.so.2 => /usr/lib/ld-linux.so.2 (0xf7f38000)I would be happy to extend the sample with additional queries and test. I believe you are referring to SELECT COUNT(*), name FROM kine GROUP BY name ORDER BY COUNT(*) DESC LIMIT 50;Now the 🔥 fighting has concluded I can attempt a cronjob to collect this daily. |
|
@andrewwebber the info requested here #1575 (comment) |
|
@andrewwebber Can you pull the |
|
@bhass1 i can now, before it was 24 GB 😂 Analyzer output: /** Disk-Space Utilization Report For /tmp/state.db
Page size in bytes................................ 4096
Pages in the whole file (measured)................ 16585
Pages in the whole file (calculated).............. 16585
Pages that store data............................. 6078 36.6%
Pages on the freelist (per header)................ 10507 63.4%
Pages on the freelist (calculated)................ 10507 63.4%
Pages of auto-vacuum overhead..................... 0 0.0%
Number of tables in the database.................. 3
Number of indices................................. 5
Number of defined indices......................... 5
Number of implied indices......................... 0
Size of the file in bytes......................... 67932160
Bytes of user payload stored...................... 17589750 25.9%
*** Page counts for all tables with their indices *****************************
KINE.............................................. 6076 36.6%
SQLITE_SCHEMA..................................... 1 0.006%
SQLITE_SEQUENCE................................... 1 0.006%
*** Page counts for all tables and indices separately *************************
KINE.............................................. 5049 30.4%
KINE_NAME_PREV_REVISION_UINDEX.................... 317 1.9%
KINE_NAME_ID_INDEX................................ 316 1.9%
KINE_NAME_INDEX................................... 311 1.9%
KINE_ID_DELETED_INDEX............................. 42 0.25%
KINE_PREV_REVISION_INDEX.......................... 41 0.25%
SQLITE_SCHEMA..................................... 1 0.006%
SQLITE_SEQUENCE................................... 1 0.006%
*** All tables and indices ****************************************************
Percentage of total database...................... 36.6%
Number of entries................................. 60116
Bytes of storage consumed......................... 24895488
Bytes of payload.................................. 20876354 83.9%
Bytes of metadata................................. 321497 1.3%
Average payload per entry......................... 347.27
Average unused bytes per entry.................... 61.58
Average metadata per entry........................ 5.35
Average fanout.................................... 73.00
Maximum payload per entry......................... 320073
Entries that use overflow......................... 420 0.70%
Index pages used.................................. 63
Primary pages used................................ 4588
Overflow pages used............................... 1427
Total pages used.................................. 6078
Unused bytes on index pages....................... 118214 45.8%
Unused bytes on primary pages..................... 3567868 19.0%
Unused bytes on overflow pages.................... 15583 0.27%
Unused bytes on all pages......................... 3701665 14.9%
*** All tables ****************************************************************
Percentage of total database...................... 30.5%
Number of entries................................. 10026
Bytes of storage consumed......................... 20688896
Bytes of payload.................................. 17590568 85.0%
Bytes of metadata................................. 154457 0.75%
Average payload per entry......................... 1754.50
Average unused bytes per entry.................... 294.03
Average metadata per entry........................ 15.41
Average fanout.................................... 301.00
Maximum payload per entry......................... 320073
Entries that use overflow......................... 420 4.2%
Index pages used.................................. 12
Primary pages used................................ 3612
Overflow pages used............................... 1427
Total pages used.................................. 5051
Unused bytes on index pages....................... 13724 27.9%
Unused bytes on primary pages..................... 2918592 19.7%
Unused bytes on overflow pages.................... 15583 0.27%
Unused bytes on all pages......................... 2947899 14.2%
*** All indices ***************************************************************
Percentage of total database...................... 6.2%
Number of entries................................. 50090
Bytes of storage consumed......................... 4206592
Bytes of payload.................................. 3285786 78.1%
Bytes of metadata................................. 167040 4.0%
Average payload per entry......................... 65.60
Average unused bytes per entry.................... 15.05
Average metadata per entry........................ 3.33
Average fanout.................................... 20.00
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 51
Primary pages used................................ 976
Overflow pages used............................... 0
Total pages used.................................. 1027
Unused bytes on index pages....................... 104490 50.0%
Unused bytes on primary pages..................... 649276 16.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 753766 17.9%
*** Table KINE and all its indices ********************************************
Percentage of total database...................... 36.6%
Number of entries................................. 60108
Bytes of storage consumed......................... 24887296
Bytes of payload.................................. 20875525 83.9%
Bytes of metadata................................. 321348 1.3%
Average payload per entry......................... 347.30
Average unused bytes per entry.................... 61.46
Average metadata per entry........................ 5.35
Average fanout.................................... 73.00
Maximum payload per entry......................... 320073
Entries that use overflow......................... 420 0.70%
Index pages used.................................. 63
Primary pages used................................ 4586
Overflow pages used............................... 1427
Total pages used.................................. 6076
Unused bytes on index pages....................... 118214 45.8%
Unused bytes on primary pages..................... 3560654 19.0%
Unused bytes on overflow pages.................... 15583 0.27%
Unused bytes on all pages......................... 3694451 14.8%
*** Table KINE w/o any indices ************************************************
Percentage of total database...................... 30.4%
Number of entries................................. 10018
Bytes of storage consumed......................... 20680704
Bytes of payload.................................. 17589739 85.1%
Bytes of metadata................................. 154308 0.75%
B-tree depth...................................... 3
Average payload per entry......................... 1755.81
Average unused bytes per entry.................... 293.54
Average metadata per entry........................ 15.40
Average fanout.................................... 301.00
Non-sequential pages.............................. 1892 37.5%
Maximum payload per entry......................... 320073
Entries that use overflow......................... 420 4.2%
Index pages used.................................. 12
Primary pages used................................ 3610
Overflow pages used............................... 1427
Total pages used.................................. 5049
Unused bytes on index pages....................... 13724 27.9%
Unused bytes on primary pages..................... 2911378 19.7%
Unused bytes on overflow pages.................... 15583 0.27%
Unused bytes on all pages......................... 2940685 14.2%
*** Indices of table KINE *****************************************************
Percentage of total database...................... 6.2%
Number of entries................................. 50090
Bytes of storage consumed......................... 4206592
Bytes of payload.................................. 3285786 78.1%
Bytes of metadata................................. 167040 4.0%
Average payload per entry......................... 65.60
Average unused bytes per entry.................... 15.05
Average metadata per entry........................ 3.33
Average fanout.................................... 20.00
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 51
Primary pages used................................ 976
Overflow pages used............................... 0
Total pages used.................................. 1027
Unused bytes on index pages....................... 104490 50.0%
Unused bytes on primary pages..................... 649276 16.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 753766 17.9%
*** Index KINE_ID_DELETED_INDEX of table KINE *********************************
Percentage of total database...................... 0.25%
Number of entries................................. 10018
Bytes of storage consumed......................... 172032
Bytes of payload.................................. 116530 67.7%
Bytes of metadata................................. 30554 17.8%
B-tree depth...................................... 2
Average payload per entry......................... 11.63
Average unused bytes per entry.................... 2.49
Average metadata per entry........................ 3.05
Average fanout.................................... 42.00
Non-sequential pages.............................. 34 82.9%
Maximum payload per entry......................... 12
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1
Primary pages used................................ 41
Overflow pages used............................... 0
Total pages used.................................. 42
Unused bytes on index pages....................... 3332 81.3%
Unused bytes on primary pages..................... 21616 12.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 24948 14.5%
*** Index KINE_NAME_ID_INDEX of table KINE ************************************
Percentage of total database...................... 1.9%
Number of entries................................. 10018
Bytes of storage consumed......................... 1294336
Bytes of payload.................................. 1037829 80.2%
Bytes of metadata................................. 35619 2.8%
B-tree depth...................................... 3
Average payload per entry......................... 103.60
Average unused bytes per entry.................... 22.05
Average metadata per entry........................ 3.56
Average fanout.................................... 18.00
Non-sequential pages.............................. 255 81.0%
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 17
Primary pages used................................ 299
Overflow pages used............................... 0
Total pages used.................................. 316
Unused bytes on index pages....................... 34760 49.9%
Unused bytes on primary pages..................... 186128 15.2%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 220888 17.1%
*** Index KINE_NAME_INDEX of table KINE ***************************************
Percentage of total database...................... 1.9%
Number of entries................................. 10018
Bytes of storage consumed......................... 1273856
Bytes of payload.................................. 989582 77.7%
Bytes of metadata................................. 34694 2.7%
B-tree depth...................................... 3
Average payload per entry......................... 98.78
Average unused bytes per entry.................... 24.91
Average metadata per entry........................ 3.46
Average fanout.................................... 19.00
Non-sequential pages.............................. 258 83.2%
Maximum payload per entry......................... 159
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 16
Primary pages used................................ 295
Overflow pages used............................... 0
Total pages used.................................. 311
Unused bytes on index pages....................... 32539 49.7%
Unused bytes on primary pages..................... 217041 18.0%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 249580 19.6%
*** Index KINE_NAME_PREV_REVISION_UINDEX of table KINE ************************
Percentage of total database...................... 1.9%
Number of entries................................. 10018
Bytes of storage consumed......................... 1298432
Bytes of payload.................................. 1036581 79.8%
Bytes of metadata................................. 35631 2.7%
B-tree depth...................................... 3
Average payload per entry......................... 103.47
Average unused bytes per entry.................... 22.58
Average metadata per entry........................ 3.56
Average fanout.................................... 19.00
Non-sequential pages.............................. 261 82.6%
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 16
Primary pages used................................ 301
Overflow pages used............................... 0
Total pages used.................................. 317
Unused bytes on index pages....................... 30461 46.5%
Unused bytes on primary pages..................... 195759 15.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 226220 17.4%
*** Index KINE_PREV_REVISION_INDEX of table KINE ******************************
Percentage of total database...................... 0.25%
Number of entries................................. 10018
Bytes of storage consumed......................... 167936
Bytes of payload.................................. 105264 62.7%
Bytes of metadata................................. 30542 18.2%
B-tree depth...................................... 2
Average payload per entry......................... 10.51
Average unused bytes per entry.................... 3.21
Average metadata per entry........................ 3.05
Average fanout.................................... 41.00
Non-sequential pages.............................. 36 90.0%
Maximum payload per entry......................... 11
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1
Primary pages used................................ 40
Overflow pages used............................... 0
Total pages used.................................. 41
Unused bytes on index pages....................... 3398 83.0%
Unused bytes on primary pages..................... 28732 17.5%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 32130 19.1%
*** Table SQLITE_SCHEMA *******************************************************
Percentage of total database...................... 0.006%
Number of entries................................. 7
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 818 20.0%
Bytes of metadata................................. 137 3.3%
B-tree depth...................................... 1
Average payload per entry......................... 116.86
Average unused bytes per entry.................... 448.71
Average metadata per entry........................ 19.57
Maximum payload per entry......................... 260
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 3141 76.7%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 3141 76.7%
*** Table SQLITE_SEQUENCE *****************************************************
Percentage of total database...................... 0.006%
Number of entries................................. 1
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 11 0.27%
Bytes of metadata................................. 12 0.29%
B-tree depth...................................... 1
Average payload per entry......................... 11.00
Average unused bytes per entry.................... 4073.00
Average metadata per entry........................ 12.00
Maximum payload per entry......................... 11
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 4073 99.44%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4073 99.44%
*** 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_MASTER 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_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.
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,7,7,1,818,0,0,260,0,1,0,0,3141,0,0,4096);
INSERT INTO space_used VALUES('kine','kine',0,0,13627,10018,3,17589739,5823701,420,320073,12,3610,1427,13724,2911378,15583,1892,20680704);
INSERT INTO space_used VALUES('sqlite_sequence','sqlite_sequence',0,0,1,1,1,11,0,0,11,0,1,0,0,4073,0,0,4096);
INSERT INTO space_used VALUES('kine_name_index','kine',1,0,10018,9724,3,989582,0,0,159,16,295,0,32539,217041,0,258,1273856);
INSERT INTO space_used VALUES('kine_name_id_index','kine',1,0,10018,9720,3,1037829,0,0,164,17,299,0,34760,186128,0,255,1294336);
INSERT INTO space_used VALUES('kine_id_deleted_index','kine',1,0,10018,9978,2,116530,0,0,12,1,41,0,3332,21616,0,34,172032);
INSERT INTO space_used VALUES('kine_prev_revision_index','kine',1,0,10018,9979,2,105264,0,0,11,1,40,0,3398,28732,0,36,167936);
INSERT INTO space_used VALUES('kine_name_prev_revision_uindex','kine',1,0,10018,9718,3,1036581,0,0,164,16,301,0,30461,195759,0,261,1298432);
COMMIT;SQL lite output:database page size: 4096
write format: 2
read format: 2
reserved bytes: 0
file change counter: 2778739
database page count: 16585
freelist page count: 10507
schema cookie: 8
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3032001
number of tables: 2
number of indexes: 5
number of triggers: 0
number of views: 0
schema size: 565
data version 2
rows compact_rev current_rev
----- ----------- -----------
10018 9083007 9091793
COUNT(*) name
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------
239 /registry/leases/knative-serving/autoscaler-bucket-00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.apiserversource.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.channel.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.containersource.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.eventtype.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.parallel.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.pingsource.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.sequence.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.source.crd.reconciler.00-of-01
173 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.subscription.reconciler.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.configmapwebhook.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.conversionwebhook.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.defaultingwebhook.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.sinkbindings.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.sinkbindings.webhook.sources.knative.dev.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.validationwebhook.00-of-01
173 /registry/leases/knative-eventing/eventing-webhook.webhookcertificates.00-of-01
173 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.broker.reconciler.00-of-01
173 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.sink.reconciler.00-of-01
173 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.trigger.reconciler.00-of-01
173 /registry/leases/knative-eventing/kafka-webhook-eventing.defaultingwebhook.00-of-01
173 /registry/leases/knative-eventing/kafka-webhook-eventing.validationwebhook.00-of-01
173 /registry/leases/knative-eventing/kafka-webhook-eventing.webhookcertificates.00-of-01
173 /registry/leases/knative-eventing/kafkachannel-controller.knative.dev.eventing-kafka.pkg.channel.consolidated.reconciler.controller.reconciler.00-of-01
173 /registry/leases/knative-eventing/kafkachannel-webhook.conversionwebhook.00-of-01
173 /registry/leases/knative-eventing/kafkachannel-webhook.defaultingwebhook.00-of-01
173 /registry/leases/knative-eventing/kafkachannel-webhook.validationwebhook.00-of-01
173 /registry/leases/knative-eventing/kafkachannel-webhook.webhookcertificates.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.configuration.reconciler.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.gc.reconciler.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.labeler.reconciler.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.revision.reconciler.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.route.reconciler.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.serverlessservice.reconciler.00-of-01
173 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.service.reconciler.00-of-01
173 /registry/leases/knative-serving/domainmapping-webhook.defaultingwebhook.00-of-01
173 /registry/leases/knative-serving/domainmapping-webhook.validationwebhook.00-of-01
173 /registry/leases/knative-serving/domainmapping-webhook.webhookcertificates.00-of-01
173 /registry/leases/knative-serving/domainmapping.knative.dev.serving.pkg.reconciler.domainmapping.reconciler.00-of-01
173 /registry/leases/knative-serving/net-istio-controller.knative.dev.net-istio.pkg.reconciler.ingress.reconciler.00-of-01
173 /registry/leases/knative-serving/net-istio-controller.knative.dev.net-istio.pkg.reconciler.serverlessservice.reconciler.00-of-01
173 /registry/leases/knative-serving/net-istio-webhook.configmapwebhook.00-of-01
173 /registry/leases/knative-serving/net-istio-webhook.defaultingwebhook.00-of-01
173 /registry/leases/knative-serving/net-istio-webhook.webhookcertificates.00-of-01
173 /registry/leases/knative-serving/webhook.configmapwebhook.00-of-01
173 /registry/leases/knative-serving/webhook.defaultingwebhook.00-of-01
173 /registry/leases/knative-serving/webhook.validationwebhook.00-of-01
173 /registry/leases/knative-serving/webhook.webhookcertificates.00-of-01
173 /registry/services/endpoints/kube-system/rancher.io-local-path
47 /registry/configmaps/istio-system/istio-leader |
|
@andrewwebber is that when it was 24GB, or after you've been able to vacuum it back down? The info is only useful when the database is excessively large. |
|
Understood, I will analyze that file when it grows again. I have in the mean time a second machine, untouched, growing in size. The output might be of interest but I will post again when in the gigabytes. Analyzer/** Disk-Space Utilization Report For /tmp/state.db
Page size in bytes................................ 4096
Pages in the whole file (measured)................ 26788
Pages in the whole file (calculated).............. 26788
Pages that store data............................. 13997 52.3%
Pages on the freelist (per header)................ 12791 47.7%
Pages on the freelist (calculated)................ 12791 47.7%
Pages of auto-vacuum overhead..................... 0 0.0%
Number of tables in the database.................. 3
Number of indices................................. 5
Number of defined indices......................... 5
Number of implied indices......................... 0
Size of the file in bytes......................... 109723648
Bytes of user payload stored...................... 37951902 34.6%
*** Page counts for all tables with their indices *****************************
KINE.............................................. 13995 52.2%
SQLITE_SCHEMA..................................... 1 0.004%
SQLITE_SEQUENCE................................... 1 0.004%
*** Page counts for all tables and indices separately *************************
KINE.............................................. 11257 42.0%
KINE_NAME_ID_INDEX................................ 851 3.2%
KINE_NAME_PREV_REVISION_UINDEX.................... 851 3.2%
KINE_NAME_INDEX................................... 812 3.0%
KINE_ID_DELETED_INDEX............................. 117 0.44%
KINE_PREV_REVISION_INDEX.......................... 107 0.40%
SQLITE_SCHEMA..................................... 1 0.004%
SQLITE_SEQUENCE................................... 1 0.004%
*** All tables and indices ****************************************************
Percentage of total database...................... 52.3%
Number of entries................................. 165476
Bytes of storage consumed......................... 57331712
Bytes of payload.................................. 47180054 82.3%
Bytes of metadata................................. 863230 1.5%
Average payload per entry......................... 285.12
Average unused bytes per entry.................... 56.16
Average metadata per entry........................ 5.22
Average fanout.................................... 92.00
Maximum payload per entry......................... 320073
Entries that use overflow......................... 524 0.32%
Index pages used.................................. 134
Primary pages used................................ 12217
Overflow pages used............................... 1646
Total pages used.................................. 13997
Unused bytes on index pages....................... 167515 30.5%
Unused bytes on primary pages..................... 9102013 18.2%
Unused bytes on overflow pages.................... 23388 0.35%
Unused bytes on all pages......................... 9292916 16.2%
*** All tables ****************************************************************
Percentage of total database...................... 42.0%
Number of entries................................. 27586
Bytes of storage consumed......................... 46116864
Bytes of payload.................................. 37952720 82.3%
Bytes of metadata................................. 403807 0.88%
Average payload per entry......................... 1375.80
Average unused bytes per entry.................... 281.48
Average metadata per entry........................ 14.64
Average fanout.................................... 343.00
Maximum payload per entry......................... 320073
Entries that use overflow......................... 524 1.9%
Index pages used.................................. 28
Primary pages used................................ 9585
Overflow pages used............................... 1646
Total pages used.................................. 11259
Unused bytes on index pages....................... 19607 17.1%
Unused bytes on primary pages..................... 7721830 19.7%
Unused bytes on overflow pages.................... 23388 0.35%
Unused bytes on all pages......................... 7764825 16.8%
*** All indices ***************************************************************
Percentage of total database...................... 10.2%
Number of entries................................. 137890
Bytes of storage consumed......................... 11214848
Bytes of payload.................................. 9227334 82.3%
Bytes of metadata................................. 459423 4.1%
Average payload per entry......................... 66.92
Average unused bytes per entry.................... 11.08
Average metadata per entry........................ 3.33
Average fanout.................................... 25.00
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 106
Primary pages used................................ 2632
Overflow pages used............................... 0
Total pages used.................................. 2738
Unused bytes on index pages....................... 147908 34.1%
Unused bytes on primary pages..................... 1380183 12.8%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 1528091 13.6%
*** Table KINE and all its indices ********************************************
Percentage of total database...................... 52.2%
Number of entries................................. 165468
Bytes of storage consumed......................... 57323520
Bytes of payload.................................. 47179225 82.3%
Bytes of metadata................................. 863081 1.5%
Average payload per entry......................... 285.13
Average unused bytes per entry.................... 56.12
Average metadata per entry........................ 5.22
Average fanout.................................... 92.00
Maximum payload per entry......................... 320073
Entries that use overflow......................... 524 0.32%
Index pages used.................................. 134
Primary pages used................................ 12215
Overflow pages used............................... 1646
Total pages used.................................. 13995
Unused bytes on index pages....................... 167515 30.5%
Unused bytes on primary pages..................... 9094799 18.2%
Unused bytes on overflow pages.................... 23388 0.35%
Unused bytes on all pages......................... 9285702 16.2%
*** Table KINE w/o any indices ************************************************
Percentage of total database...................... 42.0%
Number of entries................................. 27578
Bytes of storage consumed......................... 46108672
Bytes of payload.................................. 37951891 82.3%
Bytes of metadata................................. 403658 0.88%
B-tree depth...................................... 3
Average payload per entry......................... 1376.17
Average unused bytes per entry.................... 281.30
Average metadata per entry........................ 14.64
Average fanout.................................... 343.00
Non-sequential pages.............................. 5233 46.5%
Maximum payload per entry......................... 320073
Entries that use overflow......................... 524 1.9%
Index pages used.................................. 28
Primary pages used................................ 9583
Overflow pages used............................... 1646
Total pages used.................................. 11257
Unused bytes on index pages....................... 19607 17.1%
Unused bytes on primary pages..................... 7714616 19.7%
Unused bytes on overflow pages.................... 23388 0.35%
Unused bytes on all pages......................... 7757611 16.8%
*** Indices of table KINE *****************************************************
Percentage of total database...................... 10.2%
Number of entries................................. 137890
Bytes of storage consumed......................... 11214848
Bytes of payload.................................. 9227334 82.3%
Bytes of metadata................................. 459423 4.1%
Average payload per entry......................... 66.92
Average unused bytes per entry.................... 11.08
Average metadata per entry........................ 3.33
Average fanout.................................... 25.00
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 106
Primary pages used................................ 2632
Overflow pages used............................... 0
Total pages used.................................. 2738
Unused bytes on index pages....................... 147908 34.1%
Unused bytes on primary pages..................... 1380183 12.8%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 1528091 13.6%
*** Index KINE_ID_DELETED_INDEX of table KINE *********************************
Percentage of total database...................... 0.44%
Number of entries................................. 27578
Bytes of storage consumed......................... 479232
Bytes of payload.................................. 326510 68.1%
Bytes of metadata................................. 84134 17.6%
B-tree depth...................................... 2
Average payload per entry......................... 11.84
Average unused bytes per entry.................... 2.49
Average metadata per entry........................ 3.05
Average fanout.................................... 117.00
Non-sequential pages.............................. 107 92.2%
Maximum payload per entry......................... 12
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1
Primary pages used................................ 116
Overflow pages used............................... 0
Total pages used.................................. 117
Unused bytes on index pages....................... 1915 46.8%
Unused bytes on primary pages..................... 66673 14.0%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 68588 14.3%
*** Index KINE_NAME_ID_INDEX of table KINE ************************************
Percentage of total database...................... 3.2%
Number of entries................................. 27578
Bytes of storage consumed......................... 3485696
Bytes of payload.................................. 2913487 83.6%
Bytes of metadata................................. 98097 2.8%
B-tree depth...................................... 4
Average payload per entry......................... 105.65
Average unused bytes per entry.................... 17.19
Average metadata per entry........................ 3.56
Average fanout.................................... 22.00
Non-sequential pages.............................. 717 84.4%
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 38
Primary pages used................................ 813
Overflow pages used............................... 0
Total pages used.................................. 851
Unused bytes on index pages....................... 59206 38.0%
Unused bytes on primary pages..................... 414906 12.5%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 474112 13.6%
*** Index KINE_NAME_INDEX of table KINE ***************************************
Percentage of total database...................... 3.0%
Number of entries................................. 27578
Bytes of storage consumed......................... 3325952
Bytes of payload.................................. 2777810 83.5%
Bytes of metadata................................. 95081 2.9%
B-tree depth...................................... 3
Average payload per entry......................... 100.73
Average unused bytes per entry.................... 16.43
Average metadata per entry........................ 3.45
Average fanout.................................... 25.00
Non-sequential pages.............................. 684 84.3%
Maximum payload per entry......................... 159
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 32
Primary pages used................................ 780
Overflow pages used............................... 0
Total pages used.................................. 812
Unused bytes on index pages....................... 42206 32.2%
Unused bytes on primary pages..................... 410855 12.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 453061 13.6%
*** Index KINE_NAME_PREV_REVISION_UINDEX of table KINE ************************
Percentage of total database...................... 3.2%
Number of entries................................. 27578
Bytes of storage consumed......................... 3485696
Bytes of payload.................................. 2912041 83.5%
Bytes of metadata................................. 98097 2.8%
B-tree depth...................................... 3
Average payload per entry......................... 105.59
Average unused bytes per entry.................... 17.24
Average metadata per entry........................ 3.56
Average fanout.................................... 25.00
Non-sequential pages.............................. 734 86.4%
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 34
Primary pages used................................ 817
Overflow pages used............................... 0
Total pages used.................................. 851
Unused bytes on index pages....................... 42373 30.4%
Unused bytes on primary pages..................... 433185 12.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 475558 13.6%
*** Index KINE_PREV_REVISION_INDEX of table KINE ******************************
Percentage of total database...................... 0.40%
Number of entries................................. 27578
Bytes of storage consumed......................... 438272
Bytes of payload.................................. 297486 67.9%
Bytes of metadata................................. 84014 19.2%
B-tree depth...................................... 2
Average payload per entry......................... 10.79
Average unused bytes per entry.................... 2.06
Average metadata per entry........................ 3.05
Average fanout.................................... 107.00
Non-sequential pages.............................. 100 94.3%
Maximum payload per entry......................... 11
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1
Primary pages used................................ 106
Overflow pages used............................... 0
Total pages used.................................. 107
Unused bytes on index pages....................... 2208 53.9%
Unused bytes on primary pages..................... 54564 12.6%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 56772 13.0%
*** Table SQLITE_SCHEMA *******************************************************
Percentage of total database...................... 0.004%
Number of entries................................. 7
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 818 20.0%
Bytes of metadata................................. 137 3.3%
B-tree depth...................................... 1
Average payload per entry......................... 116.86
Average unused bytes per entry.................... 448.71
Average metadata per entry........................ 19.57
Maximum payload per entry......................... 260
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 3141 76.7%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 3141 76.7%
*** Table SQLITE_SEQUENCE *****************************************************
Percentage of total database...................... 0.004%
Number of entries................................. 1
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 11 0.27%
Bytes of metadata................................. 12 0.29%
B-tree depth...................................... 1
Average payload per entry......................... 11.00
Average unused bytes per entry.................... 4073.00
Average metadata per entry........................ 12.00
Maximum payload per entry......................... 11
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 4073 99.44%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4073 99.44%
*** 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_MASTER 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_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.
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,7,7,1,818,0,0,260,0,1,0,0,3141,0,0,4096);
INSERT INTO space_used VALUES('kine','kine',0,0,37160,27578,3,37951891,6712044,524,320073,28,9583,1646,19607,7714616,23388,5233,46108672);
INSERT INTO space_used VALUES('sqlite_sequence','sqlite_sequence',0,0,1,1,1,11,0,0,11,0,1,0,0,4073,0,0,4096);
INSERT INTO space_used VALUES('kine_name_index','kine',1,0,27578,26799,3,2777810,0,0,159,32,780,0,42206,410855,0,684,3325952);
INSERT INTO space_used VALUES('kine_name_id_index','kine',1,0,27578,26766,4,2913487,0,0,164,38,813,0,59206,414906,0,717,3485696);
INSERT INTO space_used VALUES('kine_id_deleted_index','kine',1,0,27578,27463,2,326510,0,0,12,1,116,0,1915,66673,0,107,479232);
INSERT INTO space_used VALUES('kine_prev_revision_index','kine',1,0,27578,27473,2,297486,0,0,11,1,106,0,2208,54564,0,100,438272);
INSERT INTO space_used VALUES('kine_name_prev_revision_uindex','kine',1,0,27578,26762,3,2912041,0,0,164,34,817,0,42373,433185,0,734,3485696);
COMMIT;Sqlightdatabase page size: 4096
write format: 2
read format: 2
reserved bytes: 0
file change counter: 2860248
database page count: 26788
freelist page count: 12791
schema cookie: 6
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3032001
number of tables: 2
number of indexes: 5
number of triggers: 0
number of views: 0
schema size: 565
data version 2
rows compact_rev current_rev
----- ----------- -----------
27578 9533631 9559903
COUNT(*) name
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------
872 /registry/leases/knative-serving/autoscaler-bucket-00-of-01
511 /registry/leases/knative-serving/domainmapping-webhook.webhookcertificates.00-of-01
511 /registry/leases/knative-serving/webhook.defaultingwebhook.00-of-01
511 /registry/leases/knative-serving/webhook.webhookcertificates.00-of-01
510 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.parallel.reconciler.00-of-01
510 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.sequence.reconciler.00-of-01
510 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.subscription.reconciler.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.configmapwebhook.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.conversionwebhook.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.defaultingwebhook.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.sinkbindings.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.sinkbindings.webhook.sources.knative.dev.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.validationwebhook.00-of-01
510 /registry/leases/knative-eventing/eventing-webhook.webhookcertificates.00-of-01
510 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.broker.reconciler.00-of-01
510 /registry/leases/knative-eventing/kafka-webhook-eventing.defaultingwebhook.00-of-01
510 /registry/leases/knative-eventing/kafka-webhook-eventing.validationwebhook.00-of-01
510 /registry/leases/knative-eventing/kafka-webhook-eventing.webhookcertificates.00-of-01
510 /registry/leases/knative-eventing/kafkachannel-controller.knative.dev.eventing-kafka.pkg.channel.consolidated.reconciler.controller.reconciler.00-of-01
510 /registry/leases/knative-eventing/kafkachannel-webhook.conversionwebhook.00-of-01
510 /registry/leases/knative-eventing/kafkachannel-webhook.defaultingwebhook.00-of-01
510 /registry/leases/knative-eventing/kafkachannel-webhook.validationwebhook.00-of-01
510 /registry/leases/knative-eventing/kafkachannel-webhook.webhookcertificates.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.configuration.reconciler.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.gc.reconciler.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.labeler.reconciler.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.revision.reconciler.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.route.reconciler.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.serverlessservice.reconciler.00-of-01
510 /registry/leases/knative-serving/controller.knative.dev.serving.pkg.reconciler.service.reconciler.00-of-01
510 /registry/leases/knative-serving/domainmapping-webhook.defaultingwebhook.00-of-01
510 /registry/leases/knative-serving/domainmapping-webhook.validationwebhook.00-of-01
510 /registry/leases/knative-serving/domainmapping.knative.dev.serving.pkg.reconciler.domainmapping.reconciler.00-of-01
510 /registry/leases/knative-serving/net-istio-controller.knative.dev.net-istio.pkg.reconciler.ingress.reconciler.00-of-01
510 /registry/leases/knative-serving/net-istio-controller.knative.dev.net-istio.pkg.reconciler.serverlessservice.reconciler.00-of-01
510 /registry/leases/knative-serving/net-istio-webhook.configmapwebhook.00-of-01
510 /registry/leases/knative-serving/net-istio-webhook.defaultingwebhook.00-of-01
510 /registry/leases/knative-serving/net-istio-webhook.webhookcertificates.00-of-01
510 /registry/leases/knative-serving/webhook.configmapwebhook.00-of-01
510 /registry/leases/knative-serving/webhook.validationwebhook.00-of-01
510 /registry/services/endpoints/kube-system/rancher.io-local-path
509 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.apiserversource.reconciler.00-of-01
509 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.channel.reconciler.00-of-01
509 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.containersource.reconciler.00-of-01
509 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.eventtype.reconciler.00-of-01
509 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.pingsource.reconciler.00-of-01
509 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.source.crd.reconciler.00-of-01
509 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.sink.reconciler.00-of-01
509 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.trigger.reconciler.00-of-01
138 /registry/configmaps/istio-system/istio-leader |
|
Hey @brandond - sorry for the radio silence. I ran into some other issues that took priority. Unfortunately in the process I wiped the k3s server logs you requested, but I still have the
I haven't been able to reproduce the issue yet. Appreciate any further insights you may have. I like the idea about compaction being cpu / io bound, but I can't verify that until I can reproduce the issue. |
|
yeah, it sure looks like it's just never been able to compact (delete) anything. If you ever start this up again, can you grab the k3s service logs after it's been running for a bit? |
|
Caught a big fish today, just leaving a dev-machine over the weekend. No VACCUM performed. df -lh
Filesystem Size Used Avail Use% Mounted on
/dev/nvme0n1p1 30G 28G 1.5G 95% /ls /var/lib/rancher/k3s/server/db/ -alh
total 24G
drwx------ 2 root root 4.0K Sep 21 18:39 .
drwxr-xr-x 7 root root 4.0K Sep 21 18:39 ..
-rw-r--r-- 1 root root 0 Sep 21 18:39 joined-a0416b1bb1e9
-rw-r--r-- 1 root root 119M Sep 27 08:36 state.db
-rw-r--r-- 1 root root 48M Sep 27 08:36 state.db-shm
-rw-r--r-- 1 root root 24G Sep 27 08:36 state.db-walAnalyzer/** Disk-Space Utilization Report For /tmp/state.db
Page size in bytes................................ 4096
Pages in the whole file (measured)................ 30396
Pages in the whole file (calculated).............. 30396
Pages that store data............................. 4425 14.6%
Pages on the freelist (per header)................ 25971 85.4%
Pages on the freelist (calculated)................ 25971 85.4%
Pages of auto-vacuum overhead..................... 0 0.0%
Number of tables in the database.................. 3
Number of indices................................. 5
Number of defined indices......................... 5
Number of implied indices......................... 0
Size of the file in bytes......................... 124502016
Bytes of user payload stored...................... 13468965 10.8%
*** Page counts for all tables with their indices *****************************
KINE.............................................. 4423 14.6%
SQLITE_SCHEMA..................................... 1 0.003%
SQLITE_SEQUENCE................................... 1 0.003%
*** Page counts for all tables and indices separately *************************
KINE.............................................. 3788 12.5%
KINE_NAME_ID_INDEX................................ 201 0.66%
KINE_NAME_PREV_REVISION_UINDEX.................... 197 0.65%
KINE_NAME_INDEX................................... 189 0.62%
KINE_ID_DELETED_INDEX............................. 25 0.082%
KINE_PREV_REVISION_INDEX.......................... 23 0.076%
SQLITE_SCHEMA..................................... 1 0.003%
SQLITE_SEQUENCE................................... 1 0.003%
*** All tables and indices ****************************************************
Percentage of total database...................... 14.6%
Number of entries................................. 33548
Bytes of storage consumed......................... 18124800
Bytes of payload.................................. 15303237 84.4%
Bytes of metadata................................. 188488 1.0%
Average payload per entry......................... 456.16
Average unused bytes per entry.................... 78.62
Average metadata per entry........................ 5.62
Average fanout.................................... 71.00
Maximum payload per entry......................... 320440
Entries that use overflow......................... 481 1.4%
Index pages used.................................. 40
Primary pages used................................ 2806
Overflow pages used............................... 1579
Total pages used.................................. 4425
Unused bytes on index pages....................... 77877 47.5%
Unused bytes on primary pages..................... 2540422 22.1%
Unused bytes on overflow pages.................... 19168 0.30%
Unused bytes on all pages......................... 2637467 14.6%
*** All tables ****************************************************************
Percentage of total database...................... 12.5%
Number of entries................................. 5598
Bytes of storage consumed......................... 15523840
Bytes of payload.................................. 13469783 86.8%
Bytes of metadata................................. 94239 0.61%
Average payload per entry......................... 2406.18
Average unused bytes per entry.................... 350.88
Average metadata per entry........................ 16.83
Average fanout.................................... 276.00
Maximum payload per entry......................... 320440
Entries that use overflow......................... 481 8.6%
Index pages used.................................. 8
Primary pages used................................ 2203
Overflow pages used............................... 1579
Total pages used.................................. 3790
Unused bytes on index pages....................... 11766 35.9%
Unused bytes on primary pages..................... 1933276 21.4%
Unused bytes on overflow pages.................... 19168 0.30%
Unused bytes on all pages......................... 1964210 12.7%
*** All indices ***************************************************************
Percentage of total database...................... 2.1%
Number of entries................................. 27950
Bytes of storage consumed......................... 2600960
Bytes of payload.................................. 1833454 70.5%
Bytes of metadata................................. 94249 3.6%
Average payload per entry......................... 65.60
Average unused bytes per entry.................... 24.09
Average metadata per entry........................ 3.37
Average fanout.................................... 19.00
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 32
Primary pages used................................ 603
Overflow pages used............................... 0
Total pages used.................................. 635
Unused bytes on index pages....................... 66111 50.4%
Unused bytes on primary pages..................... 607146 24.6%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 673257 25.9%
*** Table KINE and all its indices ********************************************
Percentage of total database...................... 14.6%
Number of entries................................. 33540
Bytes of storage consumed......................... 18116608
Bytes of payload.................................. 15302408 84.5%
Bytes of metadata................................. 188339 1.0%
Average payload per entry......................... 456.24
Average unused bytes per entry.................... 78.42
Average metadata per entry........................ 5.62
Average fanout.................................... 71.00
Maximum payload per entry......................... 320440
Entries that use overflow......................... 481 1.4%
Index pages used.................................. 40
Primary pages used................................ 2804
Overflow pages used............................... 1579
Total pages used.................................. 4423
Unused bytes on index pages....................... 77877 47.5%
Unused bytes on primary pages..................... 2533208 22.1%
Unused bytes on overflow pages.................... 19168 0.30%
Unused bytes on all pages......................... 2630253 14.5%
*** Table KINE w/o any indices ************************************************
Percentage of total database...................... 12.5%
Number of entries................................. 5590
Bytes of storage consumed......................... 15515648
Bytes of payload.................................. 13468954 86.8%
Bytes of metadata................................. 94090 0.61%
B-tree depth...................................... 3
Average payload per entry......................... 2409.47
Average unused bytes per entry.................... 350.09
Average metadata per entry........................ 16.83
Average fanout.................................... 276.00
Non-sequential pages.............................. 1747 46.1%
Maximum payload per entry......................... 320440
Entries that use overflow......................... 481 8.6%
Index pages used.................................. 8
Primary pages used................................ 2201
Overflow pages used............................... 1579
Total pages used.................................. 3788
Unused bytes on index pages....................... 11766 35.9%
Unused bytes on primary pages..................... 1926062 21.4%
Unused bytes on overflow pages.................... 19168 0.30%
Unused bytes on all pages......................... 1956996 12.6%
*** Indices of table KINE *****************************************************
Percentage of total database...................... 2.1%
Number of entries................................. 27950
Bytes of storage consumed......................... 2600960
Bytes of payload.................................. 1833454 70.5%
Bytes of metadata................................. 94249 3.6%
Average payload per entry......................... 65.60
Average unused bytes per entry.................... 24.09
Average metadata per entry........................ 3.37
Average fanout.................................... 19.00
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 32
Primary pages used................................ 603
Overflow pages used............................... 0
Total pages used.................................. 635
Unused bytes on index pages....................... 66111 50.4%
Unused bytes on primary pages..................... 607146 24.6%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 673257 25.9%
*** Index KINE_ID_DELETED_INDEX of table KINE *********************************
Percentage of total database...................... 0.082%
Number of entries................................. 5590
Bytes of storage consumed......................... 102400
Bytes of payload.................................. 63092 61.6%
Bytes of metadata................................. 17066 16.7%
B-tree depth...................................... 2
Average payload per entry......................... 11.29
Average unused bytes per entry.................... 3.98
Average metadata per entry........................ 3.05
Average fanout.................................... 25.00
Non-sequential pages.............................. 21 87.5%
Maximum payload per entry......................... 12
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1
Primary pages used................................ 24
Overflow pages used............................... 0
Total pages used.................................. 25
Unused bytes on index pages....................... 3661 89.4%
Unused bytes on primary pages..................... 18581 18.9%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 22242 21.7%
*** Index KINE_NAME_ID_INDEX of table KINE ************************************
Percentage of total database...................... 0.66%
Number of entries................................. 5590
Bytes of storage consumed......................... 823296
Bytes of payload.................................. 580474 70.5%
Bytes of metadata................................. 20262 2.5%
B-tree depth...................................... 3
Average payload per entry......................... 103.84
Average unused bytes per entry.................... 39.81
Average metadata per entry........................ 3.62
Average fanout.................................... 20.00
Non-sequential pages.............................. 185 92.5%
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 10
Primary pages used................................ 191
Overflow pages used............................... 0
Total pages used.................................. 201
Unused bytes on index pages....................... 18658 45.6%
Unused bytes on primary pages..................... 203902 26.1%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 222560 27.0%
*** Index KINE_NAME_INDEX of table KINE ***************************************
Percentage of total database...................... 0.62%
Number of entries................................. 5590
Bytes of storage consumed......................... 774144
Bytes of payload.................................. 554518 71.6%
Bytes of metadata................................. 19666 2.5%
B-tree depth...................................... 3
Average payload per entry......................... 99.20
Average unused bytes per entry.................... 35.77
Average metadata per entry........................ 3.52
Average fanout.................................... 18.00
Non-sequential pages.............................. 174 92.6%
Maximum payload per entry......................... 159
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 10
Primary pages used................................ 179
Overflow pages used............................... 0
Total pages used.................................. 189
Unused bytes on index pages....................... 20716 50.6%
Unused bytes on primary pages..................... 179244 24.4%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 199960 25.8%
*** Index KINE_NAME_PREV_REVISION_UINDEX of table KINE ************************
Percentage of total database...................... 0.65%
Number of entries................................. 5590
Bytes of storage consumed......................... 806912
Bytes of payload.................................. 579171 71.8%
Bytes of metadata................................. 20213 2.5%
B-tree depth...................................... 3
Average payload per entry......................... 103.61
Average unused bytes per entry.................... 37.12
Average metadata per entry........................ 3.62
Average fanout.................................... 19.00
Non-sequential pages.............................. 182 92.9%
Maximum payload per entry......................... 164
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 10
Primary pages used................................ 187
Overflow pages used............................... 0
Total pages used.................................. 197
Unused bytes on index pages....................... 19360 47.3%
Unused bytes on primary pages..................... 188168 24.6%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 207528 25.7%
*** Index KINE_PREV_REVISION_INDEX of table KINE ******************************
Percentage of total database...................... 0.076%
Number of entries................................. 5590
Bytes of storage consumed......................... 94208
Bytes of payload.................................. 56199 59.7%
Bytes of metadata................................. 17042 18.1%
B-tree depth...................................... 2
Average payload per entry......................... 10.05
Average unused bytes per entry.................... 3.75
Average metadata per entry........................ 3.05
Average fanout.................................... 23.00
Non-sequential pages.............................. 19 86.4%
Maximum payload per entry......................... 11
Entries that use overflow......................... 0 0.0%
Index pages used.................................. 1
Primary pages used................................ 22
Overflow pages used............................... 0
Total pages used.................................. 23
Unused bytes on index pages....................... 3716 90.7%
Unused bytes on primary pages..................... 17251 19.1%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 20967 22.3%
*** Table SQLITE_SCHEMA *******************************************************
Percentage of total database...................... 0.003%
Number of entries................................. 7
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 818 20.0%
Bytes of metadata................................. 137 3.3%
B-tree depth...................................... 1
Average payload per entry......................... 116.86
Average unused bytes per entry.................... 448.71
Average metadata per entry........................ 19.57
Maximum payload per entry......................... 260
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 3141 76.7%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 3141 76.7%
*** Table SQLITE_SEQUENCE *****************************************************
Percentage of total database...................... 0.003%
Number of entries................................. 1
Bytes of storage consumed......................... 4096
Bytes of payload.................................. 11 0.27%
Bytes of metadata................................. 12 0.29%
B-tree depth...................................... 1
Average payload per entry......................... 11.00
Average unused bytes per entry.................... 4073.00
Average metadata per entry........................ 12.00
Maximum payload per entry......................... 11
Entries that use overflow......................... 0 0.0%
Primary pages used................................ 1
Overflow pages used............................... 0
Total pages used.................................. 1
Unused bytes on primary pages..................... 4073 99.44%
Unused bytes on overflow pages.................... 0
Unused bytes on all pages......................... 4073 99.44%
*** 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_MASTER 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_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.
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,7,7,1,818,0,0,260,0,1,0,0,3141,0,0,4096);
INSERT INTO space_used VALUES('kine','kine',0,0,7790,5590,3,13468954,6442100,481,320440,8,2201,1579,11766,1926062,19168,1747,15515648);
INSERT INTO space_used VALUES('sqlite_sequence','sqlite_sequence',0,0,1,1,1,11,0,0,11,0,1,0,0,4073,0,0,4096);
INSERT INTO space_used VALUES('kine_name_index','kine',1,0,5590,5412,3,554518,0,0,159,10,179,0,20716,179244,0,174,774144);
INSERT INTO space_used VALUES('kine_name_id_index','kine',1,0,5590,5400,3,580474,0,0,164,10,191,0,18658,203902,0,185,823296);
INSERT INTO space_used VALUES('kine_id_deleted_index','kine',1,0,5590,5567,2,63092,0,0,12,1,24,0,3661,18581,0,21,102400);
INSERT INTO space_used VALUES('kine_prev_revision_index','kine',1,0,5590,5569,2,56199,0,0,11,1,22,0,3716,17251,0,19,94208);
INSERT INTO space_used VALUES('kine_name_prev_revision_uindex','kine',1,0,5590,5404,3,579171,0,0,164,10,187,0,19360,188168,0,182,806912);
COMMIT;Querydatabase page size: 4096
write format: 2
read format: 2
reserved bytes: 0
file change counter: 3322719
database page count: 30396
freelist page count: 25971
schema cookie: 6
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3032001
number of tables: 2
number of indexes: 5
number of triggers: 0
number of views: 0
schema size: 565
data version 2
rows compact_rev current_rev
---- ----------- -----------
5590 10752940 10757170
COUNT(*) name
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.apiserversource.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.channel.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.containersource.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.eventtype.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.parallel.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.pingsource.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.sequence.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.source.crd.reconciler.00-of-01
150 /registry/leases/knative-eventing/controller.knative.dev.eventing.pkg.reconciler.subscription.reconciler.00-of-01
150 /registry/leases/knative-eventing/eventing-webhook.conversionwebhook.00-of-01
150 /registry/leases/knative-eventing/eventing-webhook.validationwebhook.00-of-01
150 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.broker.reconciler.00-of-01
150 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.sink.reconciler.00-of-01
150 /registry/leases/knative-eventing/kafka-broker-controller.knative.dev.eventing-kafka-broker.control-plane.pkg.reconciler.trigger.reconciler.00-of-01
150 /registry/leases/knative-eventing/kafkachannel-controller.knative.dev.eventing-kafka.pkg.channel.consolidated.reconciler.controller.reconciler.00-of-01
149 /registry/leases/knative-eventing/eventing-webhook.configmapwebhook.00-of-01
149 /registry/leases/knative-eventing/eventing-webhook.defaultingwebhook.00-of-01
149 /registry/leases/knative-eventing/eventing-webhook.sinkbindings.00-of-01
149 /registry/leases/knative-eventing/eventing-webhook.sinkbindings.webhook.sources.knative.dev.00-of-01
149 /registry/leases/knative-eventing/eventing-webhook.webhookcertificates.00-of-01
149 /registry/leases/knative-eventing/kafka-webhook-eventing.defaultingwebhook.00-of-01
149 /registry/leases/knative-eventing/kafka-webhook-eventing.validationwebhook.00-of-01
149 /registry/leases/knative-eventing/kafka-webhook-eventing.webhookcertificates.00-of-01
149 /registry/leases/knative-serving/domainmapping-webhook.defaultingwebhook.00-of-01
149 /registry/leases/knative-serving/domainmapping-webhook.validationwebhook.00-of-01
149 /registry/leases/knative-serving/domainmapping-webhook.webhookcertificates.00-of-01
149 /registry/leases/knative-serving/domainmapping.knative.dev.serving.pkg.reconciler.domainmapping.reconciler.00-of-01
149 /registry/services/endpoints/kube-system/rancher.io-local-path
31 /registry/leases/kube-node-lease/deepc-os
31 /registry/masterleases/10.0.0.205
6 /registry/horizontalpodautoscalers/knative-eventing/eventing-webhook
2 /registry/events/default/deepc-os.16a822de9d294860
2 /registry/events/istio-system/istiod.16a6ea65f5cec120
2 /registry/events/knative-eventing/kafka-webhook.16a6ea972f1c1f98
2 /registry/events/knative-serving/activator.16a6ea60327fb921
2 /registry/events/knative-serving/webhook.16a6ea603f24bc66
2 /registry/minions/deepc-os
1 /bootstrap/a0416b1bb1e9
1 /registry/apiextensions.k8s.io/customresourcedefinitions/addons.k3s.cattle.io
1 /registry/apiextensions.k8s.io/customresourcedefinitions/apiserversources.sources.knative.dev
1 /registry/apiextensions.k8s.io/customresourcedefinitions/authorizationpolicies.security.istio.io
1 /registry/apiextensions.k8s.io/customresourcedefinitions/bgpconfigurations.crd.projectcalico.org
1 /registry/apiextensions.k8s.io/customresourcedefinitions/bgppeers.crd.projectcalico.org
1 /registry/apiextensions.k8s.io/customresourcedefinitions/blockaffinities.crd.projectcalico.org
1 /registry/apiextensions.k8s.io/customresourcedefinitions/brokers.eventing.knative.dev
1 /registry/apiextensions.k8s.io/customresourcedefinitions/certificates.networking.internal.knative.dev
1 /registry/apiextensions.k8s.io/customresourcedefinitions/channels.messaging.knative.dev
1 /registry/apiextensions.k8s.io/customresourcedefinitions/clusterdomainclaims.networking.internal.knative.dev
1 /registry/apiextensions.k8s.io/customresourcedefinitions/clusterinformations.crd.projectcalico.org
1 /registry/apiextensions.k8s.io/customresourcedefinitions/configurations.serving.knative.dev
``` #
</details> |
|
@brandond I am happy to provide you k3s.service logs if needed however I expect them to be quite verbose |
|
I don't think they should be necessary. The above output shows that kine compaction is running, as your actual row count is low. I think what's going on is something described in the docs at https://sqlite.org/wal.html#bigwal:
It might be worth adding some manual checkpointing to the compaction code to ensure that the WAL is reset at the end of every compaction cycle. It is curious that this only affects some users though; I suspect that knative may just keep the datastore busy to the point where it doesn't ever get a chance to reset the WAL. |
|
@andrewwebber : Sorry to bother, but can you please edit your prior posts to contain the logging output in a gist so it is easier to scroll along and follow this issue thread? |
|
@bhass1 should now be easier to scroll |
|
I usually recommend just attaching files to the comment. |
|
Thank you, @andrewwebber 👍. @brandond, noted. I appreciate your time and attention on this issue - I still have yet to reproduce the issue on my end due to other priorities. |
|
FYI - Still seeing this in v1.20.7+k3s1 ls /var/lib/rancher/k3s/server/db/ -alh
total 114G
drwx------ 2 root root 4.0K Nov 26 12:27 .
drwxr-xr-x 7 root root 4.0K Nov 26 12:31 ..
-rw-r--r-- 1 root root 0 Nov 26 12:27 joined-a0416b1bb1e9
-rw-r--r-- 1 root root 1.2G Dec 10 08:45 state.db
-rw-r--r-- 1 root root 223M Dec 10 08:45 state.db-shm
-rw-r--r-- 1 root root 112G Dec 10 08:45 state.db-wal |
|
No, it hasn't been fixed in any released version yet, which is why this issue is still open. For the 1.20 branch this is fixed by 03f7af0 |
|
Thanks for the update @brandond 🤞 |
|
This repository uses a bot to automatically label issues which have not had any activity (commit/comment/label) for 180 days. This helps us manage the community issues better. If the issue is still relevant, please add a comment to the issue so the bot can remove the label and we know it is still valid. If it is no longer relevant (or possibly fixed in the latest release), the bot will automatically close the issue in 14 days. Thank you for your contributions. |
|
Not stale, see #4044 (comment) |
|
That is a very old comment. It's been released to multiple branches for quite a while. Anyone still affected by this should open a new issue. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Environmental Info:
K3s Version:
k3s version v1.20.4+k3s1 (bc400f5)
go version go1.16.5
Node(s) CPU architecture, OS, and Version:
Linux nitrogen8mm 5.4.110+ge5cde7b05e54 #1 SMP PREEMPT Wed Apr 14 00:16:51 UTC 2021 aarch64 aarch64 aarch64 GNU/Linux
OS is based on Yocto Hardknott using Boundary Devices BSP: https://boundarydevices.com/boot2qt-embedded-qt6-image-and-toolchain-hardknott-release/
We are using the k3s recipe from meta-virtualization: https://layers.openembedded.org/layerindex/recipe/176807/
Cluster Configuration:
1 server
Describe the bug:
I booted the embedded device and left it to run overnight. By the next day, my rootfs was out of space.
Symptoms smell like: #1575 & #3660
Steps To Reproduce:
Expected behavior:
The k3s state.db-wal shouldn't grow when not being used.
Actual behavior:
Additional context / logs:
I couldn't find precompiled sqlite binaries for aarch64 and didn't want to spend time cross-compiling, so I brought the /var/lib/rancher/k3s/server/db/state.db over to my x86_64 linux dev machine and ran the commands using the precompiled binaries mentioned in #1575
https://gist.github.com/bhass1/e9d7ce2c81f9742d4bf091e770d23cba
Backporting
The text was updated successfully, but these errors were encountered: