myisam innodb 변경

MySQL에서의 INSERT, UPDATE, DELETE문 실행이 매우 큰 부하를 가져온다는 사실을 알고 계실 겁니다.
MyISAM, InnoDB를 DB 엔진이라고 하는데 뭐 언제 추가 됬던데요.. InnoDB는 어떤 회사가 개발한거라 그러더군요.
여튼, 간단한 방법으로 바꿀 수 있습니다.
혹여나 문제가 생길 수 있으니 DB를 백업해 두십시오. 백업이 생명임..

ALTER TABLE 테이블명 ENGINE = InnoDB;

라고 쿼리를 입력하면 바로 엔진이 바꾸어집니다.
XpressEngine에서 이런 방식을 쓰지 않기를 권합니다. 오류가 생길 수 있음.. (files/db.config.php 수정도 필요할겁니다.. 안되도 원망하지 마시구요)
출처 http://folderfile.net/xe/1859

 

 

[Mysql]InnoDB와 MyIsam 비교 및 설치세팅방법

Posted on 1월 29, 2013

myisam에서 innodb 로 전환하는 방법에 대해 기술합니다.

1. MyISAM

1) 예전의 MySQL 의 Storage Engines 으로 MyISAM 을 사용했었습니다.

    예를 들자면 블로그라던지, 게시판 처럼 한사람이 글을 쓰면 다른 많은 사람들이 글을 읽는 방식에

    최적의 성능을 발휘를 하지요. 지금도 많이 사용하고 있는 방식입니다.

2) 제공하는 웹서비스다 그닥 크지 않다면 이것을 사용해도 괜찮다고 생각을 합니다.

 

2. InnoDB

1) 트랜잭션-세이프 스토리지 엔진입니다.

2) MyISAM 과 비슷하지만 ORACLE 처럼 많은 기능을 지원을 합니다.

   (* commit, rollback, 장애복구, row-level locking, 외래키 등)

3) 다수의 사용자 동시접속과 퍼포먼스가 증가하여 대용량 데이터를 처리할 때 최대의 퍼포먼스를 내도록 설계되었습니다.    CPU효율은 어느 디스크 기반의 데이터 베이스와 비교해도 손색이 없고    자체적으로 메인 메모리 안에 데이터 캐싱과 인덱싱을 위한 버퍼 풀(pool)을 관리합니다.
4) 테이블과 인덱스를 테이블 스페이스에 저장을 하고 테이블 스페이스는 몇개의 서버파일이나 디스크 파티션으로

   구성되어있습니다. 이것은 MyISAM 과 다른 점인데, MyISAM은 테이블과 인덱스를 각각 분리된 파일로 관리합니다.

   여기서 중요한것이 이제 InnoDB 를 제대로 사용을 하기 위해서는 테이블 스페이스 라는 개념을 파악을 하셔야합니다.    이것에 대해서는 밑에서 따로 언급을 하겠습니다.

5) InnoDB 테이블은 OS의 파일 사이즈 한계가 2GB이더라도 상관없이 어느 크기나 가질 수 있습니다.
6) InnoDB는 높은 퍼포먼스가 필요한 대용량 사이트에 적합합니다.

3. InnoDB 사용하기

1) InnoDB 는 MyISAM 과 공유하는 메모리도 있지만 별도의 Buffer pool을 가지고 있으니까

   InnoDB 전용 DB를 구성한다면 MyISAM 이 사용하는 record_boffer 과 key_buffer 에 너무 많은 메모리를 할당하지 마세요

  2) InnoDB 설정    ㄱ) MySql 을 설치한 폴더 아래에 ibdata 와 iblogs 폴더를 생성합니다.    ㄴ) my.ini 파일 설정을 변경       * innodb_buffer_pool_size

        – 현재 자신의 시스템 메모리의 50~80% 사이로 만듭니다.

           x86 시스템에서는 2G 이상 설정을 할 수 없습니다.

      * innodb_additional_mem_pool_size

        – 데이터 사전정보 나 내부의 데이터 구조 정보를 담는 메모리 입니다.

           보통 2M 정도 잡아주면 아주 많은 테이블을 사용한다면 좀 늘려주시면 됩니다.

           만약 메모리공장이 부족하면 error log 에 warning 메서지를 남기니 그때 늘려주세요

      * innodb_flush_log_at_trx_commit

        – insert, update 등 데이터 삽입과 관계가 있습니다.

           commit 을 하였을때 그 즉시 commit 된 데이터를 log file 에 기록할지 안할지를설정합니다.

           로그파일을 기록할 경우 갑작스러운 경우 데이터 손실을 막을 수 있지만 매번 로그를 기록하므로 속도가 저하됩니다.

           1 일경우 기록을 하는것이고, 0일 경우 기록을 안하는것입니다.

      * innodb_log_file_size

        – 트랜잭션을 기록하는 로그 파일의 크기를 결정하는 옵션입니다.

           inno_buffer_pool_size 옵션은 성능을 위한것이지만 시스템이 다운되었을 경우 데이터가 손실이 되므로

           이것을 방지하기 위해 log file 을 만들어서 commit 될때마다 로그에 기억을 하고 자동복구를 합니다.

           로그파일은 무한정 계속 커지는 것이 아니라 일정한 크기와 갯수를 가지고 순환식으로 처리되므로 크기는

           inno_buffer_pool_size 의 15% 정도로 설정을 합니다.

           만약 메모리가 1기가이면 inno_buffer_pool_size = 512M 이고, innodb_log_file_size = 80M 가 됩니다.

      * innodb_log_buffer_size

        – 로그 파일을 기록하기 위한 버퍼 사이즈입니다.

          트랜잭션이 작거나 거의 없다면 크게 잡는것은 낭비이므로 보통 1M~8M 사이로 설정을 합니다.

      [mysqld]

      innodb_data_home_dir=”C:/MySQL/MySQL Server 5.0/ibdata/”       innodb_log_group_home_dir=”C:/MySQL/MySQL Server 5.0/iblogs”       innodb_data_file_path=ibdata1:10M:autoextend:max:1000M       innodb_additional_mem_pool_size=3469K       innodb_flush_log_at_trx_commit=1       innodb_log_buffer_size=2M       innodb_buffer_pool_size=256M       innodb_log_file_size=40M       innodb_thread_concurrency=8       innodb_log_archive=0

   ㄷ) my.ini 을 수정했으면 mysql 서버를 재시작합니다.
3) InnoDB 테이블 만들기    create table test_inno (

~

)type=innodb;

으로 맨 마지막에 type=innodb; 라고 명시해주시면 됩니다. 4) InnoDB 트랜잭션 사용   ㄱ) 트랜잭션을 사용하기 위해서는 처음에 set autocommit=0; 이나 begin; 을 선언해야 합니다.

      선언 후 데이터 변경이 있을 때, 이상이 없을 경우는 commit를 하고, 이상이 있을 경우 rollback을 실행합니다.       오라클이랑 비슷하다고 보시면 되요

mysql>set autocommit=0; //begin; 같음 mysql>insert into test_inno values (1,’aaa’); mysql>select * from test_inno; //현재창에서는 입력한 내용이 보이지만 다른창에서는 보이지않음 mysql>commit; //다른창에서 select를 할경우 입력한 값이 보임

4. InnoDB 테이블 스페이스

좀전에 MyISAM 과 차이점이 InnoDB 는 테이블과 인덱스를 테이블 스페이스 라는곳에 저장을 한다고 하였습니다.

그런데 사용을 하다보니 점차 DB가 늘어나서 테이블 스페이스가 FULL 이 발생하는 경우가 생깁니다.

4.0.x 버전부터는 일일이 할 필요가 없다는 글이 있는데 그래도 FULL이 발생하였을 때 대처방법을 알면 좋겠죠

my.ini 에서 아래와 같이 추가를 해주면 안됩니다.

innodb_data_file_path = /ibdata/ibdata1:1000M:autoextend 라고 할 경우

innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend 라고 ibdata2를 추가하면 안됩니다.

아래와 같은 과정대로 하세요

1. Use mysqldump to dump all your InnoDB tables.

2. Stop the server.

3. Remove all the existing tablespace files.

4. Configure a new tablespace.

5. Restart the server.

6. Import the dump files.

즉, 일단 mysqldump 로 InnoDB 테이블의 전체 덤프뜬 다음 MySQL 서버를 중지시킵니다.

그다음 테이블스페이스 파일을 모두 지우고 테이블스페이스를 아래와 같이 추가를 합니다.

innodb_data_file_path = /ibdata/ibdata1:1000M;/ibdata/ibdata2:1000M:autoextend

그다음 MySQL 서버를 구동시킨다음 처음에 덤프뜬 파일을 import 하시면 됩니다.

5. 마지막으로

위의 단계처럼 참 어렵게 설정을 하였지만, mysql.com 사이트에 들러보시면 MySQL GUI Tools 라는 프로그램이 있습니다.

물론 무료로 다운로드 가능하고요, 이 프로그램을 설치를 하면 MySQL Administrator 라는 것이 있는데 이것을 통해서

아주 쉽게 MySQL 의 설정을 변경하실 수 있습니다.

———————————————————————————

결론 by 애셔

초간단 이노디비로 변경하기 ㅋ

show table status; — 엔진확인 show engines;

확인하고

ALTER TABLE 테이블명 ENGINE=INNODB ALTER TABLE 테이블명 TYPE=INNODB

————————————————————————————

추가 도움말

설정 관련

윈도우에서의 버퍼 풀 크기 한계를 뛰어넘기 위한 파라미터 역시 제공하는데 이것이 MS에서 최근에 발표한 AWE(Address Windowing Extensions)라는 메모리 확장 기술을이다. 이는 기존 윈도우 시스템의 메모리 한계인 4GB 이상을 관리자가 사용할 수 있도록 해 주는 기술로써 innodb_buffer_pool_awe_mem_mb 라는 파라미터이다. 이 파라미터를 통해 64GB까지의 메모리를 버퍼 풀로 사용할 수 있다. 그리고 버퍼 풀 이외에 메모리가 필요한 작업들을 위해 설정하는 파라미터로서 innodb_additinoal_mem_pool_size라는 파라미터가 있는데 대부분의 경우는 기본 값으로 설정해도 무방하다.

InnoDB 버퍼 풀의 모니터링과 튜닝

앞에서 MyISAM 스토리지 엔진에서 한 것과 같이 InnoDB 버퍼 풀을 위해 MySQL Administrator에 커스텀 그래프들을 생성해 모니터링하고 이를 바탕으로 튜닝을 하자. 버퍼 풀을 모니터링하기 위해 새로운 페이지를 구성한 것이다.

버퍼풀사용량 전체 버퍼 풀 크기인 innodb_buffer_pool_pages_total 중에서 현재 사용중인 버퍼 풀 크기인 innodb_buffer_pool_pages_data로 전체 버퍼 풀 사용량을 알 수 있다. 시작하자마자 차지하는 용량은 InnoDB가 내부적인 용도로 사용중인 것이다.

버퍼풀내의더티페이지사용량 전체 버퍼 풀 크기인 innodb_buffer_pool_pages_total 중에서 현재 사용중인 더티 페이지 크기인 innodb_ _pool_pages_dirty로 전체 버퍼 풀 중 더티 페이지가 차지하는 용량을 알 수 있다.

버퍼풀적중률 버퍼 풀이 용도에 맞게 효율적으로 설정되었는지 판단하는 가장 중요한 그래프이다. 그래프의 계산 공식은 다음과 같다.

계산공식 = 100-(100×(^[innodb_pages_read]/^[innodb_buffer_pool_read_ requests]))

이 계산 공식은 전체 버퍼 풀 읽기 요청 중 실제로 디스크에서 읽지 않고 버퍼 풀에서 읽은 횟수를 계산하는 것이다. 이 수치가 높다는 것은 버퍼 풀이 적절히 구성되어 동작중이라는 것이고 수치가 낮은 것은 버퍼 풀에서 실제로 자주 필요로 하는 정보를 찾을 수 없다는 것이다.

버퍼풀읽기요청 이 그래프는 시시각각 변하는 버퍼 풀 읽기 요청을 모니터링하기 위해 쓰인다. 언제 어떤 작업을 수행하기 위해 갑자기 버퍼 풀 읽기 요청이 증가하는지를 파악하는데 도움을 준다.

버퍼풀의연속적인데이터미리읽기활동측정 InnoDB는 복잡한 알고리즘으로 구현되어 있으며 어떠한 프로그램이 많은 양의 연속적인 데이터 읽기 작업으로 보통 전체 테이블 스캔되는 경우 등이 발생할지를 미리 판단한다. Innodb_buffer_pool_ read_ahead_seq의 상태를 지속적으로 모니터링함으로써 파악할 수 있으며 계속해서 상승하는 것은 InnoDB가 더 많은 테이블 스캔을 하고 있다는 의미이다.

버퍼풀의랜덤한미리읽기활동측정 InnoDB의 미리 읽기 알고리즘(read-ahead algorithm)은 연속적인 읽기뿐만 아니라 비연속적인 읽기가 대량으로 발생하는 것도 미리 예측해 작업을 한다. 이는 innodb_buffer_pool_read_ahead_rnd의 상태를 모니터링함으로써 파악할 수 있다.

버퍼풀에대한쓰기요청 버퍼 풀이 얼마나 자주 변경되는지 파악하려면 계속적으로 변하는 값인 innodb_buffer_pool_write_request의 상태를 추적하면 된다.

플러시된버퍼풀페이지 앞에서 언급했듯이 MySQL은 정기적으로 버퍼 풀에 있는 페이지를 디스크로 싱크하는 작업을 한다. 이는 데이터의 순간적인 손실을 막기 위한 작업이다. Innodb_buffer_pool_pages_flushed의 상태를 추적함으로서 모니터링할 수 있다.

버퍼풀에들어가기위해대기하고있는큐의수 버퍼 풀의 용량이 충분하지 못하면 앞의 플러시 이벤트가 발생할 때까지 기다린다. 이런 상황이 얼마나 발생하는 지 카운트하는 것은 매우 중요하다. 이런 일이 자주 발생한다는 것은 현재 버퍼 풀의 크기가 요구량에 비해 작게 설정되어 있다는 뜻이다. 이는 innodb_ buffer_pool_wait_free를 추적함으로써 모니터링할 수 있다.

[출처] [Mysql]InnoDB와 MyIsam 비교 및 설치세팅방법|작성자 하얀늑대

 

 

 

 

====================

alter table 로 innodb 로 변경을 했습니다만,

기존에 중요 테이블만 부분적으로 해서 사용을 하다가,

이번에 모조리 변경을 했거든요.

이후, 관리자 화면이 정상작동 하지 않네요.

최근글, 댓글 정도 뿌려지는 부분외에,

기존 즐겨찾기나 메뉴등도 보이지 않고,

캐시 재생성 해도 마찬가지 입니다. ㅡㅜ;

관리자 메뉴 초기화 는 누르면 잘못된 요청이라고 나오고 있습니다.

로고 및 하단 버젼정보도 정상 출력이 되지 않고 있네요.

사이트는 모두 정상 동작하고 있는데요.

관리자 페이지만 문제가 있네요.

경험하신분 계실까요? 또는 짚이는 곳이라도? ㅜㅜ

 

/files/config/db.config.php 에서 db 타입을 mysql_innodb로 바꿨는지 확인하독

저는 dump받아 isam을 inno로 바꾸는 작업을 했었습니다만.. 당연히 중지하고 덤프방식이다보니 문제는 없었습니다ㅎㅎ;;

 

http://code.google.com/p/xe-core/issues/detail?id=636

예전에 패치를 제출한건데 1.5.0에서 적용된것으로 보입니다.

버전 업을 해보시고 __XE_ADMIN_ 을 제거하시기 바랍니다.

===========================================================

다른 분들께서 답변을 잘 달아주셨으니 저는 '게시판의 댓글'을 위해 사용하시려는 의도에 좀 더 집중해서 답변을 하겠습니다.

아시다시피 MyISAM의 경우 자료를 입력/수정/삭제할 경우 테이블 전체에 락이 걸립니다.

InnoDB는 위의 분들께서도 말씀하신 것처럼 트랜잭션을 지원하며 입력/수정/삭제에 대해 건건에 락이 걸립니다.

따라서 게시판의 동시 접속량이 얼마 정도인지 사이트에 따라 많은 차이가 있습니다만 입력/수정/삭제가 아주 빈번하게 일어나면서 동시에 조회가 많이 일어나는 자료에 대해서는 InnoDB를 사용하시는 것을 권장합니다.

그리고 마지막에 말씀하신 것은 다른 타입의 테이블들, 특히 MyISAM과 InnoDB테이블은 가급적 같이 사용하시지 않으시는 것이 좋습니다.

키 캐쉬 혹은 InnoDB Pool의 사용에 대한 기본적은 것이 틀린 것은 차지하고라도

만약 MyISAM 테이블 A와 InnoDB 테이블 B를 조인해서 사용하시는 경우,

MyISAM 테이블에 테이블 락이 걸리면 해당 SQL의 성능이 아주 안 좋아질 수 있습니다.

도움이 되었길 바라면서, 즐거운 일요일 맞이하세요~ ^0^/"

 

 

 

==============================================

http://wanderer.pe.kr/tc/106

왜 MySQL 에서 InnoDB 를 써야하는가? InnoDB vs MyISAM 비교

출처 : http://dev.kthcorp.com/2011/06/17/why-we-need-to-use-innodb-on-mysql-vs-myisam-comparison/

Overview

MySQL에서는 MyISAM, InnoDB, Archive 등과 같은 훌륭한 엔진을 제공합니다. 목적에 맞게 적절(?)하게 사용을 한다면, 기타 고 비용 상용 DBMS 부럽지 않게 DB를 구성할 수 있습니다. 물론 적절이라는 말이 세상에서 가장 난해한 단어겠지만요.^^

사실 OLTP 성 서비스에는 대부분 innodb위주로 설정을 해왔기에, 특별하게 MyISAM 영역에 대해서는 신경을 쓰지 않았습니다. 단순하게 해당 스토리지 엔진의 특성만 기억하고 있었지, OLTP 성격의 서비스에 굳이 투입하고 싶지도 않았고요. 하지만 얼마전 공간 인덱스(R-Tree)에 관한 요구사항이 발생하여 어쩔 수 없이 MyISAM 위주로 구성을 한 Case가 있습니다. 그래서 MyISAM 스토리지 엔진에 관한 간단한 BMT를 진행하였고, 결과에 관해서 공유드리고자 합니다.

Feature

MyISAM과 InnoDB를 간단하게 비교한 표입니다. Geospatial 기능과 Full-text Search 기능을 제외하고 눈여겨 보셔야 할 부분은 트랜잭션, Locking Level, Data caches 등 세가지 입니다. 이 세가지 특성으로 인하여 특수한 경우(spatial, full-text search)등을 제외한 경우 InnoDB를 선택해야만 하는 이유가 나옵니다.

아직 InnoDB에서는 Compressed 기능 적용 시 테이블 사이즈 변화는 검토해볼 사항이겠군요. Barracuda format을 적용하여 Compressed 적용 시 성능 및 데이터 사이즈 변화에 관한 내용은 조만간 비교해서 공유드리겠습니다.^^

MyISAM Benchmark

< 시스템>

• CPU
  4-core : Intel(R) Xeon(R) CPU E5405 @ 2.00GHz
  8-core : Intel(R) Xeon(R) CPU L5520 @ 2.27GHz
• Memory : 8G
• DISK :
  Raid-1 for OS
  Raid-1 for DATA (I/O성능은 좋지 못하다고 보면 됨)
• MySQL verstion : mysql Ver 14.14 Distrib 5.1.57

<Benchmark>

• Sysbench-0.4.12 tool사용
• MyISAM 1000만 건 데이터 생성 후 Read-only/Complex Test 수행
• 4-core/8-core/key_cache_block_size 변경 후 테스트

<Query Cache on/off READ TEST result>
사용 유무에 따라서 2배 성능 차이가 발생합니다.
두 경우 모두 5개 Thread이상에서는 더이상 성능 향상이 발생하지 않습니다.

<Key Block Size 변경 READ TEST result>
key_cache_block_size를 OS와 동일하게 구성한 결과 15% 성능 향상되었습니다.
두 경우 모두 5개 Thread이상에서는 더이상 성능 향상이 발생하지 않습니다.

<CPU core 개수 변화에 따른 READ TEST result>
8-core가 2배 정도 성능이 좋게 나오나, 장비가 다르므로 큰 의미는 없습니다.
다만 4-core는 5-Thread에서, 8-core는 10-thread 이상에서는 성능에 변동이 없는 것을 확인할 수 있네요.

<Key Block Size 변경에 따른 READ/WRITE TEST result>
key_cache_block_size를 OS와 동일하게 구성한 결과 15% 성능 향상되었습니다.
5개 Thread이상에서는 더이상 성능 향상이 없습니다.

<CPU core 개수 변화에 따른 READ/WRITE TEST result>
8-core가 성능이 훨씬 좋은 것으로 나오나, 장비가 다르므로 큰 의미는 없습니다.
4-core, 8-core 모두 5-thread 이상에서 성능상 큰 변동은 없군요.

다음은 MySQL에서 최근 발표한 MyISAM vs InnoDB 성능 테스트 BMT결과입니다. MySQL 5.5 Version부터 innodb를 default-storage-engine으로 적용하면서 비교 분석한 자료네요. 이 자료를 보면 왜 OLTP환경에서는 InnoDB 를 채택하는 것이 좋은지를 아주 명확하게 알려줍니다. Core가 많을 수록 InnoDB성능은 비례하게 올라가는 반면, MyISAM은 그대로 유지합니다.

Conclusion

일정 동시 요청 건 수(5~10 Thread) 이상으로는 더이상 성능 향상이 없는 것을 확인할 수 있습니다. 이는 MyISAM이 Table-level Lock으로 데이터 DML을 처리하고, Data를 직접 DISK에서 읽어오는 것에서 발생하는 한계점이라고 판단이 듭니다.

따로 내용을 정리하지는 않았었지만, Key Cache 사이즈 변화에 따른 성능 변화도 테스트해 보았습니다. 그 결과 인덱스 총 사이즈보다 Key Cache 사이즈가 크게 설정되어 있으면 성능상 변화는 거의 없었습니다. 인덱스 또한 MyISAM은 인덱스 압축 기법을 통하여 사이즈를 최소화하기 때문에 굳이 Key Cache사이즈를 4G 이상으로 할당하는 것은 큰 의미가 없겠습니다.

결국 MyISAM을 사용 시 고비용의 고스펙 장비는 필요없다는 결과가 나왔습니다. CPU/Memory 부분보다는 오히려 DISK 성능을 향상시키는 것이 성능상 큰 효과를 거둘 수 있겠습니다. (Random I/O가 좋은 SSD라면 상당한 효과를 거두겠습니다.) 메모리는 OS Cache를 고려하여 6G~8G, CPU는 최대 8-core까지만 사용하는 것이 낫다고 생각합니다. 그리고 key cache block size는 OS 사이즈를 확인 후 동일하게 유지하는 것을 권고드립니다.