Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Will partitions improve MySQL INSERT speed?

Tags:

mysql

I'm doing a lot of INSERTs via LOAD DATA INFILE on MySQL 5.0. After many inserts, say a few hundred millions rows (InnoDB, PK + a non-unique index, 64 bit Linux 4GB RAM, RAID 1), the inserts slow down considerably and appear IO bound. Are partitions in MySQL 5.1 likely to improve performance if the data flows into separate partition tables?

like image 958
Joshua Martell Avatar asked Nov 16 '09 03:11

Joshua Martell


1 Answers

The previous answer is erroneous in his assumptions that this will decrease performance. Quite the contrary.

Here's a lengthy, but informative article and the why and how to do partitioning in MySQL:

http://dev.mysql.com/tech-resources/articles/partitioning.html

Partitioning is typically used, as was mentioned, to group like-data together. That way, when you decided to archive off or flat out destroy a partition, your tables do not become fragmented. This, however, does not hurt performance, it can actually increase it. See, it is not just deletions that fragment, updates and inserts can also do that. By partitioning the data, you are instructing the RDBMS the criteria (indeces) by which the data should be manipulated and queried.

like image 56
Wayne Hartman Avatar answered Oct 18 '22 12:10

Wayne Hartman