更改分区功能以包含 1.5TB 的数据以进行快速切换
我继承了一个未维护的数据库，其中分区函数设置在日期字段上，并在今年的第一天到期。这些数据大部分是历史数据，我可以控制将新数据导入到该表中的作业。
我的问题与设置或更改分区以包含如此多的数据（大约 1.5TB 计数索引）有关。这是在一个实时系统上，我不知道这么多用户同时连接到它会产生什么样的影响。我将在非产品系统上对此进行测试，但随后我无法在那里获得实际的使用负载。我的替代解决方案是杀死所有访问数据库的用户并快速重命名表，并重命名具有正确分区方案的表。
我想：
- 保留相同的分区函数，但将其扩展为： 将 2011 年的所有数据保留在 1 个分区上的某个日期（假设是 2011 年 11 月 22 日），之后进入的所有数据都需要放入自己的新分区中
- 快速切换具有完整年份值的特定分区 任何人都知道更改实时系统上的
分区以包含全年数据（大约 5-60 亿条记录和 1.5tb）的新分区是否可行？有什么陷阱吗？一旦完成，我将分享我的测试结果，但需要任何意见。谢谢！
I inhereted a unmaintained database in which the partition function was set on a date field and expired on the first of the year. The data is largely historic and I can control the jobs that import new data into this table.
My question is relating to setting up or altering partitioning to include so much data, roughly 1.5TB counting indexes. This is on a live system and I don't know what kind of impact it will have with so many users connecting to it at once. I will test this on a non prod system but then I can't get real usage load on there. My alternative solution was to kill all the users hitting the DB and quickly doing a rename of the table, and renaming a table that does have a proper partitioning scheme in.
I wanted to:
-Keep the same partition function but extend it to:
keep all 2011 data up to a certain date (let's say Nov 22nd 2011) on 1 partition, all data coming in after that need to be put in their own new partitions
-Do a quick switch of the specific partition which has the full years worth of data
Anyone know if altering a partition on a live system to include a new partition for a full years worth of data, roughly 5-6 billion records and 1.5tb, is plausible? Any pitfalls? I will share my test results once I complete them but want any input. Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
分区切换是仅元数据操作，切换入或出的分区大小并不重要，可以是 1Kb 或 1TB，所需的时间完全相同（即非常快）。
然而，您所描述的不是分区切换操作，而是分区拆分：您希望将表的最后一个分区拆分为两个分区，一个包含所有现有数据，另一个为空。分割分区必须分割数据，不幸的是这是一个离线的数据大小操作。
Partitions switch are a metadata only operation and the size of the partition switched in or out does not matter, it can be 1Kb or 1TB, it takes the exactly same amount of time (ie. very fast).
However what you're describing is not a partition switch operation, but a partition split: you want to split the last partition of the table into two partitions, one containing all the existing data and a new one empty. Splitting a partition has to split the data, and unfortunately this is an offline size-of-data operation.