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文を生成するコードを書いてみました。
恥ずかしいけど、書いたコードは以下です。環境はEclipse202006@Windows10です。
''' 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文を色々計測したい。
今日は以上!
コメント