MySQL이 CPU를 너무 많이 차지할 때 최적화 방법에 대한 자세한 소개

다음 편집기에서는 Mysql이 CPU를 너무 많이 차지할 때 최적화 방법에 대한 기사를 제공합니다(필독). 에디터가 꽤 좋다고 생각해서 지금 공유해서 참고용으로 올려보겠습니다. 에디터를 따라가서 살펴볼까요

MySQL이 CPU를 너무 많이 차지할 때 어떤 부분을 최적화해야 할까요?

CPU 사용량이 너무 높으면 다음을 고려할 수 있습니다.

1) 일반적으로 높은 동시성 요인을 제거하려면 여전히 원인을 찾아야 합니다. CPU가 너무 높아 어떤 SQL 문이 실행되고 있습니까? show processlist 문을 사용하여 가장 부하가 큰 SQL 문을 찾고 특정 필드에 대한 인덱스를 적절하게 설정하는 등 SQL을 최적화합니다. 🎜>

2) 느린 쿼리 로그를 열고, 실행 시간이 너무 오래 걸리는 SQL 문을 사용하고, 설명 및 분석에 리소스를 너무 많이 차지하여 과도한 CPU 사용량이 발생합니다. 대부분은 GroupBy 및 OrderBy 정렬 문제로 인해 발생합니다. , 그런 다음 천천히 최적화하고 개선합니다. 예를 들어, insert 문 최적화, group by 문 최적화, order by 문 최적화,

3) 정기적으로 파일 및 인덱스 최적화를 고려하세요.

4) 테이블을 정기적으로 분석하세요.

5) 데이터베이스 개체를 최적화합니다.

6) 잠금 문제인지 고려합니다.

7) key_buffer_size와 같은 일부 MySQL 서버 매개변수를 조정합니다. , table_cache, innodb_buffer_pool_size, innodb_log_file_size 잠깐만요;

8) 데이터 양이 너무 많으면 MySQL 클러스터를 사용하거나 고가용성 환경을 구축하는 것을 고려해 볼 수 있습니다.

9) 메모리 래치(누수)로 인해 데이터베이스 CPU가 높을 수 있습니다

10) 다중 사용자 높은 동시성의 경우 어떤 시스템에서도 이를 보유할 수 없으므로 캐시 사용이 필요합니다. 예, memcached 또는 redis 캐시를 사용할 수 있습니다.

11) tmp_table_size 크기가 너무 작은지 확인하세요.

12) max_heap_table_size 너무 작게 구성되었습니다. 조금 늘리세요.

13) MySQL SQL 문 대기 연결 시간 초과 설정 문제(wait_timeout)

14) show processlist를 사용하여 mysql 연결 수를 확인하세요. mysql에서 설정한 접속 범위를 초과했습니다.

다음은 제가 겪은 사례입니다.

피크 시간대에 해당 사이트에 접속했는데, 페이지 클릭이 약간 멈췄습니다. 서버에 로그인하여 아래와 같이 머신 로드가 약간 높고 mysql이 CPU 리소스를 많이 차지하는 것을 확인합니다.

MySQL 로드 느림이 켜져 있는 경우 쿼리 로그 기능의 경우 가장 좋은 방법은 느린 쿼리 로그에서 SQL 문이 많은 수의 그룹별 및 기타 문을 사용하는 경우

UnionUnion 쿼리 등 mysql 점유율이 확실히 높아질 겁니다. 따라서 SQL 문을 최적화해야 합니다

SQL 문을 최적화하는 것 외에도 일부 구성 최적화를 수행할 수도 있습니다. mysql에서 show processlist를 실행하면 다음과 같은 에코 결과가 나타납니다.

1. 쿼리에 디스크 상태
에 많은 수의 Copying이 있습니다. 🎜>

분명히 임시 테이블이 너무 커서 mysql이 임시 테이블을 하드 디스크에 기록하게 되어 전체 성능에 영향을 미칩니다.

Mysql에서 tmp_table_size의 기본값은 16MB에 불과하며 현재 상황에서는 분명히 충분하지 않습니다.

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmp
dir
 | /tmp |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in 
set
 (0.00 sec)

해결책: 임시 테이블 크기 조정

1) 수정할 mysql 터미널 명령을 입력합니다. , Go to global을 추가하면 다음에 mysql

mysql> set global tmp_table_size=33554432;
Query OK, 0 rows affected (0.00 sec)

mysql에 다시 로그인할 때 적용됩니다

mysql> show variables like "%tmp%";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_table_size | 33554432 |
| tmpdir | /tmp |
+-------------------+----------+
4 rows in set (0.01 sec)

2) my. cnf

구성 파일수정

[root@www ~]# vim my.cnf
.....
tmp_table_size = 32M

mysql 다시 시작

[root@www ~]# /etc/init.d/mysqld restart

2.show processlist; 스레드가 실행 중이므로 문제가 있는 쿼리 문을 식별하는 데 도움이 될 수 있습니다. 예를 들어 다음 결과는 다음과 같습니다.

Id User Host db Command Time State Info 
207 root 192.168.1.25:51718 mytest Sleep 5 
NULL

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库 。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

常见问题：

一般是睡眠连接过多，严重消耗mysql服务器资源(主要是cpu, 内存)，并可能导致mysql崩溃。

解决办法 ：

在mysql的配置my.cnf文件中，有一项wait_timeout参数设置.即可设置睡眠连接超时秒数，如果某个连接超时，会被mysql自然终止。
wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题。
通常来说，把wait_timeout设置为10小时是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：

MySQL服务器默认的“wait_timeout”是28800秒即8小时，意味着如果一个连接的空闲时间超过8个小时，MySQL将自动断开该连接。

然而连接池却认为该连接还是有效的(因为并未校验连接的有效性)，当应用申请使用该连接时，就会导致下面的报错：

The last packet successfully received from the server was 596,688 milliseconds ago.
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

28800seconds，也就是8小时。

如果在wait_timeout秒期间内，数据库连接(java.sql.Connection)一直处于等待状态，mysql就将该连接关闭。这时，你的Java应用的连接池仍然合法地持有该连接的引用。当用该连接来进行数据库操作时，就碰到上述错误。
可以将mysql全局变量wait_timeout的缺省值改大。

查看mysql手册，发现对wait_timeout的最大值分别是24天/365天(windows/linux)。

比如将其改成30天

mysql> set global wait_timeout=124800;
Query OK, 0 rows affected (0.00 sec)

위 내용은 MySQL이 CPU를 너무 많이 차지할 때 최적화 방법에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!