0

I'm having huge troubles with the performance of a very simple indexed query on a quite large table.

Configuration:

  • 48 cores, NVMe Raid, 376GB RAM (144GB used)
  • Mysql 8 (Percona server)
  • Server dedicated to DB
  • Application with low concurrent users
  • InnoDB
  • Overall database size: ~1TB
  • Table size: ~35GB
  • Table structure: primary_key (int), indexed_field (int), value (int), plus a few other ints, dates and a varchar

The query: SELECT AVG(value) WHERE indexed_field=[somevalue] => takes 220s for ~2M rows

Here is the associated explain query explain

I tried the following other queries to try and understand what was going on:

  • SELECT COUNT(*) WHERE indexed_field=[somevalue] => 1s (~2M rows)
  • SELECT AVG(primary_key) WHERE indexed_field=[somevalue] => 1s (~2M rows)
  • SELECT AVG(value) WHERE indexed_field=[anothervalue] => 0s (~15k rows)

I understand the first two use only the index without needed access to the actual data, which makes them super fast ; and the third one is also super fast thanks to the low amount of rows selected by the indexed_field.

What I can't figure out is why the initial query is so slow - MySQL is perfectly capable of processing millions of rows almost instantly (I just tried an AVG(random_field) on a 3M rows table and got the result in 3 seconds), I do provide it with the appropriate index to find them, so why does it take so long?

Here are also a few values out of the mysql config:

  • sort_buffer_size = 4M
  • key_buffer_size=48G
  • innodb_buffer_pool_size=96G
  • max_heap_table_size=12G
  • tmp_table_size=12G
Improve this question
3
  • How long does "SELECT AVG(value) WHERE indexed_field+1=[somevalue+1] " take?
    – Gerard H. Pille
    Commented Apr 5, 2021 at 11:33
  • @GerardH.Pille I tried and though I'm not entirely sure it's related, it seems it helped cache data as I now get much faster results (60 to ...15 sec), with or without the hack. Any context as to the logic of the query and how/why it could help?
    – Wuigi
    Commented Apr 5, 2021 at 18:25
  • By disallowing the use of the index. Whenever an index isn't selective, the overhead of skipping from index to table becomes so heavy, that a full table scan is to be preferred. Using "+ 0" may even be faster - ie even less cpu. The Percona MySQL must "think" that "somevalue" is selective.
    – Gerard H. Pille
    Commented Apr 5, 2021 at 19:24

1 Answer 1

Reset to default
1 
key_buffer_size=48G  -- lower to 50M unless you are using MyISAM tables
innodb_buffer_pool_size=96G  -- why not 300G
max_heap_table_size=12G  -- limit to 1% of RAM
tmp_table_size=12G       -- ditto

Queries 2 and 3 are "covering", as you paraphrased with "use only the index without needed access to the actual data".

Queries 1 and 4 are the same, except for the number of rows involved.

Did you run the first query twice? Probably the first time, the necessary data blocks were not cached in the buffer_pool and needed to be fetched from disk. In an extreme case, that could be 2M disk reads. Even NVMe probably takes more than 220s to do 2M reads.

If you have a lot of "average" and similar queries, consider building and maintaining "summary tables". They can make things significantly faster for huge tables. http://mysql.rjweb.org/doc.php/summarytables

Improve this answer
5
  • I did run the query several times but did not get better results - I'm guessing it means the buffer pool size is too small for this table and therefore the table only runs from disk, which would explain the performance? In which case I'm guessing the solution is indeed to increase the buffer pool size and / or to try and partition the table vertically or horizontally to reduce its size and allow it to live in memory? AVG was more of an example/test, in reality I actually need to fetch all 2M rows in PHP ideally (I do have a summary table but it can't cover all needed cases unfortunately).
    – Wuigi
    Commented Apr 5, 2021 at 16:17
  • With the table being 35GB and the buffer_pool being 96GB, I ruled out the buffer_pool size. Partitioning won't help except in a few rare cases. What are the summary cases? Usually it is by-day; are you doing something different?
    – Rick James
    Commented Apr 5, 2021 at 16:52
  • Considering the DB is more than 1TB and has lots of big tables, can't the buffer pool be saturated even though it's bigger than this particular table? For the summary, the issue is that we have multiple important dimensions in addition to date (such as country), which dramatically increase the size and computation time for this summary table. We still use it, but it's not as "real time" as we'd like it to be, and doesn't cover all use cases
    – Wuigi
    Commented Apr 5, 2021 at 17:13
  • Does the following configuration seem ok to you? innodb_buffer_pool_size=300G, innodb_buffer_pool_instances=20, innodb_buffer_pool_chunk_size=500M (since we're currently using the default value for instances and chunk size, which would make the number of chunks > 1000, which is apparently not recommended)
    – Wuigi
    Commented Apr 5, 2021 at 17:17
  • @Wuigi - Sounds good. The increased buffer_pool_size may help, or it may not make any difference for the current question.
    – Rick James
    Commented Apr 5, 2021 at 17:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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