3

22 hours ago I started a script that executes an alter table to add a primary key to a table which has 225 millions of records. MSISDN is the only column.

In showproceslist its process status is "copy to tmp table". Can anyone tell me if this is the correct behaviour and how long it will take to complete?

miracle173
  • 7,662
  • 25
  • 41
sohan singh
  • 31
  • 2

2 Answers2

4

Yes, creating a PK on a big table takes time.

When you don't have PK (more precisely a clustered index - the two is the same in MySQL using InnoDB) the table is called heap and the DBMS adds a 'row identifier' to each row to track them. This ID is used to identify records in indexes and other places.

When you have a clustered index (PK in MySQL), the PK key will be the row's identifier and all indexes are using the clustered key to identify records.

When you modify the type of the table (heap to clustered), all indexes and the DBMS should rebuild the table and reorganize the data stored in it.

Best practices:

  • Drop ALL nonclustered indexes before you change (or drop/create) the clustered key
  • Stop all processes which are using that table to prevent deadlocks and long wainting (do it in your maintenance window)
  • If you want to change (or drop/create) the clustered key on a huge table, create a new table with the required primary ky (clustered index), then copy all data from the original table, then replace the new table with the old by renaming. When this done, you can add additional indexes to the table.

how long it will take to complete?

It depends on the server and the processes runing next to the alter on the server. In short, we simply can not say a number how long it will take.

Pred
  • 1,051
  • 10
  • 9
  • 1
    Minor note: Only InnoDB tables have clustered indexes. The OP did not state which engine the table uses. So, the "(more precisely a clustered index - the two is the same in MySQL)" is not correct. A myisam table can have a PK but it's not clustered. And an innodb table without a PK is still clustered (by a hidden internal 6-byte column). – ypercubeᵀᴹ Nov 26 '14 at 10:13
  • Good point, I've edited the answer. – Pred Nov 26 '14 at 10:16
  • As an addendum, I highly recommend using an online alter table tool like pt-online-schema-change http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html if you want to do live changes to a production database and do not have a proper HA system (a slave to failover). Regarding time, both Percona and MariaDB have easy ways to predict completion time; it can be done manually by checking the Handler status varaibles. – jynus Nov 26 '14 at 12:39
0

it took about 7 mins for me for 10 million records for a table with 4 varchar 20 columns.(Lightsail and over the network)

Karthik A
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Community Oct 15 '23 at 10:29