select count(*) takes too much time in RDS/MySQL during high traffic

Question

Setup:

• db.t3.xlarge RDS instance.
• MySQL 8.0.33 engine.
• Default parameter and options group.
• ~900k records table.
• RDS has no logging enabled at all, and we cannot restart the instance for now to enable it.

The database is connected to a Laravel application that runs in a Lambda. One of the processes in the lambda requires counting the number of rows in the spin table.

During the first iteration, we found that the select count() written in the following manner took a good amount of seconds from time to time. Some requests were immediate and others may take over 20 secs.

select count(*) from spin;

Digging the Internet we found some answers from people complaining about this. We decided to add a condition to the query, and that made it a sub-second query:

select count(*) from spin where id > 0;

Until a few days ago, when our service started to receive more traffic than usual and the query running times became very jumpy.

+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
| ID     | USER  | HOST             | DB      | COMMAND | TIME | STATE     | INFO                                                     |
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+
| 114168 | vmx   | 10.0.2.175:43169 | fdata   | Execute |   60 | executing | select count(*) as aggregate from `spin` where `id` > 0  |
| 114171 | vmx   | 10.0.3.149:31136 | fdata   | Execute |   58 | executing | select count(*) as aggregate from `spin` where `id` > 0  |
| 114118 | vmx   | 10.0.2.175:36571 | fdata   | Execute |  109 | executing | select count(*) as aggregate from `spin` where `id` > 0  |
+--------+-------+------------------+---------+---------+------+-----------+----------------------------------------------------------+

I suspect this has to be due to some access lock to the spin table. During the table lock, the select count() hangs.

Any input is appreciated, thanks.

Answer 1

Any input is appreciated

Why live query the count of the entire table in an almost million row table? It sounds a bit arbitrary - who is going to notice or care that the exact count is 957,432 right this second, and a few minutes later it's now 957,433?

I'd minimally recommending caching that count and then reading from the cache for repeated hits.

Answer 2

Not sure how you are utilizing those counts in your application but you could query INFORMATION_SCHEMA to get approximate counts, they get updated with table stats are updated, should be close to the actual row counts.

SELECT TABLE_ROWS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME = 'spin'
AND TABLE_SCHEMA = 'schema_name'

Answer 3

The reason is that other connections sometimes touch that table in a way that conflicts with the COUNT. Or at least bogs it down.

I agree with J.D. that finding the count is probably unnecessary. And I agree with S.D. that you can get an estimate; however, the estimate could be drastically far off.

Please explain your use for the count; perhaps we can tailor some trick to help you.

One trick is to have a secondary INDEX(foo) where foo is one of the smallest columns in the table. The `COUNT(*) will use that BTree to do the counting. This may run faster and may hit the speed bump less often.