0

We have one large table with 100 million records and its size is 100 GB and data is continuously growing day by day. This table has 30 partitions with RANGE by client_id(table's column). One client_id is having minimum 1 million records.

For solution we have break this large table into 30 small tables with group of client_id data in each table. Now we want to do partition in each table.

  • So which partition would work for us best: Range or Hash?

We don't know which table has which set of client_id.

Thanks

Improve this question

1 Answer 1

Reset to default
1

Short answer: Neither.

Long answer:

Partitioning is not a panacea. It does not automatically provide performance. In fact, it sometimes slows down processing.

I have never see a use case where BY HASH helped. I have seen a few use cases for BY RANGE. Off hand, it does not sound like your use case will benefit.

Please provide SHOW CREATE TABLE and a few of the critical SELECTs. With those, I will either

  • Explain why partitioning is useless. Or...
  • Explain how to benefit from partitioning for those queries.

In either case, I will probably suggest index (including PK) changes. Proper indexes is where performance usually comes from.

In another direction... Is this some form of "multi-tenant" app? Will it eventually out-grow the server? If so, Sharding may be in your future. This is when you scatter "clients" (or whatever) across multiple servers. But, alas, MySQL does not have any built-in support of such. There are 3rd party packages (eg, Spider) that do.

Improve this answer
1
  • Adding to @Rick's answer - if some clients have a lot more data than others, AND those clients get queried frequently, then partitioning on client_id could result in a few partitions being very busy while others are relatively idle. This would result in a very uneven distribution of the workload, quite possibly with no improvement or even worse performance than before. Commented Sep 24, 2018 at 19:30

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.