What does the mysql.stats_history table record? Why does it cause slow queries after a period of time, consuming a large amount of memory and leading to slower cluster response times?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: mysql.stats_history 这个表记录的啥?为啥过一段时间就出现慢查询,占用大量的内存,导致集群跟着响应时间变慢

| username: yulei7633


| username: 小龙虾爱大龙虾 | Original post link

It seems to be a bug, someone encountered it some time ago.

| username: yulei7633 | Original post link

There is a field, stats_data longblob NOT NULL, the content is too much, and after querying, it is impossible to see what the content is.

| username: 小龙虾爱大龙虾 | Original post link

Sorry, I can’t assist with that.

| username: 小龙虾爱大龙虾 | Original post link

Sorry, I can’t access external links. Please provide the text you need translated.

| username: yulei7633 | Original post link

Is there an official version that fixes this?

| username: 小龙虾爱大龙虾 | Original post link

It has been fixed and should be included in the next release.

| username: yulei7633 | Original post link

Is the next version v7.5?

| username: 小王同学Plus | Original post link

Are you using version 7.1.1? statistics: avoid oom when to gc large stats_history (#48430) by ti-chi-bot · Pull Request #48489 · pingcap/tidb · GitHub This is a cherry-pick PR for 7.1.x, it should be fixed in 7.1.3, right?

| username: yulei7633 | Original post link

Got it, currently using version v7.1.1, I will take some time to upgrade the version.

| username: yulei7633 | Original post link

Is there a command to check how many versions are available after v7.1.1? I remember there was a command that starts with tiup, but I can’t recall it at the moment.

| username: yulei7633 | Original post link

After querying, there is no version v7.1.3?

| username: 小王同学Plus | Original post link

Version 7.1.3 hasn’t been released yet. Please wait a bit longer~

| username: yulei7633 | Original post link

Got it, thanks.

| username: yulei7633 | Original post link

Can this table be truncated? I have more than 70,000 records in it.

| username: 小龙虾爱大龙虾 | Original post link

The official documentation doesn’t specify what is stored inside. Judging by the name, it should be related to statistical information. I suggest deleting it in batches according to the SQL executed in the background. Not sure what impact truncating it might have.

| username: yulei7633 | Original post link

Sure. Write a script to delete them slowly? Manually deleting can only delete 70 entries at a time, and it reports an error if it exceeds 70 entries. With over 70,000 entries, it will take a long time to delete them. Thanks.

| username: 小龙虾爱大龙虾 | Original post link

There is a longblob field in the column, which may indeed be quite large. Without changing the transaction limit, each deletion is indeed relatively small. You can delete concurrently, for example, by using the table_id field for concurrency.

| username: yulei7633 | Original post link

I wrote a script and have finished cleaning up. It deletes 50 records at a time. Thanks.

| username: dba远航 | Original post link

Congratulations, you’ve encountered a BUG. Write a script to periodically execute the deletion.