MySQL8.0 TempTable Flow

MySQL 8.0에서 Internal Temporary Table 방식이 변경되었다. Default TempTable이라는데 이게 기존과 어떤 차이가 있는지 확인해보자

Internal Temporary Table은 언제 쓰는가.

  • UNION 특정케이스
  • UNION, aggregation사용하는 view
  • derived table
  • CTE(WITH clause)
  • subquery, semijoin
  • ORDER BY, GROUP BY 특정케이스
  • INSER.. SELECT 동일테이블에서
  • multiple-table UPDATE
  • GROUP_CONCAT(), COUNT(DISTINCT)
  • window functions 특정케이스 이런 작업인데, 이런저런 쿼리사용하다보면 자주 쓰이게 된다. 쓴다고해서 무조건 이상한 것은 아니고.. 너무 temp영역을 크게 사용하는것이 문제이겠다.

8.0이전에는…

8.0 이전에는 internal temporary이던 create temporary table이던 모두 ibtmp1(global temporary tablespace)을 사용했다. 그래서 무거운 집계쿼리가 도는 DB에서 ibtmp1가 (max값 안줬다면) 걷잡을수 없이 커지고, mysql restart해야하는 경험.. 한번쯤 해봤을 것이다.

8.0부터는…

  1. Internal Temporary Table Storage Engine 선택가능
    • TempTable : BLOB, TEXT까지 다룰수 있는 in-memory엔진이다.
    • Memory : in-memory상에서는 기존과 동일한 방식이다.
  2. MMAP 사용여부 선택가능
    • memory-mapped temporary files로 temptable 엔진의 내용을 on-disk로 다룰때 처리가 빠른 file format이다.
  3. 템프 사이즈 관리 가능
    • memory 공간 점유가 너무 늘어나거나, file 사이즈가 너무 늘어나지 않도록 max size를 지정할 수 있다.
    • mmap max까지 초과한 경우에는 on-disk temporary table 로 전환되는데, on-disk temptorary tablespace도 개선이 되었다.
    • global temporary tablespace(ibtmp1)과 session temporary tablespace(innodb_temp_tablespaces_dir)로 분리가 됨
    • session temporary tablespace는 pool방식으로 동작하고 최초 10개로 시작하고 세션별로 최대 2개의 tablespaces가 할당될 수 있다.
    • 각 tablespace size는 5pages에서 시작하고 세션별로 사용했다가 세션사용이 종료되면 반환된다.(information_schema.innodb_session_temp_tablespaces에서 사용확인가능)
    • tablespace pool 개수는 줄어들진 않고 필요하면 자동으로 늘어날 수 있다.

Flow Chart

temptable

그런데 문제.. 아직도 버그

  • 성능문제가 심했는데, 이건 8.0.21에서 fix됨
    • InnoDB: Use of memory-mapped files after exceeding the temptable_max_ram threshold caused a performance degradation. (Bug #30952983, Bug #98739)
  • https://bugs.mysql.com/bug.php?id=99100 : 8.0.27 fixed
    • InnoDB: A query that used a temporary table for aggregation exhausted the memory available to the TempTable storage engine, causing an update operation to fail with a table is full error. (Bug #31117893, Bug #99100)