When would one NOT want to partition a database? (thinking MySQL partitioning)

In my case

  • I'll be starting with a couple millions rows, it should grow from there.
  • Primary key on a character field that serves as the most frequent query restraint (and lookups are frequent - at least a few per second).
  • Primary key would be hashed to serve as the partition key
  • Updates will be made to every row that is pulled in the frequent queries mentioned above
  • Less frequent lookups (against date columns or other) will need to hit all partitions

Even for the last point, doesn't the lookup run in parallel so in all cases, is this a win? What are the downsides to partitioning? Why isn't it something that EVERYONE uses by default, at least when you are looking at a million+ records?

UPDATE - I selected zgguy's answer but note that I added my own answer with the results of my own research including a link to a really good answer on a similar question that was highly useful to me.

up vote 5 down vote accepted

There is no silver bullet for performance problems, and partitioning is not one either.

Every partition is essentially a table for itself. Hence queries that are written in a way that allows the database to look for rows in only one partition become faster. Difference can be huge for queries that would need to scan the entire large table, but can restrict themselves to scanning only one partition in the partitioned table. For unique key lookups, difference is much smaller.

However, queries that use index lookups in a way that requires the database to visit all or most of table(index) partitions will run considerably slower.

Parallel execution is a topic for itself. If you run large overnight batches, and have the entire machine to do that single job, then its parallelization is a good thing. However in an OLTP system where the database constantly serves queries from many concurrent users, you don't want one user to take up all the resources.

  • So unique/primary key lookups won't actually see much (if any?) improvement because PK index is faster? Is this across the board - are there times when a PK index is slower? What if lookups are skewed to more recently added PKs? Would a partition based on the PK (I think partition key algo would need to be modulus or similar and NOT hash, right?) that causes most activity to hit only one partition be helpful? – chell Jul 18 '15 at 9:53
  • Primary/unique key lookups will at best see a minor performance improvement. On the other hand, if your goal is to reduce contention of DML statements, you should partition in a way so that DML is spread out equally across all partitions instead of being focused on few of them. – zgguy Jul 18 '15 at 10:25
  • sorry to come back 10 days later, but you raise a key point -- You provided good reason to see partitioning as possibly not necessary, however, my scenario includes updating every record after it is read (several per second). Does the need for so many writes make a more convincing case for partitions (with even distribution) so the write load is spread out? – chell Jul 29 '15 at 2:12
  • I'm also trying to understand your comment about queries that hit many partitions (which are slower). If queries are against the PK which is also used (hashed) as the partition key, doesn't the DB immediately know which partition to go to based on the hash of the lookup? Thanks for help! – chell Jul 29 '15 at 2:29
  • Sorry, wasn't able to visit stack exchange lately. The answer you linked to is great. I believe it answers both of your questions. – zgguy Aug 2 '15 at 19:46

The answer over here is well written and makes arguments similar to zgguy's answer, that partitioning doesn't buy you much, if any, benefit to a single-machine scenario where the most frequent lookups are predicated on the primary key or something similar (because indexed lookups should be just as fast).

In fact, a common thread of advice seems to be that the main reason to partition is tangential and mostly management-related: e.g., segregate your data based on date if you need to purge old records every so often. Although it was noted that this can also benefit your lookup performance if your data is such that most all queries will only hit recently added records.

I also saw mention that MySQL never does anything in parallel (would be nice to see some links or more explanation about that).

Haven't seen anyone speak to whether or not write activity adds different considerations.

  • I don't think writes change your Answer. You mentioned 2 of the 4 use cases that I have found. Still no parallelism, even in 8.0. – Rick James Dec 16 '16 at 17:28

Very first thing comes to mind is partition pruning; if that's not something your queries can use.

Are your going to need the purging large amount of data from the table as partitioning would help you out. Though old but this post from Peter has few points to consider.

and another thing one can think of is ease of use for simple tables... partitioning needs additional work and maintenance.

  • Newer versions have a syntax for explicitly limiting the query to a partition. I can't think of a valid reason for ever using such. – Rick James Dec 16 '16 at 17:30

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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