Incredibly slow count(*) on MariaDB / MySQL
Problem:
SELECT COUNT(*) FORM contact_activity takes ~6 minutes
Profiling the query shows 100% of the time is spent over 'sending data' although it's only the count that is returned, no other data, no join, nothing.
There are ~600M records in the table, roughly 3GB in size, including indices.
It's well partitioned.
It has relevant indices, and the EXPLAIN for SELECT COUNT(*) shows its users the index.
Regular operations take reasonable time i.e. up to 2-3 minutes
Additional info:
MariaDB - version 10.11.7
3 indices defined, none on text, all BTREE -
Primary - int(11) + datetime, index size is ~30% of the table size
Index-1, nullable, non unique - index int(11), size is ~15% of the table size
Index-1, nullable, non unique - index int(11), size is ~15% of the table size
Partition is monthly i.e. PARTITION BY RANGE COLUMNS(activity_datetime)
What I've tried
- Isolation level was set REPEATABLE-READ (it was changed after the problem occurred hence this isn't the cause, but neither the solution)
- Standalone server with SSD, 32GB RAM and 16 cores, solely serves the DB
- SELECT COUNT(id) - doesn't change anything as the same index is used
- FORCE INDEX usage - doesn't change anything
- innodb_buffer_pool_size set to 50GB
- innodb_buffer_pool_chunk_size set to 2GB
- innodb_read_io_threads set to 8 (changed from default 4)
- SHOW ENGINE INNODB STATUS - shows nothing special, deadlock rarely happens, and for sure isn't the cause
5.5 seconds - when attempted sampling the same table over 20M records
17.8 seconds - when attempted sampling on a copy table with 100M records and 2 columns only - id and active_datetime, indexed.
The above simply doesn't make sense to me, I'd appreciate any guidance on how to further dig into it / fix it.
Cheers