1 / 70

운영시스템 튜닝 방법론

운영시스템 튜닝 방법론. 김 우 진 ㈜ 데브피아 DB 사업부 / 차장 Microsoft .Net Regional director MCT & MCDBA. http://www.devpia.com. 차례. 튜닝 ? Hardware 상의 ISSUE Configuration 상의 ISSUE Database 운영상의 ISSUE Index 상의 ISSUE SQL 문 사용의 ISSUE 기타 ( Design ) ISSUE. 튜닝 ?. 튜닝 정의 병목점 처리 -> 튜닝 방법론 ?

ishmael
Download Presentation

운영시스템 튜닝 방법론

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 운영시스템 튜닝 방법론 김 우 진 ㈜ 데브피아 DB사업부 / 차장 Microsoft .Net Regional director MCT & MCDBA http://www.devpia.com

  2. 차례 • 튜닝? • Hardware 상의 ISSUE • Configuration 상의 ISSUE • Database 운영상의 ISSUE • Index 상의 ISSUE • SQL문 사용의 ISSUE • 기타 ( Design )ISSUE

  3. 튜닝? • 튜닝 정의 • 병목점 처리 -> 튜닝 방법론? • 협의의 튜닝 – DB , HW , Programming • 광의의 튜닝 – 운영 시스템 • 현 운영시스템 성능의 장애요소 • 지식 인정(X) -> 지식은 부가가치가 아니다? • 열심히 그냥 열심히 …..

  4. 튜닝 방법론 • 바뀌어야 할 3요소 – 시야 , 시각 , 마인드 • 튜닝은 기술이 아니라 예술이다. • 운영시스템 튜닝을 위한 시야 • 운영시스템 튜닝을 위한 시각 • 운영시스템 튜닝을 위한 마인드 • Project 구축 시에도…

  5. 운영시스템 튜닝을 위한 시야 • Hardware • Network • Operating System • Database • Application (Programming) • 설계 및 구현 로직

  6. 운영시스템 튜닝을 위한 시각 • 튜너는 엔지니어? 아티스트? • 기술 vs 비즈니스 • 튜닝이란 효율의 철학이다. • 검은 고양이와 흰 고양이

  7. 운영시스템 튜닝을 위한 마인드 • 튜닝은 전략가를 요한다. • 병목자원을 튜닝하기 위한 가장 좋은 방법? • 성능을 위해선… • 전략 ( 처리 로직 설계 및 DB 설계 ) • 전술 ( SQL 문 & Programming 처리 ) • 병력 ( Hardware Resource ) • Hint , Tip , Trick -> 기본으로 돌아가자.

  8. 튜닝 시 사용할 툴 • NT Performance 모니터 • SQL Server Profiler • SQL Server Index Tunning Wizard • SQL Server Query Analyzer

  9. Hardware 상의 ISSUE • NT Performance 모니터 사용 • CHECKPOINT • CPU • Memory • DISK • NETWORK • RAID?

  10. CPU • 프로세서 모니터링 • Processor • % Processor Time • % Privileged Time • % User Time • Processor Queue Length

  11. CPU • 원인분석 • Application 처리 로직 • DB설계 • Index • SQL처리문 • 과도한 Hash 조인 • 과도한 재 컴파일 등등…. • 분석된 원인 처리후에도 병목이라면… • 프로세서 추가 • 더 빠른 프로세서로 교체 • 더 많은 캐쉬를 갖고 있는 프로세서로 교체

  12. Memory • 메모리 모니터링 • Available Bytes • Pages/sec (paging in/out) • Page Reads/sec • Page Writes/sec • SQLServer:Buffer Manager->Buffer Cache Hit Ratio

  13. Memory

  14. Memory • 원인분석 • Application 처리 로직 • DB설계 • Index • SQL처리문 • 과도한 재 컴파일 • 과도한 단편화 등등…. • 분석된 원인 처리후에도 병목이라면… • 메모리추가

  15. DISK • % Disk Read Time • % Disk Write Time • % Disk Time • Avg. Disk Queue Length

  16. DISK • 원인분석 • Application 처리 로직 • DB설계 • Index • SQL처리문 • 파일그룹 및 파일 위치 • 과도한 단편화 등등…. • 분석된 원인 처리후에도 병목이라면… • 디스크 추가 • RAID 고려

  17. Network • Network이 병목 상태이고 이 운영시스템 서버가 주요 원인이라면.. • 원인분석 • Application 처리 로직 • DB설계 • Index • SQL처리문 등등…. • 분석된 원인 처리 후에도 병목이라면… • NI 추가 • Subneting • Network 업그레이드 고려

  18. RAID • RAID는 RAID5? • Workload 분석 • DATA와 LOG가 동일? • Sequential vs Non Sequential

  19. RAID

  20. Configuration 상의 ISSUE • 고정 vs 동적 메모리 할당 • DB전용 머신 vs 혼용 서버 • 파일 그룹 • 그냥 디폴트로 사용한다? • RAID 와 파일 그룹 • 파일 증가 속성 • Log 백업

  21. Database Data (file) .mdf or .ndf Log (file) .ldf Tables, Indexes Extent (8개의 연결된 8KB page들) Data Page (8KB) 한 row의 최대 길이는 8092 bytes 물리적 저장구조

  22. OrdHistYear1 Products Customers Orders … sysobjects sysusers sys… sys… OrdHistYear2 C:\ D:\ E:\ Northwnd.mdf OrdHist1.ndf OrdHist2.ndf Northwnd.ldf Primary Filegroup User-defined Filegroup Transaction Log 파일 그룹 의 종류

  23. RAID와 파일 그룹 FileA.mdf FileB.ndf FileC. ndf FileD. ndf FileE. ndf FileF. ndf FileG. ndf FileH. ndf 18GB drives MYFILEGROUP All Tables and all indexes On single filegroup 50% read 50% write DB LOG

  24. Database 운영상의 ISSUE • 서비스 팩 적용 유무 • 단편화 • DEAD LOCK • BLOCKING • ERROR

  25. 서비스 팩 적용 유무 • ‘_WA ‘ 및 ‘hind_’ 로 시작하는 인덱스는 MSSQL 서버의 버그로서 불필요한 리소스를 낭비하게 되고 옵티마이저가 잘못된 판단을 내리게 되는 원인이 될 수 있습니다. • 서비스 팩은 제품 출시 이후 발견된 특수한 문제들에 대한 해결책들을 담고 있으며, 실제로 기술지원 결과 많은 문제들이 서비스 팩 설치만으로 해결되기도 합니다.

  26. 단편화 • DBCC SHOWCONTIG로 체크

  27. 단편화 • 단편화의 영향 • 과도한 IO 발생 • CACHE 적중률 저하 • 메모리 병목 • 성능에 심각한 영향 • 단편화 처리 - Index Defragmenting vs. Index Rebuilding • 인덱스 재생성 (DBCC DBREINDEX) • DBCC INDEXDEFRAG • DBCC INDEXDEFRAG이 작업은 잠금을 오래 보유하지 않으므로 실행 중인 쿼리나 업데이트를 차단하지 않고 진행 도중에 언제든지 종료할 수 있으며 완료된 작업은 모두 그대로 보존된다.

  28. DB 성능 모니터링 도구 • SQL Profiler • Index Tuning Wizard • Query Analyzer

  29. SQL Profiler • 성능이 좋지않은 쿼리를 찾는다. • 교착 상태를 발견한다. • 저장 프로시저 성능을 모니터링한다. • Microsoft® SQL Server™ 동작 감사 • 사용자 당 Transact-SQL 동작을 모니터링한다.

  30. DEAD LOCK • 시스템에 심각한 부하 • 빈번한 발생은 반듯이 원인 규명 및 해결

  31. DEAD LOCK

  32. DEAD LOCK

  33. BLOCKING • 운영중의 시스템에서 체크가 쉽지 않음. • DEAD LOCK 과는 달리 정상적인 LOCK • 프로필러에서 실행 기간이 긴 Query들 분석하여원인 파악.

  34. 오류처리 • Application에서 발생시키는 오류를 체크 • 시스템 성능 저하 요소 • 데이터 무결성에도 악영향

  35. 오류처리

  36. Index 상의 ISSUE • Index ? • Clustered vs. NonClustered • PK는 Clustered? • Composite Index • Indexed View • Index Turning Wizard

  37. Index ? • 옵티마이저가 최적의 처리 경로를 결정하기 위한 요소 • 포인터로 직접 엑세스 할 수 없는 RDB의 단점 해소 • 다수의 애플리케이션을 커버할 수 있도록 고려 • Seek, Scan의 의미 • B-tree의 구조 • 인덱스와 클러스터

  38. Table Scan Data Pages Data Pages … Index 검색 Index Pages Data Pages … SQL 서버의 데이터 액세스 방법

  39. Index 장단점 • 목적 & 장점 검색 속도 증가 유일성 강화 경우에 따라 갱신 속도 향상 (update,delete) • 단점 공간 점유 갱신 속도 저하 ( insert ) • Trade-off (정답이 없음 - insert도 hot spot시 clustered index로 성능 향상)

  40. B-Tree 구조 N D H K Q S W E F G I J L M R T U V X Y Z A B C O P H N B D K Q S W C E F I J L M R T U V X Y Z $ A G O P  $ Insert

  41. Data Pages LeafLevel Index 구조 Nonclustered Index Clustered Index Index Pages Non-LeafLevel Index Pages Non-LeafLevel Leaf Level(Key Value) Data Pages

  42. 클러스터와 인덱스 비교 INDEX CLUSTER Cluster Cluster Key Header Index Rowid column Rowid Columns Rowid Columns 111 1 . . . . . . . . . 3 BB . . . . . 111 3 . . . . . . . . . . . . . . . . . 1 AB 123 . . . . . . 123 10 10 Cluster Header 4 CA 354 . . . . . . . . . . . 123 1 . . . . 1 AB . . . . . 12 BS . . . . . 10 BD . . . . . . . . . . . . . . 99 DD . . . . . 123 10 12 BS 123 . . 999 . . 123 12 3 BB 217 . . . . . . . 10 BD 123 . . 123 99 9 CS 5 . . . . . . . . . . . . . . . . . . . . . . . . 99 DD 123 . . . . . . . . . . . . . . . . . . . . . . . . 999 . . . 클러스터는 인덱스 개념을 데이터 페이지에 적용한 개념 CLUSTERD INDEX INDEX TABLE TABLE

  43. id indid = 1 root sysindexes Clustered Index Clustered Index Akhtar Akhtar … … Martin Martin Page 140 - Root Page 140 - Root Akhtar Akhtar Martin Martin Ganio Ganio Smith Smith … … … … Page 141 Page 141 Page 145 Page 145 Akhtar Akhtar 2334 2334 ... ... Ganio Ganio 7678 7678 ... ... Martin Martin 1234 1234 ... ... Smith Smith 1434 1434 ... ... Barr Barr 5678 5678 ... ... Hall Hall 8078 8078 ... ... Martin Martin 7778 7778 ... ... Smith Smith 5778 5778 ... ... Con Con 2534 2534 ... ... Jones Jones 2434 2434 ... ... Ota Ota 5878 5878 ... ... Smith Smith 7978 7978 ... ... Funk Funk 1334 1334 ... ... Jones Jones 5978 5978 ... ... Phua Phua 7878 7878 ... ... White White 2234 2234 ... ... ... ... ... ... ... ... ... ... Funk Funk 1534 1534 Jones Jones 2634 2634 Rudd Rudd 6078 6078 White White 1634 1634 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Page 100 Page 100 Page 110 Page 110 Page 120 Page 120 Page 130 Page 130 Ota 5878 ... Clustered Index Martin SELECT lastname, firstname FROM member WHERE lastname = 'Ota' Martin

  44. Clustered Index • 테이블에 1개 만 존재 가능 • Data 자체가 물리적으로 Disk 드라이브에 정렬 • Clustered Index의 leaf노드는 실제 data page 따라서 Pointer jump가 필요없고 디스크상에서 대량 범위 처리시(64KB이상) sequential I/O 작업으로 처리 • 대량 범위 처리에 강점 • PK는 Clustered ? Index 장점 + scan 장점 = 슈퍼 스타?

  45. id indid = 2 root Non clustered Index Non Clustered Index Non-Leaf Level Non-Leaf Level Akhtar Akhtar ... ... Martin Martin Martin Akhtar Akhtar Page 37 Page 37 Page 28 Page 28 Page 12 - Root Page 12 - Root Ganio Ganio Martin Martin Martin ... ... Smith Smith ... ... Leaf Level(Key Value) Leaf Level(Key Value) Page 41 Page 41 Page 51 Page 51 Page 61 Page 61 Page 71 Page 71 Akhtar Akhtar 4:706:01 4:706:01 Smith Ganio Ganio Smith 4:709:01 4:709:01 4:706:03 4:706:03 Barr Barr 4:705:03 4:705:03 Hall Hall Smith Smith 4:709:04 4:708:04 4:709:04 4:708:04 Martin Martin 4:708:01 4:708:01 Con Con 4:704:01 4:704:01 Smith Jones Smith Jones 4:709:02 4:707:01 4:707:01 4:709:02 Matey Matey Matey 4:706:04 4:706:04 4:706:04 Funk Funk 4:706:02 4:706:02 White Jones Jones White 4:704:03 4:704:03 4:708:03 4:708:03 Ota Ota 4:707:02 4:707:02 Funk Funk 4:704:02 4:704:02 White Jones White Jones 4:707:03 4:705:02 4:707:03 4:705:02 Ota Phua Phua Phua 4:707:02 4:708:02 4:708:02 4:708:02 Rudd Rudd Rudd 4:705:01 4:705:01 4:705:01 Heap Heap Page 704 Page 704 Page 705 Page 705 Page 706 Page 706 Page 707 Page 707 Page 808 Page 808 Page 709 Page 709 01 01 ... ... Akhtar Akhtar 01 01 ... ... Conn Conn 01 01 01 ... ... ... Rudd Rudd Rudd 01 01 ... ... Smith Smith 01 01 ... ... Martin Martin 01 01 ... ... Ganio Ganio 02 02 ... ... Funk Funk 02 02 ... ... Funk Funk 02 02 ... ... White White 02 02 ... ... Ota Ota 02 02 02 02 ... ... ... ... Phua Ota Phua Phua 02 02 ... ... Jones Jones 03 03 ... ... Smith Smith 03 03 ... ... White White 03 03 ... ... Barr Barr 03 03 ... ... Jones Jones 03 03 ... ... Jones Jones 03 03 ... ... Hall Hall 04 04 ... ... Matey Matey ... ... ... ... ... ... ... ... ... ... ... ... 04 ... Matey ... ... ... ... ... ... 04 04 ... ... Smith Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... File ID #4 File ID #4 Nonclustered Index sysindexes SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Masters' AND 'Rudd'

  46. Nonclustered Index • Table당 249개 까지 생성 가능 • 각각의 row에 대해 Pointer jump가 필요 • 이 Pointer jump는 디스크상에서 nonsequential I/O 작업 필요 • 선택성이 높은 소수의 row fetch에 유용

  47. Logical Read 페이지수 측정 • 인덱스 없을시 : Logical Read 수 = 테이블의 페이지 수 (sysindexes.dpages) • Clustered 인덱스 • 인덱스에 있는 수준들의 수 • 스캔 될 Data pages 수 • Non-clustered 인덱스 • 인덱스에 있는 수준들의 수 • Leaf 페이지의 수 • 각 행 검색당 1 logical read

  48. id indid = 2 root Nonclustered Index on First Name Nonclustered Index on First Name Non-Leaf Level Non-Leaf Level Aaron Aaron ... ... Jose Jose Aaron Aaron Jose Jose Deanna Deanna Nina Nina … … … … Leaf Level(Clustered Key Value) Leaf Level(Clustered Key Value) Aaron Aaron Con Con Jose Jose Lugo Lugo Deanna Deanna Daum Daum Adam Adam Barr Barr Judy Judy Kaethler Kaethler Don Don Hall Hall Amie Amie Baldwin Baldwin Mike Mike Mike Nash Nash Nash Doug Doug Hampton Hampton … … … … … … … … … … … … Barr Barr Clustered Index On Last Name Clustered Index On Last Name Kim Kim Nagata Nagata O’Melia O’Melia Barr Barr Adam Adam … … Kim Kim Shane Shane … … Nagata Nagata Susanne Susanne … … Cox Cox Arlette Arlette … … Kobara Kobara Linda Linda … … Nash Nash Nash Mike Mike Mike … … … Daum Daum Deanna Deanna … … LaBrie LaBrie Ryan Ryan … … Nixon Nixon Toby Toby … … … … … … … … … … … … … … … … … … … … Nonclustered Index with a Clustered Index sysindexes SELECT lastname, firstname, phone FROM member WHERE firstname = 'Mike' Nagata

  49. Composite Index-최적의 Column 순서 결정 • CREATE INDEX문에 기술된 Key Column들의 순서가 중요 • 예: CREATE INDEX test_ind ON test (A, B) • WHERE A=‘Value’ : efficient • WHERE A=‘Value’ and B=‘Value’: efficient • WHERE B=‘Value’ : less efficient • Query 형태 분석 • Selectivity 고려

  50. Covering Indexes • Composite Index에 Query검색에 필요한 모든 Column들이 포함되는 특별한 Nonclustered Index • Covering index의 예: select col1, col3 from table1 where col2 = ‘value’ go create index indexname1 on table1 (col2, col1, col3) go

More Related