mita2 database life

主にMySQLに関するメモです

MySQL Shell 8.0.34 を CentOS7 でビルドする

MySQL Shell のソースコードCentOS 7 でビルドするのはいろいろ面倒で、以前も記事を書きました。

いつのまにか依存関係が変わったようで、以前の手順ではビルドできなくなってました。 どんどん難易度が上がってく。。。

MySQL Server をビルドする

MySQL Shell をビルドするには、MySQL Server も事前にビルドしておく必要があります。

$ sudo yum install git wget openssl-devel ncurses-devel epel-release \
  http://mirror.centos.org/centos/7/extras/x86_64/Packages/centos-release-scl-2-3.el7.centos.noarch.rpm  \
http://mirror.centos.org/centos/7/extras/x86_64/Packages/centos-release-scl-rh-2-3.el7.centos.noarch.rpm

$ sudo yum install --enablerepo='epel' cmake3 
$ sudo yum install devtoolset-11-gcc*
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.34.tar.gz
$ tar xvfz mysql-8.0.34.tar.gz

全部をビルドする必要はありません。Shell に必要なライブラリだけビルドしましょう。

$ scl enable devtoolset-11 bash

$ cd mysql-8.0.34
$ mkdir bld; cd bld
$ cmake3 -DDOWNLOAD_BOOST=1 -DWITH_BOOST=/tmp/boost -Dprotobuf_BUILD_SHARED_LIBS=OFF ..

$ cmake3 --build . --target mysqlclient
$ cmake3 --build . --target mysqlxclient

必要なライブラリをビルドする

libssh と antlr4 は必要なバージョンのRPMがどこからも提供されていないので、自分でソースからビルドする必要がありました。うはー。

$ sudo yum install libuuid libuuid-devel

$ git clone https://github.com/antlr/antlr4.git
$ git checkout -b v4.10.1 refs/tags/v4.10.1

$ cd runtime/Cpp/

$ mkdir bld; cd bld
$ cmake3 ..
$ make
Install the project...
-- Install configuration: "Release"
-- Up-to-date: /usr/local/include
-- Installing: /usr/local/include/gmock
-- Installing: /usr/local/include/gmock/gmock-actions.h
-- Installing: /usr/local/include/gmock/gmock-cardinalities.h
-- Installing: /usr/local/include/gmock/gmock-function-mocker.h
-- Installing: /usr/local/include/gmock/gmock-matchers.h
-- Installing: /usr/local/include/gmock/gmock-more-actions.h
-- Installing: /usr/local/include/gmock/gmock-more-matchers.h
-- Installing: /usr/local/include/gmock/gmock-nice-strict.h
-- Installing: /usr/local/include/gmock/gmock-spec-builders.h
-- Installing: /usr/local/include/gmock/gmock.h
-- Installing: /usr/local/include/gmock/internal
-- Installing: /usr/local/include/gmock/internal/custom
-- Installing: /usr/local/include/gmock/internal/custom/README.md
-- Installing: /usr/local/include/gmock/internal/custom/gmock-generated-actions.h
-- Installing: /usr/local/include/gmock/internal/custom/gmock-matchers.h
<snip>
$ wget https://git.libssh.org/projects/libssh.git/snapshot/libssh-0.9.2.tar.gz
$ tar xvfz libssh-0.9.2.tar.gz

$ cd libssh-0.9.2
$ mkdir bld; cd bld
$ cmake3 ..
$ make
$ sudo make install
Install the project...
-- Install configuration: ""
-- Installing: /usr/local/lib64/pkgconfig/libssh.pc
-- Installing: /usr/local/lib64/cmake/libssh/libssh-config-version.cmake
-- Installing: /usr/local/include/libssh/callbacks.h
-- Installing: /usr/local/include/libssh/libssh.h
-- Installing: /usr/local/include/libssh/ssh2.h
-- Installing: /usr/local/include/libssh/legacy.h
-- Installing: /usr/local/include/libssh/libsshpp.hpp
-- Installing: /usr/local/include/libssh/sftp.h
-- Installing: /usr/local/include/libssh/server.h
-- Installing: /usr/local/lib64/libssh.so.4.8.3
-- Installing: /usr/local/lib64/libssh.so.4
-- Installing: /usr/local/lib64/libssh.so
-- Installing: /usr/local/lib64/cmake/libssh/libssh-config.cmake
-- Installing: /usr/local/lib64/cmake/libssh/libssh-config-noconfig.cmake

Python 3.8 もビルドします。RH SCL に Python 3.8 のRPMが存在するのですが、それを使おうとすると余計ややこしかったです。

デフォルトの configure オプションだと、mysqlsh コマンドの内部で python module をロードする際に undefined symbol: PyFloat_Type エラーが出ます。--enable-shared して、共有ライブラリありでビルドすると回避できるようです。

$ sudo yum install libffi-devel

$ wget https://www.python.org/ftp/python/3.8.18/Python-3.8.18.tgz
$ tar xvfz Python-3.8.18.tgz
$ cd Python-3.8.18

$ ./configure --enable-shared
$ make
$ sudo make install

MySQL shell に必要な pip モジュールを入れます。

$ LD_LIBRARY_PATH=/usr/local/lib/ pip3.8 install certifi pyYAML

MySQL Shell をビルドする

$ sudo yum install  libcurl libcurl-devel patchelf

$ wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.34-src.tar.gz
$ tar xvfz mysql-shell-8.0.34-src.tar.gz

cmake3 中 に patchelfPermission denied コケるので、パーミッションを変えて雑に回避しておく。

--   Executing: /usr/bin/patchelf  --remove-rpath;/usr/local/lib64/libantlr4-runtime.so.4.10.1
--   Executing: /usr/bin/patchelf  --set-rpath;$ORIGIN;/usr/local/lib64/libantlr4-runtime.so.4.10.1
patchelf: open: Permission denied

$ sudo chmod 666 /usr/local/lib64/libantlr4-runtime.so.4.10.1 /usr/local/lib64/libssh.so.4.8.3

MySQL Shell の python モードを使えるようにビルドします(HAVE_PYTHON=1)。

$ cd mysql-shell-8.0.34-src
$ mkdir bld; cd bld
$ cmake3  \
  -DMYSQL_BUILD_DIR=$HOME/mysql-8.0.34/bld/ \
  -DMYSQL_SOURCE_DIR=$HOME/mysql-8.0.34/  \
  -DProtobuf_INCLUDE_DIR=$HOME/mysql-8.0.34/extra/protobuf/protobuf-3.19.4/src/ \
  -DBUNDLED_ANTLR_DIR=/usr/local/ \
  -DBUNDLED_SSH_DIR=/usr/local/ \
  -DBUNDLED_PYTHON_DIR=/usr/local/ \
  -DHAVE_PYTHON=1 ..

$ make
$ sudo make install

めでたし、めでたし。

[vagrant@localhost bld]$ mysqlsh
MySQL Shell 8.0.34

Copyright (c) 2016, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
 MySQL  Py >

MySQL のデータを BigQuery にサクっとインポートした

MySQL のデータを BigQuery にサクっとインポートしたメモ。

Big Query がサポートしている形式

Arvo/ORC/Parquet は MySQL からそれぞれのデータ形式に簡単に変換する方法が見つからなかった。 CSVはカンマや改行などの特殊記号がうまく扱えるか不安があったので、JSONを選択。

MySQL Shell で JSON 形式で export する

MySQL Shell を使うと、非常に簡単に、結果を JSON形式 で取り出すことが出来て便利でした。

--result-format=ndjson を指定して、SELECT結果をJSON 形式で出力します。 念の為、記号や改行を入れたデータで試してみましたが、問題なくインポートできてました。

$ cat exp.sql
SELECT * FROM d1.tbl1


$ mysqlsh --result-format=ndjson --sql user@localhost --save-passwords=never --file exp.sql | tee exp.json
{"pk":1,"v1":"foo","j1":{"key1":"value1","array1":[1,2,3,4,5]}}
{"pk":2,"v1":"this is include\nspecial \"' characters","j1":{"key1":"value1","array1":[1,2,3,4,5]}}

BQへインポート

JSONファイルをCloud Storage にコピーしインポートするだけ。

LOAD DATA OVERWRITE dataset1.tbl1
FROM FILES (
  format = 'JSON',
  uris = ['gs://bucket/exp.json']);

Cloud SQL 連携クエリ

そもそも、MySQL が Cloud SQL であれば、Cloud SQL 連携クエリ  |  BigQuery  |  Google Cloud を使うのが良いのかもしれない。

Aurora v2 に Upgrade Checker Utility を実行してみた

Aurora MySQL v2 (MySQL 5.7 互換) の EOL が 2024/10/31 ということで、そろそろソワソワし始めている方も多いのではないでしょうか。

Upgrade Checker Utility

MySQL Shell には、MySQL 5.7 から 8.0 へアップグレードする際に問題になりそうな点を洗い出してくれる、便利なユーティリティがあります。 例えば、予約語や廃止される機能の利用の有無をチェックしてくれます。

dev.mysql.com

Aurora でも動く?

Upgrade Checker Utility は Vanilla MySQL を想定して作られているものです。Auroraでどこまで動くかわかりませんが、「予約語のチェックだけでも動いてくれればラッキー」ぐらいの感覚で試してみました。

MySQL Shell のオプションに --mc を指定して、 Classic Protocol 使うようにしましょう。 指定しなくても接続できましたが、Aurora がサポートしていない X Protocol での接続がまず試みられるため、接続に時間がかかりました。

$ mysqlsh --mc admin@database-1.cluster-cm3tklt7o4tn-ro.ap-northeast-1.rds.amazonaws.com
Please provide the password for 'admin@database-1.cluster-cm3tklt7o4tn-ro.ap-northeast-1.rds.amazonaws.com':

MySQL 8.0 の予約語 を使ったテーブル(ADIMN)を作って試してみます。Upgrade Checker で問題点として検知されるはず。

mysql> create table ADMIN (pk int auto_increment primary key, v int);
Query OK, 0 rows affected (0.06 sec)

実行結果

途中でエラーで止まるのでは。。。と予想していたのですが、ちゃんと結果が出ました。 予約語が使われているテーブルが検知できてます 👍

3) Usage of db objects with names conflicting with new reserved keywords
  Warning: The following objects have names that conflict with new reserved
    keywords. Ensure queries sent by your applications use `quotes` when
    referring to them or they will result in errors.
  More information:
    https://dev.mysql.com/doc/refman/en/keywords.html

  t.ADMIN - Table name

一部、Aurora 固有のプロシージャが問題扱いになってますが、Aurora 固有のものはスルーで問題ないでしょう。

  mysql.lambda_async - at line 4,12: unexpected token '('
  mysql.rds_import_binlog_ssl_material - at line 12,14: unexpected token
    'ebr_clear_ssl_material'
  mysql.rds_remove_binlog_ssl_material - at line 2,16: unexpected token
    'ebr_remove_ssl_material'
  mysql.rds_set_external_master - at line 40,20: unexpected token
    'ebr_export_ssl_material'
  mysql.rds_set_external_master_with_auto_position - at line 38,20: unexpected
    token 'ebr_export_ssl_material'


  mysql.rds_heartbeat2 - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES
    table but missing from TABLES table

意外にちゃんと動いてくれそうなので、Auroraでも、Upgrade Checker を流してみて損はなさそう。

JS > util.checkForServerUpgrade()
The MySQL server at database-1.cluster-cm3tklt7o4tn.ap-northeast-1.rds.amazonaws.com:3306, version
5.7.12 - MySQL Community Server (GPL), will now be checked for compatibility
issues for upgrade to MySQL 8.0.33...

1) Usage of old temporal type
  No issues found

2) MySQL 8.0 syntax check for routine-like objects
  The following objects did not pass a syntax check with the latest MySQL 8.0
    grammar. A common reason is that they reference names that conflict with new
    reserved keywords. You must update these routine definitions and `quote` any
    such references before upgrading.
  More information:
    https://dev.mysql.com/doc/refman/en/keywords.html

  mysql.lambda_async - at line 4,12: unexpected token '('
  mysql.rds_import_binlog_ssl_material - at line 12,14: unexpected token
    'ebr_clear_ssl_material'
  mysql.rds_remove_binlog_ssl_material - at line 2,16: unexpected token
    'ebr_remove_ssl_material'
  mysql.rds_set_external_master - at line 40,20: unexpected token
    'ebr_export_ssl_material'
  mysql.rds_set_external_master_with_auto_position - at line 38,20: unexpected
    token 'ebr_export_ssl_material'

3) Usage of db objects with names conflicting with new reserved keywords
  Warning: The following objects have names that conflict with new reserved
    keywords. Ensure queries sent by your applications use `quotes` when
    referring to them or they will result in errors.
  More information:
    https://dev.mysql.com/doc/refman/en/keywords.html

  t.ADMIN - Table name

4) Usage of utf8mb3 charset
  No issues found

5) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

6) Partitioned tables using engines with non native partitioning
  No issues found

7) Foreign key constraint names longer than 64 characters
  No issues found

8) Usage of obsolete MAXDB sql_mode flag
  No issues found

9) Usage of obsolete sql_mode flags
  No issues found

10) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

11) Usage of partitioned tables in shared tablespaces
  No issues found

12) Circular directory references in tablespace data file paths
  No issues found

13) Usage of removed functions
  No issues found

14) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

15) Removed system variables for error logging to the system log configuration
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

16) Removed system variables
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

17) System variables with new default values
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

18) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/

  global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
    which allows insertion of zero dates

19) Schema inconsistencies resulting from file removal or corruption
  Error: Following tables show signs that either table datadir directory or frm
    file was removed/corrupted. Please check server logs, examine datadir to
    detect the issue and fix it before upgrade

  mysql.rds_heartbeat2 - present in INFORMATION_SCHEMA's INNODB_SYS_TABLES
    table but missing from TABLES table

20) Tables recognized by InnoDB that belong to a different engine
  No issues found

21) Issues reported by 'check table x for upgrade' command
  No issues found

22) New default authentication plugin considerations
  Warning: The new default authentication plugin 'caching_sha2_password' offers
    more secure password hashing than previously used 'mysql_native_password'
    (and consequent improved client connection authentication). However, it also
    has compatibility implications that may affect existing MySQL installations.
    If your MySQL installation must serve pre-8.0 clients and you encounter
    compatibility issues after upgrading, the simplest way to address those
    issues is to reconfigure the server to revert to the previous default
    authentication plugin (mysql_native_password). For example, use these lines
    in the server option file:

    [mysqld]
    default_authentication_plugin=mysql_native_password

    However, the setting should be viewed as temporary, not as a long term or
    permanent solution, because it causes new accounts created with the setting
    in effect to forego the improved authentication security.
    If you are using replication please take time to understand how the
    authentication plugin changes may impact you.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication

23) Columns which cannot have default values
  No issues found

24) Check for invalid table names and schema names used in 5.7
  No issues found

25) Check for orphaned routines in 5.7
  No issues found

26) Check for deprecated usage of single dollar signs in object names
  No issues found

27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7
  No issues found

Errors:   6
Warnings: 3
Notices:  0

ERROR: 6 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

Aurora MySQL と MySQL Community Edition の performance_schema instruments 差分

メモです。performance_schema.setup_instruments テーブルの比較

MySQL Community Edition (v5.7.38) にしかない instruments

$ diff setup_instruments_aurora.log setup_instruments_community.log | grep '^>'
> wait/synch/mutex/sql/LOCK_slave_trans_dep_tracker
> wait/synch/mutex/sql/LOCK_keyring_operations
> wait/synch/mutex/sql/LOCK_thread_cache
> wait/synch/mutex/sql/LOCK_connection_count
> wait/synch/mutex/sql/LOCK_thd_remove
> wait/synch/mutex/innodb/dict_sys_mutex
> wait/synch/mutex/innodb/fts_pll_tokenize_mutex
> wait/synch/mutex/innodb/log_sys_write_mutex
> wait/synch/mutex/innodb/log_cmdq_mutex
> wait/synch/mutex/innodb/page_cleaner_mutex
> wait/synch/mutex/innodb/thread_mutex
> wait/synch/mutex/innodb/sync_array_mutex
> wait/synch/mutex/innodb/row_drop_list_mutex
> wait/synch/rwlock/validate/LOCK_dict_file
> wait/synch/cond/sql/COND_thread_cache
> wait/synch/cond/sql/COND_flush_thread_cache
> memory/sql/Gtid_state::group_commit_sidno_locks

AWS Aurora (v2.10.2) にしかない instruments

$ diff setup_instruments_aurora.log setup_instruments_community.log | grep '^<'
< wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_io_cache
< wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_inactive_binlogs_map
< wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_dump_thread_metrics_collection
< wait/synch/mutex/sql/SERVER_THREAD::LOCK_sync
< wait/synch/mutex/sql/FILE_AS_TABLE::LOCK_offsets
< wait/synch/mutex/sql/FILE_AS_TABLE::LOCK_shim_lists
< wait/synch/mutex/sql/TABLESPACES:lock
< wait/synch/mutex/sql/THD::LOCK_epoch_id_master
< wait/synch/mutex/sql/Query_cache::free_memory_list_mutex
< wait/synch/mutex/sql/Query_cache::flush_mutex
< wait/synch/mutex/sql/LOCK_thread_unique_id
< wait/synch/mutex/sql/Aurora_thread_pool
< wait/synch/mutex/sql/LOG_INFO::lock
< wait/synch/mutex/sql/LOCK_thd_remove
< wait/synch/mutex/sql/LOCK_connection_count
< wait/synch/mutex/innodb/dict_sys_fast_ddl_lock
< wait/synch/mutex/innodb/trx_sys_mysql_trx_list_mutex
< wait/synch/mutex/innodb/trx_sys_deadlock_detection_mutex
< wait/synch/mutex/innodb/aurora_lock_thread_slot_futex
< wait/synch/sxlock/innodb/dict_sys_lock
< wait/synch/sxlock/innodb/dict_sys_fast_ddl_lock
< wait/synch/rwlock/sql/CRYPTO_dynlock_value::lock
< wait/synch/cond/sql/FILE_AS_TABLE::cond_request
< wait/synch/cond/sql/SERVER_THREAD::cond_checkpoint
< wait/io/file/sql/file_as_table_test
< wait/io/file/sql/DDL_log
< wait/io/file/sql/external_log
< stage/sql/cleaned up
< stage/sql/delayed commit ok initiated
< stage/sql/delayed commit ok invoked
< stage/sql/delayed commit ok done
< stage/sql/delayed send ok initiated
< stage/sql/delayed send ok invoked
< stage/sql/delayed send ok done
< stage/sql/forwarding
< stage/sql/waiting for lsn
< statement/sql/awslambda
< statement/sql/alter_system
< statement/sql/unit_test
< statement/sql/show_volume_status
< statement/com/Select Into outfile S3
< memory/sql/expr_vm
< memory/sql/parallel_export
< memory/sql/binlog_io_cache
< wait/io/aurora_redo_log_flush
< wait/io/aurora_respond_to_client

最近の MySQL の Internal Temporary Table ステータスとチューニング観点まとめ (version 8.0.28 版)

以下のエントリーでは、最近の MySQL の Internal Temporary Table の動作についてまとめました。

mita2db.hateblo.jp

条件によっては、領域が ディスク上に確保される場合があり、その場合はパフォーマンスに影響がある可能性があります。 今回はMySQLのステータスから Internal Temporary Table の状況を観測し、チューニングする方法をまとめます。

performance_schema.global_status / SHOW GLOBAL STATUS

mysql> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME IN ('Created_tmp_tables', 'Created_tmp_disk_tables') ORDER BY VARIABLE_NAME \G

*************************** 1. row ***************************
 VARIABLE_NAME: Created_tmp_disk_tables
VARIABLE_VALUE: 0
*************************** 2. row ***************************
 VARIABLE_NAME: Created_tmp_tables
VARIABLE_VALUE: 10
2 rows in set (0.00 sec)

Created_tmp_tables

  • Internal Temporary Table が作成された回数
  • Internal Temporary Table のストレージエンジンや、その領域が確保される場所(メモリ or ディスク)に関係なく、Internal Temporary Table が使われるたびにインクリメントされる
  • Internal Temporary Table の利用が避けられないケースや、Internal Temporary Table を使う実行計画を選択したほうが高速な場合もある
    • Created_tmp_tables の多寡だけではチューニングの余地があるかどうかは判断できない

Created_tmp_disk_tables

  • InnoDB ストレージエンジン で Internal Temporary Table が作成された回数
  • Temp Table エンジンが利用されたケースでは、インクリメントされない
  • (メモリではなく) ディスク上に作成されているため、このステータス値がどんどん増加している場合は、チューニングの余地があるかもしれない
    • InnoDB に落ちないよう、tmp_table_sizetemptable_max_ram を調整するとパフォーマンス改善するかも

performance_schema.memory_summary_global_by_event_name

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/temptable/physical%' \G
*************************** 1. row ***************************
                  EVENT_NAME: memory/temptable/physical_disk
                 COUNT_ALLOC: 0
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 0
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 0
             HIGH_COUNT_USED: 0
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
   HIGH_NUMBER_OF_BYTES_USED: 0
*************************** 2. row ***************************
                  EVENT_NAME: memory/temptable/physical_ram
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 1048608
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 1048608
   HIGH_NUMBER_OF_BYTES_USED: 1048608
2 rows in set (0.00 sec)

Temp Table エンジンに関するステータス。

Created_tmp_tablesCreated_tmp_disk_tables はシンプルに「作成された Internal Temporary Table の数」を示しますが、 memory/temptable/{physical_disk,physical_ram} はテーブル数ではなく、メモリやディスク上に領域が確保された回数やサイズを示します。単位が異なるので注意。

各ステータスの意味はオフィシャルドキュメント参照。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 27.12.18.10 メモリーサマリーテーブル

  • memory/temptable/physical_ram

    • Temp Table エンジンがメモリ上に確保した領域のステータス
    • HIGH_NUMBER_OF_BYTES_USEDtemptable_max_ram に近い場合、割り当てメモリが足りず、ファイル上に領域を確保しているかもしれない
      • memory/temptable/physical_disk と合わせて判断
      • ただし、一時的に足りなかっただけかもしれないので、CURRENT_NUMBER_OF_BYTES_USED を継続的に観測しておくと良い
  • memory/temptable/physical_disk

    • Temp Table エンジンがファイル(ディスク)上に確保した領域のステータス
    • HIGH_NUMBER_OF_BYTES_USED が 0 より大きい場合、割り当てメモリが足りずファイルに溢れていたことを示す

      • temptable_max_ram を引き上げるとパフォーマンスが改善するかもしれない
    • HIGH_NUMBER_OF_BYTES_USEDtemptable_max_mmap に近い場合、ファイル(mmap)領域も足りず、InnoDB が使われている可能性がある

      • Created_tmp_disk_tables と合わせて判断
    • こちらも、CURRENT_NUMBER_OF_BYTES_USED を継続的に観測しておくと良い

Temp Table エンジンの領域は 2N MB で拡張されていく

Temp Table エンジンの領域は、1,2,4,8,16...MB と 2の階乗単位で追加されていきます。 このため、追加する領域のサイズに必要な十分な空きメモリがない場合、たとえ、メモリ領域が空いていても、ファイルに領域が確保される場合があります(memory/temptable/physical_ramHIGH_NUMBER_OF_BYTES_USEDtemptable_max_ram に達していなくても、ファイルが利用される可能性がある)

/* Concrete implementation of Block_size_policy, a type which controls how big
 * next Block of memory is going to be allocated by TempTable allocator.
 *
 * In particular, this policy will make TempTable allocator to grow the
 * block-size at exponential rate with upper limit of ALLOCATOR_MAX_BLOCK_BYTES,
 * which is 2 ^ ALLOCATOR_MAX_BLOCK_MB_EXP.
 *
 * E.g. allocation pattern may look like the following:
 *  1 MiB,
 *  2 MiB,
 *  4 MiB,
 *  8 MiB,
 *  16 MiB,
 *  32 MiB,
 *  ...,
 *  ALLOCATOR_MAX_BLOCK_BYTES,
 *  ALLOCATOR_MAX_BLOCK_BYTES
 *
 * In cases when block size that is being requested is bigger than the one which
 * is calculated by this policy, requested block size will be returned (even if
 * it grows beyond ALLOCATOR_MAX_BLOCK_BYTES).
 * */
struct Exponential_policy {
  /** Given the current number of allocated blocks by the allocator, and number
   * of bytes actually requested by the client code, calculate the new block
   * size.
   *
   * [in] Current number of allocated blocks.
   * [in] Number of bytes requested by the client code.
   * @return New block size. */
  static size_t block_size(size_t number_of_blocks, size_t n_bytes_requested) {
    size_t block_size_hint;
    if (number_of_blocks < ALLOCATOR_MAX_BLOCK_MB_EXP) {
      block_size_hint = (1ULL << number_of_blocks) * 1_MiB;
    } else {
      block_size_hint = ALLOCATOR_MAX_BLOCK_BYTES;
    }
    return std::max(block_size_hint, Block::size_hint(n_bytes_requested));
  }
};

MySQLのスロークエリログにはエラーになったクエリが含まれる?

以前、エラーになったクエリがスロークエリログに落ちるかどうか試しました。

mita2db.hateblo.jp

このときは文法エラーも含め、エラー時もスロークエリログに記録されてましたが、MySQL 8.0.29, MySQL 5.7.38 で文法エラーは除外するよう挙動が変更されたようです。 改めて、挙動を確認してみます。

MySQL 8.0.29, Statements that cannot be parsed (due, for example, to syntax errors) are no longer written to the slow query log.

実行時間でフィルターされないようlong_query_time=0 を設定して試します。

log_slow_extra=ON
long_query_time=0
slow_query_log=on
slow_query_log_file=/var/log/mysqld-slow.log

文法エラー

mysql>  INSERT INTO t1 HOGEHOGE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'HOGEHOGE' at line 1

出力されませんでした。リリースノートに書かれている通りですね。

制約違反

mysql>  INSERT INTO t.t1 VALUES(1);
ERROR 1062 (23000): Duplicate entry '1' for key 't1.PRIMARY'

出力されました。 ちゃんと、Errno が出るようになってる!以前、試したときは出力されてなかったです(どのバージョンで直ったんだろ)。

# Time: 2022-05-04T08:10:47.083268Z
# User@Host: root[root] @ localhost []  Id:    14
# Query_time: 0.000293  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 0 Thread_id: 14 Errno: 1062 Killed: 0 Bytes_received: 0 Bytes_sent: 53 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-05-04T08:10:47.082975Z End: 2022-05-04T08:10:47.083268Z
SET timestamp=1651651847;
INSERT INTO t.t1 VALUES(1);

ロックタイムアウト

出力されました。こちらも、Errno が正しく出力されるようになってます。

# Time: 2022-05-04T08:22:02.224408Z
# User@Host: root[root] @ localhost []  Id:    19
# Query_time: 50.333232  Lock_time: 50.331265 Rows_sent: 0  Rows_examined: 0 Thread_id: 19 Errno: 1205 Killed: 0 Bytes_received: 0 Bytes_sent: 67 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-05-04T08:21:11.891176Z End: 2022-05-04T08:22:02.224408Z
SET timestamp=1651652471;
UPDATE t.t1 SET pk = 100 WHERE pk = 1;

中断

mysql> SELECT SLEEP(10);
^C^C -- query aborted
+-----------+
| SLEEP(10) |
+-----------+
|         1 |
+-----------+
1 row in set (1.21 sec)

出力されました。Errno0 です。

# Time: 2022-05-04T08:13:53.342742Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 1.211280  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1 Thread_id: 15 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 57 Read_first: 0 Read_last: 0 Read_key: 0 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2022-05-04T08:13:52.131462Z End: 2022-05-04T08:13:53.342742Z
SET timestamp=1651652032;
SELECT SLEEP(10);

最近の MySQL の Internal Temporary Table 動作まとめ (version 8.0.28 版)

8.0 のGA以降、Internal Temporary Table まわりは改良が重ねられきました・・・ ネットの記事では既に古い内容になってしまっているものもあり、改めて最新の挙動を確認して整理してみました*1

なお、MySQL 8.0 の Internal Temporary Table の動作は @kentarokitagawa さんの以下の記事も詳しいです。 こちらの記事を読んでから本エントリーを読むとより理解しやすいでしょう。

gihyo.jp

Internal Temporary Table

MySQL は、ORDER BY や UNION を含むクエリを処理するため、内部的に一時的なテーブル(Internal Temporary Table) を作成します。 Internal Temporary Table はクエリの実行が終わると、自動的に削除されます。

Internal Temporary Table のストレージエンジンは、Temp Table エンジン もしくは、InnoDB です。 どちらのストレージエンジンが利用されるかは、後述の条件によって動的に決まります。

TempTable エンジン

TempTable エンジンは MySQL 8.0 で登場した Internal Temporary Table 専用のストレージエンジンです。 Internal Temporary Table を効率よく処理するための工夫がされています。

割り当て可能なメモリがある限りは、メモリ上に領域を確保し、足りない分はファイル(ディスク)上に領域を拡張します。つまり、1つの Internal Temporary Table がメモリとファイル両方にまたがって、確保され得るのです。

メモリ上に確保した領域にファイル上に確保した領域を足さなければなりません。 そのため、ファイルをメモリのように扱うために、mmap を用いて実装されています。賢いですねぇ。

ファイル上に確保する領域の最大サイズは temptable_max_mmap パラメータで設定します。

このように、メモリとファイルを使ったハイブリッドな構成である旨は、公式マニュアルの以下の記述からも読み取ることができます。

dev.mysql.com

Data is never moved between RAM and temporary files, within RAM, or between temporary files. (一度 RAM上に格納したデータは、ファイルには書き出されない)

--

New data is stored in RAM if space becomes available within the limit defined by temptable_max_ram. Otherwise, new data is stored in temporary files. (新たなデータは、temptable_max_ram に収まっていれば、RAM上に格納される。そうでなければ、ファイルに格納される。)

--

If space becomes available in RAM after some of the data for a table is written to temporary files, it is possible for the remaining table data to be stored in RAM. (いくつかのデータをファイルに書いたのち、RAMに空き領域ができた場合、残りのデータはRAM上に格納される。)

InnoDB

以下で説明する特定の条件に該当した場合、Internal Temporary Table では InnoDB が使われます。 InnoDB が使われると、全てディスク上に確保されるため、パフォーマンス面ではマイナスです。

InnoDB で作られた、Internal Temporary Table はクエリ実行中に #innodb_tempディレクトリにファイルとして見えます。

$ sudo ls -ahl /var/lib/mysql/#innodb_temp
total 4.1G
drwxr-x---. 2 mysql mysql  187 Apr 19 17:07 .
drwxr-x--x. 8 mysql mysql 4.0K Apr 19 17:07 ..
-rw-r-----  1 mysql mysql 4.1G Apr 22 12:46 temp_10.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_1.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_2.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_3.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_4.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_5.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_6.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_7.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_8.ibt
-rw-r-----  1 mysql mysql  80K Apr 19 17:07 temp_9.ibt

関連するパラメータと各ストレージエンジンが使われる条件

temptable_max_ram

temptable_max_ram は Temp Table エンジンに割り当てるメモリ領域のサイズです。 1つ1つのInternal Temporary Tableのサイズも重要ですが、同時に作成される、Internal Temporary Table の数も重要です。 クライアントが同時にたくさんのクエリを実行すると、複数の Internal Temporary Table が同時に作成され、割り当てた領域を使い切る可能性があります。

temptable_max_ram で割り当てたメモリ領域を使いきると、Temp Tableエンジンは mmap によりファイル(ディスク)上に領域を確保していきます。

temptable_max_mmap

Temp Table エンジンに割り当てる、ファイル上の領域の最大サイズです。 Temp Table エンジンが temptable_max_ram によって割り当てたメモリも使い切り、さらにtemptable_max_mmap で指定したファイル上の領域も使い切ると、InnoDB で処理されるようになります。

この制限は MySQL 8.0.23 で追加されました。8.0.23 まで、ファイル上に確保する領域のサイズに制限はありませんでした。

上記の @kentarokitagawa さんの記事には、このような記述がありますが、現在は temptable_max_mmap パラメータにより制限ができるようになっています。

temptable_max_ramパラメータを超えるとmmapテンポラリファイルを作成します。その際に大きな内部テンポラリテーブルを作成するクエリがあると,大量にメモリを消費する可能性があります。

tmp_table_size

Internal Temporary Table のサイズが、tmp_table_size を超えると、InnoDB で処理されます。デフォルトは 16M です。こちらは、temptable_max_ramtemptable_max_mmap とは異なり、Internal Temporary Table 1つ1つのサイズに対する制限です。Temp Table エンジンの領域に空きがあったとしても、tmp_table_size の制限に該当すると、InnoDB に落ちます。

巨大な Internal Temporary Table を作るクエリによって、Temp Table エンジンの領域を食い潰さないようキャップがかけられるようになってます。 この制限は MySQL 8.0.28 で加わりました。

まとめ

MySQL 8.0.28 時点での挙動:

  • tmp_table_size の制限にひっかかると、InnoDB になる
  • temptable_max_ram の制限を超えると、ファイル上に領域が確保され始める
  • さらに、temptable_max_mmap で指定したサイズも超えると、InnoDB になる

パフォーマンスを意識するなら、tmp_table_sizetemptable_max_ram を引き上げて、可能な限りメモリ上で処理されるようにすると良いでしょう。

*1:version 8.0.28 時点では、今回記載した挙動ですが、今後のバージョンでまた挙動が変わってるかもしれません