Aurora数据库中的Alter命令(添加新列) [英] Alter command in the Aurora DB (add a new Column)

问题描述

要求添加一个新列，该列的默认值为varchar(1)，其中表具有超过7千5百万条记录.在性能环境中进行了测试，运行了40分钟，但没有实时流量.无论如何，我们可以加快这一步吗?可以禁用表上的索引可能有助于加快alter命令吗?

There is a requirement to add a new column with a default value varchar(1) where the table has 75 million+ records. Performed the tests in perf environment, it ran fine with 40 mins, but there is no live traffic. Is there anyway we can expedite this? Can disabling the indexes on the table may help expedite the alter command?

推荐答案

禁用索引可能会有所帮助，但是当您再次启用索引时，必须重新构建它们，这也将花费很长时间.我认为您最后不会节省任何时间.

Disabling the indexes might help, but when you enable the indexes again, they'll have to be rebuilt, which will also take a long time. I don't think you'll save any time in the end.

但是我建议学习使用pt-online-schema-change .可以说，这是一个在后台运行模式更改的工具，而您的应用程序可以继续读取和写入原始表.

But I would recommend learning to use pt-online-schema-change. This is a tool that runs schema changes in the background, so to speak, while your application can continue to read and write the original table.

运行更改可能需要更长的时间.但是您不介意，因为没有停机时间.假设它需要两个小时而不是40分钟.没关系，因为应用程序不必等待架构更改完成.

It will probably take longer to run the alter. But you don't mind, because there is no downtime. Say it takes two hours instead of 40 minutes. It's okay because the application doesn't have to wait for the schema change to finish.

在我的工作中，我们每周在生产中运行数百个架构更改，而无需停机.模式更改工具确实需要在工作的开始和结束时短暂地锁定表.

At my job, we run hundreds of schema changes per week in production, without downtime. The schema change tool does need to lock the table briefly at the start and at the end of its work.

这篇关于Aurora数据库中的Alter命令(添加新列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！