TiDB kine table data only increase not decrease fast

[zzps]

K3s Version: v1.20.4+k3s1

Node(s) CPU architecture, OS, and Version: amd64+centos7.8

Cluster Configuration:2servers+2agent

Describe the bug:
deploy k3s about one month with external database TIDB,and I found 13,483,866 rows data,hold 16GB disk space.
check the sql just include select insert without any delete
Steps To Reproduce:

• Installed K3s: install k3s offline yestoday night,and the morning today I found there are 156,614 rows

Expected behavior:
small data,not increase
Actual behavior:

Additional context / logs:

[brandond]

external database TIDB
Can you share more information on what database engine you're using?

check the sql just include select insert without any delete
Kine uses the SQL database as a time series database. Writes to the datastore insert new rows to store resource revisions, and old revisions are pruned in batches every 5 minutes or so. You won't see deletes very frequently.

install k3s offline yestoday night,and the morning today I found there are 156,614 rows

What do you have running on this cluster? What database engine and version? What sort of storage is hosting the database?

[zzps]

above are the image about database infomation and the application running on k3s. and I am absolutely sure it never run delete sql to decrease rows. it is terrible. I must consider to fix it before it become disaster

[zzps]

[zzps]

to verify and find problem I yestoday night deploy a new k3s cluster with two server without agent ,external storage TIDB 5.0.1，and no any application running on the new k3s cluster, and today morning there are 156,614 rows data in the kine table. and now the afternoon there are 207,273 rows data !!!!! oh my god!!!

[brandond]

What exactly is TIDB? It appears to be something that emulates the MySQL database protocol using a different engine? This is definitely not something we've tested and is not supported. Can you reproduce this same behavior with a true MySQL database?

[zzps]

What exactly is TIDB? It appears to be something that emulates the MySQL database protocol using a different engine? This is definitely not something we've tested and is not supported. Can you reproduce this same behavior with a true MySQL database?

Ok , I change it to real mysql now. by the TIDB is 100% compatible with mysql

[brandond]

Compatible doesn't mean identical. We've seen other users run into issues when attempting to use CockroachDB without proper serialization constraints enabled. Kine has strict requirements around the sequential ordering of the auto-increment primary key that many distributed database engines don't implement, or don't enable by default, in the interest of scalability.

And, as I said earlier, deletes happen in batches once every 5 minutes so you are unlikely to see them in a random sampling of the transaction log.

[zzps]

Compatible doesn't mean identical. We've seen other users run into issues when attempting to use CockroachDB without proper serialization constraints enabled. Kine has strict requirements around the sequential ordering of the auto-increment primary key that many distributed database engines don't implement, or don't enable by default, in the interest of scalability.

OK . for preciseness. I test it on real mysql right now!!! and will tell you the result

[brandond]

From the TiDB docs:

Auto-increment ID
In TiDB, auto-increment columns are only guaranteed to be unique and incremental on a single TiDB server, but they are not guaranteed to be incremental among multiple TiDB servers or allocated sequentially.

This specifically will not work for Kine.

There are many other differences from standard MySQL, I would definitely not say that it is 100% compatible with applications that expect MySQL behavior.
https://docs.pingcap.com/tidb/dev/mysql-compatibility

[zzps]

hi big brother! I have change to mysql8.0.25, and redeploy k3s cluster again. and put my eye on the kine data increase, finally I surpriselly found the rows count increase to 2356 ,and next second I refresh it change to 1467. it works!

[afritzler]

I have seen the same issue happening on CockroachDB k3s-io/kine#87 Was anybody out here able to fix the compaction query for mysql/posgres-ish DB derivates?

[zzps]

I have seen the same issue happening on CockroachDB k3s-io/kine#87 Was anybody out here able to fix the compaction query for mysql/posgres-ish DB derivates?

for now,you must use the standard MySQL ,without any compatible mysql product. by the way I really recommend you to change the kine autoincreament id type from int to bigint for 5-10 insert per sencond!

[afritzler]

Any hints on how to do that? :). Do I need to change the schema in the kine source?

[zzps]

the feild ' id 'of kine table created by k3s is 'int' type, and the insert action is quickly. so it is not longer for 'int'. change to 'bigint'
type for improve

[afritzler]

I was wondering if somebody managed to get cockroachDB/TiDB running via kine stable for while. Apart from the compaction issue I also see a problem with list queries when the API server builds up it's watch cache upon start up.

[zzps]

for personal opinion,I don't think the work of k3s external storage is satisfactory! but it is the fact. I faild it on TIDB, and the data size increase to 10,000,000 for only one month !