MySQL Forums
Forum List  »  Performance

Incredibly slow count(*) on MariaDB / MySQL
Posted by: Michael Modan
Date: April 04, 2024 03:30AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Incredibly slow count(*) on MariaDB / MySQL
178
April 04, 2024 03:30AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.