아리운 CPU 감지 중 MySQL 이 너무 높은 문제를 어떻게 해결합니까?

친구 호스트 (Windows 2003+IIS+PHP+MYSQL) 최근 MYSQL 서비스 프로세스 (mysqld-nt.exe) 의 CPU 사용률은 항상 100% 입니다. 이 호스트에는 약 10 개의 데이터베이스가 있으며 각각 10 개의 웹 사이트에 의해 호출됩니다. 한 친구의 테스트에 따르면 사이트 A 로 인해 mysqld-nt.exe 의 CPU 사용률이 매우 높았습니다. 이 사이트가 IIS 에서 중지되면 CPU 사용률이 낮아집니다. 활성화되면 바로 오른다.

MYSQL CPU 사용량 100% 솔루션

오늘 아침에 나는 자세히 검사했다. 현재 이 사이트는 7 일 평균 is 2000 으로 조회수가 3 만 원 안팎이다. 현재 사이트 A 에서 사용하는 데이터베이스는 39 개의 테이블, 606,5438+0,000 개의 레코드로 45MB 의 공간을 차지하고 있습니다. 이 수치에 따르면 MySQL 은 이렇게 높은 자원을 차지할 수 없다. 따라서 서버에서 이 명령을 실행하고 MySQL 의 현재 환경 변수를 output.txt 파일로 출력합니다.

D: \ web \ MySQL > Mysqld.exe- & gtOutput.txt 가 tmp_table_size 의 값이 기본적으로 32M 인 것을 발견하도록 도와주므로 tmp_table_size 를 200M 로 지정하여 My.ini 를 수정합니다

D: \ web \ MySQL > 메모장 c:\windows\my.ini

[mysqld]

Tmp _ table _ size = 200M 미터

그런 다음 MySQL 서비스를 다시 시작합니다. CPU 사용률이 약간 떨어졌습니다. 이전의 CPU 활용도 파형도는 100% 의 직선이었고 지금은 97% 에서 100% 사이로 변동하고 있습니다. 이는 tmp_table_size 매개변수를 튜닝하면 MYSQL 성능이 향상된다는 것을 의미합니다. 그러나 문제는 아직 완전히 해결되지 않았다.

그런 다음 myshell 의 셸 명령줄로 들어가 show processlist 를 호출하여 MySQL 에서 현재 사용 중인 SQL 문을 확인합니다.

Mysql & gt 는 프로세스 목록을 표시합니다.

이 명령을 반복해서 호출했을 때, 프로세스 목록에 사이트 A 의 두 개의 SQL 문이 자주 나오는데, 그 구문은 다음과 같습니다.

T 1.pid, t2.userid, t3.count, t 1. datefrom _ 마이데이터 를 t/kloc-0-으로 선택합니다

왼쪽 JOIN _myuser 는 t65438 의 T3+0.userid = t3.userid left join _ 마이데이터 _ body 는 t65438 의 T2+0.pid = t3.pody 입니다

한도 0, 15

Show columns 를 호출하여 세 테이블의 구조를 확인합니다.

Mysql & gt 는 _myuser 의 열을 표시합니다.

Mysql & gt 는 _ 마이데이터 열을 표시합니다.

Mysql & gt 는 _ 마이데이터 _ body 의 열을 표시합니다.

마지막으로 문제가 발견되었습니다. _ 마이데이터 테이블은 PID 기반 기본 키만 설정하고 userid 는 색인화하지 않습니다. SQL 문의 첫 번째 왼쪽 JOIN ON 절에서 다음을 수행합니다.

Leftjoin _ my 사용자의 userid 는 t3on t1.userid = t3.userid _ 마이데이터 참여 조건 비교 연산입니다. 그래서 필드 userid 를 기준으로 _ 마이데이터 테이블에 대한 색인을 만들었습니다.

MySQL & GT Alter Table ` _ 마이데이터 ` addindex (` userid `) 이 색인화된 후 CPU 는 즉시 약 80% 로 떨어졌습니다. 문제가 발견된 것을 보고 show processlist 에서 반복되는 다른 SQL 문을 확인했습니다.

선택 개수 (*)

From _ 마이데이터 t 1, _ 마이데이터 _ key 는 T2 입니다

여기서 t 1.pid = t2.pid 및 t2.keywords =' 공작새' 입니다

_ 마이데이터 _ 키 테이블의 구조를 검사한 후 PID 에 대한 기본 키만 만들어졌으며 키는 색인화되지 않았습니다. _ 마이데이터 _ key 현재 330,000 개의 레코드가 있습니다. 이상하게도 인덱스 없이 33 만 개의 레코드를 검색하는 데 많은 CPU 시간이 걸리지 않습니다. 이 시계의 검색에 문제가 있는 것 같습니다. 따라서 _ 마이데이터 _ key 테이블도 필드 키워드에 따라 색인화됩니다.

MySQL & GT Alter Table ` _ 마이데이터 _ 키 ` add Index (` Keywords `) 이 색인이 만들어지자 CPU 가 50% 에서 70% 사이로 급락했다.

Show prosslist 를 다시 호출하면 사이트 a 의 SQL 호출이 결과 목록에 거의 나타나지 않습니다. 하지만 이 호스트가 몇 개의 Discuz 포럼 프로그램을 실행하는 것을 발견했고, Discuz 포럼의 여러 테이블에도 이 문제가 있었다. 그래서 동시에 해결했고, CPU 사용률이 다시 떨어졌습니다. (2007 년 7 월 9 일 참고: 나중에 discuz 포럼의 구체적인 최적화 과정에 대한 문장 한 편을 더 썼습니다. 자세한 내용은 천만 개의 기록이 있는 Discuz 를 참고하세요! 포럼은 MYSQL CPU 100% 최적화 노트/dev/server/2007 0701-discuz-MySQL-CPU-/kr 로 이어졌다

Tmp_table_size 값을 늘립니다. Mysql 프로필에서 tmp_table_size 의 기본 크기는 32M 입니다. 준비 테이블이 이 크기를 초과하면 MySQL 은 테이블 tbl_name 이 꽉 찬 형식으로 오류를 생성합니다. 다수의 고급 그룹핑 질의를 수행하는 경우 tmp_table_size 값을 늘립니다. 이것은 MySQL 공식이이 옵션에 대한 설명입니다.

Tmp _ 테이블 _ 크기

이 변수는 메모리에 있는 임시 테이블의 최대 크기를 결정합니다. 테이블이 너무 커지면 디스크에 MYISAM 테이블이 생성됩니다. 가능한 경우 쿼리를 최적화하여 준비 테이블 사용을 피하지만 불가능한 경우 준비 테이블이 항상 메모리에 저장되도록 합니다. 준비 테이블이 있는 쿼리의 프로세스 목록을 관찰하고 해결 시간이 너무 길면 tmp_table_size 에 추가해야 할 초기 경고를 줄 수 있습니다. 메모리도 스레드에 따라 할당됩니다. 예를 들어, 서버 한 대의 메모리를 32MB (기본값) 에서 64MB 로 늘리고 즉시 효력을 발휘했습니다. 빠른 쿼리 구문 분석으로 인해 항상 더 적은 스레드가 활성화되므로 서버와 사용 가능한 메모리에 도움이 됩니다.

WHERE, JOIN, MAX (), MIN (), ORDER BY 등의 절에 있는 조건 판단에 사용되는 필드의 인덱스 INDEX 를 작성합니다. 색인은 열에서 특정 값을 가진 행을 빠르게 찾는 데 사용됩니다. 색인이 없는 경우 MySQL 은 첫 번째 레코드로 시작한 다음 관련 행을 찾을 때까지 전체 테이블을 읽어야 합니다. 테이블이 클수록 시간이 더 많이 걸립니다. 테이블에 조회된 열의 인덱스가 있는 경우 MySQL 은 모든 데이터에 관계없이 한 위치에서 데이터 파일의 중간에 빠르게 도달할 수 있습니다. 테이블에 1000 행이 있는 경우 순차 읽기보다 100 배 이상 빠릅니다. 모든 MySQL 인덱스 (기본 인덱스, 고유 인덱스 및 인덱스) 는 b 트리에 저장됩니다. Mysql 을 기반으로 문서 개발:

색인 색인은 다음과 같은 용도로 사용됩니다.

WHERE 절과 일치하는 행을 빠르게 찾습니다.

조인을 수행할 때 다른 테이블에서 행이 검색됩니다.

특정 인덱스 열의 MAX () 또는 MIN () 값을 찾습니다.

사용 가능한 키의 맨 왼쪽 접두어에 대해 정렬 또는 그룹화를 수행하는 경우 (예: order by key _ part _ 1, key _ part _ 2) 테이블을 정렬하거나 그룹화합니다. 모든 키 값이 DESC 를 따르는 경우 키는 반대 순서로 읽혀집니다.

경우에 따라 데이터 파일을 조회하지 않고 조회를 최적화하여 값을 검색할 수 있습니다. 일부 테이블에서 사용하는 모든 열이 숫자 열이고 일부 키의 맨 왼쪽 접두사를 구성하는 경우 인덱스 트리에서 이러한 값을 더 빨리 검색할 수 있습니다. 다음 SELECT 문을 실행한다고 가정합니다.

Mysql & gtSELECT * FROM tbl_name 여기서 col 1=val 1 및 col2 = val2 col 1 인 경우 Col 1 및 col2 에 별도의 단일 행 및 열 인덱스가 있는 경우 최적기는 찾은 행 수가 적은 인덱스를 결정하고 이를 사용하여 행을 추출함으로써 보다 제한적인 인덱스를 찾습니다.

개발자는 SQL 데이터 테이블 디자인을 할 때 반드시 명확하게 고려해야 한다.