1

I have a table in mysql Aurora 5.7 version .

The db instance has very good configuration 488 GB ram (db.r4.16xlarge).

Data base is static data base no application is running on this .

I have 1.5 Billions record approx 2TB size in the table .

The table does not have any index except primary key .

Table has 35 Columns .

This table does not have any partition

Now i need to create Index on nine other columns and here i hit hard . It takes 5 Hours to create one index and when i run command to create all 9 index at same time it took almost 10 hours and then i cancelled .

Is there any way i can make this index creation faster ? Is creating 10 partition will help creating Index faster ?

Why i need to create Index on 1.5 Billions records is because i have migrated my table from Orcale Source to Mysql using AWS DMS service .

Before migration i need to drop index at target and then have to migrate so that migration will be faster and it is actually very fast .

Without Index migration took 10 hours where as with Index it is taking 25 Hours and some time it fails as well .

8
  • How many drives are supporting the 2TB table? Commented May 23, 2020 at 15:25
  • @MichaelKutz sorry i did not get how many drivers ? Commented May 23, 2020 at 16:13
  • How many physical drives are in your system? What is the RAID configuration? What is the sustainable throughput of your disk subsystem? How fast can you copy 2TB of data? Does your expected CREATE INDEX time coincide with what your system can handle? Did you run benchmarks on the I/O subsystem before hand? If so, what are the results? Commented May 23, 2020 at 16:22
  • 1
    Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful information, if available includes - ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions for reducing index creation time required. Commented May 25, 2020 at 19:42
  • Is that 9 single-column indexes? That is rarely optimal. Let's see the queries that need them; there may be some 'composite' indexes that will help even more.
    – Rick James
    Commented May 31, 2020 at 2:30

2 Answers 2

0

You have a lot of rows., every row has to be inserted into the index, that takes time, live with it.

You can try to use only one index for all columns

CREATE INDEX name 
ON yourtable(column1,column2,column3,column4...colmn9);

That takes also long, but should be faster

4
  • Does partitioning will help ? Commented May 23, 2020 at 16:14
  • @AtharvThakur No, partitions it will only complicate your life. Commented May 25, 2020 at 19:34
  • INDEX(a,b) versus INDEX(a), INDEX(b) -- These are not equivalent. See mysql.rjweb.org/doc.php/index1
    – Rick James
    Commented May 31, 2020 at 2:40
  • With 2 or more columns, the overwhelming majority of SO prefer combined index when they are used together
    – nbk
    Commented May 31, 2020 at 7:31
0

Partitioning rarely helps with performance. http://mysql.rjweb.org/doc.php/partitionmaint

Adding an index to a partitioned table probably takes longer than to the equivalent non-partitioned table -- it will block access throughout the action.

There have been several important changes to Oracle's MySQL; Aurora may not have picked them up yet. (I don't know how close Aurora 5.7 is to MySQL 5.7.) You tagged the question [mysql-5.6]; which is it?

Let's see the actual indexes you what to add; there may be some tricks and/or cautions to discuss.

In general, it is not useful to index "flags" or other low-cardinality columns. That is, if they are alone in the index.

Prefixing is rarely useful. Example: INDEX(foo(10))

Percona Toolkit may have something to help.

Let's see the queries that need them; there may be some 'composite' indexes that will help even more. Also SHOW CREATE TABLE.

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.