삽입 쿼리를 완료하는 데 이렇게 오랜 시간이 걸리는 이유는 무엇입니까?
이것은 꽤 간단한 문제입니다.테이블에 데이터를 삽입하는 것은 보통 몇 초 정도 걸리는 것을 제외하고는 정상적으로 작동합니다. (데이터를 대량으로 삽입하려는 것은 아닙니다.)그래서 삽입 쿼리를 실행하는 데 2초 이상이 걸리는 이유를 알아보기 위해 삽입 프로세스에 대한 시뮬레이션을 설정했습니다.Joshua님께서 id(primary key field)를 제거하고 인덱스 파일을 조정할 수도 있다고 제안하셨지만, 지연이 계속 발생합니다.
My ISAM 테이블이 있습니다.daniel_test_insert(이 테이블은 완전히 비워지기 시작합니다.)
create table if not exists daniel_test_insert (
id int unsigned auto_increment not null,
value_str varchar(255) not null default '',
value_int int unsigned default 0 not null,
primary key (id)
)
데이터를 삽입하고, 삽입 쿼리를 실행하는 데 2초 이상 걸리는 경우도 있습니다.이 표에는 읽기 항목이 없습니다. 단일 스레드 프로그램에 의한 쓰기만 직렬로 수행됩니다.
저는 동일한 쿼리를 100,000번 실행하여 쿼리가 때때로 오래 걸리는 이유를 찾았습니다.아직까지는 무작위로 발생한 것으로 보입니다.
예를 들어 이 쿼리는 4.194초(삽입의 경우 매우 긴 시간)가 걸렸습니다.
Query: INSERT INTO daniel_test_insert SET value_int=12345, value_str='afjdaldjsf aljsdfl ajsdfljadfjalsdj fajd as f' - ran for 4.194 seconds
status | duration | cpu_user | cpu_system | context_voluntary | context_involuntary | page_faults_minor
starting | 0.000042 | 0.000000 | 0.000000 | 0 | 0 | 0
checking permissions | 0.000024 | 0.000000 | 0.000000 | 0 | 0 | 0
Opening tables | 0.000024 | 0.001000 | 0.000000 | 0 | 0 | 0
System lock | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0
Table lock | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0
init | 0.000029 | 0.000000 | 0.000000 | 1 | 0 | 0
update | 4.067331 | 12.151152 | 5.298194 | 204894 | 18806 | 477995
end | 0.000094 | 0.000000 | 0.000000 | 8 | 0 | 0
query end | 0.000033 | 0.000000 | 0.000000 | 1 | 0 | 0
freeing items | 0.000030 | 0.000000 | 0.000000 | 1 | 0 | 0
closing tables | 0.125736 | 0.278958 | 0.072989 | 4294 | 604 | 2301
logging slow query | 0.000099 | 0.000000 | 0.000000 | 1 | 0 | 0
logging slow query | 0.000102 | 0.000000 | 0.000000 | 7 | 0 | 0
cleaning up | 0.000035 | 0.000000 | 0.000000 | 7 | 0 | 0
(이것은 SHOW PROFILE 명령의 축약 버전으로, 모두 0인 열을 버렸습니다.)
이제 업데이트에는 엄청나게 많은 컨텍스트 스위치와 사소한 페이지 장애가 있습니다.Opened_Tables가 이 데이터베이스에서 10초당 1개씩 증가합니다(table_cache 공간이 부족하지 않음).
통계:
MySQL 5.0.89
하드웨어: 2.66GHz에서 32GB 램 / 8코어, RAID 10 SCSI 하드 디스크(SCSI II ??)
하드 드라이브와 raid controller에 문의했습니다.오류가 보고되지 않습니다.CPU는 약 50% 유휴 상태입니다.
iostat -x5 (하드디스크의 경우 reports 활용률 10% 미만) 상위 보고서 로드 평균 약 10분, 1분간(우리 db머신의 경우 정상)
스왑 공간 156k 사용(32기가 램)
저는 무엇이 이 성능 지연의 원인인지 알기가 어렵습니다.이런 일은 부하가 적은 노예에게는 일어나지 않고, 부하가 높은 주인에게만 일어납니다.이것은 기억력과 nodb 테이블에서도 일어납니다.제안해 주실 분 계신가요?(여기는 생산 시스템이라 이국적인 것은 없습니다!)
저는 제 시스템에서도 같은 현상을 발견했습니다.보통 밀리초가 걸리는 쿼리는 갑자기 1-2초가 걸립니다.모든 경우는 간단한 단일 테이블 INSERT/UPDATE/REFREATE 문입니다. 어떤 SELECT에도 없습니다.부하, 잠금 또는 스레드가 쌓이지 않은 것이 분명합니다.
더러운 페이지를 정리하거나 디스크에 변경 사항을 플러시하거나 숨겨진 뮤텍스를 제거하기 때문이라고 생각했지만 아직 좁혀지지 않았습니다.
또한 제외됨
- 서버 로드 - 높은 로드와 상관 관계 없음
- 엔진 - InnoDB/MyISAM/Memory에서 발생합니다.
- MySQL 쿼리 캐시 -- 설정 또는 해제 여부에 관계없이 발생합니다.
- 로그 순환 - 이벤트에 상관 관계 없음
이 시점에서 제가 가진 유일한 다른 관찰은 제가 여러 기계에서 동일한 DB를 실행하고 있다는 사실에서 비롯됩니다.읽기량이 많은 애플리케이션을 사용하고 있기 때문에 복제가 가능한 환경을 사용하고 있습니다. 대부분의 부하는 슬레이브에서 발생합니다.저는 마스터의 부하가 거의 없는데도 거기서 더 많은 현상이 일어난다는 것을 알아챘습니다.잠금 문제가 없다고 보지만 Innodb/Mysql에서 (스레드) 동시성 문제가 있는 것은 아닐까요?슬레이브에 대한 업데이트는 단일 스레드가 됩니다.
MySQL 버전 5.1.48
갱신하다
저는 제 사건에 대한 문제에 대한 단서가 있다고 생각합니다.일부 서버에서는 다른 서버보다 이러한 현상을 더 많이 감지했습니다.서로 다른 서버 간에 무엇이 다른지 확인하고 주변의 상황을 조정하면서 MySQL innodb system 변수로 연결되었습니다. innodb_flush_log_at_trx_commit.
나는 그 문서가 읽기에 좀 어색하다고 생각했지만,innodb_flush_log_at_trx_commit는 1,2,0 의 값을 취할 수 있습니다.
- 1의 경우 모든 커밋에 대해 로그 버퍼가 로그 파일로 플러시되고 모든 커밋에 대해 로그 파일이 Disk로 플러시됩니다.
- 2의 경우 매 커밋마다 로그 버퍼가 로그 파일로 플러시되고 약 1-2초마다 로그 파일이 디스크로 플러시됩니다.
- 0의 경우 로그 버퍼는 매초마다 로그 파일로 플러시되고 로그 파일은 매초마다 디스크로 플러시됩니다.
실제로 보고 및 문서화된 바와 같이 (1,2,0) 순서대로 위험 증가를 위해 거래 실적을 향상시켜야 합니다.
그렇게 말하고 나서, 나는 그 서버들이innodb_flush_log_at_trx_commit=0서버에 비해 성능이 떨어졌습니다(즉, "긴 업데이트" 횟수가 10-100배 더 많습니다).innodb_flush_log_at_trx_commit=2때 또한 2로 바꾸었을 때 불량한 경우는 즉시 개선되었습니다(즉시 변경 가능합니다).
그래서, 제 질문은, 당신의 의도는 무엇인가요?이 매개 변수를 탓하는 것이 아니라, 이 문제와 관련된 맥락임을 강조하는 것입니다.
INODB 테이블을 사용하는 데 문제가 생겼습니다.(그리고 INNODB 인덱스는 MYISAM보다 재작성 속도가 훨씬 느림)
일부 다른 테이블에서 여러 개의 다른 쿼리를 수행하고 있다고 가정합니다. 따라서 문제는 MySQL이 더 커진 파일의 디스크 쓰기를 처리해야 하고 해당 파일에 추가 공간을 할당해야 한다는 것입니다.
MYISAM 테이블을 사용한다면 강력하게 사용할 것을 제안합니다.
LOAD DATA INFILE 'file-on-disk' INTO TABLE `tablename`
명령; MYISAM은 (기본 키를 사용하더라도) 감각적으로 빠르며 파일 형식은 csv로 지정할 수 있으며 열 이름을 지정할 수 있습니다(또는 NULL을 autoincrement 필드의 값으로 지정할 수 있습니다).
첫 번째 팁은 자동 커밋 기능을 비활성화하고 수동으로 커밋하는 것입니다.
LOCK TABLES a WRITE;
... DO INSERTS HERE
UNLOCK TABLES;
이렇게 하면 인덱스 버퍼가 모든 INSERT 문이 완료된 후 디스크에 한 번만 플러시되므로 성능이 향상됩니다.일반적으로 인덱스 버퍼 플러시는 INSERT 문만큼 많습니다.
하지만 가능한 한 최선을 다할 수 있으며, 애플리케이션에서 가능한 경우 한 번의 선택으로 대량 삽입을 수행합니다.
이것은 벡터 바인딩을 통해 이루어지며, 당신이 갈 수 있는 가장 빠른 방법입니다.
Instead
of:
"INSERT INTO tableName values()"
DO
"INSERT INTO tableName values(),(),(),().......(n) " ,
그러나 사용 중인 mysql 드라이버로 매개 변수 벡터 바인딩이 가능한 경우에만 이 옵션을 고려하십시오.
그렇지 않으면 첫번째 가능성을 보고 1000개의 삽입마다 테이블을 잠급니다.100k 삽입용으로 잠그지 마십시오. 버퍼 오버플로우가 발생합니다.
400개(null이 아닌) 열이 있는 테이블을 하나 더 만들고 테스트를 다시 실행할 수 있습니까?느린 삽입 횟수가 많아지면 MySQL이 레코드를 쓰는 데 시간을 낭비하고 있음을 나타낼 수 있습니다. (어떻게 작동하는지는 모르겠지만, 더 많은 블록을 배치하거나 조각화를 피하기 위해 무언가를 옮기는 것일 수 있습니다. ..정말 모르겠습니다.)
MySQL 5.1로 업그레이드했는데 이 이벤트 동안 쿼리 캐시가 많은 "Freeing items?" 스레드 상태로 문제가 되었습니다.그런 다음 쿼리 캐시를 제거했습니다.
MySQL 5.1로 업그레이드하거나 쿼리 캐시를 제거하면 이 문제가 해결됩니다.
참고로, 미래의 독자들에게.
-
우리는 정확히 같은 문제를 다루었고 여기에 http://bugs.mysql.com/bug.php?id=62381 을 보고했습니다.
우리는 5.1.52를 사용하고 있고 아직 해결책이 없습니다.이런 타격을 피하려면 QC를 꺼야 할 수도 있습니다.
루프용으로 한 번에 여러 개의 삽입을 사용하는 경우 PHP의 sleep("time in seconds") 기능을 사용하여 루프가 끝날 때마다 휴식을 취하십시오.
Myisam Performance: http://adminlinux.blogspot.com/2010/05/mysql-allocating-memory-for-caches.html 에서 이 글을 읽어 보십시오.
검색:
'MyISAM 키 블록 크기 키 블록 크기가 중요합니다.'(따옴표 하나를 제외), 이것이 현재의 상황일 수 있습니다.5.1과 함께 이러한 유형의 문제를 해결했다고 생각합니다.
디스크 하위 시스템의 통계를 확인할 수 있습니까?입출력이 포화되었습니까?이것은 내부 DB 작업이 디스크/로그에 플러시하는 것처럼 들립니다.
디스크가 제대로 작동하지 않는지 확인하고 Windows(윈도우)에 있는 경우 10,000개의 파일을 만드는 배치 cmd 파일을 만들 수 있습니다.
@echo OFF
FOR /L %%G IN (1, 1, 10000) DO TIME /T > out%%G.txt
test.cmd와 같은 temp dir에 저장합니다.
/E:를 사용하여 CMD를 실행하는 명령 확장을 사용하도록 설정합니다.ON 파라미터
CMD.exe /E:ON
그런 다음 배치를 실행하여 처음 파일과 마지막 파일 사이의 시간이 초 또는 분 단위로 다른지 확인합니다.
유닉스/리눅스에서는 비슷한 셸 스크립트를 작성할 수 있습니다.
혹시 서버에 SSD 드라이브가 있습니까?일부 SSD 드라이브의 경우 'studder'로 인해 증상이 발생할 수 있습니다.
어쨌든 MySQL에서 지연이 발생하는지 디스크 서브시스템에서 발생하는지 알아보려고 합니다.
귀하의 서버는 어떤 OS이며, MySQL 데이터는 어떤 파일 시스템에 있습니까?
언급URL : https://stackoverflow.com/questions/3722510/why-does-an-insert-query-occasionally-take-so-long-to-complete
'programing' 카테고리의 다른 글
| 워드프레스 REST API : WP REST API JSON 파일에서 "단어 전용" 콘텐츠를 얻는 방법은? (0) | 2023.10.31 |
|---|---|
| 스프링 부트 응용 프로그램에서 테이블 "xx"를 확인할 수 없습니다. (0) | 2023.10.31 |
| .htaccess password protected 폴더가 404페이지로 이동합니다. (0) | 2023.10.31 |
| "document.getElementByClass가 함수가 아닙니다." (0) | 2023.10.31 |
| Linear Layout으로 화면 하단에 버튼을 넣으시겠습니까? (0) | 2023.10.31 |