Question 293097 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Slow when getting 4 milions record from table that has 60 miliions records

I’m currently using AWS Aurora Mysql(ServerlessV2).

Engine version: Aurora MySQL 3.04.0 (compatible with MySQL 8.0.28)

I have table like below:

CREATE TABLE "Test" (
  "key1" varchar(37) NOT NULL,
  "key2" varchar(50) NOT NULL,
  "key3" varchar(20) NOT NULL,
  "createdDate" timestamp(3) NOT NULL,
  "createdBy" varchar(50) NOT NULL,
  "updatedDate" timestamp(3) NOT NULL,
  "updatedBy" varchar(50) NOT NULL,
  PRIMARY KEY ("key1","key2","key3")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

I have simple query that

select key3
 FROM Test WHERE key1  = 
 'value1' and key2= 'value2'

It took me arround 30-35 seconds to return 4 milion row(s).

3934328 row(s) returned
Duration/fecth: 0.141 sec / 34.375 sec

About innodb_buffer_pool_size, its about 2Gb

SELECT @@innodb_buffer_pool_size
--
@@innodb_buffer_pool_size
2471493632

I tried to increase innodb_buffer_pool_size to arround 7Gb by increase Serverless v2 capacity settings. But seems no affection. It is still slow.
I want it to be under 5s to get arround 4milions records.
Can you suggest anyway to speed up this query

Tags:

2

Answers


  1. 0

    ggever, In your Parameters group, consider this suggestion,

    net_buffer_length=98304  # from 16384 to reduce packet count sent/received

    after this change, logoff/logon for the new NBL to be in service and get new timings, please. Please post Duration information so we all know what this one change does for you.

    select @@net_buffer_length; to confirm your new size of NBL, please.

    and check it again in a week to be sure your settings are still in service.

    If not let AWS know what you are trying to accomplish, please.

    View profile, please.

    Login or Signup to reply.
  2. 0

    The problem is not in the speed of the query – that lasts about 140 milliseconds. Your problem is in getting the results, i.e. four million values of key3.

    For some reason, those (at most) 240 megabytes take 34 seconds to be transferred.

    You already have keys 1-3 in a primary key, so indexing will yield no gains.

    The best approach would be to reduce the number of values returned:

    • do you really need four million different values each time?
    • are those four million different values? What if you requested key3, count(*) ... GROUP BY key3? Or might a DISTINCT be enough?

    Otherwise, other things might help:

    • increasing network buffer width will reduce packets to be sent, but this is unlikely to be a game changer (unless there are very specific network issues).
    • key3 already avails itself of a properly clustered index, but that index might have become fragmented. Have you tried running an OPTIMIZE TABLE on your Test?
    • do you really need utf8mb4 collation? For simple textual values, ascii_bin sometimes yields better performances.
    • while we’re talking about collations: for each and every column value extracted from the database, if the connection collation and the table collation do not match, MySQL will have to run a conversion subroutine, even if that conversion will perform no changes, because it cannot know beforehand whether it will or won’t. In other words, having a collation mismatch between connection and table will slow you down.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search