Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is something I was wondering since I'm planning to partition a aggregation table with 10 mil records where rebuilding indexes cause table locks for some time.

Because partitions are bluntly put just multiple tables that have their own index and our system only inserts new records into a single partition that is kept small (Only data from the current day is added).

So when rebuilding or adding indexes will the table lock occur on all partitions or only the partitions it is rebuilding at that moment?

Also because you have to add your partition key to the primary key are there other troubles that I might run into when partitioning a existing table?

share|improve this question

migrated from stackoverflow.com Jul 24 '13 at 4:21

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 1 down vote accepted

Partitioning usually rather decreases ALTER TABLE speed. Index and data is stored in separate files for each partition (a separate .ibd file for INNODB if innodb_file_per_table=1), so that you might make alter in parallel. But partitioning add an extra processing step to choose the partition for storing data/index, thus the whole operation is made slower.

mysql> create table alter_test (id int primary key auto_increment, v   char(20));
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table alter_test (id int primary key auto_increment, v  char(20)) partition by key(id) partitions 10;
mysql> load data infile '/tmp/1' into table alter_test;
Query OK, 500000 rows affected (9.98 sec)

mysql> alter table alter_test add index i_test(v);
Query OK, 0 rows affected (4.83 sec)

As for the shared lock for ALTER TABLE, all partitions are locked in the same time, so partitioning does not help.

Hints:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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