Bug #98289 a threads keep killed for more than 50000 second

• Submitted: 20 Jan 2020 1:39
• Modified: 20 Jan 2020 13:03
• Reporter: Danie Dong
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.18
• OS: CentOS
• CPU Architecture: x86

[20 Jan 2020 1:39] Danie Dong

Description:
we executed an SQL （select count(1) from tbale）.this is a large table(140GB)and Mysql sever was very busy at that time .we killed it when it runed for 300second .but it cant be released .Forty-eight hours have elapsed. it  kept killed . 

there were  four threads holding cpu .they all are thread/innodb/parallel_read_thread.
show variables  innodb_parallel_read_threads ,=4.,there were no any detail in errlog.

it happened for three times .it was a bad idea that i try  shutdown restart MySQL.MySQL server will hang,cant be shuted  and cant be started .at this time I dont know how to stop this thread.

How to repeat:
 above

[20 Jan 2020 1:42] Danie Dong

undo space hasty increase for 10G already

[20 Jan 2020 13:03] MySQL Verification Team

Hi Mr. Dong,

Thank you for your bug report.

However, this is not a bug. Simply, InnoDB Storage Engine is a MVCC SE. Hence, the count is different in each session. That means that all rows are counted by sequential reads.

If you wish how to get to the approximate number of rows, this is explained in our Reference Manual, where you have to use one  I_S table.

Not a bug.

[27 Feb 2020 14:39] Sveta Smirnova

Sinisa,

this bug seem to be is not about speed of SELECT COUNT(*) query, but about the fact that if innodb_parallel_threads > 1 the query cannot be killed until all read job is done.

[27 Feb 2020 15:21] MySQL Verification Team

Query or connection can not be killed ?????

I tried with 1 Gb table and it was killable ..... That is good enough for me ....

[9 Nov 2021 3:23] zhui liu

If you are willing to construct a 100GB table to test, you may have a few new discovery.

[9 Nov 2021 12:31] MySQL Verification Team

Hi,

Can you try it with 8.0.27 ?????