use table partition to increase inserting performance

Posted on In

when the number of data excess 5 million. mysql database will face a significant drop down in inserting speed.

(From 50,000 data per second to 50,000 data per minutes xD)

So we can "split" these data into different part by using mysql partition.

for example:

table
total number of data
total

change table to partition (the table can not have foreign key)

alter table songrecogn.fingerprints partition by range columns(id)
( PARTITION p0 VALUES LESS THAN (5000000),
  PARTITION p1 VALUES LESS THAN (10000000) ,
  PARTITION p2 VALUES LESS THAN (15000000) ,
  PARTITION p3 VALUES LESS THAN (20000000) ,
  PARTITION p4 VALUES LESS THAN (25000000) ,
  PARTITION p5 VALUES LESS THAN (30000000) ,
  PARTITION p6 VALUES LESS THAN (35000000) ,
  PARTITION p7 VALUES LESS THAN (40000000) ,
  PARTITION p8 VALUES LESS THAN (45000000) ,
  PARTITION p9 VALUES LESS THAN (50000000) ,
  PARTITION p10 VALUES LESS THAN (55000000) ,
  PARTITION p11 VALUES LESS THAN (60000000) ,
  PARTITION p12 VALUES LESS THAN (65000000) ,
  PARTITION p13 VALUES LESS THAN (70000000) ,
  PARTITION p14 VALUES LESS THAN (75000000),
  PARTITION p15 VALUES LESS THAN (80000000),
  PARTITION p16 VALUES LESS THAN (85000000),
  PARTITION p17 VALUES LESS THAN (90000000),
  PARTITION p18 VALUES LESS THAN (95000000),
  PARTITION p19 VALUES LESS THAN (100000000)
);

result:
files

The inserting speed remains in 50,000 per seconds.

reuslt1

extra info:

# add partition
ALTER TABLE fingerprints ADD PARTITION(
 PARTITION p2- VALUES LESS THAN (150000000)
);

# remove partition (data not lost)
alter table fingerprints remove partitioning

# remove partition (data lost)
alter table fingerprints drop partition p20