GAミント至上主義

Web Monomaniacal Developer.

Google Data StudioのためにMySQLのENGINE=MEMORYの調査をした

昨日書いた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: 0

ENGINE=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台の普通のマシンでは太刀打ちできないのか。

実践ハイパフォーマンスMySQL 第3版

実践ハイパフォーマンスMySQL 第3版


試しに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側の問題ではなかった。

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド

エキスパートのための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 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド