Slow SQL Execution

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

Original topic: SQL 执行慢

| username: 等一分钟

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.1.7
[Reproduction Path]
When running analyze table tablename;, this error occurs:

Fast analyze hasn’t reached General Availability and only supports analyze version 1 currently

| username: 等一分钟 | Original post link

The image you provided is not accessible. Please provide the text you need translated.

| username: 等一分钟 | Original post link

It took so long to query such a small amount of data.

| username: h5n1 | Original post link

Check the value of the variable tidb_enable_fast_analyze.

| username: 等一分钟 | Original post link

The performance of TiDB is indeed very good, but it is still necessary to evaluate according to the specific business scenario.

| username: h5n1 | Original post link

Change it to off and try again.

| username: 像风一样的男子 | Original post link

Take a look at the execution plan.

| username: 等一分钟 | Original post link

Untitled.xlsx (4.1 KB)

| username: 等一分钟 | Original post link

The tidb_server crashed directly.

| username: h5n1 | Original post link

Upload the TiDB logs. Are all SQL queries slow now? What operations were performed before?

| username: 等一分钟 | Original post link

Currently, we found that only this SQL is slow. Just checked and found that the tidb_server ran out of memory (OOM).

| username: 等一分钟 | Original post link

Didn’t perform any special operations.

| username: h5n1 | Original post link

Does the table atd_report_viewlist have any special fields? Let’s take a look at the table structure. The full table scan read 57.4 GB of data.

| username: 等一分钟 | Original post link

CREATE TABLE atd_report_viewlist (
Id varchar(40) NOT NULL,
PersonId varchar(40) DEFAULT NULL,
UnitId varchar(40) DEFAULT NULL,
AttendanceCycle varchar(255) DEFAULT NULL,
StartDate datetime DEFAULT NULL,
EndDate datetime DEFAULT NULL,
IsPush int(11) DEFAULT NULL,
PushDate varchar(100) DEFAULT NULL,
IsGeneratePDF int(11) DEFAULT NULL,
PDFName varchar(1000) DEFAULT NULL,
PDFData longtext DEFAULT NULL,
IsSign int(11) DEFAULT NULL,
SignDate varchar(100) DEFAULT NULL,
PDFPageCount int(11) DEFAULT NULL,
PDFSignPage int(11) DEFAULT NULL,
SignX varchar(100) DEFAULT NULL,
SignY varchar(100) DEFAULT NULL,
IsDeleted int(11) DEFAULT NULL,
CreateUser varchar(100) DEFAULT NULL,
CreateTime datetime DEFAULT NULL,
LastUpdateUser varchar(100) DEFAULT NULL,
LastUpdateTime datetime DEFAULT NULL,
pdfUrl varchar(500) DEFAULT NULL,
pdfUrlSigned varchar(500) DEFAULT NULL,
PRIMARY KEY (Id) /T![clustered_index] NONCLUSTERED/,
KEY index_reportViewUnitId (UnitId),
KEY ix_PersonId (PersonId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

| username: 等一分钟 | Original post link

read_byte: 57.4 GB, where did you see this?

| username: h5n1 | Original post link

The PDFData longtext DEFAULT NULL field in the execution plan, analyze causing OOM and large read volume should be related to this field.

| username: 等一分钟 | Original post link

57.4 GB, can this be seen from the execution plan?

| username: 等一分钟 | Original post link

In MySQL, it can be queried.

| username: xfworld | Original post link

Can this field be deleted? Try deleting it and see what happens :melting_face: :melting_face:

| username: 等一分钟 | Original post link

That’s not acceptable, it’s a production environment.