MySQL Forums
Forum List  »  InnoDB

Re: Daily backing up around 1 billion records
Posted by: Shawn Green
Date: February 17, 2018 02:16PM

You mention shards and partitions in almost the same thought.

Shards are separate physical parts of the same logical table managed by separate mysqld instances. They can be backed up individually.

Partitions are separate portions of the same table managed by the storage engine.

The performance impact of partitioning your data depends on your choice of what value you used to define those partitions (the partitioning key). If you have a wide variety of search patterns that do not include a term referencing the partition key then all of your partitions will need to be searched in order to identify which rows match your other search conditions. In that case, having partitions may hurt your overall performance. But if the majority of your "search condition" expressions involve the partition key, then you will enjoy what we call "partition pruning" meaning that only those partitions that match your "key condition" will need to be individually opened and searched to see of any rows in those specific partitions match the other conditions in your "search expression".

I hope that was not too abstract for you to understand!

Regards,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ for details.

Options: ReplyQuote


Subject
Views
Written By
Posted
1046
February 15, 2018 07:01AM
Re: Daily backing up around 1 billion records
577
February 17, 2018 02:16PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.