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 Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.18 OS:CentOS
Assigned to: CPU Architecture:x86

[20 Jan 2020 1:39] Danie Dong
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:
[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

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

Can you try it with 8.0.27 ?????