innodb 처음 사용시 고려

--------------------------------------------------------------------------------

innodb 처음 사용시 고려
(1) Linux 에서는 write-back cache 를 disable 시킬것.
(2) innodb_autoextend_increment 수치를 조정하여 tablespace 가 full 일경우
    default 로 8MB씩 증가하는것을 변경할수 있다.
(3) autoextend attribute and those following can be used only for the last
    datafile in the innodb_data_file_path line.
(4) set key_buffer to 5 ~ 10% of your RAM on how much you use myisam tables,
    but keep key_buffer_size + Innodb buffer pool size < 80 % of your RAM.
(5) set buffer pool size to 50 ~ 80% of RAM.
(6) Per-Table Tablespace 사용시 해당 테이블에 대한 복구나 이동이 쉽다.
    [mysqld]
    innodb_file_per_table
   : table 생성시에만 관여. 기존 테이블의 Access 에는 무관.
(7) skip-innodb-checksums : pull and plug 테스트 해보고 괜찮으면 써도 됨.
                            부하 별로 없으면 쓰지 말자.. 혹시나.

(8) innodb_commit_concurrency : 동시에 commit 을 날릴수 있는 Thread 의 수.
(9) innodb_concurrency_tickets 
(10) innodb_fast_shutdown : (0, 1, 2)
     0: Oracle의 Normal shutdown 과 같음.
     1: Oracle의 shutdown immediate 과 같음.
     2: Oracle의 shutdown abort 와 같음.
(11) innodb_doublewrite : write double write buffer first, and then actual 
                          data file.(vs. --skip_innodb_doublewrite)
(12) innodb_file_io_threads : On Unix, increasing the number has no effect.
                              Innodb always uses the default value.
                              (즉, 변경해도 소용없다.)
(13) innodb_flush_log_at_trx_commit : 일단 0로 해서 사용하자.
     0 : log buffer 에 있는것을 일초마다 log file 로 flush 한다.
     1 : 트랜잭션마다 log file 로 flush 한다.
     2 : 트랜잭션마다 log file 로 내리지만 disk flush 는 발생하지 않는다.
(14) innodb_flush_method : fdatasync 가 기본(변경할 필요가 있을까?)
     O_DSYNC, O_DIRECT 등의 옵션이 있다.
(15) 안전한 replication 환경을 위해서는
     innodb_flush_log_at_trx_commit = 1, sync_binlog=1 로 세팅하시오.
(16) innodb_locks_unsafe_for_binlog : default 0
     next key locking 을 안쓰려면 enable(1) 을 하면 되는데 당연히 disable 추천.
(17) innodb_log_arch_dir, innodb_log_archieve : archieve log 할시에만 사용.
     히스토리컬 이유에서만 사용. 복구는 자체의 log 파일로 복구 하기 땜시 
     별필요 없긴 한데 그래도 한번 고민 해볼 필요 있슴.
(18) innodb_log_buffer_size : 1-8 MB 가 적당. io 에 의한 부하가 있을 경우 이값을
     적당히 늘여주면서 모니터링(20MB 는 넘지 말자.)
(19) innodb_log_file_size : 로그 그룹내의 각 로그파일의 크기.
     기본값은 5MB이며 1MB 에서 1/Nth of buffer Pool 로 값을 조정. 
     N 은 그룹내의 로그파일의 수. 당연히 크면 좋지만 너무 크면 복구 할때
     오래걸린다. log_file_size 의 합이 4기가를 넘을수는 없다.
(20) innodb_log_files_in_group : 로그그룹내의 로그파일의 수. 권장값과 기본값은
     2 이지만 우린 좀 키워 볼까나?(InnoDB writes to the files in a circular fashion).
(21) innodb_log_group_home_dir : innodb_log_arch_dir 과 같아야 하며 InnoDB 로그 변수와 관련되어 아무것도 지정하지 않을 경우 5MB 의 ib_logfile0, ib_logfile1 두개의 파일이 data 디렉토리에 생성된다.
(22) innodb_max_dirty_pages_pct : 0-100 사이의 값. 기본값 90.
     buffer pool 에 있는 dirty page(not yet written)의 비율을 넘지 않도록 innodb 는 buffer pool 에서 pages 를 쓰려고 한다.
(23) innodb_mirrored_log_groups : 로그 그룹의 동일한 복제본의 수. 현재는 이 값은     1 로 세팅되어져야 한다.
(24) innodb_open_files : innoDB 에서 multiple tablespace 를 사용할경우에만 의미가  있는 값.
     InnoDB 가 한번에 열수 있는 .ibd 의 최대 수. 최소 10. 기본값 300.
     Table cache 값과는 상관 없고 --open-files-limit 옵션과도 관계없슴.
(21) innodb_support_xa : 기본값 1 또는 ON.(분산 트랜잭션 지원이란다.)
     ON 시킬경우 분산 트랜잭션 환경에서 two-phase commit 을 지원.
     transaction preparation 에 의한 disk flush 가 증가하기때문에 거시기 함.
     그래도 일단 enable.
(22) innodb_safe_binlog : innodb 테이블의 내용과 바이너리 로그의 일치성으로 보장하기 위해서 사용.
     xa transacion support 로 의미 없어짐.
(23) innodb_sync_spin_loops : thread 가 suspend 되기전 InnoDB 의 mutex 가 free 되도록
     기다리는 시간.(뭔소리여. 써야 되나? 모르니 default)
(24) innodb_table_locks : 기본값 1. LOCK TABLE 이 InnoDB 에게 내부적으로 테이블에 Lock 을 
     걸어라고 요구함.  
     autocommit = 0, innodb_table_lock = 1 로 
     해야만 innodb layer 에서 mysql 에서 table lock 을 걸었는지를 알수 있고
     그래야만 deadlock detection 이 된다.
(25) innodb_thread_concurrency : 기본값 20. 성능상의 문제나 show innodb status 결과 
     많은 스레드들이 세마포어를 기다리고 있으면 이 값을 증가 시키거나 감소 시킴.
     추천값은 CPU 숫자 + 디스크 숫자임. 20일경우 concurrency 체킹을 안함.
     일단 우리는 16으로 하지 뭐.
(26) innodb_thread_sleep_delay : innodb thread 가 innodb queue 에 joining 하기전에 
     기다리는 시간. 기본값 10,000 (ms 단위)
     일단은 기본값으로 가자.
(27) sync_binlog : syncronize its binary log to disk. 무조건 1로 하자.
2. myisam 에서 innodb 로 데이터 이관
: 당연히 mysql 시스템 테이블을 innodb 로 변경하겠다는 황당한 생각은 하지 말것.
 
테이블 생성시 default 로 innodb 로 하고 싶다면 my.cnf 에서
default-storage-engine=innodb 로 할것.
alter table imsi engine=INNODB ; 로 마이그레이션 강추.
또는 table 생성하고 insert into imsi select * from 어시기; 도 괜찮다.
만약에 unique constraint 가 secondary keys 에 걸려 있을경우
set unique_checks = 0;
.. import operation ..
set unique_checks = 1;
이렇게 하는것이 io 를 감소 시키기 땜시로 빠르다.
큰테이블 넘길때는 innodb buffer pool 사이즈를 80%까지 늘여서 작업하면 IO 가 줄어서 빠르다.
이때는 당연히 logfiles 의 크기를 늘여야 한다.
3. InnoDB data 및 Log file 추가/삭제
: 단순히 mysql shutdown 하고 innodb_data_file_path 에 추가 시키면 된다.
만약 autoextend 로 설정을 하였다면 그파일의 사이즈를 mb 단위로 환산하는 루틴이 필요하다.
(ex) innodb_data_file_path=/ibdata/ibdata1:10M:autoextend
 -->  ibdata1의 크기가 988MB 일경우
     innodb_data_file_path=/ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend 
  데이터 파일 삭제시
  : 테이블 스페이스를 줄이는 방법은 현재로서는 없단다.
    따라서 다음과 같은 절차가 필요하다.
    (1) mysqldump 로 모든 innodb 테이블을 덤프.
    (2) stop server
    (3) 모든 테이블 스페이스 파일 삭제
    (4) 새로운 테이블 스페이스 설정
    (5) Restart 서버
    (6) Import dump files
   이 이외에도 개인적으로는 innodb -> myisam 변경 -> shutdown -> 테이블스페이스설정 -> innodb 로 변경
    <Log file 숫자 변경하기>
    (1) stop mysql server (make sure it shuts down with out errors)
    (2) copy old log files into a safe place.
    (3) delete old log files from the log file directory.
    (4) edit my.cnf to change log file configuration.
    (5) start server
4. InnoDB Backup and Recovering
   : innodb hot backup 을 쓰는게 가장 좋긴 하지만 없으면 바이너리 백업을 한다.
    (1) shutdown server without errors 
    (2) copy all data files(ibdata files and .ibd files) into a safe place.
    (3) copy all ib_logfile to a safe place.
    (4) copy my.cnf to a safe place.
    (5) copy all .frm files to a safe place.
    이 이외에도 정기적으로 mysqldump 를 통한 백업을 시행해라.
    또한 mysqldump는 --single-transaction 이 있어서 다른 클라이언트에 Locking 을 거는 일 없이 백업이 가능.
    바이너리 로그를 통한 현재타임 복구는 binary 로그를 이용하여 실시.
    즉 mysqlbinlog hostname-bin.123 | mysql
    시스템 크래시로 인한 복구가 아니라면 그냥 mysql 을 리스타트 하는것으로 복구가 진행됨.
5. InnoDB 강제 복구
  : 데이터베이스 페이지가 손상되었을때에도 select into outfile 을 통해
    백업할수가 있다.
    corruption 이 select * from tbl_name 또는 InnoDB 의 roll forward recovery나
    백그라운드 작업들이 crash 되더라도 백그라운드 작업들이 돌지 않도록 막으면서 
    innodb storage engine 을 시작시킬수 있다. 따라서 테이블들을  dump 할수가
    있는 것이다. 
    [예]
    [mysqld]
    innodb_force_recovery = 4
    이렇게 세팅한후 mysql 을 리스타트 하면 스타트가 가능하다.
    여기에서 세팅 가능한 값들을 살펴보면
    1 : 손상된 페이지가 감지되더라도 mysql을 시작한다.
    2 : main thread 가 동작하는것을 차단한다.
        corruption 을 깨끗하게 하는 작업중에 crash 가 발생했었다면 이 값을
        세팅하면 mysql 이 시작 가능하다.
    3 : 스타트업 된후에 트랜잭션의 rollback 을 진행하지 않는다.
    4 : insert buffer merge operation(??) 을 차단한다. 또한
        테이블의 statistics 를 계산하지 않는다.
    5 : database 를 시작할때 undo logs 를 찾지 않는다. 또한 incomplete trasactions를 
        commit 된것으로 처리한다.
    6 : log roll-forward(check 포인트까지의 재실행)을 실행하지 않는다.
    보면 알겠지만 낮은 숫자에서 startup 이 될수록 복구할수 있는 데이터가 많다.
    forced recovery 가 사용되었지만 dump 를 위한 select와 drop, create tables
    등을 실행할수가 있다. 만약 특정테이블이 롤백과정중 crash 를 발생시켰는것을
    안다면 drop 시킬수가 있는것이다.
    어쨋거나 이값이 0보다 클경우 user 들은 암것도 못한다.(당근..)
    
6. iosolation level 확인
   : select @@global_tx_isolation;
     select @@tx_isolation;
7. DeadLock 대처법
   (1) show engine innodb status 로 최근의 deadlock 발생을 알수가 있다.
   (2) 만약에 locking reads(select .. for update 또는 lock in share mode)를
       사용한다면 READ COMMITED 같은 낮은 isolation level 을 써라
8. Transaction serialize
    : semaphore table 생성.
    각각의 transaction 에서 먼저 1 row  짜리의 semaphore 테이블을 업데이트 하고
    그리고 난뒤 다른 테이블을 억세스 한다.
9. Innodb Performance Tuning Tip
    (1) 긴 primary 키를 쓰지말고 auto_increment 칼럼을 만들어서 그넘을 
        primary key 로 잡아라.(its value must be stored with every secondary
        index record)
    (2) 70% 이하의 CPU 로드를 유지하고 Buffer Pool 은 80% 이내에서
        적당히 크게 잡아라.
    (3) 여러개의 변경을 하나의 transaction 으로 묶어라.
        innodb 는 반드시 각 commit 마다 로그를 disk 로 flush 해야 하기땜시로.
    (4) 로그파일의 크기를 크게 가져가라(심지어 buffer pool 크기만큼도 가능하다)
    (5) log buffer 의 크기도 크게 가져라(8MB 의 배수로)
    (6) innodb 로 데이터를 import 할겨우 autocommit mode 가 off 인지 확인하라.
    (7) multiple row insert 를 써라.
        insert into imsi values (1,2), (2,3), ...
    (7) big table import 시
        set unique_checks = 0;
        set foreign_key_checks = 0;
        ....
        set unique_checks = 1;
        set foreign_key_checks = 1;
    (8) query cache 를 써라. 
10. InnoDB 모니터링
    : (1) show engine innodb status G 를 이용한 모니터링
      (2) InnoDB Monitor 를 이용하는 방법.
          Create Table innodb_monitor (a INT) ENGINE=INNODB; 로 활성화.
          drop table innodb_monitor 로 비활성화.
          이와 유사한 방법으로 innodb_lock_monitor, innodb_tablespace_monitor,
          innodb_table_monitor 등이 있다.
         (ex) innodb_monitor 의 분석
          - TRANSACTIONS sectin : 만약에 이부분이 lock waits 값이 있다면, 어플리케이션이
            lock contention 을 가지고 있을 것이다. 또한 transaction 의 deadlock 에 대하여
            trace 할수 있도록 도움이 될것이다.
          - SEMAPHORES section : 세마포어를 기다리는 쓰레드와 얼마나 오래 spin, mutex wait, 
            rw-lock 세마포어를 기다렸는지에 대한 통계 정보.
            세마포어를 기다리는 쓰레드의 수가 많은것은 disk I/O, InnoDB 내의 Contention 이
            원인이다. Contention 은 많은 수의 쿼리또는 OS 의 쓰레드 스케줄링에 문제가 있어서
            발생했을 수가 있다. 이때 innodb_thread_concurrency 값을 낮게 해주는것이 도움이 된다.   
          - BUFFER POOL AND MEMORY section : 얼마나 많은 data file I/O 가 발행했는지 알수 있다.
          - ROW OPERATIONS section : 메인 쓰레드가 뭘하고 있는지 보여준다.
        
     (3) show engine innodb status 정보는 15초마다 innodb_status.pid 파일로 innodb 의 상태를 
         기록한다.(innodb_status_file = 1 로 세팅되어져 있을경우). mysql 의 정상적인 종료시
         해당 파일을 삭제하지만 비정상적인 종료시에는 수동으로 삭제 하여야 하며 당연히 
         비정상적인 종료일경우 문제를 해결할수 있는 정보가 여기에 기록되어 있을수가 있겠지.
11. Defragmenting Table
     (1) null alter table : alter table imsi engine=innodb ;
     (2) mysqldump -> drop -> create -> insert
12. Troubleshooting InnoDB Data Dictionay.
    : create table 하는 중간에 깨졋을 경우
         (1) .frm 파일은 없고 tablespace 에는 있는 경우인데 에러메세지가 지시하는 대로 
           (다른 데이터 베이스에서 같은 이름의 테이블을 생성하고 그때 생성된 .frm 파일을
            현재 데이터베이스로 이동. 그리고 drop)
            drop table 해보고 안되면 mysql client 를 --skip-auto-rehash 로 다시 시작해서 
            drop 시키면 된다.
         (2) .frm 파일은 있고 tablespace 에는 없는 경우 : 그냥 frm 파일만 삭제하면 된다.
      (중요) alter table 중에 crash 발생했거나 중지 했을 경우.
         : tablespace 에서 임시테이블로 생성이 되어 있슴. innodb_table_monitor를 이용하여
           테이블 이름이 #sql-.... 것을 drop 시키면 됨.(이때 테이블이름을 backtick 으로 감싸야 함).