번역자 주: 크기와 로드가 증가함에 따라 MySQL의 성능은 감소하는 경향이 있습니다. MySQL을 원활하게 실행하려면 다음 팁을 기억하십시오.
애플리케이션을 측정하는 방법 중 하나는 성능을 살펴보는 것입니다. 성능 지표 중 하나는 사용자 경험입니다. 이는 일반인의 용어로 "사용자가 원하는 것을 얻기 위해 더 오래 기다려야 하는지 여부"입니다.
이 표시기는 다양한 응용 프로그램에서 변경됩니다. 모바일 쇼핑 앱의 경우 응답 시간은 몇 초를 초과할 수 없습니다. 직원 HR 페이지의 경우 몇 초 정도 더 걸릴 수 있습니다.
성능이 사용자 행동에 어떤 영향을 미치는지에 대한 많은 연구가 있습니다.
79%의 고객이 느린 웹 사이트로 돌아올 가능성이 적습니다.
47%의 소비자가 웹 페이지가 2초 안에 완료되기를 기대합니다. less Loading
사이트를 로드하는 데 3초 이상 걸리면 40%의 사용자가 사이트를 포기합니다.
페이지 로드 시간이 1초 지연되면 7%의 손실과 11%의 페이지 조회수 감소가 발생할 수 있습니다
채택 여부와 관계없이 표준에 관계없이 좋은 애플리케이션 성능이 유지되어야 합니다. 그렇지 않으면 사용자가 불만을 제기할 것입니다(또는 더 나쁜 경우 다른 앱으로 이동). 애플리케이션 성능에 영향을 미치는 요소 중 하나는 데이터베이스 성능입니다. 애플리케이션, 웹 사이트 및 데이터베이스 간의 상호 작용은 애플리케이션의 성능을 확립하는 데 매우 중요합니다.
이 상호 작용의 핵심 구성 요소는 애플리케이션이 데이터베이스를 쿼리하는 방법과 데이터베이스가 요청에 응답하는 방법입니다. 그럼에도 불구하고 MySQL은 가장 널리 사용되는 데이터베이스 관리 시스템 중 하나입니다. 프로덕션 환경에서는 점점 더 많은 기업이 데이터베이스 솔루션으로 MySQL(및 기타 오픈 소스 데이터베이스)을 선택하고 있습니다.
데이터베이스가 쿼리에 빠르게 응답하고 애플리케이션 성능 저하를 최소한으로 유지하는 데 도움이 되는 MySQL을 구성하는 방법에는 여러 가지가 있습니다.
다음은 MySQL 데이터베이스 성능을 최적화하는 데 도움이 되는 몇 가지 기본 팁입니다.
모든 데이터베이스에서 내리는 가장 중요한 두 가지 결정은 애플리케이션 엔터티 간의 관계가 테이블(데이터베이스 스키마)에 매핑되는 방식을 설계하고 애플리케이션이 데이터베이스에서 작동하는 방식을 설계하는 것입니다. 원하는 형식으로 필요한 데이터(쿼리)를 가져옵니다.
복잡한 애플리케이션에는 복잡한 패턴과 쿼리가 있을 수 있습니다. 애플리케이션에 필요한 성능과 확장성을 얻으려면 직관에만 의존하여 쿼리 실행 방법을 이해할 수 없습니다.
무작위 추측과 상상 대신 EXPLAIN 명령을 사용하는 방법을 배워야 합니다. 이 명령은 쿼리를 실행하는 방법을 보여주고 예상할 수 있는 성능과 데이터 크기 변경에 따라 쿼리가 어떻게 확장되는지에 대한 아이디어를 제공합니다.
EXPLAIN 출력을 시각화할 수 있는 MySQL Workbench와 같은 많은 도구가 있지만 이를 이해하려면 여전히 기본 사항을 이해해야 합니다.
EXPLAIN 명령은 두 가지 형식으로 출력을 제공합니다. 구식 표 형식과 더 현대적인 구조의 JSON 문서로 더 자세한 내용을 제공합니다(아래 참조).
mysql> explain format=json select avg(k) from sbtest1 where MySQL 성능을 향상시키는 7가지 팁 소개 between 1000 and 2000 \G
*************************** 1. row ***************************
EXPLAIN: {
“query_block”: {
“select_MySQL 성능을 향상시키는 7가지 팁 소개”: 1,
“cost_info”: {
“query_cost”: “762.40”
},
“table”: {
“table_name”: “sbtest1”,
“access_type”: “range”,
“possible_keys”: [
“PRIMARY”
],
“key”: “PRIMARY”,
“used_key_parts”: [
“MySQL 성능을 향상시키는 7가지 팁 소개”
],
“key_length”: “4”,
“rows_examined_per_scan”: 1874,
“rows_produced_per_join”: 1874,
“filtered”: “100.00”,
“cost_info”: {
“read_cost”: “387.60”,
“eval_cost”: “374.80”,
“prefix_cost”: “762.40”,
“data_read_per_join”: “351K”
},
“used_columns”: [
“MySQL 성능을 향상시키는 7가지 팁 소개”,
“k”
],
“attached_condition”: “(`sbtest`.`sbtest1`.`MySQL 성능을 향상시키는 7가지 팁 소개` between 1000 and 2000)”
}
}
}살펴봐야 할 구성 요소 중 하나는 "쿼리 비용"입니다. . 쿼리 비용은 MySQL이 쿼리 실행의 총 비용 측면에서 이 특정 쿼리의 비용을 고려하고 다양한 요소를 기반으로 한다는 것을 의미합니다.
간단한 쿼리의 쿼리 오버헤드는 일반적으로 1,000 미만입니다. 비용이 1,000에서 100,000 사이인 쿼리는 중간 비용 쿼리로 간주되며 일반적으로 이러한 쿼리를 초당 몇 백 개만 실행하면(수만 개가 아니라) 더 빠릅니다.
비용이 100,000이 넘는 쿼리는 비용이 많이 드는 것으로 간주될 수 있습니다. 일반적으로 이러한 쿼리는 시스템의 단일 사용자인 경우에도 빠르게 실행되지만 대화형 응용 프로그램에서 이러한 쿼리를 얼마나 자주 사용하는지(특히 사용자 수가 증가함에 따라) 신중하게 고려해야 합니다.
물론 이 수치는 성능을 대략적으로 표현한 것일 뿐 일반적인 원칙을 설명합니다. 시스템은 아키텍처와 구성에 따라 쿼리 작업 부하를 더 좋게 또는 더 나쁘게 처리할 수 있습니다.
쿼리 오버헤드를 결정하는 주요 요인은 쿼리가 인덱스를 올바르게 사용하는지 여부입니다. EXPLAIN 명령은 쿼리가 인덱스를 사용하는지 여부를 알려줄 수 있습니다(일반적으로 데이터베이스에서 인덱스가 생성된 방식이나 쿼리 자체가 설계된 방식으로 인해). 그렇기 때문에 EXPLAIN 사용법을 배우는 것이 매우 중요합니다.
인덱스는 쿼리가 스캔해야 하는 데이터베이스의 데이터 양을 줄여 쿼리 효율성을 향상시킵니다. MySQL의 인덱스는 데이터베이스 액세스 속도를 높이고 데이터베이스 제약 조건(예: UNIQUE 및 FOREIGN KEY)을 적용하는 데 사용됩니다.
데이터베이스 색인은 책 색인과 매우 유사합니다. 이는 자체 위치에 저장되며 기본 데이터베이스에 이미 존재하는 정보를 포함합니다. 이는 데이터가 있는 위치를 가리키는 참조 방법 또는 맵입니다. 인덱스는 데이터베이스의 어떤 데이터도 변경하지 않습니다. 그들은 단지 데이터의 위치를 가리킬 뿐입니다.
모든 워크로드에 완벽하게 적합한 지수는 없습니다. 대신, 항상 시스템이 실행 중인 쿼리의 컨텍스트에서 인덱스를 확인해야 합니다.
인덱싱이 잘된 데이터베이스는 더 빠르게 실행될 뿐만 아니라 인덱스가 누락되어도 데이터베이스 크롤링 속도가 느려질 수 있습니다. 누락된 인덱스를 찾아서 추가하려면 EXPLAIN(앞서 언급한 대로)을 사용하십시오. 하지만 주의하세요. 필요하지 않은 색인을 추가하지 마세요! 불필요한 인덱스는 데이터베이스 속도를 저하시킵니다
(MySQL 인덱싱 모범 사례에 대한 소개를 확인하세요).
다른 소프트웨어와 마찬가지로 MySQL에는 동작(및 궁극적으로 성능)을 수정하는 데 사용할 수 있는 구성 가능한 설정이 많이 있습니다. 모든 소프트웨어와 마찬가지로 관리자는 이러한 구성 가능한 설정 중 많은 부분을 간과하고 결국 기본 모드에서 사용하게 됩니다.
MySQL에서 최고의 성능을 얻으려면 구성 가능한 MySQL 설정을 이해하는 것이 중요하며, 더 중요한 것은 이를 데이터베이스 환경에 가장 적합하게 설정하는 것입니다.
기본적으로 MySQL은 프로덕션 규모가 아닌 소규모 개발 설치용입니다. 일반적으로 사용 가능한 모든 메모리 리소스를 사용하고 애플리케이션에 필요한 만큼의 연결을 허용하도록 MySQL을 구성하려고 합니다.
항상 주의 깊게 확인해야 하는 세 가지 MySQL 성능 최적화 설정은 다음과 같습니다.
innodb_ buffer_ pool_size: 버퍼 풀은 캐시된 데이터와 인덱스를 저장하는 데 사용됩니다. 이것이 대용량 RAM을 갖춘 시스템을 데이터베이스 서버로 사용하는 주된 이유입니다. InnoDB 스토리지 엔진만 실행하는 경우 일반적으로 메모리의 80%가 버퍼 풀에 할당됩니다. 매우 복잡한 쿼리를 실행 중이거나 동시 데이터베이스 연결 수가 많거나 테이블 수가 많은 경우 이 값을 한 단계 낮추어 다른 작업에 더 많은 메모리를 할당할 수 있습니다.
InnoDB 버퍼 풀 크기를 설정할 때 너무 크게 설정하지 않도록 주의해야 합니다. 그렇지 않으면 스와핑이 발생합니다. 이는 확실히 데이터베이스 성능에 영향을 미칩니다. 확인하는 쉬운 방법은 Percona Monitoring and Management의 시스템 개요 다이어그램에서 스왑 활동을 보는 것입니다.
그림에 표시된 것처럼 때로는 스왑을 해도 괜찮을 때가 있습니다. 그러나 초당 1MB 이상의 지속적인 스왑 활동이 나타나면 버퍼 풀 크기(또는 기타 메모리 사용량)를 줄여야 합니다.
처음 액세스할 때 innodb_ Buffer_ pool_ size 값을 올바르게 얻지 못하더라도 걱정하지 마세요. MySQL 5.7부터는 데이터베이스 서버를 다시 시작하지 않고도 InnoDB 버퍼 풀의 크기를 동적으로 변경할 수 있습니다.
innodb_ log_ file_ size: 단일 InnoDB 로그 파일의 크기입니다. 기본적으로 InnoDB는 두 개의 값을 사용하므로 이 숫자를 두 배로 늘려 InnoDB가 트랜잭션 내구성을 보장하는 데 사용하는 순환 리두 로그 공간의 크기를 얻을 수 있습니다. 이는 또한 데이터베이스에 대한 변경 사항 적용을 최적화합니다. innodb_ log_ file_ 크기 설정은 절충의 문제입니다. 할당된 다시 실행 공간이 많을수록 쓰기 집약적인 작업 부하의 성능은 향상되지만 시스템의 전원이 꺼지거나 다른 문제가 발생할 경우 충돌을 복구하는 데 시간이 더 오래 걸립니다.
현재 InnoDB 로그 파일 크기로 인해 MySQL 성능이 제한되는지 어떻게 알 수 있나요? 사용 가능한 리두 로그 공간이 실제로 얼마나 사용되는지 보면 알 수 있습니다. 가장 쉬운 방법은 Percona Monitor 및 Management InnoDB Metrics 대시보드를 보는 것입니다. 아래 이미지에서는 사용된 공간이 사용 가능한 리두 로그 공간(빨간색 선으로 표시)과 매우 가깝기 때문에 InnoDB 로그 파일 크기가 충분히 크지 않습니다. 로그 파일 크기는 시스템을 최적으로 실행하는 데 필요한 공간보다 20% 이상 커야 합니다.
MAX_ 연결: 대규모 애플리케이션의 연결 수는 일반적으로 기본값보다 높아야 합니다. 다른 변수와 달리 올바르게 설정되지 않아도 성능 자체에는 문제가 없습니다. 반대로 연결 수가 애플리케이션 요구 사항에 비해 충분하지 않으면 애플리케이션이 데이터베이스에 연결할 수 없습니다. 이는 사용자에게 가동 중지 시간처럼 보입니다. 따라서 이 변수를 올바르게 처리하는 것이 중요합니다.
여러 서버에서 여러 구성 요소가 포함된 복잡한 애플리케이션을 실행하는 경우 필요한 연결 수를 파악하기 어려울 수 있습니다. 다행스럽게도 MySQL을 사용하면 최대 작업 중에 얼마나 많은 연결이 사용되고 있는지 쉽게 확인할 수 있습니다. 일반적으로 애플리케이션에서 사용하는 최대 연결 수와 사용 가능한 최대 연결 수 사이에 최소 30%의 차이가 있는지 확인하려고 합니다. 이러한 수치를 보는 쉬운 방법은 Percona Monitoring and Management의 MySQL 개요 대시보드에서 MySQL 연결 그래프를 사용하는 것입니다. 아래 다이어그램은 사용 가능한 추가 연결 수가 많은 견고한 시스템을 보여줍니다.
한 가지 기억해야 할 점은 데이터베이스가 느리면 애플리케이션이 너무 많은 연결을 생성하는 경우가 많다는 것입니다. 이 경우 단순히 더 많은 연결을 허용하기보다는 데이터베이스의 성능 문제를 해결해야 합니다. 연결이 많을수록 근본적인 성능 문제가 악화될 수 있습니다.
(참고: max_Connections 변수를 기본값보다 훨씬 높게 설정하는 경우 테이블 캐시 크기 및 열려 있는 MySQL 파일 수와 같은 다른 매개변수 증가를 고려해야 하는 경우가 많습니다. 그러나 이는 범위를 벗어납니다. 이 기사.)
최근 몇 년 동안 SSD(SolMySQL 성능을 향상시키는 7가지 팁 소개 State Disk)로의 전환이 이루어졌습니다. SSD는 회전하는 하드 드라이브보다 훨씬 빠르지만 여전히 RAM의 데이터와 비교할 수는 없습니다. 이러한 차이는 스토리지 성능 자체뿐만 아니라 디스크 또는 SSD 스토리지에서 데이터를 검색할 때 데이터베이스가 수행해야 하는 추가 작업에서도 발생합니다.
최신 하드웨어 개선으로 클라우드에서 실행하든 자체 하드웨어를 관리하든 데이터베이스를 메모리에 저장하는 것이 점점 더 가능해졌습니다.
더 좋은 소식은 인메모리의 성능 이점을 최대한 활용하기 위해 모든 데이터베이스를 메모리에 넣을 필요가 없다는 것입니다. 작업 데이터 세트(가장 자주 액세스하는 데이터)를 메모리에 저장하기만 하면 됩니다.
데이터베이스의 어느 부분이 메모리에 유지되어야 하는지에 대해 10%에서 33% 범위의 특정 숫자를 제공하는 기사를 본 적이 있을 것입니다. 사실, "모든 경우에 적합한 단일 크기" 수치는 없습니다. 최상의 성능 이점을 위해 메모리에 적합한 데이터의 양은 작업 부하에 따라 다릅니다. 특정 "마법의" 숫자를 찾는 대신 데이터베이스가 안정된 상태(보통 시작 후 몇 시간)에서 실행되고 있는지 I/O를 확인하세요. READ를 살펴보세요. 데이터베이스가 메모리에 있으면 READ가 완전히 제거될 수 있기 때문입니다. 사용 가능한 메모리의 양에 관계없이 쓰기는 항상 이루어져야 합니다.
아래에서는 Percona가 모니터링하고 관리하는 InnoDBMetrics 대시보드의 InnoDB I/O 그래프에서 I/O를 볼 수 있습니다.
위 차트에서는 초당 최대 2,000회의 I/O 작업의 최고치를 볼 수 있습니다. 세트가 메모리에 맞지 않습니다.
데이터베이스가 메모리에 맞지 않더라도(그렇지 않더라도) 쓰기를 처리하고 데이터베이스가 예열될 때 성능 문제를 방지하려면 여전히 빠른 저장소가 필요합니다. 다시 시작). 오늘날 SSD는 빠른 스토리지의 대명사입니다.
일부 "전문가"는 여전히 비용이나 신뢰성의 이유로 회전 디스크(기계식 디스크) 사용을 옹호합니다. 솔직히 말해서, 이러한 주장은 데이터베이스 운영에 있어 종종 구식이거나 완전히 잘못된 것입니다. 오늘날 SSD는 프리미엄 가격으로 인상적인 성능과 안정성을 제공합니다.
그러나 모든 SSD가 적합한 것은 아닙니다. 데이터베이스 서버의 경우 데이터를 보호할 서버 워크로드용으로 설계된 SSD를 사용해야 합니다(예: 정전 시). 데스크톱 컴퓨터와 노트북용으로 설계된 상업용 SSD를 사용하지 마세요.
NVMe 또는 Intel OpTan 기술을 통해 연결된 SSD는 최고의 성능을 제공합니다. SAN, NAS 또는 클라우드 블록 장치로 원격으로 연결된 경우에도 SSD는 회전 디스크에 비해 여전히 우수한 성능을 제공합니다.
고성능 서버에도 한계가 있습니다. 확장 방법에는 위쪽과 바깥쪽의 두 가지 방법이 있습니다. 확장한다는 것은 더 많은 하드웨어를 구입한다는 것을 의미합니다. 이는 비용이 많이 들 수 있으며 하드웨어는 빠르게 쓸모 없게 됩니다. 더 많은 로드를 처리하기 위해 확장하면 여러 가지 이점이 있습니다.
수평적 확장에는 이점이 있지만 특정 제한 사항도 있습니다. 확장에는 데이터 동기화를 위해 기본 MySQL 복제 또는 Percona XtraDB Cluster와 같은 복제가 필요합니다. 하지만 그 대가로 추가 성능과 고가용성을 얻을 수 있습니다. 더 큰 확장이 필요한 경우 MySQL 샤딩을 사용하세요.
또한 클러스터 아키텍처에 연결된 애플리케이션이 일반적으로 일부 프록시 서버 및 로드 밸런서(예: ProxySQL 또는 HAProxy)를 통해 필요한 데이터를 찾을 수 있는지 확인해야 합니다.
규모 확장을 계획할 때 너무 일찍 규모를 확장하지 마세요. 분산 데이터베이스로 작업하는 것은 더 복잡한 경향이 있습니다. 최신 하드웨어와 MySQL 서버를 사용하면 단 하나의 서버를 사용하여 좋은 경험을 할 수 있습니다. 최근 출시된 MySQL 8 릴리스 후보는 단일 시스템에서 200만 개 이상의 단순 쿼리를 처리할 수 있음을 보여줍니다.
최고의 시스템은 관찰 가능성을 염두에 두고 설계됩니다. MySQL도 예외는 아닙니다.
MySQL 환경을 설정하고 실행하고 적절하게 조정한 후에는 설정만 하고 관리하지 않을 수 없습니다. 데이터베이스 환경은 시스템 또는 워크로드 변경에 의해 영향을 받을 수 있습니다. 트래픽 급증, 애플리케이션 오류, MySQL 오류 등의 예상치 못한 상황에 대비하세요. 이런 일들은 일어날 수 있고 일어날 것입니다.
문제가 발생하면 신속하고 효율적으로 해결해야 합니다. 이를 수행하는 유일한 방법은 일종의 모니터링 솔루션을 설정하고 적절하게 초기화하는 것입니다. 이를 통해 프로덕션에서 실행되는 동안 데이터베이스 환경에서 무슨 일이 일어나고 있는지 확인하고 문제가 발생할 때 서버 데이터를 분석할 수 있습니다. 이상적으로, 시스템을 사용하면 문제가 발생하기 전이나 사용자가 영향을 볼 수 있는 지점까지 발전하기 전에 문제를 예방할 수 있습니다.
모니터링 도구에는 MySQL Enterprise Monitor, Monyog 및 Percona Monitoring and Management(PMM)가 포함되며, 후자에는 무료 오픈 소스라는 추가 이점이 있습니다. 이러한 도구는 모니터링 및 문제 해결을 위한 뛰어난 조작성을 제공합니다.
점점 더 많은 기업이 대규모 프로덕션 환경에서 비즈니스 데이터를 관리하고 제공하기 위해 오픈 소스 데이터베이스(특히 MySQL)를 사용함에 따라 이러한 데이터베이스를 최적화하고 최상의 상태로 실행하는 데 집중해야 합니다. 비즈니스 목표에 중요한 모든 것과 마찬가지로 데이터베이스 성능은 비즈니스 목표나 결과를 만들거나 깨뜨릴 수 있습니다. MySQL은 애플리케이션과 웹 사이트에 고품질 데이터베이스 솔루션을 제공할 수 있는 데이터베이스 솔루션이지만, 병목 현상과 성능 문제를 찾아 방지하려면 요구 사항에 맞게 조정하고 모니터링해야 합니다.
Peter Zaitsev는 엔터프라이즈급 MySQL 및 MongoDB 솔루션 및 서비스 제공업체인 Percona의 공동 창립자이자 CEO입니다. O'Reilly에서 출판한 "High Performance MySQL"은 가장 인기 있는 MySQL 성능 관련 서적 중 하나입니다. Zaitsev는 PerconaDatabasePerformanceBlog.com에서 자주 블로그를 운영하고 전 세계 컨퍼런스에서 연설합니다.
이 기사에서는 MySQL 성능 향상을 위한 7가지 팁을 자세히 소개합니다. 더 많은 관련 내용을 보려면 PHP 중국어 웹사이트를 주목하세요.
관련 권장 사항:
위 내용은 MySQL 성능을 향상시키는 7가지 팁 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
