CODEWITHSUNDEEP
mysqldatabase-partitioning
Paul
Paul

Reputation: 3091

Is it ok to mix partitioned and unpartitioned tables on same MySQL server?

I just went through this tutorial and the bullet on slide 39 stood out: "Do NOT mix partitioned and unpartioned tables in the same server" I don't know what the author is referring to. Does this apply only to benchmarking? Is there some requirement that all tables should be partitioned when you partition one? Even if it only applies to benchmarking, I would still like to know why they all must be partitioned to get good benchmark results.

Upvotes: 3

Views: 300

Answers (1)

Johan
Johan

Reputation: 76670

Slide 39 has the following title:

Benchmarking partitions - ISOLATION
- Try to reproduce working conditions
- no other services running while benchmarking
- restart the server before each test
- Do NOT mix partitioned and unpartitioned tables in the same server
- Use MySQL sandbox

Note that this slide only refers to isolation issues when benchmarking!
It does not refer to running your server.
If you have a table A and a table B with identical data and layout, both should be partitioned, or you will get different results.
It does not refer to a scenario where you have a big table that's partitioned that links to smaller tables that are not, because it makes no sense to partition small tables.

Is there some requirement that all tables should be partitioned when you partition one?

Nope

There is absolutly no problem in mixing partitioned and normal tables in a database.
In fact only a few key tables should ever be partitioned, the rest should not be.

@Mike Purcell: partitioning is not meant to protect data, it is meant to speed up access to the data by allowing non relevant data to be more easily excluded from the search.

Consider a logfile which is partitioned by month.
Normally you are only interested in a narrow timeslice of the logfile, therefore the partitioning will allow MySQL to consider a much smaller part of the data when looking at the log of a certain period.

Benefits and drawbacks of partitions
Partition usually speed up things, at the cost of increased use of disk space (which in rare cases can slow things down again).
Also partitions only make sense on tables with lots of rows.

Upvotes: 5

Related Questions