[번역] MySQL 5.1의 InnoDB에서 MySQL 테이블 최적화하기

by · Published · Updated

[번역] MySQL 5.1의 InnoDB에서 MySQL 테이블 최적화하기

DBMS Wisdom 2014/05/10 15:59

원문 : http://www.laurencegellert.com/2011/07/mysql-maintenance-tasks-for-innodb-with-mysql-5-1/

때때로 MySQL 5.1 데이터베이스는 몇가지 관리 작업을 수행해야 한다. 내 경우를 예로 들면, 운영서버에서 겨우 400k 행을 가진 두 개의 테이블을 조인하는 간단한 쿼리문을 실행하는데 너무 오래 걸리는 경우가 있었다. 실제로 이 쿼리문이 실행되는데 약 30초에서 100초 가량이 걸렸다. 테스트와 검수작업 후에 58ms로 처리 시간을 줄였다. 쿼리문의 컬럼은 이미 인덱스가 생성되어 있었기 때문이다. 이 정도 처리속도라면 다행히도 사용자들이 불편해 하는 정도는 아니였지만, 나는 이정도 처리 속도도 괜히 성가시게 느껴졌다. 해결책은 간단했는데, 몇가지 명령문을 실행해서 정리 작업을 하면 되었기 때문이다. 이 정리작업을 하고 나니, 동일한 쿼리문을 운영서버에서 실행하는데 겨우 4.8ms 밖에 걸리지 않게 되었다. 정말이지 만족스러운 결과였다.

내가 한 일들

  • Backup – 데이터베이스 백업하기
  • Check – 데이터베이스 체크하기
  • Optimize – 데이터베이스 최적화하기
  • Analyze – 데이터베이스 분석하기

$ mysqldump -u root -p –create-options –routines –triggers dbname > ./db.dmp

# note these cause LOCKS, so be careful on your production server!

$ mysqlcheck -u root -p —check –databases dbname

$ mysqlcheck -u root -p —optimize –databases dbname

$ mysqlcheck -u root -p —analyze –databases dbname

각 수행 단계들에 대한 상세 설명

1. 먼저 mysqldump 명령문을 이용해서 데이터베이스를 백업한다.

프로시져 또는 함수가 있다면 반드시 -routines 인자를 사용해야 한다. 또한 트리거를 사용한다면 반드시 -triggers 인자를 사용해야 한다.


$ mysqldump -u root -p –create-options –routines –triggers dbname > ./db.dmp

# copy to another server

$ scp ./db.dmp user@somehost:~/

여기에 더해서 다른 시스템에서 백업한 데이터베이스를 실제로 재생성하여 백업이 정확한지 확인해야 한다.

만약 데이터베이스가 너무 커다면 이처럼 데이터베이스 전체를 백업하는 일은 사실 불가능하다. 하지만 데이터베이스가 너무 크다면, 이미 복제 서버를 구축해서 백업 시스템을 활용중일 것이다.

2 Check


테이블 무결성을 검사한다.

http://dev.mysql.com/doc/refman/5.1/en/check-table.html

단일 테이블에 대해 검사를 하려면:

mysql> CHECK TABLE {table name};

콘솔에서 데이터베이스의 전체 테이블을 검사하려면 :

$ mysqlcheck -u root -p –check –databases dbname

테이블 무결성 검사는 정기적으로 하는 것이 좋다.

3. Optimize

단편화 제거 작업(defrag operation)과 같이, optimize table 명령문을 사용하면 사용하지 않은 공간을 회수할 수 있다. MyISAM 엔진에서는 optimize 명령문은 말그대로 단편화 제거 작업만을 수행한다. 반면 InnoDB 엔진의 경우 내부적으로 ALTER TABLE문을 실행하여, MySQL 서버에 대해 테이블과 인덱스를 재생성하도록 요청한다.

http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

단일 테이블에 대해 optimize를 하려면:

mysql> OPTIMIZE TABLE {table name};

콘솔에서 데이터베이스의 전체 테이블을 optimize를 하려면:

$ mysqlcheck -u root -p –optimize –databases dbname

만약 InnoDB라면 결과에 “Table does not support optimize, doing recreate + analyze instead” 메시지가 나온다.

4. Analyze

analyze를 실행하면 인덱스를 재생성하여 성능을 최적화하는데, 키를 재분배하기 때문이다. 만약 인덱스가 제대로 생성되어 있음에도 slow query가 발생한다면, analyze를 실행하는 것을 고려해 볼만하다. analyze를 실행하면 read 락이 걸린다. 만약 모든 테이블이 InnoDB 엔진을 사용한다면, optimize 과정에 이미 analyze가 포함되어 있다.

http://dev.mysql.com/doc/refman/5.1/en/analyze-table.html

단일 테이블에 대해 analyze를 하려면:

mysql> ANALYZE TABLE {table name};

콘솔에서 데이터베이스의 전체 테이블을 analyze를 하려면:

$ mysqlcheck -u root -p –analyze –databases dbname

InnoDB에서 analyze를 하는 경우, 몇가지 특이점이 있다. 특히 analyzer가 취하는 샘플의 갯수가 다를 수 있다는 점이다(샘플의 갯수는 innodb_stats_sample_pages 옵션으로 설정할 수 있다). 기본값이 매우 작기 때문에, analyze를 여러번 시도하게 될 때, 그때마다 결과가 달라질 수도 있다.

더 자세한 내용은 아래를 참조하라.

http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html


Tags:

서진우

슈퍼컴퓨팅 전문 기업 클루닉스/ 상무(기술이사)/ 정보시스템감리사/ 시스존 블로그 운영자

You may also like...

24 Responses

  1. online promethazine codeine cough syrup usa
    2023년 6월 8일

    … [Trackback]

    […] Find More here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  2. click through the up coming web page
    2023년 6월 13일

    … [Trackback]

    […] Find More Info here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  3. ผู้นำเข้าสินค้าอุตสาหกรรมคุณภาพ
    2023년 8월 8일

    … [Trackback]

    […] Info on that Topic: nblog.syszone.co.kr/archives/5149 […]

  4. บานประตู wpc
    2023년 8월 24일

    … [Trackback]

    […] Read More Info here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  5. บาคาร่า
    2023년 10월 22일

    … [Trackback]

    […] Read More on on that Topic: nblog.syszone.co.kr/archives/5149 […]

  6. สมัคร yehyeh
    2023년 11월 12일

    … [Trackback]

    […] There you will find 98762 more Info to that Topic: nblog.syszone.co.kr/archives/5149 […]

  7. bonanza178
    2023년 11월 18일

    … [Trackback]

    […] Here you can find 81526 additional Information to that Topic: nblog.syszone.co.kr/archives/5149 […]

  8. แจกฟรี 5 เว็บ ทีเด็ดบอล ทรรศนะบอลโคตรแม่น
    2023년 11월 24일

    … [Trackback]

    […] Here you will find 81392 more Information to that Topic: nblog.syszone.co.kr/archives/5149 […]

  9. go now
    2023년 12월 4일

    … [Trackback]

    […] Read More to that Topic: nblog.syszone.co.kr/archives/5149 […]

  10. Pragmatic Play
    2023년 12월 17일

    … [Trackback]

    […] Here you will find 35916 more Info on that Topic: nblog.syszone.co.kr/archives/5149 […]

  11. ข่าวบอล
    2023년 12월 19일

    … [Trackback]

    […] Info on that Topic: nblog.syszone.co.kr/archives/5149 […]

  12. scam site
    2024년 1월 13일

    … [Trackback]

    […] Read More on that Topic: nblog.syszone.co.kr/archives/5149 […]

  13. ผลบอล
    2024년 1월 17일

    … [Trackback]

    […] There you can find 72488 more Info to that Topic: nblog.syszone.co.kr/archives/5149 […]

  14. Ks Quik 5000
    2024년 1월 18일

    … [Trackback]

    […] Find More Information here to that Topic: nblog.syszone.co.kr/archives/5149 […]

  15. ks lumina
    2024년 1월 18일

    … [Trackback]

    […] Find More here to that Topic: nblog.syszone.co.kr/archives/5149 […]

  16. freebase cocaine for sale
    2024년 1월 21일

    … [Trackback]

    […] Info to that Topic: nblog.syszone.co.kr/archives/5149 […]

  17. bandar togel
    2024년 1월 25일

    … [Trackback]

    […] Read More here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  18. torqeedo electric motor|achiles boat|achillies inflatable boat|yamaha vmax 2021|avon boats|achilies boats|achillies inflatable boats|200 hp merc outboard|avon inflatable boats|avon boat|avon inflatable boat|honda boats|200hp mercury outboard|achillies inflatable boat|avon inflatables|200 hp mercsury outboard|yamaha 250 hp outboard|outboard jet motors for sale|4hp yamaha outboard|best 25 hp outboard|jet outboard motors for sale|yamaha extended warranty|yamaha 300hp outboard|used outboard jet motors for sale|70 hp outboard|2.5 hp mercury outboard|250hp outboard|mercury 30 horsepower outboard|outboard dealer|yamaha 4 hp outboard|25hp mercury|tohatsu 6hp|mercury 200 outboard|yamaha jet outboard|2.5 mercury outboard|yamaha 300 hp outboard|honda outboard engines|70 hp boat motor|inmar boats|merc 25 hp outboard|tohatsu 60hp|inmar boat|9.9hp mercury outboard|mercury 25 hp outboards|jet outboard motor|inflatable zodiac boats for sale|propane outboard|25 hp mercury outboard|honda outboard dealer near me|9.9 hp mercury
    2024년 2월 8일

    … [Trackback]

    […] Read More Information here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  19. ฟันคัพ 2024
    2024년 2월 10일

    … [Trackback]

    […] Find More Info here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  20. โซล่าเซล
    2024년 2월 15일

    … [Trackback]

    […] Information on that Topic: nblog.syszone.co.kr/archives/5149 […]

  21. chaga mushroom gummies
    2024년 3월 11일

    … [Trackback]

    […] Read More to that Topic: nblog.syszone.co.kr/archives/5149 […]

  22. Anya Belcampo
    2024년 3월 19일

    … [Trackback]

    […] Find More Info here on that Topic: nblog.syszone.co.kr/archives/5149 […]

  23. judi bola
    2024년 4월 17일

    … [Trackback]

    […] Find More Info here to that Topic: nblog.syszone.co.kr/archives/5149 […]

  24. clipzui,clip zui,ClipZui,Clip Zui,clipzui.cc,www.clipzui.cc
    2024년 4월 26일

    … [Trackback]

    […] Find More to that Topic: nblog.syszone.co.kr/archives/5149 […]

페이스북/트위트/구글 계정으로 댓글 가능합니다.