CODEWITHSUNDEEP
mysql
Rachit Anand
Rachit Anand

Reputation: 664

MySQL Table with ~20Mil rows - Queries getting slow

I have a table in my MySQL(5.7.32) database which currently has 20Mil rows. I have a few fairly complex queries written on that table, where I carry out FullTextSearches and join them to other tables. The queries on the table are getting slow (using appropriate indexes).

I understand that 20 Mil rows are not a lot for a DB table to handle, and would like to understand what are the factors (other than indexes) that I should consider for performance improvements. For example, any DB defaults that I should consider changing that impact performance.

NOTE: Since the table has FTS indexes, partitioning is not an option.

Upvotes: 1

Views: 102

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

There are a lot of factors that could hurt performance:

  • Buffer pool not large enough to hold the index. So as a query searches the index, it has to keep swapping parts of the index into RAM and back out. You may need to increase the innodb_buffer_pool_size.

    I'd monitor the ratio of the two numbers reported by SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_read%s'.

  • CPU is too slow. Each query is single-threaded, so CPU speed is more important than number of cores.

  • Concurrent load. If you have many queries running at the same time, they compete with each other for CPU, buffer pool, and I/O. Check SHOW PROCESSLIST or SHOW GLOBAL STATUS LIKE 'Threads_running'.

  • Server is overloaded, either by MySQL or by other apps or processes. Use top to find out if the system load average is high (I would consider anything over 10 to be too high), or if the system is using swap space instead of RAM.

  • Is the query using indexes like you expect? Did you analyze them with EXPLAIN?

Upvotes: 1

Related Questions