仕事中の問題と解決メモ。

最近はPythonとGoogle Cloud Platformがメイン。株式会社ビズオーシャンで企画と開発運用、データ活用とか。 http://mstdn.bizocean.co.jp/@uyamazak https://github.com/uyamazak/

早速App Engineのmanaged SSLを有効化してみる

Let's Encryptから大きくなったSSL自動化の流れから、いつか来るだろうと思ってたのがついに来た。さっそく有効にした。

cloudplatform.googleblog.com

既存のプロジェクトはCloud Consoleで設定が必要。簡単だった。

f:id:uyamazak:20170919101409p:plain

証明書は思っていた通りLet's Encrypt発行のものだった。


今度は、HTTPロードバランサーの方も対応してくれるのを待つ。

Raspberry Piで既存のコードを利用して複数のLEDチカチカさせたり、ブザーを鳴らしたりする

研究開発という建前で経費でRaspberry pi 3を買った。

いろいろ揃えるのが面倒なので下記のセットを購入。


Google Assistant SDKを入れていろいろやろうとしたけど、まだ英語しか使えず勇気を振り絞って「Ok,Google」といっても、私の発音では反応してくれないのが辛すぎるので、まずはLEDをチカチカさせるLチカから始めようと入門書と、スターターキットを買った。


本の通り、まずは単体でLEDを光らせるのは30分もかからず終わってしまったので、せっかくなので、他のシステムと組み合わせてたくさんチカチカさせようと思った。

そして考えた。データ収集を行っているoceanusではbizoceanの各種イベント(ページビューとか、書式ダウンロードとか)をGoogle Cloud Pub/Subに流しているので、それを受信して種類によっていろんなLEDを光らせればいいと。

github.com

Google Cloud Pub/Sub とは  |  Cloud Pub/Sub  |  Google Cloud Platform


と、やろうとしたときにはRaspbery pi 3は新人の勉強用になってしまったので、以前いた人が買って引き出しに封印されていた古いRaspbery pi Model B+でやることにした。GPIOは全く一緒なので処理がめっちゃ遅い以外は特に不都合がなかった。

で、できたやつがこれ。

ページビューで白、書式ダウンロードで緑、会員登録で黄色、会員退会で赤とブザーが鳴る。

f:id:uyamazak:20170914105254j:plain

f:id:uyamazak:20170914105300j:plain

f:id:uyamazak:20170914105304j:plain


コードは基本的にはrevelationを使ってるけど、一部下記のように書きたしたりしている。

ラズパイ操作用

from RPi import GPIO

# 前回が異常終了だと起動時にエラーで起動できないので最初にやってしまう。それはそれでエラーが出るけど気にしない
GPIO.cleanup()


# 使うGPIOのポートと繋げたLEDの色の頭文字でdictにして、指定しやすくした
GPIO_PORTS = {"w": 22,
              "y": 23,
              "g": 24,
              "r": 25,
              }
# 起動時のセットアップ
for key, num in GPIO_PORTS.items():
    GPIO.setmode(GPIO.BCM)
    GPIO.setup(num, GPIO.OUT)

# 光らせる関数を作る
def flash_led(key, time=0.01):
    GPIO.output(GPIO_PORTS[key], GPIO.HIGH)
    sleep(time)
    GPIO.output(GPIO_PORTS[key], GPIO.LOW)

こっちが光らせる関数を呼び出す部分を抜粋。少ないしめんどいからif文。
イベントの重要度によって光る時間も変えている。

if data.get("evt") == "pageview":
    logger.info("{}:pageview".format(data.get("dt")))
    flash_led("w")
    if "/download/complete" in data.get("url"):
        logger.info("{}:download".format(data.get("dt")))
        flash_led("g", 1)
    if "/entry/complete" in data.get("url"):
        logger.info("{}:entry".format(data.get("dt")))
        flash_led("y", 1)
    if "/quit/complete" in data.get("url"):
        logger.info("{}:quit".format(data.get("dt")))
        flash_led("r", 0.5)

問題としては、Googleの公式Pub/Subクライアントが重いせいか、ラズパイB+では処理が追い付かずどんどん溜まってしまい、しばらくするとスレッドが起動できずに死亡するところ。

おそらく3で動かせば大丈夫なはず。

1日もかからない作業だったけど、GPIOで3VのON、OFFができることは感覚的に理解できた。

次はスターターキットの各種センサーを使った何かをやってみる。

pythonのslackbotを使ってJSONを整形するボットを作る

新しく入ってきた人にPythonでSlack上の会話を翻訳するbotを作るにあたり、まずは自分でも作ってみた。

yfp5521.hatenablog.com


使ったライブラリはこれ。普通にpipで入れられてすぐ使える。
GitHub - lins05/slackbot: A chat bot for Slack (https://slack.com).


もう一つGoogle Cloud Natural Language APIの結果を返す動作確認用のボットも作ったけど、ぐちゃぐちゃなので公開しづらい。



下記をjson.pyとして、slackbotのインストール時に作ったpluginsに置く。

# coding: utf-8
from slackbot.bot import respond_to     # @botname: で反応するデコーダ
# from slackbot.bot import listen_to      # チャネル内発言で反応するデコーダ
# from slackbot.bot import default_reply    # 該当する応答がない場合に反応するデコーダ
import json
import logging
logger = logging.getLogger(__name__)


@respond_to('^[\{\[].*[\}\]]$')
def json_listen_func(message):
    json_text = message._body.get("text")
    logger.debug(json_text)
    try:
        json_obj = json.loads(json_text)
    except Exception as e:
        logger.debug(e)
    else:
        text = json.dumps(json_obj,
                          ensure_ascii=False,
                          indent=2,
                          sort_keys=True)
        users = message._client.users
        attachments = [{"text": text,
                        "author_name": users.get(message._body["user"], "none"),
                        
        message.reply_webapi("",
                             attachments=attachments,
                             as_user=True,
                             in_thread=False)

slackbot_settings.pyは下記のような感じ。

# coding: utf-8
# botアカウントのトークンを指定
API_TOKEN = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

# このbot宛のメッセージで、どの応答にも当てはまらない場合の応答文字列
DEFAULT_REPLY = "ルームに招待してくれたら自然言語解析、ダイレクトメッセージでJSONの変換ができるよ"

# プラグインスクリプトを置いてあるサブディレクトリ名のリスト
PLUGINS = ['plugins.json']

ERRORS_TO = "uyamazak"

DEBUG = True

ざっくりと条件として、@respond_toで最初と最後が{}、[]だけのときに反応するようにしている。

どっかのサーバーで動かすと下記のようになる

f:id:uyamazak:20170912135633p:plain

よくデータをBigQueryにJSONでぶち込んでるけど、その中身をさっと確認したいとき、今まではWEBサービスをググって使ってたけどslackでもできるようになった。


新しく入ってきた人にLinux、vim、Dockerなど開発環境を叩き込む1週間

詳しくは本人のブログだけど


yfp5521.hatenablog.com


大きめのSlerに1年ちょっといた人がビズオーシャンに入ってきてくれた。


bizocean本体はクラウドではなく、データーセンターにあったり、CentOS6だったりPHPだったり、いろいろと退屈なので、私がoceanusの開発でやっている環境をとりあえず叩き込んだ。

WEB系の開発がやりたいとのことだったので、まずは全体像を理解してもらうという目的。


具体的には

  • 作業はすべて自分のPC(Windows)から社内のサーバー(Ubuntu)にSSHでつなげて行う。
  • 効率化のためにzsh使う
  • エディタはvimIDEなんぞいらん市ね。
  • tmux使う
  • 開発はすべてDockerのコンテナを使って構築、動作までさせることで、サーバー構成の明文化、本番まで一気通貫を目指す。


と、Linuxすらあんまり触ったことない人にとって、いたるところに無理がある内容だったけど、実際にDockerコンテナを立てるのを何回かやってもらって、全体のイメージはつかんでくれたっぽい。


5日目ぐらいに、部長から社内に英語しかしゃべれない外国人がいるので、翻訳bot作ってよと来たので、ちょうどいいと思い作ってもらった。


横から口出しをしながら、Google Translate APIを使うことで、多言語に対応したボットを1日半ぐらいで完了し、その外国人との会話テストまでやってもらえて、1週間で小さい成功体験ができたのはよかったと思う。


PyQも試しに契約して進めてもらってるけど、実際に役立つものが作れるなら作った方が面白いと思った。



次は、Linuxの勉強もかねて、会社に転がっているRaspbery pi 3の初期インストールから、Dockerの動作環境づくりまで行い、作ったbotをそこで動かしてもらおうと思う。

買ったのは下記のフルセット。SDカードは彼用に新しく買った。


早速インストールしたけど動かないと言われ、もしかしたらSDカードの相性問題かと思ったので、私が試しにやったら問題なく動いた。

Linux教材としてはラズパイの当初の目的だけあって、かなりいいと思う。安いから1人1台買ってもいいし、SDを買い足すことで使いまわしも簡単。

SDはこれ買った。もちろん使えるかどうか私は保証できない。

scikit-learnを使ってナイーブベイズでお問合せ分類するまでの流れ

bizoceanのお問合せを自動で分類して、定型文で済むやつは送信前に出して、サポートコストの削減とともにユーザーがすぐ問題を解決できるようにしようと思い、まずはナイーブベイズを試してみる。

環境は、Dockerで動かしたJupyterと、Python3.6とsklearn等を使う。

最近は小さなものから大きなものまで動かす力だDockerコンテナ。

ベイズを使ったフィルタリングは個人的に尊敬しているプログラマPaul Grahamも昔提唱しており、自分の中で定番化している。

ベイジアンフィルタは2002年にポール・グレアムが発表した論文“A Plan for Spam”が元になって開発された[2][3][1]。さらに改良されたアルゴリズムは“Better Bayesian Filtering”に発表されている[4][5]。

ベイジアンフィルタ - Wikipedia

まずは全体的なデータの流れを整理する。

生のソースコードは本物の問い合わせ内容が入ってしまっていて、再現性のあるコードの用意が大変なのでまた今度。

1 訓練データ(テキストと分類ラベルのペア)を準備する。

CSVでやるのが普通だと思う。CSV化にあたって個人情報やテストデータなどを消してしまう。もちろんCSVなのでテキストに改行が入ってしまっている場合は消しておく。

句読点とか括弧とか意味のないものも手で消してしまってもいいけど、そういうのは後でもライブラリ側で消せるのでしない方がいい。自動できるものはしておかないと、新しいデータが入った時に手間が増える。

ラベル付けはまず手作業で行う必要がある。

scikit-learnのチートシートではデータ数が50以下だったら「get more data」となっている。

分類数にもよるけど、ある程度作ったら、実際に動かしながら追加していった方が無駄にならないかも。

Choosing the right estimator — scikit-learn 0.19.0 documentation


このチートシートでナイーブベイズはサンプル数が100K以下の場合となっているのに気付き、今回は多くても5kしか用意できないからちょうど良さげ。100K以上のときはSGD Classifierとやらの出番らしいことを知る。

実際のCSVはこんな感じ。雑多な奴は全部「その他」をつけてしまった。

書式の使い方を教えてください,書式について
登録できません,登録について
ログインできません,ログインについて
パスワードはあっているのに入れません,ログインについて

ラベルは、数字などのIDでもいいけど、日本語のままでも問題ない。

2 日本語は分かち書きする

自然言語処理では単語ごとにデータが分かれている必要がある。

英語などもともとスペースで区切られたアルファベット系言語等でなければ分かち書きが必要になる。アラビア語もよく知らんけど画像を見る感じスペースあるな。スペースがないのは漢字圏の特徴だろうか。

今まではMecabを使うことが多かったけど、先週GoogleのNatural Language APIを使った記事を書いたように今回もそっちを使う。

データ量、目的、コストによってどっちがいいかは変わってくる。

個人的にはデータ量が多く速度、コスト、オリジナル辞書を重視するならMecab、構築スピードと運用の楽さであればGoogleを使う。

uyamazak.hatenablog.com

3 データをベクタライズする

分かち書きされた単語そのままでは計算ができないため、単語と出現頻度の数字の配列にする必要がある。

ただ単にカウントするもので十分な場合もあれば、TFIDFを計算した方がよいこともある。

tf-idf - Wikipedia

もちろん自分で書く必要はなく、sklearnに用意されているので、詳細までわからなくてもできてしまう。

Working With Text Data — scikit-learn 0.19.0 documentation

4 分類器を作成する

3で出来上がったデータを教師として作成する。

scikit-learnには、GaussianNB, BernoulliNB, MultinomialNBの3種類が用意されており、簡単に比較することができる。

TFIDF版のテストはMultinomialNBだけで行った。

multinomial_clf = MultinomialNB()
multinomial_clf.fit(X_train_v, Y_train)
print("multinomial_clf\n{}\n".format(multinomial_clf.score(X_train_v, Y_train)))

# IFIDF
multinomial_tfidf_clf = MultinomialNB()
multinomial_tfidf_clf.fit(X_train_tfidf, Y_train)
print("multinomial_tfidf_clf\n{}\n".format(multinomial_tfidf_clf.score(X_train_tfidf, Y_train)))

bernoulli_clf = BernoulliNB()
bernoulli_clf.fit(X_train_v, Y_train)
print("bernoulli_clf\n{}\n".format(bernoulli_clf.score(X_train_v, Y_train)))

gaussian_clf = GaussianNB()
gaussian_clf.fit(X_train_v.toarray(), Y_train)
print("gaussian_clf\n{}\n".format(gaussian_clf.score(X_train_v.toarray(), Y_train)))

multinomial_clf
0.907537688442211

multinomial_tfidf_clf
0.8979899497487437

bernoulli_clf
0.8748743718592965

gaussian_clf
0.8020100502512563

bizoceanの問い合わせデータだとMultinomialNB + CountVectorizerが無難そう。

5 未知のデータでテストしてみる

教師に使っていないテストデータを用意して判定させる。

テストの文章についても、分類器を作る際と同じように分かち書きベクター化が必要になるので、前処理は関数化しておいた方がいい。

今やってるものだと、10個出して正解が8,9個だった。

ベクター化しているので、scikit-learnの他の分類アルゴリズムも使うことができそうなので、試してみる予定。


ナイーブベイズは単純ベイズと訳されるようにシンプルで軽いところがいい。


Pythonではじめる機械学習 ―scikit-learnで学ぶ特徴量エンジニアリングと機械学習の基礎

Pythonではじめる機械学習 ―scikit-learnで学ぶ特徴量エンジニアリングと機械学習の基礎

入門 自然言語処理

入門 自然言語処理

日本語の分かち書きにGoogle Cloud Natural Language APIを使う

日本語の文章を、検索用のデータにしたり、機械学習などする際に必須になる分かち書き

これまではMecabを使ってたけど、インストールが必要になり、辞書も変えたりすると結構大変。

そこで、Google Cloud Natural Language APIを使ってみることにした。

cloud.google.com


環境はプロトタイプ作りにも便利なJupyter+Python3.6をDockerを使って社内サーバーで動かして使っている。
一つなのでdocker-composeは使っていない。

Dockerfile

ROM jupyter/notebook

RUN pip install --upgrade -q \
pip \
datalab

RUN pip install --upgrade google-cloud-language

ENV GOOGLE_APPLICATION_CREDENTIALS service_account.json

service_account.jsonは公式ドキュメント通りにとってきておいておく。

クイックスタート  |  Google Cloud Natural Language API ドキュメント  |  Google Cloud Platform


これをjupyter-devというタグをつけてビルドし、

sudo docker build -t jupyter-dev .


docker run時に/notebooksをマウントするのを忘れないようにする。消えちゃうから。

sudo docker run \
        -d \
        -v /home/username/path/to/notebooks:/notebooks \
        -p 8282:8888 \
        jupyter-dev \
        jupyter notebook


jupyter上でメッセージを渡すと、スペース区切りで分かち書きしたものと、感情スコア、感情のマグニチュードだけを、とりあえず返す関数を作る

# Imports the Google Cloud client library
from google.cloud import language
from google.cloud.language import enums
from google.cloud.language import types
client = language.LanguageServiceClient()

def get_analyze_results(message):    
    document = types.Document(
        content=message,
        type=enums.Document.Type.PLAIN_TEXT)
    
    syntax_response = client.analyze_syntax(
        document=document,
    )
    separeted_text = " ".join([s.text.content for s in syntax_response.tokens])
    
    sentiment_response = client.analyze_sentiment(document=document)
    return {
        "separeted_text": separeted_text,
        "magnitude": sentiment_response.document_sentiment.magnitude,
        "score": sentiment_response.document_sentiment.score
    }
    

実行すると

get_analyze_results("試合を終えて家路へ向かうサッカー部員達。疲れからか、不幸にも黒塗りの高級車に追突してしまう。後輩をかばいすべての責任を負った三浦に対し、車の主、暴力団員谷岡が言い渡した示談の条件とは・・・。")
{'magnitude': 1.100000023841858,
 'score': 0.0,
 'separeted_text': '試合 を 終え て 家路 へ 向かう サッカー 部 員 達 。 疲れ から か 、 不幸 に も 黒 塗り の 高級 車 に 追突 し てしまう 。 後輩 を かばい すべて の 責任 を 負っ た 三浦 に対し 、 車 の 主 、 暴力 団員 谷岡 が 言い渡し た 示談 の 条件 とは・・・ 。'}

もっと細かい単語同士の関係とかも返してくれるようだけど、難しすぎてよくわからない。

この関数でbizoceanのお問合せを分かち書きしたものをナイーブベイズで分類してカテゴリを返すAPIみたいのを作ろうとしている。

感情分析はシンプルでいいと思うけど、実際読んでる感じとちょっとずれもあって、使いどころが思いつかない。


Mecabとかを使わずにGoogleのアカウントと少しのお金があれば使えるので、細かいカスタマイズが不要な普通の分かち書き程度に使うのなら非常に便利だと思う。

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が作るクエリはシンプルなので特に改善策が思いつかないうえに、思いついたとしてもこちらでは細かく制御できない。

複数台分散ぐらいしか今のところ思いつかない。