昨日書いたData Studio（以下DS）の記事で問題になっていたBigQuery（以下BQ）の割り当てエラー解消のため、専用のMySQLを立てることにした。
メモリに乗せたテーブルで楽勝やろ、と内心思っていたが、そう簡単にはいかなかった。PostgreSQLではなく、MySQLにしたのもメモリテーブルのため。
まずCloud SQLで試そうと思い、SSD＋db-n1-standard-1で立てて、この記事にある通りインポートして見て、DSからつなげてみたけど、表示が非常に遅い。
そしてENGINEはInnoDBしか使えないようになっいたので、インメモリが使えず早々に落とした。
次に、普通にGCEをUbuntu16.04、n1-standard-1（vCPU x 1、メモリ 3.75 GB）で立てて、SSHでインスタンスに入って、普通にMySQLサーバーを入れる。
cloud.google.com
ネットワークタグをつけて（今回はumicos-mysql）、ネットワークの設定のファイアウォールルールを新規作成。
MySQLのbind-addressに内部IPを入れる必要もあるかもしれない。
ターゲットタグにumicos-mysqlを入れて、自社サーバーとDSが使うIP（下記ページ参照）を入れる
support.google.com
たくさんコピペするのがめんどい
64.18.0.0/20
64.233.160.0/19
66.102.0.0/20
66.249.80.0/20
72.14.192.0/18
74.125.0.0/16
108.177.8.0/21
173.194.0.0/16
207.126.144.0/20
209.85.128.0/17
216.58.192.0/19
216.239.32.0/19
BQ用に作ったCSVはもうCloud Storageに上がっているのでgsutil cpでローカルに落とす。
あたりまえだけどGCEだと何もせずにgoogle系コマンドを使えて便利。
MySQLに入ってデータベースとテーブルを作って、LOAD DATAする。
今回は両方member。
CREATE DATABASE member;
CREATE TABLE member.member(
member_id INT,
employee_scale VARCHAR(32),
employee_scale_id INT,
prefecture VARCHAR(32),
prefecture_id INT,
managerial_position VARCHAR(32),
managerial_position_id INT,
job_spec1 VARCHAR(32),
job_spec1_id INT,
industry VARCHAR(32),
industry_id INT,
annual_profit VARCHAR(32),
annual_profit_id INT,
gender VARCHAR(10),
mail_magazine_status INT,
age INT,
last_login_date VARCHAR(20),
create_date VARCHAR(20)
) DEFAULT CHARSET=utf8;
LOAD DATA LOCAL INFILE './member.csv' INTO TABLE member.member FIELDS TERMINATED BY ',' ENCLOSED BY '"';まずこれで元テーブルの準備が完了。
次に、DSで使うようにいろいろ調整したものを作る。最初Viewでも試したけど、処理を軽くするために別テーブルにすることにした。
さっき作ったmemberテーブルからSELECTしてCREATEする。まずは普通にInnoDB（ディスク）。
ディスク用
mysql> CREATE TABLE member.member_datastudio_disk(
member_id INT,
create_date VARCHAR(32),
es_id VARCHAR(32),
mp_id VARCHAR(32),
job_spec1 VARCHAR(32),
industry VARCHAR(32),
gender VARCHAR(32),
age INT,
ages VARCHAR(32),
prefecture VARCHAR(32)
) DEFAULT CHARSET=utf8
SELECT
member_id,
create_date,
CONCAT(LPAD( employee_scale_id, 2, "0"),"_", employee_scale) AS es_id,
CONCAT(LPAD( managerial_position_id, 2, "0"),"_", managerial_position) AS mp_id,
CONCAT(LPAD( job_spec1_id, 2, "0"),"_", job_spec1) AS job_spec1,
CONCAT(LPAD( industry_id, 2, "0"),"_", industry ) AS industry,
gender,
age,
CASE
WHEN age > 19 AND age < 85 THEN CONCAT( FLOOR(age/5) * 5 , '-', FLOOR(age/5) * 5 + 4)
ELSE 'その他'
END AS ages,
CONCAT(LPAD(prefecture_id, 2, "0"), "_", prefecture ) AS prefecture
FROM
member.member;
Query OK, 2135809 rows affected (40.18 sec)
Records: 2135809 Duplicates: 0 Warnings: 0
メモリ用
最初はLOAD DATAで
ERROR 1114 (HY000): The table 'member_memory' is full
とエラーが出たので、
下記を参考にmax_heap_table_sizeを2Gぐらいに変更してMySQLを再起動した。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 15.3 MEMORY ストレージエンジン
mysql> CREATE TABLE member.member_datastudio_memory(
member_id INT,
create_date VARCHAR(32),
es_id VARCHAR(32),
mp_id VARCHAR(32),
job_spec1 VARCHAR(32),
industry VARCHAR(32),
gender VARCHAR(32),
age INT,
ages VARCHAR(32),
prefecture VARCHAR(32)
) DEFAULT CHARSET=utf8 ENGINE=MEMORY
SELECT
member_id,
create_date,
CONCAT(LPAD( employee_scale_id, 2, "0"),"_", employee_scale) AS es_id,
CONCAT(LPAD( managerial_position_id, 2, "0"),"_", managerial_position) AS mp_id,
CONCAT(LPAD( job_spec1_id, 2, "0"),"_", job_spec1) AS job_spec1,
CONCAT(LPAD( industry_id, 2, "0"),"_", industry ) AS industry,
gender,
age,
CASE
WHEN age > 19 AND age < 85 THEN CONCAT( FLOOR(age/5) * 5 , '-', FLOOR(age/5) * 5 + 4)
ELSE 'その他'
END AS ages,
CONCAT(LPAD(prefecture_id, 2, "0"), "_", prefecture ) AS prefecture
FROM
member.member;
Query OK, 2135809 rows affected (15.03 sec)
Records: 2135809 Duplicates: 0 Warnings: 0ENGINE=MEMORY以外は全く一緒だけど、ロード時間は約2.5倍メモリの方が早い。CSVのデータは334MB。
メモリめっちゃ使ってる。
yu_yamazaki@umicos-mysql:~$ free -h
total used free shared buff/cache available
Mem: 3.6G 2.4G 286M 5.2M 976M 1.0G
Swap: 0B 0B 0B気を付けたいのが、メモリだからあたりまえだけど、サーバー自体の再起動、MySQLの再起動でもメモリのものはテーブルが空になってしまうこと。
テーブルは残るので、再度データを入れる場合は、上のCREATE TABLEそのままは使えず、事前にDROP TABLEするか、INSERT用に一部書き直す必要がある。
次にローカルからDSから投げられるのに近い、GROUP BY＋countで速度をチェック。
ディスク
mysql> SELECT ages, count(*) FROM member.member_datastudio_disk GROUP BY ages ORDER BY ages;
+-----------+----------+
| ages | count(*) |
+-----------+----------+
| 20-24 | 51921 |
| 25-29 | 114889 |
| 30-34 | 182203 |
| 35-39 | 263532 |
| 40-44 | 315729 |
| 45-49 | 463698 |
| 50-54 | 249621 |
| 55-59 | 200168 |
| 60-64 | 132682 |
| 65-69 | 91182 |
| 70-74 | 36624 |
| 75-79 | 14316 |
| 80-84 | 4738 |
| その他 | 14506 |
+-----------+----------+
14 rows in set (3.96 sec)
メモリ
mysql> SELECT ages, count(*) FROM member.member_datastudio_memory GROUP BY ages ORDER BY ages;
+-----------+----------+
| ages | count(*) |
+-----------+----------+
| 20-24 | 51921 |
| 25-29 | 114889 |
| 30-34 | 182203 |
| 35-39 | 263532 |
| 40-44 | 315729 |
| 45-49 | 463698 |
| 50-54 | 249621 |
| 55-59 | 200168 |
| 60-64 | 132682 |
| 65-69 | 91182 |
| 70-74 | 36624 |
| 75-79 | 14316 |
| 80-84 | 4738 |
| その他 | 14506 |
+-----------+----------+
14 rows in set (2.67 sec)
あれ、、、あんまり変わらない(´･ω･`)
SHOW CREATE TABLEで確認したけど、ENGINEに間違いはない。
よくあるMySQLのチューニングもだいたいInnoDB用のものだから、メモリテーブルには使えない。
試しにテスト用に使うagesにインデックスも貼ってみたけど、大して早くならなかった。さらにDSからの実利用時はほとんどのカラムを条件に使うので、インデックスは意味なさそう。
ここまできて改めて実感するBigQueryの速さと安さ。メモリテーブルを使っても1台の普通のマシンでは太刀打ちできないのか。
試しにDSのデータソースに選択して試してみるとすぐ、DS上にエラーが出た
エラーの詳細
データベースに接続できません。詳細については、こちらをクリックしてください。
Unable to connect to database: Communications link failure
The last packet successfully received from the server was 2,553 milliseconds ago. The last packet sent successfully to the server was 2,532 milliseconds ago.
実際どんなクエリが出てくるか見たいので、スロークエリーログを出す設定追加。
今回の環境だとファイルは/etc/mysql/mysql.conf.d/mysqld.cnfで、コメントアウトされてる「log_slow_queries」は有効化するとMySQLが起動しなくなる罠だった。
long_query_time = 3
slow_query_log_file = /var/log/mysql/mysql-slow.log
slow_query_log = 1
DSの方をちょっといじるだけでモリモリ出てきた。日本語は文字化けしている。
yu_yamazaki@umicos-mysql:~$ tail -f /var/log//mysql/mysql-slow.log
# Time: 2017-08-23T03:51:28.192125Z
# User@Host: DATASTUDIO[DATASTUDIO] @ [74.125.72.101] Id: 39
# Query_time: 11.794037 Lock_time: 0.000166 Rows_sent: 14 Rows_examined: 2135837
SET timestamp=1503460288;
SELECT COUNT(member_id) AS _member_id_, ages FROM member_datastudio_memory AS t0 WHERE (es_id NOT IN ('00_(æªåç)', '01_1ï½9å') AND industry NOT IN ('00_(æªåç)', '01_å°å£²ã»å¸å£²ã»å社')) GROUP BY ages ORDER BY ages ASC;
# Time: 2017-08-23T03:51:28.267944Z
# User@Host: DATASTUDIO[DATASTUDIO] @ [74.125.72.100] Id: 41
# Query_time: 11.822358 Lock_time: 0.000203 Rows_sent: 4 Rows_examined: 2135817
SET timestamp=1503460288;
SELECT COUNT(member_id) AS _member_id_, gender FROM member_datastudio_memory AS t0 WHERE (ages NOT IN ('20-24', '25-29') AND es_id NOT IN ('00_(æªåç)', '01_1ï½9å') AND industry NOT IN ('00_(æªåç)', '01_å°å£²ã»å¸å£²ã»å社')) GROUP BY gender ORDER BY gender ASC;
# Time: 2017-08-23T03:51:28.936976Z
# User@Host: DATASTUDIO[DATASTUDIO] @ [74.125.72.101] Id: 36
# Query_time: 12.705882 Lock_time: 0.000213 Rows_sent: 3519 Rows_examined: 2142847
SET timestamp=1503460288;
SELECT DATE_FORMAT(t0.create_date, '%Y%m%d') AS _create_date_ FROM member_datastudio_memory AS t0 WHERE (ages NOT IN ('20-24', '25-29') AND es_id NOT IN ('00_(æªåç)', '01_1ï½9å') AND industry NOT IN ('00_(æªåç)', '01_å°å£²ã»å¸å£²ã»å社')) GROUP BY _create_date_ ORDER BY _create_date_ DESC;
# Time: 2017-08-23T03:51:29.577944Z
# User@Host: DATASTUDIO[DATASTUDIO] @ [74.125.72.101] Id: 38
# Query_time: 13.205146 Lock_time: 0.000186 Rows_sent: 48 Rows_examined: 2135905
SET timestamp=1503460289;
SELECT COUNT(member_id) AS _member_id_, prefecture FROM member_datastudio_memory AS t0 WHERE (ages NOT IN ('20-24', '25-29') AND es_id NOT IN ('00_(æªåç)', '01_1ï½9å') AND industry NOT IN ('00_(æªåç)', '01_å°å£²ã»å¸å£²ã»å社')) GROUP BY prefecture ORDER BY prefecture ASC;
# Time: 2017-08-23T03:51:29.773474Z
# User@Host: DATASTUDIO[DATASTUDIO] @ [74.125.72.96] Id: 40
# Query_time: 13.321377 Lock_time: 0.000142 Rows_sent: 26 Rows_examined: 2135861
SET timestamp=1503460289;
SELECT COUNT(member_id) AS _member_id_, job_spec1 FROM member_datastudio_memory AS t0 WHERE (ages NOT IN ('20-24', '25-29') AND es_id NOT IN ('00_(æªåç)', '01_1ï½9å') AND industry NOT IN ('00_(æªåç)', '01_å°å£²ã»å¸å£²ã»å社')) GROUP BY job_spec1 ORDER BY job_spec1 ASC;
# Time: 2017-08-23T03:51:30.379144Z
# User@Host: DATASTUDIO[DATASTUDIO] @ [74.125.72.34] Id: 37
# Query_time: 14.134725 Lock_time: 0.000184 Rows_sent: 8 Rows_examined: 2135825
SET timestamp=1503460290;
SELECT COUNT(member_id) AS _member_id_, mp_id FROM member_datastudio_memory AS t0 WHERE (ages NOT IN ('20-24', '25-29') AND es_id NOT IN ('00_(æªåç)', '01_1ï½9å') AND industry NOT IN ('00_(æªåç)', '01_å°å£²ã»å¸å£²ã»å社')) GROUP BY mp_id ORDER BY mp_id ASC;エラーログも見てみる
yu_yamazaki@umicos-mysql:~$ sudo vim /var/log/mysql/error.log
2017-08-23T03:29:52.402311Z 23 [Warning] IP address '74.125.72.97' could not be resolved: Name or service not known
2017-08-23T03:29:52.408403Z 17 [Note] Aborted connection 17 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.96' (Got an error reading communication packets)
2017-08-23T03:29:52.408585Z 25 [Warning] IP address '74.125.72.97' could not be resolved: Name or service not known
2017-08-23T03:30:49.259759Z 14 [Note] Aborted connection 14 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.34' (Got an error reading communication packets)
2017-08-23T03:30:49.288349Z 29 [Warning] IP address '74.125.72.33' could not be resolved: Name or service not known
2017-08-23T03:30:49.302459Z 16 [Note] Aborted connection 16 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.100' (Got an error reading communication packets)
2017-08-23T03:30:50.402584Z 15 [Note] Aborted connection 15 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.100' (Got an error reading communication packets)
2017-08-23T03:30:52.305685Z 35 [Warning] IP address '74.125.72.37' could not be resolved: Name or service not known
2017-08-23T03:31:12.423806Z 20 [Note] Aborted connection 20 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.100' (Got an error reading communication packets)
2017-08-23T03:31:17.011812Z 23 [Note] Aborted connection 23 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.97' (Got an error reading communication packets)
2017-08-23T03:31:17.403391Z 27 [Note] Aborted connection 27 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.99' (Got an error reading communication packets)
2017-08-23T03:31:17.719796Z 24 [Note] Aborted connection 24 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.100' (Got an error reading communication packets)
2017-08-23T03:31:18.881199Z 22 [Note] Aborted connection 22 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.34' (Got an error reading communication packets)
2017-08-23T03:31:19.107785Z 21 [Note] Aborted connection 21 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.96' (Got an error reading communication packets)
2017-08-23T03:32:28.482330Z 13 [Note] Aborted connection 13 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.101' (Got an error reading communication packets)
2017-08-23T03:34:55.629272Z 26 [Note] Aborted connection 26 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.99' (Got an error reading communication packets)
2017-08-23T03:35:54.224650Z 28 [Note] Aborted connection 28 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.99' (Got an error reading communication packets)
2017-08-23T03:35:58.123819Z 32 [Note] Aborted connection 32 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.36' (Got an error reading communication packets)
2017-08-23T03:36:20.635054Z 35 [Note] Aborted connection 35 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.37' (Got an error reading communication packets)
2017-08-23T03:36:38.842405Z 33 [Note] Aborted connection 33 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.101' (Got an error reading communication packets)
2017-08-23T03:37:02.263344Z 25 [Note] Aborted connection 25 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.97' (Got an error reading communication packets)
2017-08-23T03:37:02.287946Z 30 [Note] Aborted connection 30 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.36' (Got an error reading communication packets)
2017-08-23T03:38:19.575781Z 31 [Note] Aborted connection 31 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.101' (Got an error reading communication packets)
2017-08-23T03:39:16.481444Z 34 [Note] Aborted connection 34 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.96' (Got an error reading communication packets)
2017-08-23T03:41:07.126064Z 29 [Note] Aborted connection 29 to db: 'member' user: 'DATASTUDIO' host: '74.125.72.33' (Got an error reading communication packets)
なんと別々のIPから同時にアクセスが来ている。これがエラーの原因かも。
"could not be resolved: Name or service not known"のWraningは設定にskip-name-resolveを追加で出なくなった。
"Got an error reading communication packets"の方は、検索するとアプリケーション側の切断方法がよろしくないとのことで、MySQL側の問題ではなかった。
最大でどのくらいコネクションかを確認する
architect.hatenadiary.jp
mysql> SHOW STATUS LIKE 'Max%';
+-------------------------------+---------------------+
| Variable_name | Value |
+-------------------------------+---------------------+
| Max_execution_time_exceeded | 0 |
| Max_execution_time_set | 0 |
| Max_execution_time_set_failed | 0 |
| Max_used_connections | 17 |
| Max_used_connections_time | 2017-08-23 04:55:29 |
+-------------------------------+---------------------+
5 rows in set (0.02 sec)
多くても17だった。
設定は151なので全然問題ない。
mysql> SHOW VARIABLES LIKE 'Max%';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| max_allowed_packet | 16777216 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_digest_length | 1024 |
| max_error_count | 64 |
| max_execution_time | 0 |
| max_heap_table_size | 2147483648 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_points_in_geometry | 65536 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
+----------------------------+----------------------+
23 rows in set (0.01 sec)
と、なるとやっぱり一つ一つクエリが重いうえに、それが同時にたくさん来るのが悪いことになる。
DSが作るクエリはシンプルなので特に改善策が思いつかないうえに、思いついたとしてもこちらでは細かく制御できない。
複数台分散ぐらいしか今のところ思いつかない。
![エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド](https://images-fe.ssl-images-amazon.com/images/I/41oqE-9dM2L._SL160_.jpg "エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド")
