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
Answer Answered by Ian Gilfillan in this comment.
With InnoDB, since it only stores estimates of the number of rows in the table, in order to obtain the specific value with COUNT(*), all rows need to be read, which is very slow.
If you're happy with an estimate, you can query information_schema.tables, for example:
SELECT TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';
See also MDEV-18188.
