MySQL tmp_table_size max_heap_table_size

MySQL tmp_table_size max_heap_table_size

---

2 일 전 내 서버에서 my tmp_table_size= max_heap_table_size(16M).

나는 한 시간 한 번 실행이과에서 시작하는 보고서를 생성하는 cron 작업을했다 : created_tmp_disk_tables, created_tmp_files,created_tmp_tables

내 보고서에서 : created_tmp_disk_tables+ created_tmp_files+ created_tmp_tables= 내 임시 데이터의 100 %

그것으로 :

1. 와 tmp_table_size= max_heap_table_size= 16M이 보고서는 나에게 다음 평균 보고서를 보여 주었다 :
   • 27.37 % (created_tmp_disk_tables)
   • 1.16 % (created_tmp_files)
   • 71.48 % (created_tmp_tables)

이러한 결과를 어떻게 최적화 할 수 있습니까?

2. 함께 tmp_table_size= max_heap_table_size= 20M제 시간에 :

   • 23.48 % (created_tmp_disk_tables)
   • 32.44 % (created_tmp_files)
   • 44.07 % (created_tmp_tables)

7 시간 후 (다시 시작 후) :

• 21.70 % (created_tmp_disk_tables)
• 33.75 % (created_tmp_files)
• 44.55 % (created_tmp_tables)

내가 기대했던 것이 아닙니다.

• 디스크 테이블에서 감소 27.37%에 21.70%> 훨씬 더 예상 한 -
• 임시 파일 형태 상승 1.16%에를 33.75%-> 왜?
• 메모리 테이블에서 감소 71.48%에 44.55%-> 이상한; 상승 할 것으로 예상 됨

---

tmp_table_size 및 max_heap_table_size 를 늘릴 때마다 이를 설정해도 쿼리가 더 잘 작동하지 않는다는 점에 유의하십시오. 실제로 비효율적 인 쿼리가 이전보다 더 나 빠지게 작동합니다. 어떤 상황에서?

쿼리 ORDER BY가 인덱스의 이점없이 조인 또는 정렬 (을 통해 )을 수행하는 경우 임시 테이블이 메모리에 형성되어야합니다. 이렇게하면 Created_tmp_tables 가 증가 합니다.

임시 테이블이 tmp_table_size 의 바이트 수까지 커지고 더 많은 공간이 필요한 경우 어떻게해야합니까? 다음과 같은 일련의 이벤트가 발생합니다.

1. 쿼리 처리를 중지해야합니다.
2. 디스크에 임시 테이블 만들기
3. 메모리 기반 임시 테이블의 내용을 디스크 기반 임시 테이블로 전송
4. 메모리 기반 임시 테이블에서 삭제
5. 디스크 기반 임시 테이블을 사용하여 쿼리 처리 계속

이 프로세스는 Created_tmp_disk_tables를 증가시킵니다.

이러한 메커니즘을 알고 각 인스턴스에서 발생한 일을 살펴 보겠습니다.

디스크 테이블이 27.37 %에서 21.70 %로 감소-> 훨씬 더 많이 예상 됨

이는 이전에 실행 된 쿼리가 RAM에 남아있는 결과를 캐시 한 경우 쉽게 발생할 수 있습니다. 이렇게하면 처음부터 쿼리를 처리 할 필요가없고 동일한 큰 임시 테이블을 다시 만들지 않아도됩니다.

임시 파일이 1.16 %에서 33.75 %로 증가했습니다.-> 왜 그런가요?

이것은 놀라운 일이 아닙니다. 이것은 단순히 임시 테이블이 필요한 쿼리가 있다는 사실을 나타냅니다. 먼저 RAM에서 생성되었습니다. 이는 잘 조인되지 않는 쿼리 ( join_buffer_size 가 너무 작음) 또는 ORDER BY인덱싱되지 않은 열 또는 임시 테이블이있는 열 (아마도 sort_buffer_size 가 너무 작음)이 있음을 나타냅니다.

메모리 테이블이 71.48 %에서 44.55 %로 감소-> 이상 함; 상승 할 것으로 예상 됨

이것은 놀라운 일이 아닙니다. 동일한 값을 가진 동일한 쿼리에 대한 충분한 호출이있는 경우 이전에 캐시 된 결과의 쿼리 이행으로 정렬 및 조인이 선점 될 수 있습니다.

추천

이러한 점을 고려하여 조정할 수있는 항목은 다음과 같습니다.

• join_buffer_size
• sort_buffer_size
• 사용될 인덱스 생성
  • in joins via eq_ref
  • in sorts via index scans in order

The overall goal should be to prevent temp table creation as much as possible. Simply increasing tmp_table_size and max_heap_table_size lets inefficient queries and tables that lack proper indexing run amok.

ReferenceURL : https://stackoverflow.com/questions/13259275/mysql-tmp-table-size-max-heap-table-size