테이블을 잠그지 않고 ALTER TABLE?

테이블을 잠그지 않고 ALTER TABLE?

---

MySQL에서 ALTER TABLE 문을 수행 할 때 문이 실행되는 동안 전체 테이블이 읽기 잠금 상태가됩니다. 큰 테이블이면 삽입 또는 업데이트 문이 잠깐 동안 잠길 수 있음을 의미합니다. 프로세스 전반에 걸쳐 테이블을 업데이트 할 수있는 방식으로 열을 추가하는 것과 같이 "핫 변경"을 수행하는 방법이 있습니까?

주로 MySQL 솔루션에 관심이 있지만 MySQL이 할 수없는 경우 다른 RDBMS에 관심이 있습니다.

명확히하기 위해, 제 목적은 추가 테이블 열이 필요한 새로운 기능이 프로덕션으로 푸시 될 때 다운 타임을 방지하는 것입니다. 모든 데이터베이스 스키마 는 시간이 지남에 따라 변경 됩니다 . 이러한 변경으로 인해 필연적으로 다운 타임이 발생해야한다는 사실을 왜 받아 들여야하는지 모르겠습니다. 그것은 단지 약합니다.

---

유일한 다른 옵션은 어쨌든 많은 RDBMS 시스템이 수행하는 작업을 수동으로 수행하는 것입니다 ...-
새 테이블 만들기

그런 다음 한 번에 청크에 이전 테이블의 내용을 복사 할 수 있습니다. 항상 소스 테이블의 INSERT / UPDATE / DELETE에주의해야합니다. (트리거에 의해 관리 될 수 있습니다. 이로 인해 속도가 느려지지만 잠금이 아닙니다 ...)

완료되면 소스 테이블의 이름을 변경 한 다음 새 테이블의 이름을 변경합니다. 가급적 거래에서.

완료되면 해당 테이블을 사용하는 저장 프로 시저 등을 다시 컴파일합니다. 실행 계획은 더 이상 유효하지 않을 것입니다.

편집하다:

이 제한이 약간 부족하다는 의견이 있습니다. 그래서 나는 그것이 왜 그런지 보여주기 위해 그것에 새로운 관점을 넣을 것이라고 생각했습니다.

• 새 필드를 추가하는 것은 모든 행에서 하나의 필드를 변경하는 것과 같습니다.
• 필드 잠금은 행 잠금보다 훨씬 어렵고 테이블 잠금은 신경 쓰지 않습니다.
  
  
• 실제로 디스크의 물리적 구조를 변경하고 모든 레코드가 이동합니다.
• 이것은 정말로 Whole 테이블에 대한 UPDATE와 같지만 더 많은 영향을 미칩니다.

---

Percona는 이를 수행 할 수있는 pt-online-schema-change 라는 도구를 만듭니다 .

본질적으로 테이블의 복사본을 만들고 새 테이블을 수정합니다. 새 테이블을 원본과 동기화 상태로 유지하기 위해 트리거를 사용하여 업데이트합니다. 이렇게하면 백그라운드에서 새 테이블을 준비하는 동안 원본 테이블에 액세스 할 수 있습니다.

이는 위에서 제안한 Dems 방법과 유사하지만 자동화 된 방식으로 수행됩니다.

그들의 도구 중 일부는 데이터베이스에 연결하는 학습 곡선이 있지만 일단 다운되면 훌륭한 도구입니다.

전의:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends

---

이 질문은 2009 년입니다. 이제 MySQL은 다음과 같은 솔루션을 제공합니다.

온라인 DDL

DDL (주로 ALTER TABLE) 작업 중에 InnoDB 테이블의 성능, 동시성 및 가용성을 개선하는 기능입니다. 자세한 내용은 14.11 절.“InnoDB 및 온라인 DDL”을 참조하십시오.

세부 사항은 작업 유형에 따라 다릅니다. 경우에 따라 ALTER TABLE이 진행되는 동안 테이블을 동시에 수정할 수 있습니다. 테이블 복사를 수행하지 않거나 특별히 최적화 된 유형의 테이블 복사를 사용하지 않고 작업을 수행 할 수 있습니다. 공간 사용량은 innodb_online_alter_log_max_size 구성 옵션에 의해 제어됩니다.

테이블에 대한 액세스를 완전히 차단 (LOCK = EXCLUSIVE 절), DML이 아닌 쿼리 허용 (LOCK = SHARED 절) 또는 전체 쿼리 및 DML 허용 여부를 선택하여 DDL 작업 중 성능과 동시성 간의 균형을 조정할 수 있습니다. 테이블에 대한 액세스 (LOCK = NONE 절). LOCK 절을 생략하거나 LOCK = DEFAULT를 지정하면 MySQL은 작업 유형에 따라 가능한 한 많은 동시성을 허용합니다.

테이블의 새 복사본을 생성하는 대신 가능한 한 제자리에서 변경을 수행하면 테이블 복사 및 보조 인덱스 재구성과 관련된 디스크 공간 사용량 및 I / O 오버 헤드가 일시적으로 증가하지 않습니다.

자세한 내용은 MySQL 5.6 참조 설명서-> InnoDB 및 온라인 DDL 을 참조하십시오.

MariaDB에서도 온라인 DDL을 사용할 수있는 것 같습니다.

또는 ALTER ONLINE TABLE을 사용하여 ALTER TABLE이 동시 작업을 차단하지 않도록 할 수 있습니다 (잠금을 사용하지 않음). LOCK = NONE과 동일합니다.

ALTER TABLE에 대한 MariaDB KB

---

Facebook의 온라인 스키마 변경 도구를 참조하십시오.

http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932

약한 마음을위한 것이 아닙니다. 그러나 그것은 일을 할 것입니다.

---

옵션이라면 Postgres를 추천합니다. postgres를 사용하면 기본적으로 다음 절차에 다운 타임이 없습니다.

• ALTER TABLE ADD COLUMN (열이 NULL 일 수있는 경우)
• ALTER TABLE DROP COLUMN
• CREATE INDEX (CREATE INDEX를 동시에 사용해야 함)
• 드롭 인덱스

다른 훌륭한 기능은 대부분의 DDL 문이 트랜잭션 적이므로 SQL 트랜잭션 내에서 전체 마이그레이션을 수행 할 수 있으며 문제가 발생하면 전체가 롤백된다는 것입니다.

I wrote this a little bit ago, perhaps it can shed some more insight on the other merits.

---

Since you asked about other databases, here's some information about Oracle.

Adding a NULL column to an Oracle table is a very quick operation as it only updates the data dictionary. This holds an exclusive lock on the table for a very short period of time. It will however, invalidate any depedant stored procedures, views, triggers, etc. These will get recompiled automatically.

From there if necessary you can create index using the ONLINE clause. Again, only very short data dictionary locks. It'll read the whole table looking for things to index, but does not block anyone while doing this.

If you need to add a foreign key, you can do this and get Oracle to trust you that the data is correct. Otherwise it needs to read the whole table and validate all the values which can be slow (create your index first).

If you need to put a default or calculated value into every row of the new column, you'll need to run a massive update or perhaps a little utility program that populates the new data. This can be slow, especially if the rows get alot bigger and no longer fit in their blocks. Locking can be managed during this process. Since the old versino of your application, which is still running, does not know about this column you might need a sneaky trigger or to specify a default.

From there, you can do a switcharoo on your application servers to the new version of the code and it'll keep running. Drop your sneaky trigger.

Alternatively, you can use DBMS_REDEFINITION which is a black box designed to do this sort of thing.

All this is so much bother to test, etc that we just have an early Sunday morning outage whenever we release a major version.

---

If you cannot afford downtime for your database when doing application updates you should consider maintaining a two-node cluster for high availability. With a simple replication setup, you could do almost fully online structural changes like the one you suggest:

• wait for all changes to be replicated on a passive slave
• change the passive slave to be the active master
• do the structural changes to the old master
• replicate changes back from the new master to the old master
• do the master swapping again and the new app deployment simultaneously

It is not always easy but it works, usually with 0 downtime! The second node does not have to be only passive one, it can be used for testing, doing statistics or as a fallback node. If you do not have infrastructure replication can be set up within a single machine (with two instances of MySQL).

---

Nope. If you are using MyISAM tables, to my best understanding they only do table locks - there are no record locks, they just try to keep everything hyperfast through simplicity. (Other MySQL tables operate differently.) In any case, you can copy the table to another table, alter it, and then switch them, updating for differences.

This is such a massive alteration that I doubt any DBMS would support it. It's considered a benefit to be able to do it with data in the table in the first place.

---

Temporary solution...

Other solution could be, add a another table with primary key of the original table, along with your new column.

Populate your primary key onto the new table and populate values for new column in your new table, and modify your query to join this table for select operations and you also need to insert, update separately for this column value.

When you able to get downtime, you can alter the original table, modify your DML queries and drop your new table created earlier

Else, you may go for clustering method, replication, pt-online-schema tool from percona

---

Using the Innodb plugin, ALTER TABLE statements which only add or drop secondary indexes can be done "quickly", i.e. without rebuilding the table.

Generally speaking however, in MySQL, any ALTER TABLE involves rebuilding the entire table which can take a very long time (i.e. if the table has a useful amount of data in it).

You really need to design your application so that ALTER TABLE statements do not need to be done regularly; you certainly don't want any ALTER TABLE done during normal running of the application unless you're prepared to wait or you're altering tiny tables.

---

I would recommend one of two approaches:

1. Design your database tables with the potential changes in mind. For example, I've worked with Content Management Systems, which change data fields in content regularly. Instead of building the physical database structure to match the initial CMS field requirements, it is much better to build in a flexible structure. In this case, using a blob text field (varchar(max) for example) to hold flexible XML data. This makes structural changes very less frequent. Structural changes can be costly, so there is a benefit to cost here as well.

2. Have system maintenance time. Either the system goes offline during changes (monthly, etc), and the changes are scheduled during the least heavily trafficked time of the day (3-5am, for example). The changes are staged prior to production rollout, so you will have a good fixed window estimate of downtime.

2a. Have redundant servers, so that when the system has downtime, the whole site does not go down. This would allow you to "roll" your updates out in a staggered fashion, without taking the whole site down.

Options 2 and 2a may not be feasible; they tend to be only for larger sites/operations. They are valid options, however, and I have personally used all of the options presented here.

---

If anyone is still reading this or happens to come here, this is the big benefit of using a NoSQL database system like mongodb. I had the same issue dealing with altering the table to either add columns for additional features or indexes on a large table with millions of rows and high writes. It would end up locking for a very long time so doing this on the LIVE database would frustrate our users. On small tables you can get away with it.

I hate the fact that we have to "design our tables to avoid altering them". I just don't think that works in today's website world. You can't predict how people will use your software that's why you rapidly change things based on user feedback. With mongodb, you can add "columns" at will with no downtime. You don't really even add them, you just insert data with new columns and it does it automatically.

Worth checking out: www.mongodb.com

---

In general, the answer is going to be "No". You're changing the structure of the table which potentially will require a lot of updates" and I definitely agree with that. If you expect to be doing this often, then I'll offer an alternative to "dummy" columns - use VIEWs instead of tables for SELECTing data. IIRC, changing the definition of a view is relatively lightweight and the indirection through a view is done when the query plan is compiled. The expense is that you would have to add the column to a new table and make the view JOIN in the column.

Of course this only works if you can use foreign keys to perform cascading of deletes and whatnot. The other bonus is that you can create a new table containing a combination of the data and point the view to it without disturbing client usage.

Just a thought.

---

The difference between Postgres and MySQL in this regard is that in Postgres it doesn't re-creates a table, but modifies data dictionary which is similar to Oracle. Therefore, the operation is fast, while it's still requires to allocate an exclusive DDL table lock for very short time as stated above by others.

In MySQL the operation will copy data to a new table while blocking transactions, which has been main pain for MySQL DBAs prior to v. 5.6.

The good news is that since MySQL 5.6 release the restriction has been mostly lifted and you now can enjoy the true power of the MYSQL DB.

---

As SeanDowney has mentioned, pt-online-schema-change is one of the best tools to do what you have described in the question here. I recently did a lot of schema changes on a live DB and it went pretty well. You can read more about it on my blog post here: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/.

---

You should definitely try pt-online-schema-change. I have been using this tool to do migrations on AWS RDS with multiple slaves and it has worked very well for me. I wrote an elaborate blog post on how to do that which might be helpful for you.

Blog: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/

---

Dummy columns are a good idea if you can predict their type (and make them nullable). Check how your storage engine handles nulls.

MyISAM will lock everything if you even mention a table name in passing, on the phone, at the airport. It just does that...

That being said, locks aren't really that big a deal; as long as you are not trying to add a default value for the new column to every row, but let it sit as null, and your storage engine is smart enough not to go writing it, you should be ok with a lock that is only held long enough to update the metadata. If you do try to write a new value, well, you are toast.

---

TokuDB can add/drop columns and add indexes "hot", the table is fully available throughout the process. It is available via www.tokutek.com

---

Not really.

You ARE altering the underlying structure of the table, after all, and that's a bit of information that's quite important to the underlying system. You're also (likely) moving much of the data around on disk.

If you plan on doing this a lot, you're better off simply padding the table with "dummy" columns that are available for future use.

참고URL : https://stackoverflow.com/questions/463677/alter-table-without-locking-the-table