投稿日：2019-06-27T21:41:27+09:00

MySQL explainをフィルタリングして出力するツール

はじめに

sqlログからまとめてexplainをするツールを紹介させていただきます。
結果をフィルタリング表示させたりもできます。

expl
https://github.com/muroon/expl

例えば、Indexが貼られていないSQLを特定したりするのに便利かと思います。

explainをフィルタリングできる

indexが貼はられていないSQLを検出

using filesortが発生しているSQLを検出

logの中から特定のtableのSQLのみに限定できる

explainを集約して実行できる

内部でSQLを解析して同一SQLのexplainは1回のみにできる

CIツール内で使用してもいいと思います。

使用例

下記のようなdatabase, table構成があったとします

下記のような独自SQLログを常に吐き出していた場合
sql.log
2019-06-21 01:00:00 select * from user where id = 1;
2019-06-21 01:00:00 select * from item where id = 1;
2019-06-21 01:00:00 select user_item.* from user_item, item where item.id = 1 and user_item.item_id = item.id;
1. Download
# Macの場合
curl -vLJO -H 'Accept: application/octet-stream' https://github.com/muroon/expl/releases/download/v1.0.2/expl_1.0.2_Darwin_x86_64.tar.gz
mkdir expl_1.0.2_Darwin_x86_64
tar -zxvf expl_1.0.2_Darwin_x86_64.tar.gz -C expl_1.0.2_Darwin_x86_64

# Linuxの場合
wget https://github.com/muroon/expl/releases/download/v1.0.2/expl_1.0.2_Linux_x86_64.tar.gz
mkdir expl_1.0.2_Linux_x86_64
tar -zxvf expl_1.0.2_Linux_x86_64.tar.gz -C expl_1.0.2_Linux_x86_64
2. 各DB情報を追加

confサブコマンドを実施することにより必要DB情報をconfigファイルに保存します。
expl conf add localhost master user password -c ./config.yaml
expl conf add localhost user user password -c ./config.yaml
confオプション(-c, --conf)でconfigファイル(YAML形式)を指定

3. explainを実行

explainサブコマンドを実施することにより、sqlログを読み込みexplainを実行しています。
expl explain log -c ./config.yaml -l ./sql.log --format command --format-cmd 'cut -c 21-' -I -v -C
logモードによりログファイルから読み込みSQL実行(第二引数)

logオプション(-l, --log)でログファイルパスを指定

confオプション(-c, --conf)で上記のconfigファイルを指定

error無視指定(-I, --ignore-error)

実行SQLの集約(-C, --combine-sql)

詳しくはexplainサブコマンドの処理内部参照

出力結果をフィルタリングする

explainのTYPEがconst以外で絞って表示させる場合
# --filter-no-type constを指定

expl explain log -c ./config.yaml -l ./sql.log --format command --format-cmd 'cut -c 21-' -I -v -C --filter-no-type const
フィルタリングオプションについてはこちら

環境変数で省略可能

下記のオプションは環境変数で省略可能です
EXPL_CONF : -c --conf オプション
EXPL_LOG : -l --log  オプション
EXPL_OPTION : --option-file  オプション(後述)
explainサブコマンド
expl explain モード [各オプション]
処理内部

上記の使用例にてexplainサブコマンドを実行した場合

logファイル(sql.log)を1行づつ読み込み、順次SQLを実行して出力します

必要に応じてファイルタリングを実施します

仮にlogファイル(sql.log)に複数database(master, user)のSQLが両方存在したとしてもツール側で判別します

itemテーブルのようにmaster, userデータベースに存在する場合は、itemテーブルへのSQLはmaster, userデータベースの両方にexplainを試みます

上記のようなlogモードで使用する際にはignore error optionを指定してご利用ください

例えばselect * from item where cateogry_id = 1のようなSQLで考えてみます

explはitemというtable名からmaster, userのデータベースにitemテーブルが存在することを認識します

このような場合、master, userデータベースの両方に向けてexplain SQLを投げてクエリーsyntaxエラーが起きなかった方を出力しようとします。上記のエラーオプションを付けていないとエラー出力して処理がファイルの途中で落ちます

実行SQLの集約によりパラメータ違いの同一SQLは集約して出力できます

各モード

explはSQLの単体実行、独自ファイルからの読み込み、MySQLクエリーログ(FILE)からの読み込み、MySQLクエリーログ(DB)を想定して下記の3つのモードが存在します。

mode 内容 1コマンド内のクエリー実行数備考

simple SQLを直接指定 1クエリーのみ第三引数にクエリーを指定

log ログファイルからSQLを読み込み複数クエリー実行可 MySQLクエリーログ(FILE)または独自のログの場合に使用

log-db DBからSQLを読み込み複数クエリー実行可 MySQLクエリーログ(DB)の場合に使用
# simple mode
expl explain simple "select * from memo" --database database1 --host localhost --user root --pass ""

# log mode
expl explain log --conf config.yaml --format official --log sql.log

# log-db mode
expl explain log-db --conf config.yaml --format official
各オプション

configファイル指定
-c configファイル
--conf configファイル

# EXPL_CONF環境変数で省略可能
logファイル指定
-l logファイル
--log logファイル

# EXPL_LOG環境変数で省略可能
logファイルのフォーマット

explain実行前にログファイルの行からSQLを取り出します。
下記の2つのオプションを使用します。

format

format-cmd

format
-fm フォーマット
--format フォーマット
logファイルフォーマットを指定します。

format 内容

simple 素のSQL

official MySQLのgeneral_log形式

command OSコマンドにより編集

format-cmd
--format-cmd OSコマンド
OSコマンドを実行して、ログファイルの行を編集したい場合に実行します。
expl explain log --conf config.yaml --log custom_sql.log --format command --format-cmd "cut -c 21-"

# 上記はパイプからsimple modeを実行するの同じ
cut -c 21- custom_sql.log | xargs -I$ expl explain simple "$" --conf config.yaml --format command --format-cmd "cut -c 21-"
explainの結果のフィルタリング
--filter-select-type 条件
--filter-no-select-type 条件
--filter-table 条件
--filter-type 条件
--filter-no-type 条件
--filter-extra 条件
--filter-no-extra 条件

# 複数の場合は,区切り
各種条件に応じたexplain結果のみを表示する

option 内容

filter-select-type 指定された"Select Type"のみ表示

filter-no-select-type 指定されなかった"Select Type"のみ表示

filter-table 指定されたTableのみ表示

filter-no-table 指定されなかったTableのみ表示

filter-type 指定された"Type"のみ表示

filter-no-type 指定されなかった"Type"のみ表示

filter-extra 指定文字列が含まれているExplain結果のみ表示

filter-no-extra 指定文字列が含まれているExplain結果を除外して表示
# TYPEに"ALL"が含まれるものを抽出 

expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --filter-type ALL

  DataBase:  memo_sample
  SQL:       select tag.* from tag, tag_memo where tag.id = tag_memo.tag_id
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
| ID | SELECTTYPE |  TABLE   | PARTITIONS | TYPE | POSSIBLEKEYS |   KEY   | KEYLEN |        REF         | ROWS | FILTERED |    EXTRA    |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
|  1 | SIMPLE     | tag      |          0 | ALL  | PRIMARY      |         |      0 |                    |   22 | 100.0000 |             |
|  1 | SIMPLE     | tag_memo |          0 | ref  | PRIMARY      | PRIMARY |      4 | memo_sample.tag.id |    1 | 100.0000 | Using index |
+----+------------+----------+------------+------+--------------+---------+--------+--------------------+------+----------+-------------+
実行SQLの集約
-C
--combine-sql
同じタイプのSQLは一つにまとめます。
下記のようなパラメータ違いのSQLは一つに集約して実行を行います。
# sql1
select * from memo where id = 1;

# sql2
select * from memo where id = 100;
expl explain log --conf config.yaml --format official --log /var/lib/mysql/general_sql.log --combine-sql
error無視
-I
--ignore-error
下記のエラーを実行する

explainクエリー実行時のエラー

sqlパーサーエラー(内部でsqlの解析を行っています)

オプションをファイルに保存
--option-file オプションファイル

# EXPL_OPTION環境変数で設定可能
ファイルはこちらをベースに各オプション項目を設定してoption-fileオプションで指定する

指定が重複した場合の優先順位(高い順)
1. コマンド
2. 環境変数
3. オプションファイル

詳細出力
-v
--verbose
最終的に実行に使用されてたオプションの値を出力
expl explain simple "select * from memo" -d localhost -H localhost -u root -v
      INPNUT OPTION         VALUE
------------------------+--------------
  database               memo_sample
  host                   localhost
  user                   root
  pass
  conf
  log
  format                 simple
  format-cmd
  filter-select-type
  filter-no-select-type
  filter-table
  filter-no-table
  filter-type
  filter-no-type
  filter-extra
  filter-no-extra
  update-table-map       false
  ignore-error           false
  combine-sql            false
ヘルプ
expl explain -h
expl explain --help
confも同様ですが、サブコマンド単位のヘルプもあります。

mode	内容	1コマンド内のクエリー実行数	備考
simple	SQLを直接指定	1クエリーのみ	第三引数にクエリーを指定
log	ログファイルからSQLを読み込み	複数クエリー実行可	MySQLクエリーログ(FILE)または独自のログの場合に使用
log-db	DBからSQLを読み込み	複数クエリー実行可	MySQLクエリーログ(DB)の場合に使用

format	内容
simple	素のSQL
official	MySQLのgeneral_log形式
command	OSコマンドにより編集

option	内容
filter-select-type	指定された"Select Type"のみ表示
filter-no-select-type	指定されなかった"Select Type"のみ表示
filter-table	指定されたTableのみ表示
filter-no-table	指定されなかったTableのみ表示
filter-type	指定された"Type"のみ表示
filter-no-type	指定されなかった"Type"のみ表示
filter-extra	指定文字列が含まれているExplain結果のみ表示
filter-no-extra	指定文字列が含まれているExplain結果を除外して表示

　 Tweet
Qiitaで続きを読む

投稿日：2019-06-27T17:28:06+09:00

【開発環境構築】誰でもお手軽LAMP環境Scotch-box 3.5 使ってみる

「ローカルにLAMP環境を勉強用に用意したいけどまた1から作るのめんどくさいなー」
ポチポチ

【ラクしてLAMPとかの環境を用意したい人のためのScotch Box】
https://qiita.com/naru0504/items/a14681b030816135b868?sb2

【ラクして環境構築するためのScotch-Boxが2.0になってかなり進化してた。】
https://qiita.com/naru0504/items/560a150943d2251e1ae8

「LAMP環境をラクに用意したいとか思っていたら、いいものを見つけました。」

わぁ！？なんと！
調べてみたところ現在はVersion3.5がリリースされているみたいです。

ということで実際に導入してみました。

前提条件

Vagrant導入済
https://www.vagrantup.com/downloads.html

VirtualBox導入済
https://www.virtualbox.org/wiki/Downloads

私のPC環境
$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.12.4
BuildVersion:   16E195
Scotch Boxに関して

公式サイト
https://box.scotch.io/
リポジトリ
https://github.com/scotch-io/scotch-box

本文

インストール

公式サイト手順どおりやっていきましょう。
$ cd [インストールしたいディレクトリ]
$ git clone https://github.com/scotch-io/scotch-box.git
$ cd scotch-box
$ vagrant up
    ...
    default: Guest Additions Version: 5.1.21
    default: VirtualBox Version: 5.2
==> default: [vagrant-hostsupdater] Checking for host entries
==> default: [vagrant-hostsupdater] Writing the following entries to (/etc/hosts)
==> default: [vagrant-hostsupdater]   192.168.33.10  scotchbox  # VAGRANT: c545e317eb29acab529f8d8c2d591bb2 (default) / 10c73402-26f2-42d6-98a2-a122365b9dd3
==> default: [vagrant-hostsupdater] This operation requires administrative access. You may skip it by manually adding equivalent entries to the hosts file.
# PCのパスワードを入力
Password:
インストール終わったら　　
下記URLにアクセス
http://192.168.33.10/

TOPページが表示されましたね。

SSHログインしてみよう
$ vagrant ssh
...
MMMMMMMMMMMMMMMXl.:kXNWMMMMMMMMMMMMMMWWXOc.cXMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMXx:;;:clooddddddddoolc:;;:dKWMMMMMMMMMMMMMMM
MMMMMMMMMMMMMMMMMWXOdl:;,,.........,;:cdkXWMMMMMMMMMMMMMMMMM
  ______                      _        ______                ______
 / _____)            _       | |      (____  \              (_____ \
( (____   ____ ___ _| |_ ____| |__     ____)  ) ___ _   _    _____) )___ ___
 \____ \ / ___) _ (_   _) ___)  _ \   |  __  ( / _ ( \ / )  |  ____/ ___) _ \
 _____) | (__| |_| || |( (___| | | |  | |__)  ) |_| ) X (   | |   | |  | |_| |
(______/ \____)___/  \__)____)_| |_|  |______/ \___(_/ \_)  |_|   |_|   \___/

For help, please visit box.scotch.io or scotch.io. Follow us on Twitter @scotch_io and @whatnicktweets.

Last login: Wed May 31 01:25:33 2017 from 10.0.2.2
$ 
sshでログインできました。
LAMP環境構成されているか確認してみよう。
# OS
$ cat /etc/issue
Ubuntu 16.04.2 LTS \n \l

# Apache
$ apachectl -v
Server version: Apache/2.4.18 (Ubuntu)
Server built:   2017-05-05T16:32:00

# PHP
$ php -v
PHP 7.0.18-0ubuntu0.16.04.1 (cli) ( NTS )
Copyright (c) 1997-2017 The PHP Group
Zend Engine v3.0.0, Copyright (c) 1998-2017 Zend Technologies
    with Zend OPcache v7.0.18-0ubuntu0.16.04.1, Copyright (c) 1999-2017, by Zend Technologies

# MySQL
$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper
全部入ってる！！！

ドキュメントルートを設定しよう

/etc/apache2/sites-available/000-default.conf
の設定を書き得えればいいみたい

念の為デフォルト設定をコピー
$ cp 000-default.conf 000-default_origin.conf
000-default.confを編集
$ sudo vim 000-default.conf
<VirtualHost *80:>  
    ServerAdmin webmaster@localhost  
    DocumentRoot /var/www/public  
    ErrorLog ${APACHE_LOG_DIR}/error.log  
    CustomLog ${APACHE_LOG_DIR}/access.log combined  
    <Directory "/var/www/public">  
        Options Indexes FollowSymLinks  
        AllowOverride all  
        Require all granted  
    </Directory>  
</VirtualHost>  
↓↓↓
<VirtualHost *80:>    
    ServerAdmin webmaster@localhost  
    DocumentRoot /var/www/html  
    ErrorLog ${APACHE_LOG_DIR}/error.log  
    CustomLog ${APACHE_LOG_DIR}/access.log combined  
    <Directory "/var/www/html">  
        Options Indexes FollowSymLinks  
        AllowOverride all  
        Require all granted  
    </Directory>  
</VirtualHost>  
Apacheを再起動しましょう
# シンタックスチェック
$ apachectl configtest
Syntax OK
# Apacheリスタート
$ sudo service apache2 restart
試しにHTMLを配置してあげましょう。
$ cd /var/www/
$ sudo mkdir html
$ cd html
# 最初からvimも入ってる！
$ vim index.html

Hello World
~                                  
"index.html" 1L, 12C  
:wqで保存後
http://192.168.33.10/
にアクセスするとHello Worldが表示されると思います。

初期画面はどこにいったの？

あくまで一例ですが僕は
# 名前を変更  
$ mv /var/www/public/index.php /var/www/public/doc.php  
# ファイルを移動  
$ mv /var/www/public/index.php /var/www/public/doc.php /var/www/html/  
http://192.168.33.10/doc.php
に移動させました。
他にもポート開けてあげるとかの方法はあると思います。

synced_folderについて

公式のVagrantfileで既にsynced_folderが適応されているので
config.vm.synced_folder ".", "/var/www", :mount_options => ["dmode=777", "fmode=666"] 
ホスト側から編集してもリアルタイムで反映されてると思います。

最後に

WEB界隈は開発環境構築が簡単と聞きますが躓く人も多々いると思うのでお役に立てたら光栄です。
Scotch-boxBOXを使用しないVarant環境構築方法は別記事で書こうとは思ってます。
タイポや嘘が紛れてたら指摘頂けると幸いです。
お読み頂きありがとうございました。

参考記事

【ラクしてLAMPとかの環境を用意したい人のためのScotch Box】
https://qiita.com/naru0504/items/a14681b030816135b868?sb2

【ラクして環境構築するためのScotch-Boxが2.0になってかなり進化してた。】
https://qiita.com/naru0504/items/560a150943d2251e1ae8

　 Tweet
Qiitaで続きを読む

投稿日：2019-06-27T17:22:58+09:00

各SQLで処理中にスリープする方法

プログラム側でタイムアウト設定したけど、どうやって試そう…って時用。

SQL Server
-- hh:mm:ss
WAITFOR DELAY '00:00:30';
Oracle

Oracle 12c まで
先にDBMS_LOCK権限を付与
CONN / AS SYSDBA
GRANT EXECUTE ON DBMS_LOCK TO ユーザー名 ;
-- 10秒
DBMS_LOCK.SLEEP(10);

-- 0.5秒
DBMS_LOCK.SLEEP(0.5);
Oracle 18c 以降
権限付与必要なし
-- 10秒
DBMS_SESSION.SLEEP(10);
MySQL
-- 10秒
SELECT SLEEP(10);
PostgreSQL
-- 10秒
SELECT pg_sleep(10);

　 Tweet
Qiitaで続きを読む

投稿日：2019-06-27T14:07:49+09:00

カラムの型について

integer:数を扱う
string:文字に使用する。mysqlでは256で転ける仕様になっている？
text:長い文字に使用。ある程度長い文章を取り扱い場合は明示的にするためこちらを利用するほうが吉か。
boolean：真偽
datetime:日付時刻

　 Tweet
Qiitaで続きを読む

投稿日：2019-06-27T14:03:13+09:00

RDS(MySQL)のストレージ領域の肥大化

概要

RDS(MySQL)を運用していて、ストレージ領域が肥大化することがあったのでその対応を共有します。

環境

エンジン：MySQL
エンジンバージョン：5.7.19

結論

まずは、結論から。
MySQLを再起動するとストレージ領域が解放されます。

事象

MySQL(リードレプリカ)のストレージが150GBあり、
DBサイズが60GBだったにも関わらず、ストレージの残容量が1GB近くになっていました。

原因

MySQLのテンポラリーテーブルが肥大化していました。

MySQLには、クエリ実行時にメモリ上に収まりきらなかったデータ(tmp_table_size以上)をInnoDBテーブルとしてテンポラリテーブル(ibtmp1) に書き出します。tmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成されるようです。

このテンポラリーテーブルは、MySQL 5.7.6以からストレージエンジンがInnoDBになったようなんですが、厄介なのは、このテーブルはMySQLを再起動しないと確保したストレージ領域を解放しないのです。

ちなみにMySQL 5.7.5以前は、テンポラリーテーブルのストレージエンジンはMyISAMだったようで、テンポラリーテーブルがディスクを使い切り、クエリがabortされるとストレージ容量は元に戻る仕様？だったようです。
参考：https://yoku0825.blogspot.com/2015/04/mysql-576.html

テンポラリテーブルについてはこちらの解説が分かりやすかったです。

対応

こちらを拝見すると、テンポラリーテーブルのサイズを制限することは可能なんですが、RDS(MySQL)だとパラメータ変更はできませんでした。

つまり、現状だとDBの再起動するしかなさそうです。
こちらにもさらっと記載があります。

MySQL 5.7 以降では、一時テーブル (ibtmp1) が過剰なストレージ領域を使用すると、領域を解放するために、DB インスタンスを再起動します。

別の手段として(お金がかかってしまいますが)、RDS(MySQL)のストレージの自動拡張機能がサポートされたみたいなのでそちらを設定しても良いですね。
https://dev.classmethod.jp/cloud/aws/rds-storage-auto-scaling/

まとめ

ibtmp1のサイズはどこかで見れないんですかね？
RDS(MySQL)のストレージ容量はちゃんと監視しましょう。

　 Tweet
Qiitaで続きを読む

投稿日：2019-06-27T12:34:27+09:00

MySQLClient (CLI) はデフォルトでコメントを無効にするので optimizer hints が効かない

MySQL には、通常の SQL の他に、明示的にオプティマイザに指示を出す optimizer hints という機能がある。
MySQL :: MySQL 8.0 Reference Manual :: 8.9.3 Optimizer Hints

プログラムから実行すると hint が効く。
しかし、同じ SQL を mysqlclient (mysql cli) で実行すると、 hint が効かない現象に出会った。

理由は、 mysqlclient がデフォルトで、コメントを無効化してしまうためだった。
hint はコメントとして追加するため、コメントが無効化されてしまえば、 hint も無効化されてしまう。
適切ではないとして、 deprecated になっている。

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html#option_mysql_comments

mysqlコマンドに、 -cオプションを付けることで、有効にすることができる。
これだけのことなのだが、普段-c オプションを付けることを意識したことはなく、また検索しても出てこなかったので、共有のために記載する。

　 Tweet
Qiitaで続きを読む

20190627のMySQLに関する記事は6件です。

MySQL explainをフィルタリングして出力するツール

はじめに

使用例

1. Download

2. 各DB情報を追加

3. explainを実行

出力結果をフィルタリングする

環境変数で省略可能

explainサブコマンド

処理内部

各モード

各オプション

configファイル指定

logファイル指定

logファイルのフォーマット

format

format-cmd

explainの結果のフィルタリング

実行SQLの集約

error無視

オプションをファイルに保存

詳細出力

ヘルプ

【開発環境構築】誰でもお手軽LAMP環境Scotch-box 3.5 使ってみる

前提条件

私のPC環境

Scotch Boxに関して

本文

インストール

SSHログインしてみよう

全部入ってる！！！

ドキュメントルートを設定しよう

初期画面はどこにいったの？

synced_folderについて

最後に

参考記事

各SQLで処理中にスリープする方法

SQL Server

Oracle

MySQL

PostgreSQL

カラムの型について

RDS(MySQL)のストレージ領域の肥大化

概要

環境

結論

事象

原因

対応

まとめ

MySQLClient (CLI) はデフォルトでコメントを無効にするので optimizer hints が効かない

日付別

タグ一覧