1

I have a particular task which is to maximize the concurrent performance. There is only one particular type of query, which is

select * from table where col1 between ? and ? and col2 between ? and ?

I have created a composite index for (col1, col2). The table is about 20G in size and 100 million rows

However, even in peak concurrent requests, the CPU utilization for MySQL is only 30%. I have tried various techniques like increase max_connections, innodb_buffer_pool_instances but none of them are working.

How to maximize the configuration so that it can perform such read-only query to extreme?

4
  • 1
    increasing innodb_buffer_pool_size be the thing to change to have an effect. What is its current value? max_connections and innodb_buffer_pool_instances are unlikely to improve. Look at SHOW GLOBAL STATUS LIKE "innodb_buffer_pool_%" look at innodb_buffer_pool_read_requests vs innodb_buffer_pool_reads when you before/and after re-reruning the query. How many rows does the col1 range correspond to? I assume its less than the col2 range? – danblack Apr 4 '20 at 3:01
  • I have changed innodb_buffer_pool_size to 70% of memory but still the same 30% CPU. About 20000 rows correspond to col1 range – Barry Apr 4 '20 at 17:14
  • 1
    Please post TEXT of A) your query, B) text results of EXPLAIN SELECT SQL_NO_CACHE * (rest of your query); C) text results of SHOW CREATE TABLE table_name; D) text results of SHOW INDEX FROM table_name; E) do you REALLY need all the columns in the result? – Wilson Hauck Apr 4 '20 at 23:31
  • @Barry Please post complete MySQLTuner report after 24 hours uptime for us to have a sense of your instance workload, since you have not posted any other information requested. – Wilson Hauck Apr 7 '20 at 20:21
0

Can't optimize it without some serious surgery. Think of it as a "bounding box" as discussed in http://mysql.rjweb.org/doc.php/find_nearest_in_mysql

It discusses 5 ways of doing "find nearest". You are on the second-worst, and probably do not have the best indexes for that. Then it moves on to 3 faster ways to run the query, but they require non-trivial effort. But the speedup is quite significant.

As for concurrency, those algorithms provide performance by decreasing the I/O and CPU. This allows for more concurrency and even lower CPU usage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.