MySQL 5.6 Developer試験対策 5 基本的な最適化 -- 最適化が必要な文を識別する

MySQLRDB勉強メモ

MySQL 5.6 Developer試験 公式「試験内容 チェックリスト」

[https://education.oracle.com/ja/mysql-56-developer/pexam_1Z0-882:embed:cite]

MySQL 5.6 リファレンスマニュアル

[https://dev.mysql.com/doc/refman/5.6/ja/:embed:cite]


公式: 最適化の概要

[https://dev.mysql.com/doc/refman/5.6/ja/optimize-overview.html:embed:cite]

最適化が必要な文を識別する

パフォーマンスの測定

[https://dev.mysql.com/doc/refman/5.6/ja/optimize-benchmarking.html:embed:cite]

式と関数の速度の測定

SELECT BENCHMARK(10000000, 1+1);
+--------------------------+
| BENCHMARK(10000000, 1+1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.09 sec)
SELECT BENCHMARK(10000000, SIN(SQRT(RAND())));
+----------------------------------------+
| BENCHMARK(10000000, SIN(SQRT(RAND()))) |
+----------------------------------------+
|                                      0 |
+----------------------------------------+
1 row in set (0.42 sec)
  • クエリで特定の関数が問題になっているかどうかを調べる
  • 【補】「式」しか受け取れない
SELECT BENCHMARK(10000000, SIN(SQRT(RAND())) FROM DUAL);
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 'FROM DUAL)' at line 1

MySQL ベンチマークスイート

sql-bench/

OSDB: Open Source Database Benchmark

[http://osdb.sourceforge.net/:embed:cite]

独自のベンチマークの使用

  • mysqlslap
  • 負荷エミュレーションクライアント
  • 独自のベンチマークを使用できる
mysqlslap -proot --delimiter=";" \
  --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" \
  --query="SELECT * FROM a" --concurrency=50 --iterations=200
Warning: Using a password on the command line interface can be insecure.
Benchmark
	Average number of seconds to run all queries: 0.002 seconds
	Minimum number of seconds to run all queries: 0.002 seconds
	Maximum number of seconds to run all queries: 0.011 seconds
	Number of clients running queries: 50
	Average number of queries per client: 1
  • --create,--queryにはSQLもしくはSQLを記述したファイルのパスを指定する
  • クエリも自動生成したいとき:--auto-generate-sql

-a, —auto-generate-sql Generate SQL where not supplied by file or command line.

mysqlslap -proot --concurrency=5 --iterations=20 \
  --number-int-cols=2 --number-char-cols=3 \
  --auto-generate-sql
Warning: Using a password on the command line interface can be insecure.
Benchmark
	Average number of seconds to run all queries: 0.014 seconds
	Minimum number of seconds to run all queries: 0.011 seconds
	Maximum number of seconds to run all queries: 0.023 seconds
	Number of clients running queries: 5
	Average number of queries per client: 0

performance_schema によるパフォーマンスの測定

[https://dev.mysql.com/doc/refman/5.6/ja/performance-schema.html:embed:cite]

  • 低レベルでMySQLサーバーの実行をモニタリングする
mysqlshow -proot
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| SAMPLE             |
| mysql              |
| performance_schema |  <-- これ
| sample             |
+--------------------+
  • 5.7からはさらに見やすいビューを定義したsysスキーマがデフォルトで作られる

[https://thinkit.co.jp/article/10028:embed:cite]

  • PostgreSQLでいうところの「稼働統計情報」(アクセス統計情報)が取れるらしい

【補】永続的統計テーブル

  • PostgreSQLのアクセス統計情報にあたるものはperformance_schemaにある
  • PostgreSQLのテーブル/カラム統計情報にあたるものは「永続的統計テーブル」と呼ばれ、 innodb_table_statsinnodb_index_statsにあるよう
SELECT * FROM innodb_table_stats WHERE database_name="sample" AND table_name="items";
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
| sample        | items      | 2020-05-24 07:05:53 |      3 |                    1 |                        0 |
+---------------+------------+---------------------+--------+----------------------+--------------------------+
1 row in set (0.00 sec)
  • ANALYZE TABLEで統計情報を更新する
ANALYZE TABLE items;
+-------------+---------+----------+----------+
| Table       | Op      | Msg_type | Msg_text |
+-------------+---------+----------+----------+
| mysql.items | analyze | status   | OK       |
+-------------+---------+----------+----------+
1 row in set (0.01 sec)
  • あと、PostgreSQLのVACUUM FULLにあたるOPTIMIZE TABLEステートメントがある
OPTIMIZE TABLE items;
+-------------+----------+----------+-------------------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                          |
+-------------+----------+----------+-------------------------------------------------------------------+
| mysql.items | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mysql.items | optimize | status   | OK                                                                |
+-------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.02 sec)
  • InnoDBにおいては実質テーブル再構築 + ANALYZE TABLE

スレッド情報の検査

[https://dev.mysql.com/doc/refman/5.6/ja/thread-information.html:embed:cite]

  • SHOW PROCESSLIST

    • 何が実行されているか確認する
mysqlslap -proot --delimiter=";" \
  --create="CREATE TABLE a (b int);INSERT INTO a VALUES (RAND())" \
  --query="SELECT RAND(),a.* FROM a" --concurrency=50 --iterations=1000 &
mysql -proot -t  <<< "SHOW PROCESSLIST;"
+--------+----------------------+-----------------+-----------+---------+------+------------------+--------------------------+
| Id     | User                 | Host            | db        | Command | Time | State            | Info                     |
+--------+----------------------+-----------------+-----------+---------+------+------------------+--------------------------+
| 380969 | root                 | localhost       | mysqlslap | Sleep   |    0 |                  | NULL                     |
| 414306 | unauthenticated user | localhost       | NULL      | Connect | NULL | Reading from net | NULL                     |
| 414307 | unauthenticated user | connecting host | NULL      | Connect | NULL | login            | NULL                     |
| 414308 | unauthenticated user | connecting host | NULL      | Connect | NULL | login            | NULL                     |
| 414310 | root                 | localhost       | mysqlslap | Query   |    0 | Writing to net   | SELECT RAND(),a.* FROM a |
| 414313 | unauthenticated user | localhost       | NULL      | Connect | NULL | Reading from net | NULL                     |
| 414328 | root                 | localhost       | mysqlslap | Query   |    0 | Sending data     | SELECT RAND(),a.* FROM a |
| 414329 | root                 | localhost       | NULL      | Query   |    0 | init             | SHOW PROCESSLIST         |
+--------+----------------------+-----------------+-----------+---------+------+------------------+--------------------------+
  • Timeは秒数
SELECT BENCHMARK(100000000, SIN(SQRT(RAND())));
SHOW PROCESSLIST;
+--------+------+-----------+-------+---------+------+-----------+------------------------------------------------+
| Id     | User | Host      | db    | Command | Time | State     | Info                                           |
+--------+------+-----------+-------+---------+------+-----------+------------------------------------------------+
| 430982 | root | localhost | mysql | Query   |    2 | executing | SELECT BENCHMARK(100000000, SIN(SQRT(RAND()))) |
| 430983 | root | localhost | NULL  | Query   |    0 | init      | SHOW PROCESSLIST                               |
+--------+------+-----------+-------+---------+------+-----------+------------------------------------------------+
2 rows in set (0.00 sec)

スレッドのコマンド値

[https://dev.mysql.com/doc/refman/5.6/ja/thread-commands.html:embed:cite]

  • Sleep

    • クライアントからのステートメント待ち
  • Query

    • ステートメントを実行中

ほか多数

一般的なスレッドの状態

[https://dev.mysql.com/doc/refman/5.6/ja/general-thread-states.html:embed:cite]

  • Reading from net/Writing to net

    • ネットワークI/O
  • login

    • クライアントが正常に認証されるまでの接続スレッドの初期状態
  • Sending data

    • SELECTステートメントの処理結果をクライアントに送信中
    • 大量のディスクアクセスを実行する傾向がある

ほか多数

遅延挿入スレッドの状態

遅延挿入とは

\h INSERT DELAYED
Name: 'INSERT DELAYED'
Description:
Syntax:
INSERT DELAYED ...

The DELAYED option for the INSERT statement is a MySQL extension to
standard SQL that can be used for certain kinds of tables (such as
MyISAM). When a client uses INSERT DELAYED, it gets an okay from the
server at once, and the row is queued to be inserted when the table is
not in use by any other thread.
...
  • 書き込む内容をメモリで保持しておいて、後で書き込むやつ

    • ログ先行書き込みもないので、mysqldが不慮の死を遂げると未書き込みのデータは失われる
  • MySQL5.6.6時点でdeprecated
  • MySQL8.0でサポートされなくなった
  • InnoDBでは使えない
CREATE TABLE a(col INT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

INSERT DELAYED INTO a(col) values (1);
ERROR 1616 (HY000): DELAYED option not supported for table 'a'
  • MyISAM等で使用可能
CREATE TABLE b(col INT) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

INSERT DELAYED INTO b(col) values (1);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysqlslap -proot --delimiter=";" \
  --create="CREATE TABLE a (b int) ENGINE=MyISAM;INSERT DELAYED INTO a VALUES (RAND())" \
  --query="SELECT * FROM a" --concurrency=50 --iterations=1000 &
mysql -proot -t <<< 'SHOW PROCESSLIST'
+--------+----------------------+-----------------+-----------+----------------+------+--------------------+------------------+
| Id     | User                 | Host            | db        | Command        | Time | State              | Info             |
+--------+----------------------+-----------------+-----------+----------------+------+--------------------+------------------+
| 430982 | root                 | localhost       | mysql     | Sleep          |  336 |                    | NULL             |
| 430987 | DELAYED              | localhost       | mysql     | Delayed insert |  262 | Waiting for INSERT |                  |
| 474707 | root                 | localhost       | mysqlslap | Sleep          |    0 |                    | NULL             |
| 496436 | DELAYED              | localhost       | mysqlslap | Delayed insert |    0 | Waiting for INSERT |                  |
| 496446 | unauthenticated user | connecting host | NULL      | Connect        | NULL | login              | NULL             |
| 496458 | unauthenticated user | connecting host | NULL      | Connect        | NULL | login              | NULL             |
| 496459 | unauthenticated user | connecting host | NULL      | Connect        | NULL | login              | NULL             |
| 496461 | unauthenticated user | connecting host | NULL      | Connect        | NULL | login              | NULL             |
| 496462 | unauthenticated user | connecting host | NULL      | Connect        | NULL | login              | NULL             |
| 496466 | root                 | localhost       | NULL      | Query          |    0 | init               | SHOW PROCESSLIST |
+--------+----------------------+-----------------+-----------+----------------+------+--------------------+------------------+

これ

| 430987 | DELAYED              | localhost       | mysql     | Delayed insert |  262 | Waiting for INSERT |                  |

クエリーキャッシュスレッドの状態

[https://dev.mysql.com/doc/refman/5.6/ja/query-cache-operation.html:embed:cite]

  • 決定的なクエリがバイト単位でぴたり一致するとき、結果をキャッシュできるやつ
  • デフォルト無効
SELECT @@GLOBAL.query_cache_type;
+---------------------------+
| @@GLOBAL.query_cache_type |
+---------------------------+
| OFF                       |
+---------------------------+
1 row in set (0.00 sec)
  • SET GLOBALではクエリーキャッシュスレッドの起動不可
SET GLOBAL query_cache_type="ON";
ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it
  • my.cnfの書き換え・再起動が必要
query_cache_type=1
SHOW VARIABLES LIKE "query_cache_type";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | ON    |
+------------------+-------+
1 row in set (0.00 sec)
  • 【疑問点】有効化してはみたが、SHOW PROCESSLISTには現れなかった…

レプリケーションマスタースレッドの状態

レプリケーションスレーブの I/O スレッド状態

レプリケーションスレーブ SQL スレッドの状態

レプリケーションスレーブ接続スレッドの状態

  • レプリケーション未習なので略

MySQL Cluster スレッドの状態

  • MySQL Cluster未習なので略

イベントスケジューラスレッドの状態

[https://dev.mysql.com/doc/refman/5.6/ja/create-event.html:embed:cite]

  • MySQLイベント: 定期実行、または特定の時間経過後に1度実行するやつ
  • イベントスケジューラスレッドを起動する必要がある
SET GLOBAL event_scheduler = ON;
SHOW PROCESSLIST;
+--------+-----------------+-----------+-------+---------+------+------------------------+------------------+
| Id     | User            | Host      | db    | Command | Time | State                  | Info             |
+--------+-----------------+-----------+-------+---------+------+------------------------+------------------+
| 430982 | root            | localhost | mysql | Sleep   |   37 |                        | NULL             |
| 525712 | root            | localhost | NULL  | Query   |    0 | init                   | SHOW PROCESSLIST |
| 525713 | event_scheduler | localhost | NULL  | Daemon  |   38 | Waiting on empty queue | NULL             |
+--------+-----------------+-----------+-------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
  • 【補】イベントスケジューラ「スレッド」なので、psで独立したプロセスとして現れたりはしない

    • PostgreSQLでは機能ごとに別プロセス
    • 「統計情報コレクタ」とか
ps -aux | grep mysql
mysql        1  2.2  2.3 3890748 606792 pts/0  Ssl+ 17:12   3:27 mysqld
root     25525  0.0  0.0  30900  6116 pts/2    S+   18:46   0:00 mysql -px xx
  • イベントを積んでみる
CREATE EVENT myevent
  ON SCHEDULE
  EVERY 1 SECOND
  DO
    SELECT BENCHMARK(100000000, SQRT(RAND()));
SHOW PROCESSLIST;
+--------+-----------------+-----------+-------+---------+------+-----------------------------+-------------------------------------------+
| Id     | User            | Host      | db    | Command | Time | State                       | Info                                      |
+--------+-----------------+-----------+-------+---------+------+-----------------------------+-------------------------------------------+
| 430982 | root            | localhost | mysql | Sleep   |    7 |                             | NULL                                      |
| 525712 | root            | localhost | NULL  | Query   |   -1 | init                        | SHOW PROCESSLIST                          |
| 525713 | event_scheduler | localhost | NULL  | Daemon  |    0 | Waiting for next activation | NULL                                      |
| 525724 | root            | localhost | mysql | Connect |    1 | executing                   | SELECT BENCHMARK(100000000, SQRT(RAND())) |
| 525727 | root            | localhost | mysql | Connect |    0 | executing                   | SELECT BENCHMARK(100000000, SQRT(RAND())) |
+--------+-----------------+-----------+-------+---------+------+-----------------------------+-------------------------------------------+
5 rows in set (0.00 sec)