[MySQL] MySQL Tuning(튜닝) 그리고 Optimization(최적화)

MySQL 서버 하드웨어 및 OS 조정:

1. 전체 InnoDB 파일을 메모리에 로드하기에 충분한 물리적 메모리가 있어야 합니다. InnoDB는 디스크가 아닌 메모리에서 파일에 액세스할 수 있을때 훨씬 빠릅니다.

2. 스와핑을 피합니다. 스와핑은 디스크에서 읽는 것이므로 속도가 느립니다.

3. 배터리 지원 RAM(Battery-Backed RAM)을 사용합니다.

4. 고급 RAID를 사용합니다. 가급적이면 RAID10 이상을 사용합니다.

5. RAID5를 피합니다. 무결성을 보장하는데 필요한 체크섬은 비용이 많이 듭니다.

6. OS와 데이터 파티션을 논리적으로뿐만 아니라 물리적으로 분리합니다. 비용이 많이 드는 OS 쓰기 및 읽기는 데이터베이스 성능에 영향을 미칩니다.

7. mysql 임시 공간과 복제 로그를 데이터가 아닌 별도의 파티션에 둡니다. 디스크에서 쓰기/읽기를 할 때 백그라운드 쓰기는 데이터베이스에 영향을 미칩니다.

8. 디스크가 많을수록 속도가 빨라집니다.(병렬 I/O 구성)

9. 디스크는 빠를수록 좋습니다.

10. SATA보다 SAS를 사용합니다.

11. 특히 RAID 구성에서 더 작은 디스크는 더 큰 디스크보다 빠릅니다.

12. 배터리 지원 캐시 RAID 컨트롤러를 사용합니다.

13. 소프트웨어 레이드를 가능하면 사용하지 않습니다.

14. 데이터 파티션에 솔리드 스테이트 I/O 카드(디스크 드라이브 아님)사용을 고려합니다. 이 카드는 거의 모든 양의 데이터에 대해 2GB/s 이상의 쓰기를 유지할 수 있습니다.

15. Linux에서 swappiness 값을 0으로 설정합니다. 데이터베이스 서버에 파일을 캐시할 이유가 없습니다. 이것은 웹 서버 또는 데스크탑의 이점에 가깝습니다.

16. 가능한 경우 noatime 및 nodirtime을 사용하여 파일 시스템을 마운트합니다. 액세스를 위해 데이터베이스 파일 수정 시간을 업데이트할 이유가 없습니다.

17. XFS 파일 시스템 사용 – ext3보다 빠르고 작은 파일 시스템으로 저널링 옵션이 더 많습니다. 또한 ext3은 MySQL에서 이중 버퍼링 문제가 있는 것으로 나타났습니다.

18. 최대 성능 벤치마크를 위해 XFS 파일 시스템 로그 및 버퍼 변수를 조정합니다.

19. Linux 시스템에서 NOOP 또는 DEADLINE IO 스케줄러를 사용합니다. CFQ 및 ANTICIPATORY 스케줄러는 NOOP 및 DEADLINE 스케줄러에 비해 느린것으로 나타났습니다.

20. 64비트 OS사용 - 더 많은 메모리 주소 지정이 가능하고 MySQL에서 사용할 수 있습니다.

21. 서버에서 사용하지 않는 패키지와 데몬을 제거하여 리소스 도용을 줄입니다.

22. dns 조회 없이 MySQL을 사용하는 호스트와 MySQL 호스트를 호스트 파일에 넣습니다.

23. 절대로 MySQL 프로세스를 강제 종료하지 않습니다. 데이터베이스가 손상되고 백업을 위해 실행됩니다.

24. MySQL 전용 서버 – 백그라운드 프로세스 및 기타 서비스가 db cpu 시간을 훔칠 수 있습니다.

 

MySQL 구성:

25. 쓸때(Write시) 이중 버퍼를 피하기 위해 innodb_flush_method=O_DIRECT를 사용합니다.

26. O_DIRECT 및 EXT3 파일 시스템을 피하십시오. 모든 쓰기를 직렬화합니다.

27. 전체 InnoDB 파일을 메모리에 로드하기에 충분한 innodb_buffer_pool_size를 할당하십시오. 디스크에서 읽기가 적습니다.

28. 더 빠르고 더 많은 디스크로 innodb_log_file_size를 너무 크게 만들지 않습니다. 더 자주 플러시하는 것이 좋으며 충돌시 복구 시간을 줄입니다.

29. innodb_thread_concurrency 및 thread_concurrency 변수를 혼합하지 않습니다. 이 두 값은 호환되지 않습니다.

30. max_connections에 최소량을 할당합니다. 너무 많은 연결은 RAM을 사용하고 MySQL 서버를 잠글 수 있습니다.

31. 연결을 열 때 속도가 느려지는 것을 방지하기 위해 thread_cache를 약 16으로 비교적 높은 숫자로 유지합니다.

32. skip-name-resolve를 사용하여 DNS 조회를 제거합니다.

33. 쿼리가 반복적이고 데이터가 자주 변경되지 않는 경우 쿼리 캐시를 사용합니다. 그러나 자주 변경되는 데이터에 쿼리 캐시를 사용하면 성능이 저하됩니다.

34. 디스크 쓰기를 방지하기 위해 temp_table_size를 늘립니다.

35. max_heap_table_size를 늘려 디스크 쓰기를 방지합니다.

36. sort_buffer_size를 너무 높게 설정하지 않습니다. 이것은 연결당이며 메모리를 빨리 소모할 수 있습니다.

37. key_read_requests 및 key_reads를 모니터링하여 key_buffer 크기를 결정합니다. 키 읽기 요청은 key_reads보다 높아야 합니다. 그렇지 않으면 key_buffer를 효율적으로 사용하고 않을 수 있습니다.

38. innodb_flush_log_at_trx_commit = 0으로 설정하면 성능이 향상되지만 기본값(1)으로 두면 데이터 무결성이 보장되고 복제가 지연되지 않는지 확인할 수 있습니다.

39. 프로덕션에 영향을 주지 않고 구성을 테스트하고 자주 다시 시작할 수 있는 테스트 환경을 갖습니다.

 

 

 

MySQL 스키마 최적화:

40. 데이터베이스 공백제거(TRIM)를 유지합니다.

41. 오래된 데이터 보관 - 사용하지 않는 데이터에 대해 과도한 행 반환 또는 쿼리 검색을 하지않도록 별도로 보관합니다.

42. 데이터에 인덱스를 추가합니다.

43. 인덱스를 남용하지 말고 쿼리와 비교하여 적절히 생성합니다.

44. 텍스트 및 Blob 데이터 유형을 압축하여 공간을 절약하고 디스크 읽기 횟수를 줄입니다.

45. UTF8 및 UTF16은 latin1보다 느립니다.

46. ​​트리거를 가능하면 적게 사용합니다.

47. 중복 데이터를 최소화합니다. 불필요하게 데이터를 복제하지 않습니다.

48. 행을 확장하는 대신 연결 테이블을 사용합니다.

49. 데이터 유형에 주의를 기울이고 실제 데이터에는 가능한 한 가장 작은 유형을 사용합니다.

50. blob/text가 아닌 다른 데이터가 쿼리에 자주 사용되는 경우 blob/text 데이터를 다른 데이터와 분리합니다.

51. 테이블을 자주 확인하고 최적화합니다.

52. InnoDB 테이블을 자주 다시 작성하여 최적화합니다.

53. 열을 추가할 때 인덱스를 삭제한 다음 다시 추가하는 것이 더 빠른 경우가 있습니다.

54. 필요에 따라 다른 스토리지 엔진을 사용합니다.

55. 로깅 테이블 또는 감사 테이블에 ARCHIVE 스토리지 엔진을 사용합니다. 이것은 쓰기에 훨씬 더 효율적입니다.

56. MySQL이 아닌 Memcache에 세션 데이터 저장 - memcache는 값 자동 만료를 허용하고 시간 데이터를 위해 MySQL에 값비싼 읽기 및 쓰기를 생성할 필요가 없도록 합니다.

57. 가변 길이 문자열을 저장할 때 CHAR 대신 VARCHAR를 사용합니다. CHAR은 고정 길이이고 VARCHAR는 아니기 때문에 공간을 절약합니다(utf8은 이에 영향을 받지 않음).

58. 스키마를 점진적으로 변경합니다. - 작은 변경이 큰 영향을 미칠 수 있습니다.

59. 프로덕션을 미러링하는 개발 환경에서 모든 스키마 변경을 테스트합니다.

60. 설정 파일의 값을 임의로 변경하지 마십시오. 치명적인 영향을 미칠 수 있습니다.

61. 때로는 MySQL 구성에서 더 적은 것이 더 많습니다.

62. 확실하지 않은 경우 일반 MySQL 구성 파일을 사용하십시오.

 

쿼리 최적화:

63. Slow Query Log를 사용하여 느린 쿼리를 찾습니다.

64. EXPLAIN을 사용하여 쿼리가 적절하게 작동하는지 확인합니다.

65. 쿼리가 최적으로 수행되는지 확인하기 위해 자주 테스트합니다. 성능은 시간이 지남에 따라 변할 것입니다.

66. 전체 테이블에 대한 count(*)를 피합니다. 전체 테이블을 잠글 수 있습니다.

67. 후속에 유사한 쿼리가 쿼리 캐시를 사용하도록 쿼리를 균일하게 만듭니다.

68. 적절한 경우 DISTINCT 대신 GROUP BY를 사용합니다.

69. WHERE, GROUP BY, ORDER BY 절에서 인덱스 컬럼을 사용합니다.

70. 인덱스를 단순하게 유지하고 여러 인덱스에서 열을 재사용하지 않습니다..

71. 때때로 MySQL은 잘못된 인덱스를 선택합니다. 이 경우에는 USE INDEX를 사용하지 않습니다.

72. SQL_MODE=STRICT를 사용하여 문제를 확인합니다.

73. 5개 미만의 인덱스 필드에 대해 OR 대신 UNION에 LIMIT를 사용합니다.

74. 업데이트 전에 SELECT를 피하려면 UPDATE 대신 INSERT ON DUPLICATE KEY 또는 INSERT IGNORE를 사용합니다.

75. MAX 대신 인덱스 필드와 ORDER BY를 사용합니다.

76. ORDER BY RAND()를 사용하지 마십시오.

77. LIMIT M,N은 특정 상황에서 실제로 쿼리 속도를 늦출수 있으므로 드물게 사용합니다.

78. WHERE 절에서 하위 쿼리 대신 UNION을 사용합니다.

79. 업데이트의 경우 공유 모드(Share Mode)를 사용하여 독점 잠금(exclusive locks)을 방지합니다.

80. MySQL을 다시 시작할 때 데이터가 메모리에 있고 쿼리가 빠른지 확인하기 위해 데이터베이스를 워밍업해야 합니다.

81. DELETE FROM 대신 DROP TABLE을 사용한 다음 CREATE TABLE을 사용하여 테이블에서 모든 데이터를 제거합니다.

82. 쿼리의 데이터를 필요한 데이터로만 최소화합니다. *를 사용하는 것은 대부분 과도합니다.

83. 오버헤드를 줄이기 위해 다중 연결 대신 지속적인 연결을 고려합니다.

84. 서버에 부하를 사용하는 것을 포함하여 쿼리를 벤치마킹하고 때로는 간단한 쿼리가 다른 쿼리에 영향을 줄 수 있습니다.

85. 서버의 부하가 증가하면 SHOW PROCESSLIST를 사용하여 느리거나 문제가 있는 쿼리를 봅니다.

86. 프로덕션 데이터를 미러링한 개발 환경에서 의심스러운 모든 쿼리를 테스트합니다.

 

MySQL 백업 절차:

87. 2차 복제 서버에서 백업.

88. 데이터 종속성 및 외부 제약 조건에 대한 불일치를 방지하기 위해 백업 중에 복제를 중지합니다.

89. MySQL을 완전히 중지하고 데이터베이스 파일을 백업합니다.

90. MySQL 덤프를 사용하는 경우 덤프 파일과 동시에 바이너리 로그를 백업하여 복제가 중단되지 않도록 합니다.

91. 백업을 위해 LVM 스냅샷을 전적으로 신뢰하면 안됩니다. 이는 데이터 불일치를 생성하여 향후 문제를 일으킬 수 있습니다.

92. 데이터가 다른 테이블과 분리되어 있는 경우 단일 테이블 복구를 쉽게 하기 위해 테이블당 덤프를 만듭니다.

93. mysqldump를 사용할 때 –opt를 사용하십시오.

94. 백업전에 테이블을 확인하고 최적화하십시오.

95. 가져올 때 더 빠른 가져오기를 위해 외부 제약 조건을 일시적으로 비활성화합니다.

96. 가져올 때 더 빠른 가져오기를 위해 고유 검사를 일시적으로 비활성화합니다.

97. 증가를 모니터링하기 위해 각 백업 후에 데이터베이스/테이블 데이터 및 인덱스의 크기를 계산합니다.

98. cron 스크립트를 사용하여 오류 및 지연에 대한 슬레이브 복제를 모니터링합니다.

99. 정기적으로 백업을 수행합니다.

100. 백업을 정기적으로 테스트하십시오.

 

 

참고사이트 : https://programmerclick.com/article/97181173517/

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※