2017-08-25Updated 2020-05-07

MySQL 一定秒以上 Sleep しているプロセスを一括 kill

メモ

300秒以上 Sleep しているプロセスIDをまとめて表示

$ mysql -h <host> -u <user> -p -e'SELECT GROUP_CONCAT(ID) FROM information_schema.PROCESSLIST WHERE TIME > 300;'

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(ID)                                                                                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2147,2143,2138,2113,2111,2104,2102,2098,2087,2085,2082,2081,2079,2078,2069,2068,2045,2037,2029,2025,2023,2016,2015,2006,2005,2003,2002,2001,1999,1998,1997,1995,1987,1986,1984,1982,1981,1974,1973,1968,1966,1963,1961,1959,1957,1955,1954,1949,1937,1936,1928,1925,1923,1920,1916,1914,1912,1908,1906,1898,1892,1869,1847,1842,1651,1650,1572,1570,1568,1566,1539,1522,1517,1516,1514,1511,1506,1483 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

プロセスIDをまとめて一括 kill

1	$ mysqladmin kill <pid,pid,pid...> -h <host> -u <user> -p

2016-10-20Updated 2020-05-07

MySQL COUNT, SUM, GROUP BY, CASE WHEN THEN で集計する

概要

ECサイトに新しい決済機能の利用率出したいな
と思ったときのクエリです。

ちょうどいくつかの集計関数がまとまった1クエリとなったので
まとめました。

SELECT
     DATE_FORMAT(create_date, '%Y-%m-%d') AS 日付
    ,COUNT(order_id)                      AS 全件数
    ,FORMAT(SUM(payment_total),0)         AS "全支払い合計(円)"
    ,COUNT(payment_id = 12 or NULL)       AS "新決済機能の購入件数"
    ,COUNT(customer_id = 0 or NULL)       AS "ゲスト購入数"
    ,FORMAT(SUM(CASE WHEN payment_id = 12 THEN payment_total else 0 END),0) AS "新決済機能の購入支払い合計(円)"
    ,count(payment_id = 12 or NULL)/count(order_id) * 100 AS "新決済機能の購入比率(%)"
    ,SUM(CASE WHEN payment_id = 12 THEN payment_total else 0 END)/SUM(payment_total) * 100 AS "新決済機能の購入支払い合計比率(%)"
    ,COUNT(customer_id = 0 or NULL)/COUNT(order_id) * 100 AS "ゲスト購入比率"
FROM
     dtb_order
WHERE 1=1
     AND site_id = 1
     AND create_date > '2016-09-21 10:00:00'
GROUP BY
     DATE_FORMAT(create_date, '%Y%m%d')
    ;

パッケージ = EC-CUBE 2.11.5
新決済ID = 12

結果

CASE文をさらっと書けるようになると少し大人になった気分になります。
心残りは比率部分の重複部分がまとまったらかっこいいかなと。

精進します。

2016-08-24

SlowQuery を検知して Explain で解析し Slack へ通知

fluentd でエラーログを Slack へ通知の続きです。

エラーログをSlack通知する

環境 CentOS Linux release 7.1.1503 (Core) td-agent: 0.12.12 Nginx: 1.8.0 概要社内でSlackによる連携が進みログ管理もfluentdにまとめつつあるのでエラーログで何かあったらSlack通知させようと思いチャ…

概要

MySQL DB サーバの SlowQuery 状況を
リアルタイムに Slack で確認できるようにする為に導入しました。

環境

CentOS 6.5
td-agent 0.12.26

Fluent Plugin インストール

今回必要モジュールをインストールします。

1
2
3

# td-agent-gem install fluent-plugin-nata2
# td-agent-gem install fluent-plugin-mysql_explain
# td-agent-gem install fluent-plugin-sql_fingerprint

fluent-plugin-nata2
- SET timestamp をあらかじめ除外しアクセスしている DB 情報も保持してくれる
- [https://github.com/studio3104/fluent-plugin-nata2]
fluent-plugin-mysql_explain
- in_mysqlslowquery_ex で取得した JSON の sql 属性に EXPLAIN 実行結果を取得
- [https://github.com/kikumoto/fluent-plugin-mysql_explain]
  [https://github.com/kikumoto/fluent-plugin-sql_fingerprint]
fluent-plugin-sql_fingerprint
- SQL のパラメータにマスクをする
- [https://github.com/kikumoto/fluent-plugin-sql_fingerprint]

Percona Tool Kit インストール

fluent-plugin-sql_fingerprint で利用する fingersprint をインストールします。

1 2	# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm # yum install -y percona-toolkit-2.2.5-2.noarch

fluentd 設定ファイル作成

以下ファイル設定するとします。

/etc/td-agent/conf.d/mysql.conf

<source>
  type mysqlslowquery_ex
  read_from_head
  path /var/lib/mysql/mysql-slow.log
  pos_file /var/log/td-agent/mysql-slow.pos
  tag mysqld.slow_query.bp
  last_dbname_file /tmp/slowquery.log.lastdb
</source>

<filter mysqld.slow_query.**>
  type record_transformer
  <record>
    hostname ${hostname}
  </record>
</filter>

<filter mysqld.slow_query.**>
  type     mysql_explain
  host     127.0.0.1
  port     3306
  database <DB_NAME>
  username <DB_USER>
  password <DB_PASSWORD>
  sql_key  sql
  added_key explain
</filter>

<filter mysqld.slow_query.**>
  type sql_fingerprint
  fingerprint_tool_path /usr/bin/pt-fingerprint
</filter>

<match mysqld.slow_query.**>

  type copy

  <store>
    type slack
    webhook_url <Slack Webhook URL>
    channel <Slack Channel>
    username xxx DB1 [MySQL] Slow Query
    icon_emoji :ghost:
    color danger
    message "*[User]* %s\r\n *[Host]* %s\r\n *[Query Time]* %s\r\n *[Lock Time]* %s\r\n *[Rows sent]* %s\r\n *[Rows Examined]* %s\r\n *[SQL]* %s \r\n *[Explain]* %s \r\n"
    message_keys user,host,query_time,lock_time,rows_sent,rows_examined,fingerprint,explain
    flush_interval 1m
  </store>

</match>

※slowquery のパス、DB のアクセスアカウントなどは各環境により変更してください。

td-agent 再起動

1	# service td-agent restart

確認

SlowQuery を発行し、Slack に通知されるか確認します。

3 秒 sleep させ、my.cnf に設定されている long-query-time の閾値の秒数を超えるようにしています。

1	mysql > SELECT count(*), sleep(3) FROM <table>;

Slack 通知確認

Slack に通知されました！

show more をクリックすると Explain が通知されているのがわかる。

総評

リアルタイム通知は
特に新規開発時に効果的でした。

また
ElasticSearch へ蓄積し時間軸で分析するのは
サイトのイベントとの相関性が見え面白いです。

その環境と状況により発生するスロークエリが見えてきます。

以上です。

2016-06-27Updated 2020-05-07

MySQL テーブル単位の容量確認

mysql> use zabbix
Database changed

mysql> select
    -> table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,
    -> floor((data_length+index_length)/1024/1024) as allMB,  #総容量
    -> floor((data_length)/1024/1024) as dMB,  #データ容量
    -> floor((index_length)/1024/1024) as iMB   #インデックス容量
    -> from information_schema.tables
    -> where table_schema=database()
    -> order by (data_length+index_length) desc;

実行結果

+----------------------------+--------+----------+-------+-------+------+------+
| table_name                 | engine | tbl_rows | rlen  | allMB | dMB  | iMB  |
+----------------------------+--------+----------+-------+-------+------+------+
| history                    | InnoDB | 23815217 |    51 |  1802 | 1179 |  623 |
| history_uint               | InnoDB | 21709131 |    51 |  1646 | 1075 |  571 |
| trends_uint                | InnoDB |    45152 |   650 |    28 |   28 |    0 |
| trends                     | InnoDB |    30965 |   914 |    27 |   27 |    0 |
| history_str                | InnoDB |   129224 |   109 |    19 |   13 |    5 |
| items                      | InnoDB |     2242 |   708 |     2 |    1 |    0 |
| images                     | InnoDB |      144 | 11036 |     1 |    1 |    0 |
| items_applications         | InnoDB |     2468 |    66 |     0 |    0 |    0 |
| triggers                   | InnoDB |      805 |   223 |     0 |    0 |    0 |
| graphs_items               | InnoDB |      948 |   103 |     0 |    0 |    0 |
| functions                  | InnoDB |      833 |    98 |     0 |    0 |    0 |
| events                     | InnoDB |      718 |   114 |     0 |    0 |    0 |
| alerts                     | InnoDB |       88 |   930 |     0 |    0 |    0 |
| graphs                     | InnoDB |      329 |   248 |     0 |    0 |    0 |

...
...

ちなみに上記は Zabbix DBの結果
history (履歴) にデータが肥大化傾向にあるので
パーティションや Zabbix housekeeping での
保存期間設定を調整するなど必要なことがわかります。

2016-05-20

MySQが落ちるトラブルシューティング Cannot allocate memory for the buffer pool

概要

AWS E2 インスタンス上に MySQL, SonarQube インストールし起動するものの
MySQL が落ちるという事象が発生。

ログを見ると以下のエラーが。。

/var/log/mysqld.log

InnoDB: mmap(137363456 bytes) failed; errno 12
[ERROR] InnoDB: Cannot allocate memory for the buffer pool
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

1
2
3

Cannot allocate memory for the buffer pool

バッファープールへのメモリ割当ができない

割りあてるメモリがないという話。
なので、メモリを作ります。

対策 1. swap 領域を作成する

swap 領域作成

// 空ファイル作成
# dd if=/dev/zero of=/swapfile bs=1M count=1024

// 作成した空ファイルをswap領域に設定
# mkswap /swapfile

// スワップ領域を有効化
# swapon /swapfile

swap 領域確認

# free

              total        used        free      shared  buff/cache   available
Mem:        1015472      833592       66456        1456      115424       54708
Swap:       1048572      491136      557436   ← Swapの設定が追加されていることを確認

mysql 再起動

1	# systemctl restart mysqld

対策 2. innodb_buffer_pool_size の割当を増やす

現在設定されている innodb_buffer_pool_size 確認

$ mysql -u <user> -p<pass> <db>  -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 118835956 |
+-------------------------+-----------+

エラーログにあった 137363456 を下回ってるのがわかります。
この割当メモリを増やします。

my.cnf の場所探し

左から順に検索し該当するファイルがあればその my.cnf を参照します。
/etc/my.cnf → /etc/mysql/my.cnf → /usr/etc/my.cnf → ~/.my.cnf

1
2
3

# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

メモリ追加

自分の方では
my.cnf 上に innodb_buffer_pool_size の設定項目がなかったので
追加しました。

my.cnf

[mysqld]
...
...

# 以下追加
innodb_buffer_pool_size = 256M

[mysqld_safe]
...
...

mysql 再起動

1	# systemctl restart mysqld

増えているか確認

$ mysql -u <user> -p<pass> <db>  -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

今回発生していたエラーログが消えました。

2016-03-14

MySQL SQL結果をINTO OUTFILEを使用せずCSV取得

概要

MySQL SELECT 文の結果を csv で取得する際
以下のように CLI 上で実行することで取得できます。

1 2	$ mysql -u <user> -p<password> <db_name> mysql> SELECT * FROM [table] WHERE hoge=hoge INTO OUTFILE "/tmp/output.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

ですが、mysql ログインユーザの権限に FILE がない場合
以下のようなエラーで出力できません。

1	RROR 1 (HY000): Can't create/write to file '/tmp/output.csv' (Errcode: 13)

FILE の READ/WRITE 権限を付与すれば問題ないですが
権限周りをいじりたくないときなどあるかと思います。

本番環境の DB で権限周りがブラックボックス化していて
FLUSH PRIVILEGES すると何か障害が出るんじゃないかとか汗

その際に実施したことを以下まとめました。

考え方

TXT として取得して sed で csv ファイルに加工する、
という方法で実行しています。

例) 以下のような SQL 実行結果を取得したとすると

$ less output.txt

商品1    2    1,000    2,000
商品2    3    1,500    3,000

↓ Excel で表示されるように加工すると

$ less output.txt

"商品1", "2","1,000","2,000"
"商品1", "3","1,500","3,000"

手順

query 結果を ouput.txt に出力

1	$ mysql -u <user> -p<password> <db_name> -e"<query>" > output.txt

Excel 用に加工

力技

// 各行の一番前(「^」)に「"」を付加
$ cat output.txt | sed -e 's/^/"/g' > output2.txt

"商品1    2    1,000    2,000
"商品2    3    1,500    3,000

// 各行の一番後(「$」)に「"」を付加
$ cat output2.txt | sed -e 's/$/"/g' > output3.txt


"商品1    2    1,000    2,000"
"商品2    3    1,500    3,000"

// タブ「/t」を「","」に変更
$ cat output3.txt | sed -e 's/\t/","/g' > ouptut4.txt

"商品1","2","1,000","2,000"
"商品2","3","1,500","3,000"

文字コード変更

この CSV を利用する方(提出先)の方の文字コードに合わせます。
提出先では Shift JIS がデフォルトとのことでそこに変更します。

1 2	$ nkf -g output4.txt UTF-8

$ nkf -sLw output4.txt > output.csv

$ nkf -g output.csv
Shift_JIS

これで INTO OUTFILE を利用せず CSV ファイルを取得できました。
ouput.csv

おまけ

ファイル名に日付をつける。

1	$ mv output.csv output_`date '+%Y%m%d'`.csv

$ ls

output.csv
output_20160314.csv

2016-01-19Updated 2020-05-07

MySQLトラブルシューティング - mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

概要

以下のように mysqldump コマンド実行時に掲題のエラーが発生しました。

1
2
3

$ mysqldump --lock-all-tables --events -h <host_name> -u <user> -p<password> --no-create-info <db_name> <table, ...> > output.sql

mysqldump: Couldn't execute 'FLUSH TABLES': Access denied; you need (at least one of) the RELOAD privilege(s) for this operation (1227)

対策

エラー文の通り、RELOAD権限を付与する。

1 2	mysql> GRANT RELOAD ON . TO '<user>'@'<host_name>'; mysql> FLUSH PRIVILEGES;

以上

2016-01-15Updated 2020-05-07

MySQLトラブルシューティング - ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

概要

ローカルでMacOSXでMAMPを使っていてある日誤った操作により以下のようなエラーが発生した為
対応まとめました。

1 2	$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

ローカル環境でrootユーザのアクセス権を誤って削除してしまったときなどに発生します。

対策

// mysql停止
$ service mysqld stop

// rootユーザのアクセス権限が失われている
// → テーブル権限をスキップ(無視)して作業するオプション付きでセーフモードでmysql起動
$ mysqld_safe --skip-grant-tables &

// rootユーザでアクセス
$ mysql -u root

// 現状の権限設定テーブルを空にする
mysql> TRUNCATE TABLE mysql.user;
Query OK, 0 rows affected (0.00 sec)

// 権限反映
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

// rootユーザにlocalhostの全DBアクセス権限付与
mysql> GRANT ALL PRIVILEGES on *.* to root@localhost IDENTIFIED BY '(root's password)' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

// 権限反映
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

// 権限設定確認
mysql> SELECT host, user FROM mysql.user;

// mysql CLIモード停止 (Ctl+c)でもOK
mysql> quit;

// safe modeで起動させたmysql 関連psをkillする
$ ps aux | grep mysql | grep -v grep | awk '{print "kill -9", $2}'|sh

// mysql 起動
$ service mysqld start

// rootユーザでmysqlアクセス
$ mysql -u root -p(root's password)
mysql>

rootユーザに権限が失われお手上げ状態になったとき、困ったときに safe_mode 使えます。

以上

2015-12-17Updated 2020-05-07

MySQLトラブルシューティング - ERROR 2006 (HY000) at line ***: MySQL server has gone away

概要

DBインポート時に掲題のエラーが発生しました。

インポートサイズが大きすぎる為です。

インポートデータサイズのデフォルト値は 1M です。

以下コマンドで確認できます。

1	mysql> show variables like 'max_allowed_packet';

対策

2点あります。

mysqlコマンドラインから設定 (一時的)
my.cnf に設定 (恒久的)

mysqlコマンドラインから一時的に引き上げる

再起動の必要がなく影響範囲が少なく済みます。
但し、再起動後、デフォルト値に戻るので
恒久的な対応が必要な場合
my.cnf に設定しmysqldを再起動する必要があります。

例) 10MB に設定

1	mysql> set global max_allowed_packet = 1000000;

my.cnf に `max_allowed_packet` を引き上げる様設定

my.cnf パス探索

1
2
3

# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

以下の順で my.cnf を探しています。
/etc/my.cnf → /etc/mysql/my.cnf → /usr/etc/my.cnf → ~/.my.cnf

個々の環境で異なるので本当に存在するかも含め設定ファイルを見定めてください。

おおよそ /etc/my.cnf が一般的かと思います。

my.cnf に [mysqld] に属する様に設定します。

10MBに設定してみます。

1 2	[mysqld] max_allowed_packet=10MB

以上設定して再起動で設定反映完了です。

確認

設定が反映されているか確認します。

mysql> show variables like 'max_allowed_packet';

+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 10485760 |
+--------------------+----------+

以上

300秒以上 Sleep しているプロセスIDをまとめて表示

プロセスIDをまとめて一括 kill

概要

結果

概要

環境

Fluent Plugin インストール

Percona Tool Kit インストール

fluentd 設定ファイル作成

td-agent 再起動

確認

総評

概要

対策 1. swap 領域を作成する

swap 領域作成

swap 領域確認

mysql 再起動

対策 2. innodb_buffer_pool_size の割当を増やす

現在設定されている innodb_buffer_pool_size 確認

my.cnf の場所探し

メモリ追加

mysql 再起動

増えているか確認

概要

考え方

手順

query 結果を ouput.txt に出力

Excel 用に加工

文字コード変更

おまけ

概要

対策

概要

対策

概要

対策

mysqlコマンドラインから一時的に引き上げる

my.cnf に max_allowed_packet を引き上げる様設定

my.cnf パス探索

my.cnf に [mysqld] に属する様に設定します。

確認

my.cnf に `max_allowed_packet` を引き上げる様設定