100% CPU 사용량을 보여주는 MySQL

Question

Cpanel과 함께 설치된 MYSQL 서버에 몇 가지 문제가 발생했습니다. 웹 트래픽이 너무 많지 않음에도 불구하고 내 서버는 mysql의 100% 사용량을 보여줍니다.

저는 CentOS 6.5x64(80GB SSD, 8GB RAM, 4CPU 코어)를 실행하고 있습니다.

내.cnf:

[mysqld]
max_connections=500
key_buffer=128M
myisam_sort_buffer_size=35M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=2M
table_cache=4000
thread_cache_size=286
interactive_timeout=25
wait_timeout=7000
connect_timeout=10
max_allowed_packet=268435456
max_connect_errors=10
query_cache_limit=2M
query_cache_size=256M
query_cache_type=1
tmp_table_size=16M
innodb_buffer_pool_size=134217728
open_files_limit=10000
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

MySQLTuner의 출력은 다음과 같습니다.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 164M (Tables: 88)
[--] Data in InnoDB tables: 33M (Tables: 239)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 129

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 2s (83K q [92.549 qps], 13K conn, TX: 44M, RX: 5M)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 544.0M global + 4.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.7G (34% of installed RAM)
[OK] Slow queries: 0% (0/83K)
[OK] Highest usage of available connections: 23% (116/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/59.2M
[OK] Key buffer hit rate: 97.9% (24K cached / 527 reads)
[OK] Query cache efficiency: 54.3% (18K cached / 33K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (5 temp sorts / 388 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 374 total)
[OK] Thread cache hit rate: 99% (116 created / 13K connections)
[OK] Table cache hit rate: 98% (465 open / 474 opened)
[OK] Open file limit used: 3% (311/10K)
[!!] Table locks acquired immediately: 58%
[OK] InnoDB buffer pool / data size: 128.0M/33.6M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Optimize queries and/or use InnoDB to reduce lock wait

내 TOP의 결과는 다음과 같습니다.

top - 12:01:06 up 1 day,  1:02,  1 user,  load average: 10.37, 9.22, 8.61
Tasks: 399 total,   1 running, 397 sleeping,   0 stopped,   1 zombie
Cpu(s): 84.1%us, 15.1%sy,  0.0%ni,  0.0%id,  0.0%wa,  0.0%hi,  0.2%si,      0.7%st
Mem:   8191528k total,  6042780k used,  2148748k free,   242064k buffers
Swap:        0k total,        0k used,        0k free,  4785012k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
24515 mysql     20   0 2732m 165m 6264 S 303.8  2.1  42:43.18 mysqld
19471 root  20   0  174m  10m 6148 S  1.0  0.1   0:00.03 php
19523 root  20   0  172m  10m 5864 S  1.0  0.1   0:00.03 php
19533 root  20   0  172m   9m 5804 S  0.7  0.1   0:00.02 php
19535 root  20   0  172m  10m 5864 S  0.7  0.1   0:00.02 php

이 문제의 원인은 무엇입니까?
서버가 중단되는 것을 방지하려면 my.cnf를 변경해야 합니까?
아니면 16GB RAM이 포함된 새 서버를 구입해야 합니까?

Answer 1

귀하의 세부 정보를 살펴보면 다음과 같은 일반적인 권장 사항을 결론 내릴 수 있습니다.

1. 당신의읽기 : 쓰기 비율 (61 : 39 )엔진 유형을 Innodb. 그 전에 출력을 확인하십시오.

   SHOW ENGINE INNODB STATUS\G; 
   SHOW FULL PROCESSLIST;
   

   피크 시간 동안. 아마도 LOCK상태에 대한 쿼리를 보게 될 것입니다. 테이블 잠금은 MyISAM. 동시 쓰기 및 선택 수가 많고 쿼리 성능이 일관되게 빨라야 하는 경우 Innodb더 나은 잠금 메커니즘으로 인해 유일한 선택입니다( row-level).

2. 총 조각화된 테이블: 129. OPTIMIZE TABLE더 나은 성능을 위해 모두 실행 하고 조각 모음하십시오.

3. 당신의 모습을 살펴보세요Slow_query.log, Query_time, Lock_time, Rows_sent, Rows_examined를 확인하세요. Rows Sent / Rows Examined집합 비율이 높은 쿼리를 찾으면 해당 쿼리는 에 적합합니다 optimization. Slow_query.log의 일부 쿼리가 많은 CPU 리소스를 활용하고 있을 가능성이 있습니다.

4. RAM 및 사용 가능한 디스크 공간과 관련된 서버 상태를 확인하십시오 free -m , df -h. 사용 가능한 메모리가 있는 경우 innodb_buffer_pool_size더 많은 캐싱이 가능하도록 메모리를 늘립니다.

5. MySQL 구성에서 이러한 매개변수를 조정하는 것은 항상 좋습니다.

   innodb_fast_shutdown=0 innodb_log_buffer_size innodb_log_file_size innodb_flush_method=O_DIRECT query_cache_size=0

---

경고:

• 체크리스트 1: MySQL 프로세스의 CPU(가능한 경우 CPU 증가)

• 체크리스트 2: RAM, 데이터 세트가 RAM에 맞는지 여부.

• 체크 리스트 3 : 연결 관련 매개 변수 ABORTED_CONNECTS, CONNECTIONS, MAX_USED_CONNECTIONS, OPEN_FILES, OPEN_TABLES, OPENED_FILES, OPENED_TABLES, QUERIES, SLOW_QUERIES, THREADS_CONNECTED, , , , , , , , , , , , , ,THREADS_CREATEDTHREADS_RUNNINGPERFORMANCE_SCHEMAQUERY_CACHE_SIZEWAIT_TIMEOUTLONG_QUERY_TIMEINTERACTIVE_TIMEOUTCONNECT_TIMEOUTMAX_CONNECTIONSLOCK_WAIT_TIMEOUTTX_ISOLATIONMAX_ALLOWED_PACKETGENERAL_LOGTABLE_OPEN_CACHESLOW_QUERY_LOGOPEN_FILES_LIMITINNODB_LOCK_WAIT_TIMEOUT

• 체크리스트 4: 메모리 관련 매개변수 key_buffer_size, query_cache_size, innodb_buffer_pool_size,innodb_log_buffer_size, max_connections, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, binlog_cache_size, thread_stack,tmp_table_size

• 체크리스트 5: 기타 중요한 InnoDB 매개변수 innodb_log_file_size ,innodb_file_per_table ,innodb_flush_log_at_trx_commit ,innodb_flush_method ,innodb_fast_shutdown

• 체크리스트 6: mysqltuner

  인덱스 없이 수행된 조인: 테이블 캐시 적중률:

  -------- MyISAM Metrics -------------------------------------------- 
  [!!] 
  [!!] Write Key buffer hit rate: 
  -------- InnoDB Metrics -------------------------------------------- 
  [!!] InnoDB buffer pool / data size: 
  [!!] Ratio InnoDB log file size / InnoDB Buffer pool size : 
  [!!] InnoDB buffer pool instances:
  

• 체크리스트 7: 프로세스 목록 아래 쿼리는 오랫동안 '데이터를 보내는 중' 상태에서 발견됩니다.

• 체크리스트 8: 느린 쿼리 로그 - Rows_examined 대 Rows_sent 비율이 매우 높음,

• 체크리스트 9: 중복 인덱스(있는 경우)

• 체크리스트 10: 몇 가지 느린 쿼리 계획 설명

• 체크리스트 11: where 절 열이 제대로 인덱싱되었는지 확인하세요(인덱싱되지 않은 쿼리).

• 체크리스트 12: 테이블 잠금, 메타데이터 잠금, 교착 상태가 있는지 확인하세요.

• 체크리스트 13: 모든 유지 관리 작업이 일정 간격으로 실행되고 있습니다(예: mysqlcheck, mysqldump 등).

• 검사 목록 14: 하드웨어 리소스, 느린 디스크, RAID 재구축, 디스크 I/O, 포화된 네트워크, 스레드 수 증가에 따른 처리량에 따른 네트워크 대역폭.