SQLの処理に時間がかかっている場合は、基本的にEXPLAINで実行プランを確認して頂き、
必要に応じてパフォーマンスチューニングして頂きますが、更にSQLのどのイベントで時間がかかっているか確認したい時はprofilingを利用していましたがDeprecate
Noticeが以前から出ているので、
Performance
Schemaを用いたProfilingに慣れていく必要があります。どうしても慣れているProfilingをいまだに使いがちなので、こちらに簡単にメモしておきます。色々なサイトで方法を説明して頂いているので詳細は記載しませんが、ここでは少しだけアウトプットを確認し易くしています。
環境
root@localhost [(none)]> select @@version;
+-----------+
| @@version | …
OPTIMIZE TABLEを実行する代わりに、以下の様にALTER TABLEを実行するケースも多いと思います。
バックグラウンドでどの様に処理しているかを聞かれる事も多いのでこちらにメモしておきます。
alter table sbtest6 engine=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
OPTIMIZE TABLE中の一時テーブル処理
# stat city_sample1.ibd
File: ‘city_sample1.ibd’
Size: 5272240128 Blocks: 10297376 IO Block: 4096 regular file
Device: 10301h/66305d Inode: 96469157 Links: 1
Access: (0640/-rw-r-----) Uid: ( 995/ mysql) Gid: ( 1001/ mysql)
Access: 2023-04-21 22:33:34.264902911 +0000
Modify: 2023-04-21 23:27:00.503985634 +0000
Change: 2023-04-21 23:27:00.503985634 +0000
Birth: -
# /usr/local/mysql/bin/mysql -u root -p -e "optimize table shell.city_sample1"
Enter password: …
Analyze Tableがレプリケートされない様にNO_WRITE_TO_BINLOGオプションを付けた場合の
バイナリーログの確認。殆どのケースで参照側でAnalyzeが実行されても問題は無いと思うが、
サンプリングのオーバーヘッドが無い訳ではないので、念のためのオプションとして。
By default, the server writes ANALYZE TABLE statements to the binary log so that they replicate to replicas. To suppress logging, specify the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.
13.7.3.1 ANALYZE TABLE Statement
マスターログの確認
mysql> show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | …もしMySQLにおいてROLLBACK処理が発生したら？どのくらい時間がかかるか確認する為の指標。
START TRANSACTIONまたはBEGINでを使用すると、トランザクションを COMMIT または ROLLBACK で終了するまでは、自動コミットは無効のままになるので、以下の様に明示的にトランザクション内で処理を実行して、該当プロセスをKillコマンドでCOMMIT前に停止しました。当然ながらROLLBACK処理が実行されるのですが、大量にデータを扱っている場合にどれだけ時間がかかるか不安な場合もあるかと思います。全てのケースで確認した訳ではないですが、以下の様にINFORMATION_SCHEMA.INNODB_TRXテーブル内のtrx_rows_modifiedを確認する事でおおよその目途を付ける事が可能です。
１．テスト用の処理を実行
2. …
[さらに読む]MySQL8.0.16のリリースノートに以下の様にTempTableの処理に変更が加わっていたので仕様を把握する為に、追加されたパラメータと挙動に関して確認してみました。見ていると、8.0.23での変更や、8.0.26におけるtemptable_use_mmapのdeprecate notice,8.0.28における変更もあるようです。なので、最新の挙動は8.0.31以降で再確認します。結論としては、8.0.28以降ではTempTable, MEMORYストレージエンジン共にtmp_table_sizeを適切に設定する必要あり。グローバルで利用されるTempTableリソースは, temptable_max_ramで上限を設定され、Defaultは1GBになっています。
InnoDB: When the amount of memory occupied by the TempTable storage engine exceeds the limit defined by the temptable_max_ram variable, …
[さらに読む]MySQL8.0.16のリリースノートにアカウントに広い範囲で権限を付与した後に、特定の権限だけREVOKEする方法についての機能追加が実装されていたので確認してみました。
Previously, it was not possible to grant privileges that apply globally except for certain schemas. This is now possible if the new partial_revokes system variable is enabled. For example, the following statements enable an account to select from or insert into any table except those in the mysql system schema:
Changes in MySQL 8.0.16 (2019-04-25, General Availability)
MySQLを運用する中で、SYS Schemaを利用してインスタンスの状態を確認する事が増えていますが、Performance Schemaを直接見てSQL処理を確認するケースも相変わら多いか思います。まだ具体的にどの様に活用していくかは目途が付いていませんが、MySQL8.0.14のリリースノートに以下の様にSTATEMENT_IDが付与される様になったとの記載があったので念の為に概要だけ確認しておきます。
SYS Schema
The Performance Schema statement event tables (events_statements_current, events_statements_history, and events_statements_history_long) now have a STATEMENT_ID column that indicates the query ID maintained by the server at the SQL level. Column values are unique for the server instance because they are generated using a global counter that is incremented atomically.
…
[さらに読む]MySQL8.0.14のリリースノートに以下の、管理用の接続が追加されたとの記載がありました。max_connectionに到達してしまい「Too many connections」が出た時の対策として活用出来ると思い、基本的な機能を確認してみました。
MySQL Server now permits a TCP/IP port to be configured specifically for administrative connections. This provides an alternative to the single administrative connection that is permitted on the network interfaces used for ordinary connections even when max_connections connections are already established. The administrative network interface has these characteristics:
Changes in MySQL 8.0.14 (2019-01-21, General Availability)
管理ネットワークインタフェースで TCP/IP 接続をリスニングする IP …
[さらに読む]MySQL8.0.14のリリースノートに以下の様に1つのアカウントに２つのパスワードを持つ事が出来る様になったとの記載があったので軽くレビューしてみました。オフィシャルマニュアルに記載がある様に定期的なパスワードは必要ですが、これまではまとめて１回のメンテナンスで全てのアプリケーションの接続で利用されているパスワードを変更するか、同じ権限でアカウントをもう１つ用意して、１つ１つ入れ替える事が必要でしたが、パスワードを２つ持てるのであれば、タイミングを見て複数のアプリケーションのパスワードを段階的に変更する事が出来るので有難い選択肢ですね。
…
[さらに読む]In Case of MySQL
MySQL8.0.13のリリースノートに以下の記載があったので部分的ではありますが、CREATE TEMPORARYテーブルをトランザクション内で実行し確認してみました。
Previously, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements were not supported inside transactions, procedures, functions, or triggers when using GTIDs (that is, when the enforce_gtid_consistency system variable is set to ON). It was possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.
From MySQL 8.0.13, this restriction has been removed when binlog_format is set to ROW or MIXED. With row-based logging in use, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements can now be used inside transactions, procedures, functions, or triggers when GTIDs are enabled. When binlog_format is set to STATEMENT, the restriction remains. Because of …
[さらに読む]