중단된 쿼리가 있는 Mysql 프로세스 목록이 너무 깁니다.

중단된 쿼리가 있는 Mysql 프로세스 목록이 너무 깁니다.

몇 년 동안 저는 기존 데이터베이스에 대해 반복적인 절차를 사용하여 매일 자동 데이터베이스 백업을 만들어 왔습니다.

mysqldump --user=${mysql_username} --password=${mysql_password} $db --single-transaction --events -R >> $normal_output_filename    

최근에 나는 전용 서버(S6, Apache 2.2 μ.2 μm, S6 μ.2 μm, Apache2 μ.
최근 일부 데이터베이스 액세스 시간이 느리고 결국 "시간 실행" 이 부 근 도 리 " 시 초 간 결 recently, 과 " 이 some accesses exceeded execution실됨 "
03: 모든 데이터베이스 백업 후 매일 백업을 만들 수 있습니다.모든 데이터베이스의 03:00 이후에 매일 백업을 만드는 cron 작업이 있습니다.
터에서ps aux | grep mysql I get나는 이해한다

루트 15840 0.0 126772 3456 ?SN 03:09 0:00 mysqldump --user=uuu --password=xxxxxx information_single-transaction --sysqdump - R

몇 시간 동안 기다려요.몇 시간 동안 보류되어 있습니다.
한 번은 6일 후에 mysqldump가 보류되어 새로운 DB 백업이 수행되지 않는 문제를 깨달았습니다.

show status like '%conn%';    

아무것도 출력하지 않고 보류 상태로 있습니다.

mysqladmin -uuser1 -p*** processlist     

(user1은 superuser)에는 다음과 같은 Killed 프로세스의 거의 8000 행이 표시됩니다.

| 671958 |user1 | localhost | database1 | Killed | 3 | | | 0.000 || 671959 |user1 | localhost | database1 | Killed | 3 | | | 0.000 || 671961 | user1 | localhost | database1 | Killed |2 | | | 0.000 || 671962 | user1 | localhost | database1 | Killed |2 || 0.000 || 671963 | user1 | localhost | database2 | Killed |2 | | | 0.000 || 671964 | user2 | localhost | database3 | 사망 |1 | | | 0.000 || 671965 | user1 | localhost || 사망 | 1 || 0.000 || 671966 | user1 | localhost || 쿼리 |0 | show processlist | 0.000 |+--------+-----+--------------+-----------+---------+---+---+------------------+----------+

아직 mysql 서버를 재시작하지 않았습니다.Horde와 Roundcube의 웹메일이 타임아웃과 에러 500에 도달하는 동안 몇 개의 DB 접속을 가진 웹 사이트를 빠르게 로드하는 것을 볼 수 있습니다.

왜 갑자기 프로세스의 리스트가 증가하기 시작하는지(그것이 발생하기까지 며칠이 걸릴 수 있음) 알 수 없습니다.프로세스가 정지된 상태에서 프로세스가 증가하기 시작하는지 알 수 없습니다.

1 : 데:1 :
, 디스크.빈 용량 GiB / 빈 134 / 총 용량 196 GiB 7171 VPS, 200GB SSD 71 61.93 GiB 71 / 134.78 GiB 71 / 196 GiB 。
2 (R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz, 4µ
7.7 CentOS Linux 7.7.1908
3. ( 위의 경우) 3.10.0-1062.9.1.el7.x86_64
하 : CPU 하 00.88 ( 1 )1 . 03 ( 5 ) 0 . 95 ( 15 )
RAM - 인 1 / 2 / RAM - 1.81 GiB / 2.21 GiB / 7.63 GiB
(2일, 17시간)

기타 데이터

업데이트 2

이 추가되었습니다.thread_handling=pool-of-threads.cnf로 이동합니다.

다음은 질문의 직접적인 답변은 아니지만 MyISAM의 매우 낮은 설정 및 사용법을 나타냅니다.InnoDB로 전환하거나 일부 설정을 늘리는 것이 도움이 될지 모르겠습니다.

MyISAM 테이블을 덤프하면 사용자가 데이터베이스 작업을 수행할 수 없게 됩니다(반면 데이터 세트가 다소 작고 액티비티가 다소 낮을 수 있습니다).

관찰:

• 버전: 5.5.64-MariaDB
• 8 GB의 RAM
• 가동 시간 = 21:05:35. 일부 GLOBAL STATUS 값은 아직 의미가 없을 수 있습니다.
• Windows에서 실행되고 있지 않습니다.
• 64비트 버전 실행
• 완전히(또는 대부분) MyISAM을 실행하고 있는 것 같습니다.

더 중요한 문제:

MyISAM에서 InnoDB로 전환해야 합니다. "MyISAM에서 InnoDB로의 변환"을 참조하십시오.

다음 사항을 제기할 수 있는지 확인하십시오(자세한 내용은 아래 참조).

open_files_limit = 2000
table_open_cache = 300
key_buffer_size = 200M
innodb_buffer_pool_size = 600M  -- after moving tables to InnoDB

OPTIMIZE TABLE이치노

세부사항 및 기타 관찰사항:

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((16M / 0.20 + 128M / 0.70)) / 8192M = 3.2%-- 사용 가능한 RAM의 대부분은 캐시에 사용할 수 있어야 합니다.-- http://mysql.rjweb.org/doc.php/memory

( open_files_limit ) = 760-- - n더파일을 또는 security 다른 의존) -- ulimit - n -- ulimit etc/security conf를

( table_open_cache ) = 64수으로 수백 --캐시할 테이블 기술자 수 --캐시할 테이블 기술자 수.

( innodb_buffer_pool_size ) = 128M-- InnoDB Data + Index cache -- 128M(구 디폴트)은 매우 작습니다.

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 5,578 / 8191 = 68.1%-- 현재되지 않는 는 134217728)가필요 으로 커졌습니다.buffer_pool pct --innodb_buffer_pool_size(이하 134217728)는 필요 이상으로 커졌습니다.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF로그 -- 있는 는, 으로 해 . -- 「 」 、 「 」 、 「 」 쓸 수 .교착 상태가 많으면 디스크에 많이 기록될 수 있습니다.

( join_buffer_size ) = 131,072 / 8192M = 0.00% 0~N. -- 0~N.JOIN 속도를 높일 수 있습니다(쿼리/인덱스를 수정하는 것이 좋습니다)(모든 엔진). 인덱스 스캔, 범위 인덱스 스캔, 풀 테이블스캔, 각 풀 JOIN 등에 사용됩니다.면_buffer_(현1072))를_ join_size (131072)))_____ 。의 1합니다.크기가 작을 경우 일부 쿼리를 개선하려면 RAM의 0.01%로 늘리십시오.

( innodb_buffer_pool_populate ) = OFF = 0 -- NUMA 어제

( query_prealloc_size ) = 8,192 / 8192M = 0.00%RAMRAM † PCT의

( query_alloc_block_size ) = 8,192 / 8192M = 0.00%RAMRAM † PCT의

( character_set_server ) = character_set_server = latin1-- charset_server의 latin1할 수 .-- charset character_set_server('latin1) utf8mb4'는 charset 문제를 해결할 수 있습니다이치노

( local_infile ) = local_infile = ON-- ON)=인 보안 local_infile (ON) = ON 입니다.

( Key_writes / Key_write_requests ) = 5,804 / 9232 = 62.9% 효과 는 16777216이 좋습니다.-- key_buffer 쓰 ram 。-- 이이이이 이이면면 _면면면면면면면777777777777777777777777777777777777777777777777777777777777777777777777777777777777777777 16777216777216 777716 --7777777777777777777777777777777777

( Created_tmp_disk_tables / Created_tmp_tables ) = 13,250 / 18108 = 73.2% -- ) 및 는 16777216를 늘립니다.인덱스를 피하십시오. -- tmp_table_size(현재는 16777216)_table_size 。인덱스를 개선하고 블럽을 피합니다.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (68440 + 1927 + 425 + 0) / 0 = INF Commit ( InnoDB를) :하는 데 이 될 수 : 긴 한 것을 시킵니다.-- trans : InnoDB （ inno inno inno inno를를를를를를를를를를를를를 ） 높음: 긴 트랜잭션은 여러 가지 부담을 줍니다.

( Select_scan ) = 165,862 / 75935 = 2.2 /sec풀 쿼리 테이블이 -- " - " / " " ( " " " )

( Com_optimize ) = 464 / 75935 = 22 /HR -- 되지 .-- OPTIME 탭LE - OPTIME 탭LEE le le le le le le le le le le le물론 높은 빈도는 아닙니다.

( binlog_format ) = binlog_format = STATEMENT -- (10.3)--- "/"/"/"/". -- 5.7(10.3) ROW"가

( expire_logs_days ) = 0-- -- 0)==충돌에 (=인 경우 관련 ) -- binlog = 0(으) 너무 작은 = 네트워크/컴퓨터 충돌에 신속하게 대응해야 합니다. (log_bin(현재 꺼짐) = OFF인 경우에는 관련이 없음)

( innodb_autoinc_lock_mode ) = 1 2 2= "1= "1 = "", 1 "interleaved", 1 = "interleaved", 0 = "가 일반적입니다.

( log_slow_queries ) = log_slow_queries = OFF쿼리를 (5이전 6.1)--- "(5.1.29", 5.6.1)

( slow_query_log ) = slow_query_log = OFF쿼리를 ()----------------------------------------------------------------------------------------------------------------------- 5.1.12)

( long_query_time ) = 10 "컷오프( 2 -- " " 저저"""--초초초초초초초초초초초초초"(초) -- 초 2

( back_log ) = 50 () 많은 할 때 min로 올리면 이 될 수 .-- ( 5 . 6 、 max _ connections 、 max _ connections ） -- min _ connections ( 151,50, max _ connections )

( Com_change_db / Connections ) = 1,278,567 / 363881 = 3.51-- 스위치 connection ( "을 사용하는 것을 -- ( ) "db.table" "db.table" 입니다.

( Com_change_db ) = 1,278,567 / 75935 = 17 /sec사용하다DB와의 접속, db.tbl 구문 사용, 가짜 USE 문장의 삭제 등을 검토해 주십시오.

( Threads_running / thread_cache_size ) = 1 / 0 = INFcurrent / (풀링 시 -- --- 쿼리 최적화: "/" (""/"") -- """

쿼리 캐시가 반감되었습니다.query_cache_type = OFF 및 query_cache_size = 0을 모두 설정해야 합니다. 두 설정을 모두 해제하지 않으면 QC 코드에 일부 코드가 켜져 있는 '버그'가 있다는 소문도 있습니다.

비정상적으로 작음:

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 0.0672
(innodb_buffer_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size + Max_used_connections * (thread_stack + net_buffer_length)) / _ram = 1.9%
Innodb_adaptive_hash_non_hash_searches = 1.1 /sec
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 0.00056
Innodb_buffer_pool_pages_made_young = 0
Innodb_buffer_pool_pages_old = 943
Innodb_buffer_pool_read_ahead = 0
Innodb_checkpoint_max_age = 7.78e+6
Innodb_ibuf_merged_inserts = 0
Innodb_ibuf_merges = 0
Innodb_lsn_current = 2.52e+8
Innodb_lsn_flushed = 240.6MB
Innodb_lsn_last_checkpoint = 2.52e+8
Innodb_master_thread_10_second_loops = 945
Innodb_master_thread_1_second_loops = 10,439
Innodb_master_thread_sleeps = 0.14 /sec
Innodb_mem_adaptive_hash = 2.25e+6
Innodb_mem_dictionary = 2.1e+6
Innodb_mem_total = 131.4MB
Innodb_pages_read + Innodb_pages_written = 0.067 /sec
Innodb_x_lock_spin_waits = 0.047 /HR
Open_tables = 64
net_buffer_length = 8,192

비정상적으로 크다:

Com_check = 22 /HR
Com_show_charsets = 28 /HR
Com_show_events = 1.2 /HR
Feature_gis = 0.66 /HR

비정상적인 문자열:

binlog_checksum = NONE
innodb_fast_shutdown = 1
opt_s__engine_condition_pushdown = off
opt_s__extended_keys = off

어떤 것들은 변했고...

관찰:

• 버전: 5.5.64-MariaDB
• 8 GB의 RAM
• 가동시간 = 5d 15:01:27
• Windows에서 실행되고 있지 않습니다.
• 64비트 버전 실행
• MyISAM과 InnoDB를 모두 실행하고 있는 것 같습니다.

더 중요한 문제:

MyISAM과 InnoDB가 혼재된 8GB의 경우:

key_buffer_size = 800M
innodb_buffer_pool_size = 3000M

ulimit -n199만이다. ★★★open_files_limit760밖에 안 돼요 어떻게 키워야 할지 모르겠어요

innodb_log_file_size = 5M -- 너무 낮습니다.하지만, 바꾸기는 지저분할 것이다.

24 Optimizes/hours는 MyISAM에서도 매우 높습니다.반달이 더 현실적일 수 있습니다.

느린 질의의 징후가 있습니다. 그것을 추적하는 방법은 제 블로그를 참조하십시오.

thread_cache_size -- 30으로 설정됩니다.이것에 의해, 접속 속도가 큰폭으로 향상할 가능성이 있습니다.

세부사항 및 기타 관찰사항:

MyISAM에서 InnoDB로의 전환

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((200M / 0.20 + 128M / 0.70)) / 8192M = 14.4%-- 사용 가능한 RAM의 대부분은 캐시에 사용할 수 있어야 합니다.-- http://mysql.rjweb.org/doc.php/memory

( open_files_limit ) = 760-- ulimit - n -- 더 많은 파일을 허용하려면 ulimit 또는 /etc/security/limits.conf 또는 sysctl.conf(kern.maxfiles & kern.maxfilesperproc) 또는 기타(OS 의존)로 변경합니다.

( innodb_buffer_pool_size ) = 128M-- InnoDB Data + Index cache -- 128M(구 디폴트)은 매우 작습니다.

( innodb_log_buffer_size / innodb_log_file_size ) = 8M / 5M = 160.0%-- 버퍼는 RAM에 있고 파일은 디스크에 있습니다. -- buffer_size는 더 작거나 file_size는 더 커야 합니다.

( innodb_flush_method ) = innodb_flush_method =-- InnoDB가 OS에 블록 쓰기를 요구하는 방법.이중 버퍼링을 피하려면 O_DIRECT 또는 O_ALL_DIRECT(Percona)를 권장합니다.(최소한 Unix의 경우)O_ALL_DIRECT에 대한 경고는 chrischandler를 참조하십시오.

( innodb_io_capacity ) = 200-- 디스크상의 I/O ops/초. 저속 드라이브의 경우 100, 회전 드라이브의 경우 200, SSD의 경우 1000~2000, RAID 팩터의 곱.

( innodb_stats_on_metadata ) = innodb_stats_on_metadata = ON-- 통계를 터치할 때 테이블을 다시 분석하십시오. -- ON은 특정 SHOW 및 information_schema 액세스 속도를 저하시킬 수 있습니다.

( innodb_recovery_update_relay_log ) = innodb_recovery_update_relay_log = OFF-- 충돌 후 복제 오류를 방지할 수 있습니다.

( innodb_import_table_from_xtrabackup ) = 0-- 운반 가능한 테이블스페이스에 도움이 됩니다.

( sync_binlog ) = 0-- 보안을 강화하기 위해 1을 사용하면 I/O = 1의 비용이 다소 들더라도 많은 "빈 로그 엔드"가 발생할 수 있으며, =0은 "불가능한 위치에 있는 binlog"가 발생하여 충돌 시 트랜잭션이 손실될 수 있지만 더 빠릅니다.

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 모든 교착 상태를 기록할 것인지 여부. -- 교착 상태로 인해 문제가 있는 경우, 이 기능을 켜십시오.주의:교착 상태가 많으면 디스크에 많이 기록될 수 있습니다.

( character_set_server ) = character_set_server = latin1-- charset 문제는 character_set_server(현 latin1)를 utf8mb4로 설정하면 도움이 됩니다.이것이 미래의 디폴트입니다.

( local_infile ) = local_infile = ON-- local_infile (현재 ON) = ON은 잠재적인 보안 문제입니다.

( Created_tmp_disk_tables / Created_tmp_tables ) = 98,045 / 181066 = 54.1%-- 디스크에 흘린 임시 테이블의 백분율 -- tmp_table_size(현재는 16777216) 및 max_heap_table_size(현재는 16777216)를 늘립니다.인덱스를 개선하고 블럽을 피합니다.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (388211 + 19570 + 3890 + 330) / 0 = INF-- 커밋당 스테이트먼트(모든 InnoDB를 가정) -- 낮음: 트랜잭션에서 쿼리를 함께 그룹화하는 데 도움이 될 수 있습니다. 높음: 긴 트랜잭션은 여러 가지 부담을 줍니다.

( Select_scan ) = 945,274 / 486087 = 1.9 /sec-- 전체 테이블 검색 - 인덱스 추가 / 쿼리 최적화(작은 테이블이 아닌 경우)

( Com_optimize ) = 3,202 / 486087 = 24 /HR-- OPTIME TABLE을 실행하는 빈도. -- OPTIME TABLE은 거의 도움이 되지 않습니다.물론 높은 빈도는 아닙니다.

( binlog_format ) = binlog_format = STATEMENT-- 문/행/혼합. -- 5.7(10.3)이 ROW를 선호합니다.

( expire_logs_days ) = 0-- binlog 자동 삭제 시간(이렇게 많은 날 이후) -- 너무 큰(또는 0) = 디스크 공간을 사용합니다. 너무 작은 = 네트워크/컴퓨터 충돌에 신속하게 대응해야 합니다. (log_bin(현재 꺼짐) = OFF인 경우에는 관련이 없음)

( log_slow_queries ) = log_slow_queries = OFF쿼리를 (5이전 6.1)--- "(5.1.29", 5.6.1)

( slow_query_log ) = slow_query_log = OFF쿼리를 ()----------------------------------------------------------------------------------------------------------------------- 5.1.12)

( long_query_time ) = 10 "컷오프( 2 -- " " 저저"""--초초초초초초초초초초초초초"(초) -- 초 2

( back_log ) = 50 () 많은 할 때 min로 올리면 이 될 수 .-- ( 5 . 6 、 max _ connections 、 max _ connections ） -- min _ connections ( 151,50, max _ connections )

( Com_change_db / Connections ) = 8,920,272 / 2392646 = 3.73-- 스위치 connection ( "을 사용하는 것을 -- ( ) "db.table" "db.table" 입니다.

( Com_change_db ) = 8,920,272 / 486087 = 18 /sec사용하다DB와의 접속, db.tbl 구문 사용, 가짜 USE 문장의 삭제 등을 검토해 주십시오.

쿼리 캐시가 반감되었습니다.query_cache_type = OFF 및 query_cache_size = 0을 모두 설정해야 합니다. 두 설정을 모두 해제하지 않으면 QC 코드에 일부 코드가 켜져 있는 '버그'가 있다는 소문도 있습니다.

비정상적으로 작음:

Innodb_adaptive_hash_non_hash_searches = 46 /sec
Innodb_buffer_pool_bytes_data = 272 /sec
Innodb_checkpoint_max_age = 7.78e+6
Innodb_master_thread_10_second_loops = 17,345
Innodb_master_thread_1_second_loops = 184,979
Innodb_master_thread_sleeps = 0.38 /sec
Innodb_mem_adaptive_hash = 4.17e+6
Innodb_mem_dictionary = 4.15e+6
Innodb_mem_total = 131.4MB
net_buffer_length = 8,192

비정상적으로 크다:

Com_check = 24 /HR
Com_create_db = 0.15 /HR
Com_drop_db = 0.044 /HR
Com_rename_table = 0.49 /HR
Com_show_charsets = 16 /HR
Com_show_events = 17 /HR
Com_show_storage_engines = 1.9 /HR
Feature_gis = 1.1 /HR
Feature_locale = 20 /HR
Threadpool_idle_threads = 7
Threadpool_threads = 8

비정상적인 문자열:

binlog_checksum = NONE
innodb_fast_shutdown = 1
opt_s__engine_condition_pushdown = off
opt_s__extended_keys = off
thread_handling = pool-of-threads

언급URL : https://stackoverflow.com/questions/59649941/mysql-process-list-too-long-with-killed-queries