데이터베이스를 최적화하는 방법

데이터베이스가 너무 느리게 실행되는 이유

첫 번째 요점은 하드웨어가 너무 오래되었다는 것입니다

하드웨어의 세 가지 측면에 대해 주로 이야기합니다. 네트워크 카드, 컴퓨터실 네트워크 등에 대해서는 글이 길어서 하나씩 소개하지는 않겠습니다.

먼저 MySQL의 CPU 활용 특성을 살펴보겠습니다.

5.1은 4코어, 5.5는 24코어, 5.6은 64코어를 활용할 수 있습니다

예를 들어 MySQL5.6은 48코어를 활용할 수 있습니다. 위의 경우 잘 돌아가고 64 CORE를 사용할 수 있습니다 (48CORE-64CORE 사이, 공식 발표는 48 CORE이며 실제로 테스트했는데 64 CORE에 도달할 수 있습니다).

MySQL 5.6은 48코어 이상을 사용할 수 있습니다.

MySQL 5.1은 이전에는 최대 4코어를 사용할 수 있습니다.

현재 일반 프로덕션 환경 서버는 32CORE 이상입니다.

그래서 저는 여러분의 회사 서버가 코어가 4개나 코어가 1개뿐인 아주 오래된 서버를 사용하고 있지 않는 한 여기 있는 모든 사람들에게 가능한 한 MySQL5.5 또는 MySQL5.6을 사용하도록 권장합니다.

5.1 이전(5.0과 동일)이기 때문에 내부 코드에 하드코딩되어 있고 innobase 스토리지 엔진을 기반으로 했기 때문에 데이터베이스의 하드웨어 활용도가 좋지 않았습니다. InnoDB 엔진으로 진화한 이후에는 훨씬 좋아졌습니다.

각 연결은 스레드(스레드 풀 아님)이며 각 쿼리는 하나의 코어만 사용할 수 있습니다.

또한 MySQL에서는 각 쿼리가 하나의 CPU만 사용할 수 있습니다.

Oracle은 병렬 SQL과 병렬 쿼리를 사용합니다. 이런 종류의 기능은 MySQL에 존재하지 않습니다.

실행 계획 캐시 없음(SQL 실행 계획 사전 컴파일 없음)

둘째, MySQL 내부에는 SQL 사전 컴파일이 없습니다. 따라서 오라클의 메모리 구조에는 라이브러리 캐시와 같은 구조가 없습니다. 따라서 MySQL에는 하드 구문 분석만 있고 소프트 구문 분석은 물론이고 소프트 구문 분석도 없습니다.

MySQL은 연결 수가 늘어날수록 성능 저하가 발생합니다

이것도 MySQL의 결점이지만 MySQL 버전이 발전하면서 많은 솔루션이 등장했습니다.

예: 공식적으로 출시된 스레드 풀(TP라고 함). 동시 연결 수가 너무 많은 문제를 해결하기 위한 것이지만 이는 MySQL의 추가 구성 요소이며 공식 TP를 구입하려면 추가 비용이 필요합니다.

그리고 비슷한 문제를 해결하기 위해 OneSQL 미들웨어를 개발한 중국의 Lou Fangxin이라는 사람이 있습니다.

Result 캐시가 있지만 쓸모가 없습니다.

MySQL에도 Oracle과 비슷한 Query Cache라는 결과 캐시가 있지만 상대적으로 쓸모가 없어 거의 사용되지 않습니다.

실제 프로덕션 환경은 대부분 OLTP 시스템이기 때문에 업데이트 및 수정 작업이 잦습니다. 이 Query Cache는 데이터가 자주 업데이트되고 수정되는 환경에서 사용되므로 일반적으로 MySQL의 성능이 크게 저하됩니다. 사용된.

이제는 MySQL을 사용하기 때문에 기본적으로 InnoDB 스토리지 엔진을 사용합니다. 이전 MyISAM 엔진은 거의 사용하지 않습니다. (스토리지 엔진이 뭐죠? 모르시면 gg)

트랜잭셔널 스토리지 엔진이기 때문에 InnoDB 엔진에서 이 Query Cache를 켤 필요는 없고, InnoDB를 사용한다는 것은 트랜잭션 처리 기능, 빈번한 데이터 업데이트 및 수정이 확실히 발생합니다.

MySQL의 메모리 활용 특성을 다시 살펴보겠습니다

64비트 운영체제를 사용하는 서버는 메모리((2^64-1)/1024/1024/1024)G

초고속 동시 사용이 가능합니다. 환경에서는 기본적으로 메모리 캐싱에 의존합니다. 디스크에 대한 IO 영향을 줄이기 위해

일반적으로 메모리는 실제 데이터의 15%-20%에 따라 계획됩니다. 특히 데이터가 많이 사용되는 경우 더 큰 비율을 고려해야 합니다. 캐싱 데이터

우리는 보통 이 데이터의 15~20%를 사용합니다. 이를 핫 데이터라고 합니다. (이것도 일반적인 경험치입니다)

예를 들어 MySQL의 전체 데이터 용량을 약 500G로 추정한다면 MySQL이 제공하는 메모리는 75G(500*0.15)일 수 있으므로 128G가 필요할 수 있습니다. 왼쪽 및 오른쪽 메모리가 있는 서버.

또한 QQ Farm과 같은 일부 기업에서는 특히 핫하고 대용량의 핫 데이터(15%~20% 범위를 크게 초과할 수 있음)를 보유하게 됩니다.

QQ팜, 해피팜 등 음식 훔치기 게임은 누구나 한 번쯤 해봤을 거라 믿습니다. (12306 티켓 예매 사이트도 있습니다)

이런 유형의 비즈니스는 데이터가 핫할 때 기본적으로 100% 핫 데이터라는 특징이 있습니다. 예를 들어, 모두가 QQ Farm을 플레이하면 찾아옵니다. 그들은 매일 야채 한 줌을 훔치러 가끔 찾아옵니다. 많은 사람들이 한밤중에 일어나 화장실에 갈 때 야채 한 줌을 훔칩니다.

따라서 이러한 유형의 비즈니스를 위해서는 MySQL 데이터베이스의 메모리 구성을 늘려야 합니다. 15~20%로는 충분하지 않습니다.

요약: 일반 업무의 15%~20%는 사용자 센터, 주문, 기타 일반 업무 등 핫 데이터를 계획하는 데 사용됩니다. 일부 특수 사업의 경우 구체적인 상황을 자세히 분석해야 합니다.

Query 응답 시간을 기준으로 할당 안내 가능

이런 대규모 온라인 아키텍처(대규모 데이터베이스 계획 및 설계)를 수행할 때

SQL 쿼리의 응답 시간도 매우 중요한 지표입니다.

이러한 대규모 시스템에서는 동시에 온라인으로 비즈니스를 수행하려면 수백만 또는 수천만 명의 사용자를 수용해야 합니다. SQL 쿼리(쿼리)의 응답 시간은 엄격하게 제어되어야 합니다. 얼마나 많은 시간 안에 통제될 것인가.

예를 들어 핵심 라이브러리의 경우 쿼리의 응답 시간(평균 응답)이 30ms 미만이어야 합니다. 30ms를 초과하는 경우 데이터베이스가 부하 한도에 도달한 것으로 판단하여 데이터베이스를 확장해야 합니다.

또한, 이 쿼리 응답 시간에 대한 장기적인 지표 모니터링이 필요합니다.

이것이 핵심 라이브러리입니다. 로깅 라이브러리와 같이 덜 중요한 보조 라이브러리가 있거나 성능 요구 사항이 너무 높지 않은 일부 라이브러리가 있는 경우 쿼리 응답 시간을 1초 또는 2초 이내로 완화할 수 있습니다.

비즈니스의 중요성에 따라 이 쿼리 응답 시간의 임계값을 설정하세요.

이것은 매우 중요한 지침 원칙입니다. 쿼리 응답 시간을 기준으로 성능 용량을 계획하세요.

용량에는 성능 용량과 공간 용량의 두 가지 유형이 있습니다. 공간 용량은 매우 간단합니다. 즉, SIZE 데이터를 몇 개 배치하는지, T.

성능 용량이 더 중요하며 비즈니스 압박과 부하를 처리할 수 있는지 여부를 결정합니다.

모두가 기억해야 할 점: 처리하려는 비즈니스가 수백 명의 사용자가 아닌 수백만 명의 활성 사용자라면 성능이 가장 중요하며 성능 측면에서 비즈니스 요구 사항을 충족하는 것이 가장 중요합니다.

아무리 기능이 훌륭하고 제품이 아무리 우수하더라도 성능이 타의 추종을 불허하면 다른 모든 것은 말도 안되는 일입니다. 수십만 명의 사람들이 전체 시스템과 프로젝트를 몇 초 만에 중단시킬 수 있습니다. 회사는 눈이 멀 것이다.

그렇게 애쓰신 유저분들도 대량으로 손실이 나고, 손실도 클 것입니다.

성능이 기본입니다. 전체 아키텍처는 성능이 이를 견딜 수 있는 경우에만 의미가 있습니다. 성능이 만족스럽지 않으면 나중에 고가용성을 고려하는 것은 쓸모가 없습니다.

MySQL의 디스크 활용 특성

Binlog, redo log, undo log 순차 IO

MySQL에는 다양한 IO 유형이 있습니다.

binlog, redolog, undolog는 모두 순차적 IO 쓰기입니다.

이런 것들을 SSD에 넣을 필요가 없습니다. 기존 기계식 디스크에 순차적으로 쓰는 것도 매우 빠릅니다. 게다가 SSD에는 쓰기 손실과 쓰기 수명의 문제가 있습니다. SSD 우수한에 넣을 필요가 없습니다. 기존 SAS 디스크에 넣는 것만으로도 충분합니다. SSD를 넣을 필요가 없습니다.

SSD는 데이터 파일을 저장하는 데 사용됩니다. 데이터파일에서 발생하는 IO의 대부분은 Random IO이기 때문에 SSD에서는 Random IO를 실행하는 것이 매우 유리합니다. SSD 솔리드 스테이트 디스크 + 기존 디스크 SAS 디스크를 함께 혼합하여 저장합니다. 또한 백업 디스크로 SSD를 사용하지 마십시오.

데이터파일 무작위 IO와 순차 IO 결합

순차 IO가 항상 더 빠릅니다. 데이터베이스 설계에서 당신이 훌륭한 DBA인지 훌륭한 설계자인지를 결정하는 것은 무작위 IO를 줄이면서 비즈니스를 최대한 순차 IO로 설계할 수 있는지 여부에 달려 있습니다. 예를 들어 친구 관계 비즈니스를 디자인할 때 순차적 IO를 통해 쿼리로 친구 관계를 꺼낼 수 있기를 바랍니다. 그러면 어떻게 디자인해야 할까요?

MySQL의 InnoDB에서는 InnoDB의 기능인 클러스터형 인덱스 테이블을 활용할 수 있습니다. (오라클의 IOT와 유사)

이 기능을 사용하면 사용자의 친구 데이터를 한 페이지 또는 여러 인접 페이지에 최대한 수집할 수 있습니다. 읽기 시 순차 읽기 IO가 가능해 성능이 크게 향상됐다.

친구 관계 테이블의 구조는 다음과 같습니다(전제 테이블은 InnoDB 엔진입니다):

owner_id friend_id(친구 ID)

위 두 필드는 InnoDB의 기본 키로 사용됩니다. 클러스터형 인덱스이므로 이 두 필드를 읽는 것은 확실히 순차 IO가 수행합니다.

이전의 데이터베이스 설계 서적에서는 각 테이블에 자동 증가 기본 키에 대한 사양을 추가해야 한다고 항상 언급되어 있습니다. 실제로 사양은 죽었고 위에서 예시한 친구 관계는 살아 있습니다. 자동 증가를 사용하지 않습니다. 기본 키를 기본 키로 사용하는 대신 비즈니스 속성이 있고 자주 읽는 두 개의 비즈니스 필드를 기본 키로 사용하므로 성능이 향상됩니다.

그러므로 공부할 때 이 책에 나오는 규범과 규정을 외우지 말고, InnoDB의 내부 원리를 배우는 등 어떤 것의 원리를 실제로 이해하고 실제 작업에서 지원을 받아야 합니다. 원리 및 원리 사용 하나의 예에서 추론을 이끌어냅니다.

InnoDB의 원리는 엄청난 지식이며 시간이 지남에 따라 학습해야 합니다. 제 공식 계정에 좀 더 관심을 가져주시면 되며, InnoDB에 관한 일부 기사도 속속 공개될 예정입니다.

OLTP 비즈니스에는 더 많은 랜덤 IO가 필요합니다

캐싱에 메모리를 사용할 수 있으므로 랜덤 IO가 줄어듭니다

OLAP 비즈니스에는 더 많은 순차 IO가 필요합니다

메모리 캐시는 별로 유용하지 않습니다

MySQL 5.6 이전에는 페이지 수정이 지원되지 않습니다. 기본값은 16K입니다.

MySQL5.6 이후 변경 가능합니다. 이 매개변수는 innodb_page_size이지만 MySQL5.6은 8K 또는 4K로만 변경 가능하며 MySQL5.7 이상까지는 32K 또는 64K로 변경할 수 없습니다.

OLAP 시스템의 경우 OLAP 시스템은 상대적으로 큰 쿼리가 있고 많은 데이터를 스캔하므로 페이지가 클수록 성능 향상에 도움이 됩니다.

두 번째 포인트: 데이터베이스 디자인이 좋지 않습니다

예를 들어 트리거, 파티션, 많은 저장 프로시저, 함수 등과 같은 데이터베이스 기능이 많이 사용됩니다.

우리는 흔히 작은 것이 아름답다는 말을 합니다. 이는 단순함이 최고라는 뜻입니다. 데이터베이스의 모든 기능을 사용하게 되면 자연스럽게 데이터베이스의 성능이 저하되고, 버그 및 근본적인 오류가 발생할 확률이 높아집니다.

그래서 좋은 데이터베이스 프로젝트 디자인은 작지만 아름답고 간결하며 단순하다는 것을 모두가 이해해야 합니다. 또한 데이터베이스는 전체 프로젝트의 일부일 뿐이며 전체 프로젝트에서 애플리케이션 코드를 사용하여 트리거 및 저장 프로시저와 같은 기능을 확실히 구현할 수 있습니다.

그래서 우리는 MySQL을 사용할 때 MySQL의 모든 기능을 사용하기보다는 테이블, 인덱스, 트랜잭션과 같은 강력한 기능만 사용합니다.

또 다른 점은 MySQL 5.6 이전에는 프로덕션 환경의 기본 데이터베이스에서 하위 쿼리가 허용되지 않았다는 것입니다.

MySQL 5.6 이전의 하위 쿼리 성능은 특히 나빴습니다. (구문은 지원되지만 SQL 성능은 매우 낮습니다.)

예를 들어, 현재 Oracle을 사용하고 있고 Oracle을 MySQL로 마이그레이션하려는 경우 MySQL 5.6 버전을 사용하는 것이 좋습니다. MySQL 5.6은 하위 쿼리의 지원과 성능을 크게 향상시켰습니다.

MySQL 5.6 하위 쿼리의 성능이 크게 향상됩니다.

세 번째 포인트: 프로그램 작성이 형편없다

이런 건 DBA 출신 학생들이 경험해 봤을 거라 생각해요. 중소기업에서는 프로그래머의 수준이 천차만별이죠.

특히 업계에 갓 입문한 프로그래머(신입생)를 많이 만나면, 이 업계에 갓 입문한 프로그래머들 역시 매우 긴급한 요구 사항을 수행할 가능성이 더 높습니다. 이런 환경에서 개발된 프로그램은 상상하기 어렵습니다.

물론 우리 프로그래머의 잘못은 아니므로 그들을 비난할 수는 없습니다.

위 현상의 가장 큰 이유는 국내 개발 환경 때문일 수 없습니다. 개발 요구 사항이 시급하고(제품이 매일 활성화됨) 프로그래머가 바쁘게 일하기 때문입니다(장기적인 야근). 비즈니스 프로그램을 구현하느라 바쁘고 프로그램을 최적화할 시간이 전혀 없습니다.

물론 이런 환경에서는 우리 DBA들에게는 기회입니다. 프로그래머가 작성한 잘못된 SQL과 복잡한 SQL로 인해 시스템이 느려지거나 심지어 충돌이 발생했습니다. 그런 다음 우리 DBA가 개입하여 이러한 잘못된 SQL과 느린 SQL을 최적화하고 변환했으며 시스템은 정상으로 돌아가 점점 안정되었습니다. 이는 또한 매우 성취감을 주는 일이며 동료와 리더들로부터 존경을 받게 될 것입니다.

동시에 DBA는 프로그래머를 위한 교육을 강화하고 좋은 SQL을 빠르게 작성하는 능력을 향상시킬 수도 있습니다. 더 적은 시간을 소비하고 더 나은 성능과 더 원활한 성능으로 SQL 문을 작성할 수 있습니다. 이를 통해 DBA의 부담도 줄일 수 있다.

저는 개인적으로 프로그래머들과 교육에 대해 이야기하는 것을 선호합니다. 첫째, 기술을 교환하면 누구나 무언가를 얻을 수 있습니다. 둘째, 직장에서 앞으로 협상해야 할 문제가 있으면 도움이 됩니다. 논의하기 쉽습니다. 이것은 그들에게 식사를 대접하는 것보다 낫습니다.

잘못 작성된 프로그램에 대해 주로 다음과 같은 해결책이 있습니다.

애플리케이션에서 데이터베이스 연결 풀을 사용하도록 하세요. 특히 JAVA를 기반으로 개발된 대규모 동시성 애플리케이션에서는 연결 풀을 사용해야 합니다.

연결 풀을 사용하면 애플리케이션에서 연결 수를 제한할 수 있다는 장점이 있습니다. 또한, MySQL에 대한 연결을 생성하는 데 드는 비용도 큽니다. 새로운 연결은 MySQL이 스레드를 생성하는 것과 동일합니다.

저는 방금 연결 수가 증가함에 따라 MySQL의 성능 저하가 발생할 것이라고 언급했습니다.

프로그램 코드를 작성한 학생들은 일반 PC 노트북(보통 4CORE)에서 400개의 스레드를 생성하고 각 스레드가 1+1+1+1+...간단한 작업을 수행한다는 것도 알아야 합니다. PC가 멈췄는지 여부. PC의 CPU가 거의 가득 찼음을 알 수 있습니다. 감히 600개의 스레드를 만들면 컴퓨터가 곧 다시 시작됩니다. 스레드 오버헤드로 인해 CPU가 가득 차 있기 때문입니다.

복잡한 SQL문

앞서 말했듯이 프로그래머가 작성한 SQL은 결국 너무 바빠서 이 SQL의 성능과 운영을 고려하지 않는 경우가 많습니다. 어떤 경우에는 프로그래머가 엮은 SQL이 전체 시스템을 직접 다운시킬 수도 있다.

간단한 예를 들어 보겠습니다. 우리 애플리케이션 중 하나가 데이터베이스에 대해 10개의 연결을 생성합니다(최대 연결 수 = 10). 이 10개의 연결 각각은 동시에 동일한 복잡한 SQL을 실행합니다. 이 복잡한 SQL을 실행하려면 이 10개의 연결은 10분 이내에 이 복잡한 SQL만 실행할 수 있으며 다른 모든 후속 SQL은 차단됩니다.

결과적으로 대부분의 애플리케이션은 10분 동안 사용할 수 없게 되겠죠? 그리고 눈사태를 일으키고 시스템이 붕괴될 수도 있습니다.

복잡한 SQL의 최적화도 DBA에게 매우 중요한 작업입니다. 이러한 복잡한 SQL, 느린 SQL, 잘못된 SQL을 모니터링 방법을 통해 찾아내고 프로그래머에게 최적화 제안을 하는 것이 필요합니다. (DBA는 성능 비교 테스트를 수행해야 합니다.) ), 프로그래머가 코드를 수정해야만 시스템이 교통 정체가 없는 고속도로처럼 원활하고 병렬적으로 실행될 수 있습니다.

그렇다면 우리 회사 프로그래머들은 정말 대단하군요. 죽어도 SQL 코드를 바꾸지 않고, 죽어도 최적화도 안 하고, 통신도 안 하더군요. 그럼 우리는 어떻게 해야 할까요?

아직도 전용 슬레이브 라이브러리(Slave library)를 구축하여 이를 처리할 수 있는 방법이 있습니다. 언제든지 쿼리하도록 라이브러리를 변경할 수 있습니다.

예를 들어 저희 회사를 예로 들면, 보고서를 생성하는 백그라운드 시스템은 쿼리를 위해 슬레이브 데이터베이스에 연결되어 있고, 메인 데이터베이스에는 연결되어 있지 않습니다.

잘못된 로직

전체 테이블 스캔

예: update t set a = a + 1; where 조건을 추가하는 것을 잊었습니다.

시스템이 수백만 명의 온라인 사용자를 지원하려면 SQL 검토 시스템(SQL 검토)을 추가하여 잘못된 논리가 있는 SQL과 전체 테이블 스캔이 있는 SQL을 제거해야 합니다.

SQL은 DBA의 검토 및 승인을 거친 후에만 온라인으로 공개될 수 있습니다.

또한 이런 대규모 업데이트 SQL은 일괄적으로 업데이트해야 하며, 대규모 SQL 작업은 작은 작업으로 나누어서 실행해야 합니다. MySQL에서는 이에 특별한 주의가 필요합니다.

일괄 업데이트하는 이유는 무엇인가요?

이유 1. 위에서 언급했듯이 MySQL 쿼리는 하나의 CORE만 사용할 수 있습니다. SQL 트랜잭션은 너무 크고 복잡하며 실행하는 데 오랜 시간이 걸리므로 쉽게 정체가 발생합니다.

이유 2. 온라인 환경에서 MySQL은 일반적으로 Master/Slave 아키텍처를 가지고 있습니다. Master에서 100만 행의 대규모 업데이트 트랜잭션이 발생하면 SLAVE가 거기에서 정체될 가능성이 높습니다. 단일 스레드 구조로 인해 동기화 지연이 발생합니다.

MySQL은 SQL을 작성하고 빠르게 실행되고 빠르게 제출되는 작은 트랜잭션 SQL을 생성합니다. 각 쿼리가 더 빠르게 완료되고 연결이 더 빠르게 해제됩니다.

위 공유를 기반으로 최적화한 후 데이터베이스가 더 빨라졌나요?

위 내용은 데이터베이스를 최적화하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!