RaspberryPi4にMySQL8.0.20をソースから入れてみたけど自信がない。

cmakeのオプションとか、本当に正しいのか分からないので、今現在も動いているように見えるMySQLさんが、本当に「正常」に動く代物なのか疑わしく思っている。

なので、今日は大量データをinsertしたりdeleteしたりupdateしたり色々selectしたりしてみたいと思う。

と思っていたが大量データinsertで早速エラーに遭遇したので、メモ。

ちなみにエラーメッセージは「MySQL server has gone away」ってやつです。

あとその前に。

RaspberryPi 4GB、さらに2台買っちゃいましたwww合計で3台wwwwケースも買ったったわwww

1台目はGitBucketと、あとストレージとして付けてたUSBメモリーやめてバスパワーのHDD 4TBも買って付けたので、いずれファイルサーバーにでも仕立てるつもり。

で、今回買った2台のラズパイにはMySQL8.0.20とMySQL5.7.31入れてみたので、何が違うのか自分なりに見ていこうと思っています。




1.大量データの準備 Python編

以下の記事を参考にしてpythonで大量データをbulk insertするSQL文を生成するコードを書いてみました。

MySQLで大量のテストデータを作製するには?? - Qiita

恥ずかしいけど、書いたコードは以下です。

'''
Created on 2020/07/26
@author: oraora
'''
import random
import time
import codecs
import datetime

# 名前、性別をランダムに生成
def rndNameSex():
    myouji = ["東峰","影山","澤村","田中","成田","灰羽","松川","矢井田","頼久","渡",\
              "岩泉","木下","清水","地井","西谷","日向","三井田","山口","力石","和井田",\
              "菅原","縁下","月島","谷地","武田","烏養","及川", ]
    namae = [["旭","1"],["佳愛","2"],["宰一郎","1"],["泰華","2"],["成意斗","1"],["羽亜桃","2"],["舞人","1"],["耶綾","2"],["雷","1"],["和愛","2"],\
             ["一鉄","1"],["潔子","2"],["翔陽","1"],["力","1"],["新人","1"],["仁花","2"],["美羽","2"],["弥恵","2"],["理恵","2"],["和香","2"],\
             ["飛雄","1"],["大地","1"],["孝支","1"],["夕","1"],["龍之介","1"],["久志","1"],["一仁","1"],["蛍","1"],["忠","1"],["繋心","1"],\
             ["徹","1"],["一","1"],]
    slctMyouji = random.choice(myouji)
    slctNamae = random.choice(namae)
    return slctMyouji + " " + slctNamae[0] + "," + slctNamae[1]

# 年齢をランダムに生成
def rndAge():
    return random.randint(0,100)

# 日時をランダムに生成
def rndDate(start, end):
    dtFormat = '%Y-%m-%d %H:%M:%S'
    stime = time.mktime(time.strptime(start, dtFormat))
    etime = time.mktime(time.strptime(end, dtFormat))
    ptime = stime + random.random() * (etime - stime)
    return time.strftime(dtFormat, time.localtime(ptime))


# 処理開始
startDatetime = datetime.datetime.now()
print(startDatetime.strftime('%Y-%m-%d %H:%M:%S') + " 実行開始")

# 変数初期化
OUTPUT_FILE = "testdata.sql"
RECORD_COUNT = 1000000
useDB = "testdb"
useTBL = "user"
sqlCommands = ""

# bulk insert文の生成
sqlCommands += "USE " + useDB + ";\n"
sqlCommands += "INSERT INTO " + useTBL +" (user_name, user_sex, user_age, ins_date, upd_date) VALUES \n"

# bulk insert文の1行目のデータ生成
nameSex = rndNameSex().split(',')
age  = rndAge()
date = rndDate("2000-1-1 00:00:00", "2019-12-31 23:59:59")
sqlCommands += " ('{}', '{}', '{}', '{}', '{}')\n"\
               .format(nameSex[0], nameSex[1], age, date, date)

# bulk insert文の2行目以降のデータ生成
for _ in range(RECORD_COUNT-1):

    nameSex = rndNameSex().split(',')
    age  = rndAge()
    date = rndDate("2000-1-1 00:00:00", "2019-12-31 23:59:59")
    sqlCommands += ",('{}', '{}', '{}', '{}', '{}')\n"\
                   .format(nameSex[0], nameSex[1], age, date, date)

# bulk insert文の最後
sqlCommands += ";"

# 生成したSQL文をファイルに書き出す
f = codecs.open(OUTPUT_FILE, 'w', 'utf_8')
f.write(sqlCommands)
f.close()

# 処理終了
endDatetime = datetime.datetime.now()
print(endDatetime.strftime('%Y-%m-%d %H:%M:%S') + " 実行終了")

duringTime = endDatetime - startDatetime
s=str(duringTime)
print("処理時間:" +s)

pythonほぼ初めて触ってみました。

今回勉強になったのはList(配列?)と乱数発生とファイル出力かな。

あーあと日付型と文字列の結合でエラったな、型宣言無いのに厳密なのね。って思った。

という感じで、bulk insert文はtestdata.sqlというファイル名で作成されます。

このSQL文をWinSCPでそれぞれのラズパイに転送しておきます。

2.データベース、テーブルの準備

テスト用のデータベースとユーザー、そしてテーブルを以下のSQLで生成します。

create database testdb;
create user 'test_user'@'localhost' identified by 'test_Pa55!';
grant all on testdb.* to 'test_user'@'localhost';
flush privileges;

CREATE TABLE user (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  user_name VARCHAR(45),
  user_sex CHAR(1),
  user_age INT,
  ins_date DATETIME,
  upd_date DATETIME
);

3.データ投入用シェルの作成

MySQL8.0と5.7の入っているそれぞれのラズパイ上で以下のようなシェルを作成しました。

time mysql -utest_user -p'test_Pa55!' < ./testdata.sql
echo $SECONDS

mysqlコマンドでtestdata.sqlを実行する時間を測定するために、timeコマンドを入れています。

あとシェルの単純な実行秒数は$SECONDSで表示。

このシェルに実行権限を付けておきます。

$ chmod 777 instestdata.#!/bin/sh

4.データ投入、そしてエラー

ラズパイ上では以下のような状態だとします。

raspi4_user01@raspberrypi402:~ $ pwd
/home/raspi4_user01
raspi4_user01@raspberrypi402:~ $ ls -lh
合計 73996
-rwxrwxrwx 1 raspi4_user01 raspi4_user01  70  7月 26 02:46 instestdata.sh
-rw-r--r-- 1 raspi4_user01 raspi4_user01 73M  7月 26 16:15 testdata.sql
-rw-r--r-- 1 raspi4_user01 raspi4_user01 157  7月 25 20:54 testdb.sql

bulk insert文は73MBもあります。。。これを実行してデータ投入します。

$ ./instestdata.sh
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2006 (HY000) at line 2: MySQL server has gone away

real    0m3.440s
user    0m2.112s
sys     0m0.867s
4

はい、落ちました。安定のエラー。

エラーメッセージ「MySQL server has gone away

systemctl statusコマンドでmysqld調べたけど、別段落ちていないご様子。

/var/lib/mysqld.logにログ出力指定していたので、その中身も見たけど特に何もなし。

ちなみにMySQL8.0ではシェル実行後4秒程度でエラーとなりますが、MySQL5.7のほうは約32分経ってから上記と同様のエラーとなります。

すぐにエラーとなってくれるMySQL8.0のほうが親切!これがバージョンアップの威力か!

・・・って違うよね。

5.原因と対処法

まぁ大体予想はついているんだけど、投入するSQL文が73MBと大きいのが原因です。

で、これを解消するためにMySQLのパラメーターを変更します。

対処法1.パケットサイズの改善

SQL1文のサイズはmax_allowed_packetの範囲内です。

このサイズを変更することで、長いbulk insert文を実行できるようにします。

変更するにはMySQLのrootユーザーで実施します。

$ mysql -uroot -p
Enter password:

(中略)

mysql>

まずは現状のmax_allowed_packetを確認。

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.03 sec)

67MB、惜しい!こいつを一時的に100MBにします。

mysql> SET GLOBAL max_allowed_packet = 100000000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit

$ mysql -uroot -p
Enter password:

(中略)

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 99999744 |
+--------------------+----------+
1 row in set (0.03 sec)

一度出てから再度mysqlコマンドで入ってshowすれば拡張されていることが確認できます。

ただし、この状態はmysqldが再起動されるまで有効です。

対処法2.タイムアウトの改善

処理に時間がかかりすぎるとタイムアウトになっちゃうので、その値を拡張します。

まずは現状のwait_timeoutを確認。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.03 sec)

はぁ?既に28,800秒=480分=8時間とかデカい値が設定されておりまして、今回は変更の必要はございません!

この値はMySQL8.0もMySQL5.7の両方とも設定されていました。

ちなみに、もし設定を変更するのなら以下のコマンドでOK。

mysql> SET GLOBAL wait_timeout=28800;

6.MySQL8.0 VS MySQL5.7 Bulk Insert対決

結果として対処法1でエラーは出なくなりました。

なので、ここからはBulk Insert対決!

MySQL8.0編

まずはMySQL8.0で実行してみます。

$ ./instestdata.sh
mysql: [Warning] Using a password on the command line interface can be insecure.

real    1m9.259s
user    0m2.228s
sys     0m0.783s
70

やべぇ速いぞ?!

本当にデータ入ってるのか?確かめてみる。

$ mysql -utest_user -p
Enter password:

(中略)

mysql> use testdb;
Database changed

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.14 sec)

入ってるね。。。マジか。。。

MySQL5.7編

次は5.7のほう。普段100万件のbulk insertってどのくらいかかったっけ?

$ ./instestdata.sh
mysql: [Warning] Using a password on the command line interface can be insecure.

real    35m20.587s
user    4m26.402s
sys     25m58.642s
2120

うわ何これ、遅っ!

ちゃんとデータ入ってるよな?!

$ mysql -utest_user -p
Enter password:

(中略)

mysql> use testdb;
Database changed

mysql> select count(*) from user;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.52 sec)

データ入ってました。

ちなみに全件検索も8.0の方が速いね。・・・1回しか試行してないけど。

7.まとめ

今回は2台のRaspberryPi 4GB上にMySQL8.0.20とMySQL5.7.31を構築してmy.cnfもほぼ同じ状態でBulk Insertで100万件データ入れてみて、その時のエラーへの対処と対処後の処理時間を計測してみました。

結果は8.0の圧勝!

5.7のほうはどうしたんだろう。初期パラメータが悪さしてる予感あるけども。

とにかく、これからデータ100万件使って色々測定してみたい。

むしろselect文を色々計測したい。

今日は以上!


【今日の音楽】
ヨルシカ
収録アルバム: 夏草が邪魔をする
2017/6/27