[튜닝] mysql Advanced InnoDB Optimization

Mysql User Conference 2005(http://conferences.oreillynet.com/pub/w/35/presentations.html )

발표자료 읽고 요약한것임 (by nallapic9)

Advanced InnoDB Optimization

1. Use short PK

– PK is part of all other indexes on table.

– auto_increment, unique 사용을 신중히 고려하셈

– int 가 varchar나 char보다 빠르다.

2. 웬만하면 PK를 쓰지?

– 가능하면 PK를 이영해서 table참조를 해라.

3. PK업데이트가 웬말이냐~!

– PK가 업데이트 되면 다른 row들도 수정되어야 한다.=_=

– row relocation이 자주 일어나면 안좋아요

4. Cluster your accesses by PK

– Insert in PK order are much faster

5. UK는 NON-UK보다 비싸다.

– Insert buffering does not work

6. Prefix indexes especially useful

– No Key compression as in MyISAM

7. Long row overhead

– Blob reading can be skipped if not referenced

– Vertical partition rarely makes sense

8. Manual partitioning still make sense

– table lock 문제는 없는데, alter table에 문제가…

9. Loading data to InnoDB

– SET UNIQUE_CHECKS=0

– SET FOREIGN_KEY_CHECKS=0

– Paralled load는 되도록이면 다른 테이블일 경우에만 적용(같은 테이블에서 적용할경우 fragmentation이 많이 일어난다.

– If your database size is much larger than memory

: innodb_buffer_pool_size up to 90% of memory

: innodb_log_file_size to innodb_buffer_pool_size

: 이건 메뉴얼좀 더 읽어보고 바꾸셈.

10. Rollback failue

– 10000row이상은 잘 생각해서하세용

11. Transaction

– Too small transaction

: Expensive transaction commit

– Too large transaction

: Undo space growing ( and may

: Excessive locking, larger chance

: Binary log cache overflow

– Medium transactions offer best performance

: 근데 중간정도의 트랜잭션이 도대체 뭘까요? =_=

12. Connection 문제

– Limit number of active heavy queries.

: 요런 식으로.. SELECT GET_LOCK(“mycomplexquery01”);

– innodb_thread_concurrency

: 이상한공식도 있네요… (num_cpus+num_disks)*(2..4) 근거는 뭘까요

– 기타 여러 문제점에 대해 나오는데 그건 원문을 참고하시고…

13. 함붙자~! DeadLock, Lock waits

– Deadlock은 퍼포먼스를 해치고, row lock은 response time을 증가시킨다.(당연하지)

– Handle deadlocks and timeouts (개런티 못하겠다는데…=_= 그럼 어쩌겠다는거지)

– 알아서 Connection관리 잘하고…

– 모든 Transaction에서 같은 order로 데이터를 Lock하라?..무슨말이지..=_

– Transaction의 시작에서 Lock을 하셈

– Update할때 Where절에 a like “%asdf%” 이런거 하지마세요 그럼 모든 row에 lock걸립니다.

– 위에 저런 쿼리 쓸려거든 대략 구간이라도 짤라서 쓰세요.

14. 자질구래한것들…

– innodb_buffer_pool_size : Set 70~80% of memory, Much more efficient than OS cashe especially for write loads

– innodb_additional_mem_pool_size : 보통 8~16mb면 적당하다네요. 테이블수나, 컨넥션수에따라서 늘리세요

– innodb_autoextend_increment : 오라클에 그것(이름이 잘..ㅋ)과 비슷한데 디폴트 8MB고 크면 fragmentation이 적에 일어난다네요.(당연하지+_=)

– innodb_file_per_table

: 각 테이블마다 own file/tablespace를 만드는거래요.

: Helpful to manually balance IO between IO devices

: Helpful for binary backup on per table basics

: Helpful against per inode IO kernel locks in some OS

: 저런데는 좋은데… sync나 flush등, 따로따로 있어서 update overhead가 있네요.

– innodb_flush_log_at_trx_commit

: InnoDB는 백그라운드로 1초에 한번씩 log를 flush한데요

: value 0 – transaction commit할때 flush안해유, 퍼포먼스 좋은데 DB 죽었을때 transaction loss발생가능

: value 2 – log flushing to OS cache 0보다는 느리지만 DB 죽었을때 Trasaction loss없다. OS죽으면 transaction loss 발생

: value 1 – default, 그냥 commit될때 flush

– innodb_lock_wait_timeout

: 너무 길게주면 딴얘들이 기다리고, 넘 잛게주면 timeout에러가 많이 나죠.

: Value is global, can’t be set without restarting server

: long transaction은 왠만하면 피하세용

: 가능하면 non-blocking read하세요.

15. Benchmarks

– Cpu2배로 늘리면 트랜잭션 처리 속도는 1.7배정도 늘어남.

– Connection수 vs 트랜잭션처리속도 비율이 8개 : 32개: 256개 = 1 : 0.96 : 0.64

– 다른 벤치마크는 원문참조를 ㅋ