MySQL: Select count(*) in status killed consuming 100% of CPU

Question

We've been running into a very odd problem on our MySQL RDS instances with SELECT COUNT(*) commands and after three times of it happening and no luck finding a solution, I figure I'd ask here too.

The issue happens when (but not always) we do a SELECT COUNT(*) FROM on relatively large (1M+ rows) tables. The query, for some reason, spikes the server's CPU to 100% usage and takes way longer than it should.

We try to kill it via the KILL {threadId} command and the query will just switch on the process list to status "killed" but nothing will happen.

I understand that running KILL on the thread only sets a flag that the query should be terminated - there's no immediate way to terminate it - but this is literally a SELECT COUNT(*) operation on a table to see how many rows are there. No WHERE clause, no JOINs, no need for the optimizer to create a virtual table and nothing to undo.

This has happened in production but also in our read-only replica mirror RDS, so it's not even a deadlock with other transactions [save for the replica process itself perhaps?] -- we couldn't find any notable deadlocks in the INNODB STATUS log either.

We can't figure out why

1. such a simple command just up and murders the CPU
2. why the thread just won't die.

We took the liberty last time to let the command keep running overnight to see if it was "just a matter of time and it will get killed when it's done processing whatever it is doing" but after 9 hours we had to reboot the server.

All for times this happened - thrice in Replica and unfortunately once in production - we had no choice but to reboot.

I can't tell if this is a MySQL bug [we're running 8.0.19] or what could be happening here... Any help on where to look or if this is a known issue would be greatly appreciated.

[[EDIT]] Ran the same command today to test for deadlocks. No deadlocks show up in the SHOW ENGINE INNODB STATUS results, but we noticed that the CPU only spikes once the KILL command is issued. The query itself only showed a moderate climb in Read IOPS but CPU usage was minimal - in fact it was actually falling when the SELECT was run, and only climbed once the KILL was issued.

[UPDATE]

@zedfoxus the table does have a primary key autoincrement column. Here's the EXPLAIN of the original query:

And here's the explain of a select count on the id field:

[UPDATE 2] as recommended in comments by @zedfoxus, I tried running

 select count(*) 
 from push_notification_async_queue 
 use index (primary)

The query ran successfully in 6 minutes. As a test I decided to run the query and kill it preemptively, which caused the bad result to repeat.

Interesting note: the select with use index(primary) ran in 6 minutes but the CPU usage only peaked at 70% and never really settled too high.

The killed query, however, immediately climbed to 100% and stayed there for 12+ minutes, so it's not that it was just ignoring the kill flag and running normally....

[UPDATE 3] As requested by @Wilsonhauck, here's the SHOW CREATE TABLE:

CREATE TABLE `push_notification_async_queue` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` varchar(50) DEFAULT NULL,
  `store_id` smallint DEFAULT NULL,
  `token` varchar(256) NOT NULL,
  `title` varchar(47) DEFAULT NULL,
  `body` longtext NOT NULL,
  `image` text,
  `url` text,
  `category` varchar(256) DEFAULT NULL,
  `provider` varchar(50) DEFAULT NULL,
  `sent_on` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IX_push_notification_async_queue-sent_on` (`sent_on`)
) ENGINE=InnoDB AUTO_INCREMENT=19082441 DEFAULT CHARSET=utf8

And here's the SHOW TABLE STATUS for it [apologies, I don't know how to copy a table properly here]:

Name    Engine  Version Row_format  Rows    Avg_row_length  Data_length Max_data_length Index_length    Data_free   Auto_increment  Create_time Update_time Check_time  Collation   Checksum    Create_options  Comment
push_notification_async_queue   InnoDB  10  Dynamic 14616288    2894    42310041600 0   302432256   8388608 19082441    2021-09-24 23:38:49 2021-12-10 23:44:42     utf8_general_ci     ""  ""

Answer 1

(This is not a full answer, but is some further details of what is going on.)

In InnoDB, the entire table needs to be scanned for a simple SELECT COUNT(*) without a WHERE clause. This is so that it can correctly count the rows that are 'visible'. If other connections are busy inserting/deleting rows, such rows need to be checked to see if they should be counted.

Assuming that it is a huge table, it would be best to go through all the rows via the smallest BTree. Note that the Data is stored in a BTree sorted by the PRIMARY KEY and each secondary index (if any) is in a separate BTree indexed by a combination of the index's column(s) together with the PK's column(s). Apparently, such an index is based on a DATETIME .. NULL, which shows up in Explain as key_len=6 (5 for DATETIME plus 1 for NULL).

So, why might reading that entire secondary BTree be slower?

• The Data's BTree may be fully cached in the buffer pool and the datetime index is not cached and the disk is a slow HDD (not a fast SSD). (Side questions: How big (GB) is the table; what is the setting of innodb_buffer_pool_size; how much RAM is on the server?) Did you run the COUNT(*) twice? That usually compensates for cached vs non-cached.

• Other connections may be locking rows of the table. This will slow down the COUNT. I do not have a good feel for how this would impact the timings, especially by the extent you are experiencing.

Neither of those points justifies (in my experience) the timing differences you are seeing.

Sizes:

• The Data BTree is 42GB, but, noting an average row size of 2894 bytes, the 42GB may include "off-record" bulky TEXT columns. The datetime index is no bigger than 0.3GB since that is the combined size of all secondary indexes.
• Depending on RAM size, the table scan may require lots of I/O, while the secondary index scan should be easily cached -- that is, the second timing of COUNT(*) should be very fast.
• The number of rows is estimated at 15M by Explain, and cannot be more than 19M due to auto_increment. I estimate at least 200 bytes of on-record data per row (including overhead). Meanwhile, the datetime index seems to be 20 bytes per row in the Index BTree. Hence, barring I/O, I would expect the use of the index to be at least 10 times as fast as using the PK.
• With 42GB of data and less than that of RAM, it is definitely not repeatedly scanning the data. (Alas, I don't have a good suggestion of what it is doing.)

Answer 2

Why don't you just count a column instead.... Like select count(id).... Might work

Answer 3

I think these solution can solve your problem. maybe all 3 solution or any one or two. 1 Select Data in the form Chunks. 2 Do Indexing. 3 if your are showing data on view then use pagination 4 if your are using laravel you should use db query because this this more fast then sql query.

Answer 4

Quick answer

Doing

select count(*)
from push_notification_async_queue use index (primary)

or

select count(*)
from push_notification_async_queue use index (name-of-the-primary-key-index)

can yield faster results.

But why?

Primary key is auto incrementing. So, if you had IDs 1, 25 and 400 in the database, it is easier to count that in the index on IDs. Giving MySQL the hint to use the primary index (or explicitly giving the name of the primary index you have on IDs) will make MySQL read that index to answer the query.

How can we tell what MySQL was using without the hint?

EXPLAIN select count(*) from push_notification_async_queue

will tell us what index MySQL was choosing to answer the query.

Based on the results of explain, I could tell that MySQL thought it would get count(*) fastest from IX_push_notification_async_queue-sent_on field

id = 1
select_type = SIMPLE
table = push_notification_async_queue
key = IX_push_notification_async_queue-sent_on
key_len = 6
rows = 14870073
filtered = 100
extras = using index 

However, IX_push_notification_async_queue-sent_on seems like a date. MySQL has to work lot harder to find count of rows based on an index that contained date/time. That made me think that using index hint will help.

Why would MySQL do silly things like this?

That's hard to tell. You can optimize or analyze table and see if that helps. It can take a long time, so I recommend you take a backup from your production system, restore on your local computer or a test system and try it there. https://dev.mysql.com/doc/refman/8.0/en/table-maintenance-statements.html gives you details about those commands.

On a table that is written to, deleted from, or read from heavily, statistics could take time to build and that can cause MySQL to choose an index that it feels satisfies the query best. There's some good discussion in this thread: MySQL uses incorrect index. Why?