mysql memory usage and process count reduce
쓰레드 기본 27개
프로세서의 메모리 사용량 보기
ps aux | grep mysql
ps -eLf | grep mysql //쓰레드 수 보기
- 메모리 감시
watch "ps axuw| grep mysql"
//==================
nano /etc/mysql/my.cnf
윈도우 C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
[mysqld]
performance_schema = 0 # ??
show_compatibility_56 = 1
innodb_buffer_pool_size = 100M #기본 134M
max_connections = 10 # 기본 150
max_user_connections = 5 # 0
# max_delayed_threads = 1
# thread_cache_size=1
# key_buffer = 8M #에러 발생
query_cache_size = 8M
query_cache_limit = 512K
thread_stack = 128K
//====
performance_schema 설정은 처음 시작에선 효과가 있지만 사용을 시작하면 별 효과 없음
//======
- 재시작
systemctl restart mysql
//==================================
- mysql 메모리 보기 스크립트
https://tech.labelleassiette.com/how-to-reduce-the-memory-usage-of-mysql-61ea7d1a9bd
#!/bin/sh
# you might want to add some user authentication here
mysql -e "show variables; show status" | awk '
{
VAR[$1]=$2
}
END {
MAX_CONN = VAR["max_connections"]
MAX_USED_CONN = VAR["Max_used_connections"]
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"]
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONNprintf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN
printf "| %40s | %18d |\n", "max_connections", MAX_CONN
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576
printf "+------------------------------------------+--------------------+\n"
}'
//=======================================
실행 결과
| key_buffer_size | 16.000 MB |
| query_cache_size | 16.000 MB |
| innodb_buffer_pool_size | 100.000 MB |
| innodb_additional_mem_pool_size | 0.000 MB |
| innodb_log_buffer_size | 16.000 MB |
+------------------------------------------+--------------------+
| BASE MEMORY | 148.000 MB |
+------------------------------------------+--------------------+
| sort_buffer_size | 0.250 MB |
| read_buffer_size | 0.125 MB |
| read_rnd_buffer_size | 0.250 MB |
| join_buffer_size | 0.250 MB |
| thread_stack | 0.188 MB |
| binlog_cache_size | 0.031 MB |
| tmp_table_size | 16.000 MB |
+------------------------------------------+--------------------+
| MEMORY PER CONNECTION | 17.094 MB |
+------------------------------------------+--------------------+
| Max_used_connections | 1 |
| max_connections | 30 |
+------------------------------------------+--------------------+
| TOTAL (MIN) | 165.094 MB |
| TOTAL (MAX) | 148.000 MB |
+------------------------------------------+--------------------+
//==================================
// 참고
- mysql 설정 보기
show global variables like "%connection%";
show global status like '%thread%';
show global variables like '%thread%';
- 에러 보기
systemctl status mysql.service
journalctl -xe
'Code > Web' 카테고리의 다른 글
[Javascript, php ] 파일 업로드 , (드래그앤드롭, Ajax 업로드) (0) | 2019.10.21 |
---|---|
[SQL] 같은 값을 가진 다른 칼럼의 값을 합치기 (0) | 2019.09.29 |
[MySql] mysql 사용 메모리 줄이기 (0) | 2019.09.23 |
[web] composer 에러 해결 방법 .Deprecation warning: Your package name is invalid, (0) | 2019.09.22 |
AWS EBS 볼륨 용량 줄이기 (0) | 2019.09.19 |
AWS EC2 스냅샷으로 복구하기 (0) | 2019.09.18 |
댓글을 달아 주세요