mysql - innodb 용량 늘리기

my.cnf에 innodb설정에서 autoextend를 사용해 본 경험이 있는가. 이 옵션을 사용할 경우, 관리에 편리함을 제공받지만, disk가 full이 될 경우, 서버가 죽거나 error를 발생시키는 일이 발생하곤 한다. 이로인해 새로운 디스크를 추가하는 작업/이전하는 작업등 최악의 불편함을 야기하게 된다.
autoextend 옵션이 셋팅된 서버에, 수동으로 extend할 수 있도록 셋팅하는 방법을 알아본다.

more..

초기에 InnoDB의 데이터 파일을 하나로 설정하고 자동증가로 두고 쓰다가 데이터 파일을 추가할 때 사이즈 계산하는 방법이다.

http://www.techiegroups.com/showthread.php?t=8348

since you did not have any my.cnf, ibdata1 is an auto-extending data file
(initially 10 MB) in the datadir of MySQL. And there are two 5 MB
ib_logfiles in the datadir.

"
If you specify the last data file with the autoextend option, InnoDB will
extend the last data file if it runs out of free space in the tablespace.
The increment is 8 MB at a time. An example:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

instructs InnoDB to create just a single data file whose initial size is 100
MB and which is extended in 8 MB blocks when space runs out. If the disk
becomes full you may want to add another data file to another disk, for
example. Then you have to look the size of ibdata1, round the size downward
to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the
rounded size of ibdata1 explicitly in innodb_data_file_path. After that you
can add another data file:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
"

81024 pages = 1266 MB

16384 pages = 256 MB

In your case the line

innodb_data_file_path = ibdata1:1266M;ibdata2:10M:autoextend

might work. But best to make a backup of the MySQL datadir first, just in
case something goes wrong.

Regards,

Heikki

처음 my.cnf를 셋팅할 때 100M:autoextend를 설정해두고 얼마나 늘어난지 정확히 알 수 없다면,
위와 같은 방법으로 계산하여, 수동으로 설정할 수가 있다. 따라서, diskfull에 의한 피해를 입지 않도록 할 수 있다.

100M:autoextend 로 설정했다고 해도, 총 데이터 사이즈가 100M를 넘지 않았을 경우에는 그냥 autoextend구문을 빼고, 새로운 데이터파일을 추가할 수 있다.
예) ibdata1:100M:autoextend --> ibdata1:100M; ibdata2:100M

데이터 파일 추가전에 반드시 백업을 통해 데이터유실에 대한 피해를 예방하여야 하며, 사이즈를 잘못 계산하여 에러가 발생했을때에는 err로그를 보자. 그곳에 정확한 page 사이즈가 찍힌다.

more..

위 와 같은 경험을 해본 사람이라면, mysql 사용시 autoextend를 잘 사용하지는 않을 것이다. 하지만, 아직 일부의 개발자들은 autoextend를 편리함으로 인해 사용하고 있다. 이를 지원해주기 위해 위 내용을 알아두고 활용하면 좋으리라. ^0^        

출처 - http://niflheim.tistory.com/23

---

초기에 InnoDB의 데이터 파일을 하나로 설정하고 자동증가로 두고 쓰다가 데이터 파일을 추가할 때 사이즈 계산하는 방법이다.

http://www.techiegroups.com/showthread.php?t=8348

since you did not have any my.cnf, ibdata1 is an auto-extending data file
(initially 10 MB) in the datadir of MySQL. And there are two 5 MB
ib_logfiles in the datadir.

"
If you specify the last data file with the autoextend option, InnoDB will
extend the last data file if it runs out of free space in the tablespace.
The increment is 8 MB at a time. An example:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:100M:autoextend

instructs InnoDB to create just a single data file whose initial size is 100
MB and which is extended in 8 MB blocks when space runs out. If the disk
becomes full you may want to add another data file to another disk, for
example. Then you have to look the size of ibdata1, round the size downward
to the closest multiple of 1024 * 1024 bytes (= 1 MB), and specify the
rounded size of ibdata1 explicitly in innodb_data_file_path. After that you
can add another data file:

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
"

81024 pages = 1266 MB

16384 pages = 256 MB

In your case the line

innodb_data_file_path = ibdata1:1266M;ibdata2:10M:autoextend

might work. But best to make a backup of the MySQL datadir first, just in
case something goes wrong.

Regards,

Heikki

최초 100MB로 설정된 데이터 파일이 130메가로 자동증가된 경우에는 위와 같이 자동증가 파일의 사이즈를 정해주고 새로운 데이터 파일을 추가하면 된다.

주의할 점은 autoextend 옵션은 마지막에만 쓸 수 있다. 그래서 첫번째 파일의 사이즈를 정해주는 과정이 필요한 것이다.

데이터 파일의 추가 전 반드시 백업을 하여 만일의 사태에 대비하여야 하며, 데이터 파일 추가시에 사이즈 문제로 오류가 발생한다면 에러로그를 보라. 로그안에 페이지 사이즈가 나온다. 그 페이지 사이즈로 위와 같이 용량을 계산하면 된다.

위 경우는 autoextend 된 데이터 파일이 있는 경우에 해당한다. 그러나 원래 설정한 용량이 초과하지 않은 데이터 파일만 있는 경우는 간단히 새로운 데이터 파일과 용량만 설정하고 서버를 다시 시작하면 데이터 파일 추가가 적용된다.

출처 - http://mixellaneous.tistory.com/75

---

ibdata 파일의 용량이 어떻게 증가하나요? 글쓴이 : 정우철   날짜 : 07-08-17 14:13   조회수 : 5790 mysql 의 테이블 엔진 타입이 innodb 입니다. 하루하루 늘어가는 ibdata 파일 때문에 스트레스로 인해서 탈모 현상이 생겨 나고 있습니다.   지금현재 용량이 50G 가 되었습니다. 물론 그동안 삭제한 데이터량도 무지 많은데 용량은 줄지 않고 계속 늘어만 가네요..  제가 생각하기에는 삭제된 데이터량도 그대로 다 남아있어서 그렇지 않을 까 싶거든요?   사용하지 않은 데이터량은 좀 삭제해버리고 싶은데... 방법이....     현재 ibdata 파일 하나로 되어 있습니다. 분할 후 저장하려고 해도 에러만 나고요.. 조금 있으면 디스크량도 풀이 나버릴 상황인데 고민이네요.. 고수께 좀 방법을..

강정민 MySQL의 InnoDB 타입은 테이블 스페이스 공간을 활용합니다.  ibdata(테이블 스페이스 공간)를 하나의 파일로 설정을 하셔도 되구요.  하지만 퍼포머스의 효율을 생각한다면 ibdata1, ibdata2, ibdata3, .... 으로 2기가 단위면 2기 단위로 나눠 주는 것이  좋겠죠 또한 데이타가 1M 들어가도 2기가고..... 1기가 999메가 들어가도.. 테이블 스페이스 공간은 2기가입니다.  물론 그 2기가가 넘어 선다면 테이블 스페이스 공간이 새로 또 잡히겠죠....  암턴... InnoDB에서 데이타가 삭제가 되지 않아서 용량이 늘어가는 건 아닌듯 싶습니다.  또한 님이 한개의 테이블 스페이스를 여러개로 나누고 싶다고 하셨는데 그건 의외로 간단합니다.  우선 백업을 받으신다음에  my.cnf, my.ini 환경 설정 파일에서 아래 부분을 고쳐 주세요   innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M:autoextend  그후에 덤프뜬 화일을 풀어주시면 테이블 스페이스 공간이 1개인 것에서 3개인 것으로 바뀌어 집니다...

신은미 강정민님이 말씀하신 부분은 용량문제를 해소할 수 없습니다.  삭제된 데이터가 있다 하더라도 실제 파일의 용량은 줄어들지 않습니다.  optimize table 명령을 사용하시거나 rebuild 하시는 방법이 좋을듯 합니다.

강정민 네 신은미님께서 좋은 말씀을 해주셨네요.  optimize table 코맨드가 조각모음을 해주는 기능을 하거든요 ^^

출처 - http://www.mysqlkorea.co.kr/gnuboard4/bbs/board.php?bo_table=community_03&wr_id=1424