I’m going to demonstrate cases where partitioning can slow and improve performance. I’ll extend this article:
I’ll cover a few more scenarios that aren’t covered by the article, such as a standard primary key of only orderId with InnoDB and I’ll add a few MyISAM test cases.
I’ll tackle some of the partitioning myths, such as partitioning is a good way to distribute data over multiple disks. Instead I’ll state it is better to use raid to consistently distribute the data over all disks. This avoids data hots spots where one partition is being used at a time and the others are not active.
After having shown some of the performance hits of partitioning, I’m going to show where partitioning does improve performance. Two examples of this are altering tables and only having to optimize one partition of a table, rather than the entire table. Then, for the table optimization case, I’ll come up with a rule to optimize, er, table optimization, which is to keep the partitions the size of the machine memory. I’ll show a case of how following this rule improves optimization performance.
A database guy
For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at firstname.lastname@example.org
Download the MySQL Sponsor/Exhibitor Prospectus
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the MySQL Conference newsletter.
View a complete list of MySQL contacts.