High CPU Usage in TiKV

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

Original topic: tikv cpu 使用率高

| username: Raymond

Experts, I have a question. For a table with over a hundred million rows, which is a non-clustered table, there is a frequently executed statement. The execution plan for this statement is a full table scan, and the where clause field cannot be indexed because its selectivity is too low. This is causing high TiKV CPU usage and read hotspots. Besides regularly cleaning up the data in this table and reducing the execution frequency of this statement, are there any other good solutions? Assume the statement cannot be rewritten.

| username: 我是咖啡哥 | Original post link

For tables with hundreds of millions of rows and poor selectivity, how many rows are returned per query? If the number of returned rows is small, creating an index might be worth a try. You can give it a shot.

| username: h5n1 | Original post link

First, post the SQL table structure, the distinct values of the where column, and the execution plan of explain analyze. Let’s take a look.

| username: Kongdom | Original post link

What is this query for? If it’s an aggregate function, you might consider TiFlash.

| username: Jiawei | Original post link

Low selectivity does not mean that you cannot add an index. This is a misconception. It still depends on how many rows your selection criteria can filter out. For example, take a gender field: if you have 100 billion records and only one is female while the rest are male, you can still add an index. If you want to query for the female gender, you can add an index for that as well. As mentioned earlier, please paste the SQL table structure.

| username: Raymond | Original post link

I think you can try adding an index, after all, TiDB supports histograms.

| username: Raymond | Original post link

Thank you, expert. I think we can add an index. TiDB supports histograms.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.