11

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.

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
5
  • 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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 19:46
3

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.

Improve this answer
1
  • 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, 2016 at 17:28
1

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.

Improve this answer
1
  • 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, 2016 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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