- 投稿日：2021-03-14T22:18:30+09:00
MySQLのVARCHAR型のLENGTHにおけるパフォーマンス比較
はじめに
データベースの速度改善をしているときに、「MySQLのVARCHAR型のLENGTHは255以下では、どの値でもパフォーマンスは一緒」というのをどこかで聞いたことがあるをふと思い出しました。
実際にStackOverflowこの記事ではそれに近いやり取りがなされています。
Importance of varchar length in MySQL tableI have a MySQL table where rows are inserted dynamically. Because I can not be certain of the length of strings and do not want them cut off, I make them varchar(200) which is generally much bigger than I need. Is there a big performance hit in giving a varchar field much more length than necessary?
質問の内容は、varcharの長さを念の為に200にしているが、これはパフォーマンスにどう影響があるのか？というもの。
No, in the sense that if the values you're storing in that column are always (say) less than 50 characters, declaring the column as varchar(50) or varchar(200) has the same performance.
ベストアンサーの回答には50以下の長さしか扱わないならvarchar(50)とvarchar(200)もパフォーマンスは等しいというもの。
またもっともいいねの多い回答ではtemporaryテーブルやmemoryテーブルではパフォーマンスに影響がでると言っています。
There's one possible performance impact: in MySQL, temporary tables and MEMORY tables store a VARCHAR column as a fixed-length column, padded out to its maximum length. If you design VARCHAR columns much larger than the greatest size you need, you will consume more memory than you have to. This affects cache efficiency, sorting speed, etc.
意見が割れていますが、論理的に説明するのが難しそうです。
"百聞は一見にしかず"ということで、実際に検証してみました。検証内容
環境
Docker ImageのMySQL8.0を利用します。
$ mysql --version mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)検証項目
LENGTHが15, 31, 63, 127, 255, 511, 1023のVARCHAR型のカラムを1つだけ持つテーブルを各々用意し、以下の処理における時間を計測します。実際に作成するデータの文字数は6〜12文字です。
- INSERT系
- データ作成
各テーブルに1,000,000件のデータをINSERTしたのときの時間を計測する- UPDATE系
- データ更新
各テーブルに1,000,000件のデータをUPDATEしたのときの時間を計測する- SELECT系
- 全件取得
各テーブルに1,000,000件のデータに対してSELECTしたのときの時間を計測する- 完全一致
各テーブルに1,000,000件のデータに対して完全一致文字列でSELECTしたのときの時間を計測する- 部分一致
各テーブルに1,000,000件のデータに対してLIKEを使った部分一致でSELECTしたのときの時間を計測する- ソート
各テーブルに1,000,000件のデータに対してSELECT、ORDER BYでソートしたのときの時間を計測する- DELETE系
- データ削除
各テーブルに1,000,000件のデータをDELETEしたのときの時間を計測する検証
テーブル作成
以下のSQLで長さがLENGTHが15, 31, 63, 127, 255, 511, 1023のVARCHAR型のカラムも持つテーブルを作成します。
CREATE TABLE length_31 (name varchar(31)); CREATE TABLE length_63 (name varchar(63)); CREATE TABLE length_127 (name varchar(127)); CREATE TABLE length_255 (name varchar(255)); CREATE TABLE length_511 (name varchar(511)); CREATE TABLE length_1023 (name varchar(1023));INSERT系検証
準備
今回はネットワークの速度が結果に影響しないようにProcedureを作成して、そのなかでループさせます。
以下で件数とTABLE名を指定してINSERTするProcedureを作成しています。CREATE PROCEDURE insert_loop(in i int, in table_name varchar(32)) BEGIN DECLARE cnt int default 0; START TRANSACTION; WHILE cnt < i DO SET cnt = cnt + 1; SET @name_value = CONCAT('data_', cnt); SET @sql = CONCAT('INSERT INTO ', table_name, ' VALUES (\'', @name_value,'\')'); PREPARE stmt FROM @sql; EXECUTE stmt; END WHILE; COMMIT; END;結果
TABLE NAME 実行時間 length_15 35 s 847 ms length_31 36 s 120 ms length_63 35 s 92 ms length_127 34 s 361 ms length_255 33 s 781 ms length_511 34 s 327 ms length_1023 34 s 664 ms 考察
いくぶんか差はありますが、誤差の範囲と言えるでしょうか。
UPDATE系検証
先程のINSERT系検証で作成した100万件のデータを更新していきます。
以下のSQLでデータを更新して時間を計測します。UPDATE length_15 SET name = CONCAT(name, '#'); UPDATE length_31 SET name = CONCAT(name, '#'); UPDATE length_63 SET name = CONCAT(name, '#'); UPDATE length_127 SET name = CONCAT(name, '#'); UPDATE length_255 SET name = CONCAT(name, '#'); UPDATE length_511 SET name = CONCAT(name, '#'); UPDATE length_1023 SET name = CONCAT(name, '#');結果
TABLE NAME 実行時間 length_15 10 s 979 ms length_31 11 s 516 ms length_63 10 s 773 ms length_127 10 s 779 ms length_255 10 s 849 ms length_511 10 s 908 ms length_1023 11 s 59 ms 考察
これもほとんど差がありませんでした。
SELECT系検証
- 全行取得
SELECT * FROM length_15; SELECT * FROM length_31; SELECT * FROM length_63; SELECT * FROM length_127; SELECT * FROM length_255; SELECT * FROM length_511; SELECT * FROM length_1023;
- 完全一致
CREATE PROCEDURE select_loop(in i int, in query varchar(128)) BEGIN DECLARE cnt int default 0; WHILE cnt < i DO SET cnt = cnt + 1; SET @query = REPLACE(query, '{cnt}', cnt); PREPARE stmt FROM @query; EXECUTE stmt; END WHILE; END; call select_loop(10, 'SELECT * FROM length_15 WHERE name = \'data_{cnt}\''); call select_loop(10, 'SELECT * FROM length_31 WHERE name = \'data_{cnt}\''); call select_loop(10, 'SELECT * FROM length_63 WHERE name = \'data_{cnt}\''); call select_loop(10, 'SELECT * FROM length_127 WHERE name = \'data_{cnt}\''); call select_loop(10, 'SELECT * FROM length_255 WHERE name = \'data_{cnt}\''); call select_loop(10, 'SELECT * FROM length_511 WHERE name = \'data_{cnt}\''); call select_loop(10, 'SELECT * FROM length_1023 WHERE name = \'data_{cnt}\'');
- 部分一致
call select_loop(10, 'SELECT * FROM length_15 WHERE name like \'%data_{cnt}%\''); call select_loop(10, 'SELECT * FROM length_31 WHERE name like \'%data_{cnt}%\''); call select_loop(10, 'SELECT * FROM length_63 WHERE name like \'%data_{cnt}%\''); call select_loop(10, 'SELECT * FROM length_127 WHERE name like \'%data_{cnt}%\''); call select_loop(10, 'SELECT * FROM length_255 WHERE name like \'%data_{cnt}%\''); call select_loop(10, 'SELECT * FROM length_511 WHERE name like \'%data_{cnt}%\''); call select_loop(10, 'SELECT * FROM length_1023 WHERE name like \'%data_{cnt}%\'');
- ソート
SELECT * FROM length_15 order by name; SELECT * FROM length_31 order by name; SELECT * FROM length_63 order by name; SELECT * FROM length_127 order by name; SELECT * FROM length_255 order by name; SELECT * FROM length_511 order by name; SELECT * FROM length_1023 order by name;結果
TABLE NAME 全行取得 完全一致 部分一致 ソート length_15 5 ms 1 s 693 ms 46 ms 1 s 667 ms length_31 6 ms 1 s 724 ms 52 ms 1 s 723 ms length_63 7 ms 1 s 694 ms 54 ms 1 s 723 ms length_127 4 ms 1 s 712 ms 54 ms 1 s 735 ms length_255 6 ms 1 s 692 ms 44 ms 1 s 714 ms length_511 8 ms 1 s 710 ms 63 ms 1 s 578 ms length_1023 7 ms 1 s 703 ms 51 ms 1 s 708 ms 考察
これもほとんど差がありませんでした。
DELETE系検証
- データ削除
DELETE FROM length_15; DELETE FROM length_31; DELETE FROM length_63; DELETE FROM length_127; DELETE FROM length_255; DELETE FROM length_511; DELETE FROM length_1023;結果
TABLE NAME 実行時間 length_15 2 s 360 ms length_31 2 s 332 ms length_63 2 s 364 ms length_127 2 s 347 ms length_255 2 s 281 ms length_511 2 s 436 ms length_1023 2 s 471 ms 考察
これも差がないと言って差し支えなさそうです。
結果まとめ
全検証をまとめると以下のようになりました。
INSERET系 UPDATE系 SELECT系 DELETE系 TABLE NAME データ作成 データ更新 全件取得 完全一致 部分一致 ソート データ削除 length_15 35 s 847 ms 10 s 979 ms 5 ms 1 s 693 ms 46 ms 1 s 667 ms 2 s 360 ms length_31 36 s 120 ms 11 s 516 ms 6 ms 1 s 724 ms 52 ms 1 s 723 ms 2 s 332 ms length_63 35 s 92 ms 10 s 773 ms 7 ms 1 s 694 ms 54 ms 1 s 723 ms 2 s 364 ms length_127 34 s 361 ms 10 s 779 ms 4 ms 1 s 712 ms 54 ms 1 s 735 ms 2 s 347 ms length_255 33 s 781 ms 10 s 849 ms 6 ms 1 s 692 ms 44 ms 1 s 714 ms 2 s 281 ms length_511 34 s 327 ms 10 s 908 ms 8 ms 1 s 710 ms 63 ms 1 s 578 ms 2 s 436 ms length_1023 34 s 664 ms 11 s 59 ms 7 ms 1 s 703 ms 51 ms 1 s 708 ms 2 s 471 ms どの検証でもLENGTHによる差はないように見えます。
さいごに
データをただCRUDするだけであれば、LENGTHの長さによる速度の差はほとんどありませんでした。INDEXを指定した場合や、UNIQUE制約を付けた場合は結果が変わるかもしれません。またバリデーションという意味でも無駄にLENGTHを大きくする意味はないですが、ギリギリをせめて文字数がオーバーしてマイグレーションが必要になるということが起きるのであれば、少し大きめに設定しておくといいかもしれません。
いつかダンプサイズやメモリ使用量の差、INDEX、UNIQUEを指定したときの差なども調査したいです。
- 投稿日：2021-03-14T22:09:48+09:00
17rep - GolangでMySQLのトランザクションラッパー作成
使うシーン
- 一括で複数のInsert Updateで全部成功させないといけないシーン
- リレーションを貼っているテーブルに対してデータの更新や追加などを一括して追加変更しないといけないテーブルが存在するシーン
Go言語でトランザクション制御
- トランザクション制御のラッパー関数の作成
- トランザクションラッパー関数を開始し、エラーだったらrollbackを実行エラーじゃなければcommit
Begin()、Commit()、Rollback()が同じ関数内に定義(これは冗長)
- SqlHandlerポインタレシーバからDB接続を定義し同じ関数内で実行
func (h *SqlHandler) WithTransaction() (err error) { tx, err := h.db.Begin() if err != nil { return } defer func() { if err != nil { tx.Rollback() return } err = tx.Commit() }() if _, err = tx.Exec(...); err != nil { return } if _, err = tx.Exec(...); err != nil { return } // ... return } func (t *sql.Tx)Exec(...){ // Insert or Update SQL // ... }トランザクションラッパー関数
- 実行したい関数を引数 txFuncとして受け取り、トランザクションを開始してからtxFuncを実行
- txFuncの中でerrorもしくはpanicが起きたらdeferでrecoverを使って広い、rollbackを実行
- 問題がなければcommitという流れです。
- トランザクション制御を行う際に、トランザクション境界線がリクエストの開始〜終了となりますが、ラッパー関数方式の場合トランザクション境界を柔軟に変更可能
func (t *sqlRepository) ExecWithTx(txFunc func(*sql.Tx) error) (err error) { tx, err := t.db.Begin() if err != nil { return } defer func() { if p := recover(); p != nil { tx.Rollback() } else if err != nil { tx.Rollback() } else { err = tx.Commit() } }() err = txFunc(tx) return err }トランザクションラッパー関数使い方(サンプル)
- 実行したいSQL関数を引数txFunc内に記述
func (r *Repository) DoSomething() error { return r.DB.ExecWithTx(func(tx *sql.Tx) error { if _, err := tx.Exec(...); err != nil { // ここにSQL系関数定義 return err } if _, err := tx.Exec(...); err != nil { // ここにSQL系関数定義 return err } return nil }) }参考資料
- 投稿日：2021-03-14T20:15:53+09:00
CentOSにMySQLのインストール/アンインストール
備忘録
環境
- CentOS 7
MySQLのインストール
mysqlの公式に、各OS毎のyumリポジトリがありますので、自分のOSに合うもののダウンロードページへ行きます。
# ここは人によって違う $ yum install https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm $ yum -y install mysql-community-server $ systemctl enable mysqld.service $ systemctl start mysqld.service # 表示された最後の文字列がパスワード $ sudo grep password /var/log/mysqld.log # MySQLログイン $ mysql -u root -pアンインストール
アンインストールは上記の逆を行う
# インストールされているMySQL一覧 $ rpm -qa | grep -i mysql # 全部削除 $ sudo yum remove mysql*参考
- 投稿日：2021-03-14T18:45:44+09:00
MySQL 権限
はじめに
MySQLの権限の付与のやりかた
目次
- 権限の確認
- 権限の付与
- 参考文献
1. 権限の確認
ユーザ名
select user, host from mysql.user;ユーザ権限
show grants for 'sample_user'@'localhost';2. 権限の付与
付与コマンド別
全て
grant all on `sample_db`.sample_table to 'sample_user'@'localhost';select, update, insert, delete
grant select, update, insert, delete on `sample_db`.sample_table to 'sample_user'@'localhost';selectのみ
grant select, update, insert, delete on `sample_db`.sample_table to 'sample_user'@'localhost';付与範囲別
全DB
grant all on *.* to 'sample_user'@'localhost';特定DB
grant all on `sample_db`.* to 'sample_user'@'localhost';特定DB、特定テーブル
grant all on `sample_db`.sample_table to 'sample_user'@'localhost';特定DB、特定テーブル、特定カラム
grant all (users, articles, tags) on `sample_db`.sample_table to 'sample_user'@'localhost';3. 参考文献
- 投稿日：2021-03-14T18:45:44+09:00
MySQL 権限付与
目次
- はじめに
- 権限の確認
- 権限の付与
はじめに
MySQLの権限の付与を行うたびにあれどうやるんだっけとググっていたので備忘録として残します。
権限の確認
ユーザ名の確認
select user, host from mysql.user;ユーザ権限の確認
show grants for 'sample_user'@'localhost';権限の付与
適用コマンド別
全コマンド付与
grant all on `sample_db`.sample_table to 'sample_user'@'localhost';select, update, insert, deleteの付与
grant select, update, insert, delete on `sample_db`.sample_table to 'sample_user'@'localhost';selectの付与
grant select, update, insert, delete on `sample_db`.sample_table to 'sample_user'@'localhost';適用範囲別
全DBの付与
grant all on *.* to 'sample_user'@'localhost';特定DBの付与
grant all on `sample_db`.* to 'sample_user'@'localhost';特定DB、特定テーブルの付与
grant all on `sample_db`.sample_table to 'sample_user'@'localhost';特定DB、特定テーブル、特定カラムの付与
grant all (users, articles, tags) on `sample_db`.sample_table to 'sample_user'@'localhost';
- 投稿日：2021-03-14T17:56:54+09:00
MacのDBeaverからXserverのMysqlに接続する
0. DBveaverをインストールする
こちらです。
1. XserverでSSH認証鍵をゲットする
XserverはSSHでのトンネル接続にしか対応してません。
Xserverのサーバーパネルから「SSH設定」からSSH認証鍵を手に入れてください。
詳細は公式マニュアル
もしくはググってください。僕よりカスほど日本語うまいプロたちが超良質なチュートリアルをあげてくれてると思います。
一つ注意としてはパスフレーズは設定したほうが良いです
なんでかは僕もわかりません。2.DBeaverでSSHの設定をする
DBeaverを立ち上げたらこんなの出てくるので、MySQLをえらんでもらって次へ。
次の画面でまず上のSSHタブを押してこの画面にしてください。
そして以下の設定を行ってください。
- SSH Tunnelを使用するにチェックを入れる
- Host/IP: サーバーID.xsrv.jp(例えばtest.xsrv.jp)
- Port: 10022(Xserver用の設定です。ほかのサーバーは知らん)
- User Name: サーバーID(例えばtest)
- Authentication Method/認証方法: Public Key/公開鍵認証
- Private Key: 右のフォルダボタンから1で作った秘密鍵を選択
- Passphrase: 1で入力したパスフレーズを入力。
そしてTest tunnel configurationを押す。
普通にセットアップできてたらここでつながったよ！的なのが出てきます。3.DBeaverでDBの設定をする
SSHの設定が終わったら次にDBの設定をします。
まず、XserverのサーバーパネルでMySQL設定を開きます。
ここのホスト名をコピーしてください。
そして以下のように入力します。
- Server Host: コピーしたホスト名を書きます。(mysqlxxxxx.xserver.jp)
- Database Name: 接続したいDBの名前(サーバーパネル、MySQL一覧で確認できます)
- ユーザー名: DBのユーザー名(サーバーパネル、MySQLユーザー一覧で確認できます)
- パスワード: DBユーザーのパスワード(どこでも確認できません。わからなければMySQLユーザー一覧で変更できます)
最後にテスト接続を押します。
正しく接続できていたらつながったよ！的なメッセージが出てきます。4.終了
終了ボタンを押します。お疲れさまでした。
5.私が詰まったところ
DBのホスト名とSSHのホスト名が違うことに一生気が付かなくて一生「つながらないよ！」って怒られてました。しんどかった。
学んだこと: MySQL専用のホスト名があるんだね。ありがとうXserver。
- 投稿日：2021-03-14T15:34:40+09:00
【Docker】Docker環境内でmysqlの操作方法
はじめに
ローカル環境だと
mysql -u root -pでデータベースに触れることができますが、Docker環境にいると一工夫必要です。この記事では、Docker環境内でmysqlの操作方法について紹介します。
環境
Docker version 20.10.0
docker-compose version 1.27.4
Docker内の環境
ruby:2.6.5
Rails:6.0.0
mysql8.0コマンド紹介
やり方は非常に簡単で、作ったコンテナのデータベース側に入って作業することです。
なので、下記のコマンドでデータベースのコンテナに入り、いつも通りmysqlのコマンドを打てば操作できます。コンテナのデータベースに入る
ターミナルdocker-compose exec db bashmysqlのコマンドでデータベースにアクセスする。
パスワードはdetabase.ymlで指定してるパスワードです。
ターミナルmysql -u root -p Enter password後はmysqlのコマンドを打つだけです。
どのんなデータベースがあるか確認した時
ターミナルmysql> show databases; +--------------------+ | Database | +--------------------+ | exam_development | | exam_test | | information_schema | | mysql | | performance_schema | | sys | | test-database | +--------------------+ 7 rows in set (0.07 sec)特定のデータベースのテーブルをみたい時
ターミナルmysql> show tables from データベース名; +----------------------------+ | Tables_in_exam_development | +----------------------------+ | admins | | ar_internal_metadata | | comments | | replies | | schema_migrations | | users | +----------------------------+ 6 rows in set (0.01 sec)特定のテーブルのカラムがみたい時
ターミナルmysql>SHOW COLUMNS FROM テーブル名 FROM データベース名; +---------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+----------------+ | id | bigint | NO | PRI | NULL | auto_increment | | text | text | YES | | NULL | | | comment_id_id | bigint | YES | MUL | NULL | | | user_id_id | bigint | YES | MUL | NULL | | | admin_id_id | bigint | YES | MUL | NULL | | | created_at | datetime(6) | NO | | NULL | | | updated_at | datetime(6) | NO | | NULL | | +---------------+-------------+------+-----+---------+----------------+データ取得を取得したい時
ターミナルmysql> USE データベース名 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -Aターミナルmysql> SELECT * FROM テーブル名; +----+------+---------+----------+----------------------------+----------------------------+ | id | text | user_id | admin_id | created_at | updated_at | +----+------+---------+----------+----------------------------+----------------------------+ | 3 | ??? | 1 | NULL | 2021-02-21 02:34:34.818467 | 2021-02-21 02:34:34.818467 | +----+------+---------+----------+----------------------------+----------------------------+ 1 row in set (0.00 sec)mysqlのコマンド自体はまだまだあるので下記の参考にさせていただいた記事を参照してみてください。
参考
- 投稿日：2021-03-14T14:32:22+09:00
今回はデータ生成ツールの話・・
以前の検証以降・・・・
EqualumやSingleStore関連の投稿をしていると、データ生成部分だけ下さい！というお話を頂いたりする事が増え、また確かに良く考えると、有れば有ったで何かのお役に立てるかもしれない・・と思い立ち、データ生成部分に少し汎用性を持たせたバージョンを作成してみました。
まずは冒頭部分のカオスを修正します
大量に定義されていたメタデータ系情報をCSVファイルで作成し、それを別ファイルとして読み込む形にしたいと思います。
基本的には
import csv with open('CSV_File', 'r', encoding='utf-8-sig') as f: reader = csv.reader(f) for row in reader: print(row)の定番パターンをベースに実装します。
少し工夫が必要なのは、辞書形式にデータを準備する場合で、今回は以下のパターンをベースにしてみました。import csv Dict_Data = {} with open(CSV_File, 'r', encoding='utf_8_sig') as f: reader = csv.reader(f) Dict_Data = {rows[0]:rows[1] for rows in reader} print(Dict_Data)さて出来上がったのが・・・
CSVのカラムに合わせて修正して頂けば、ほぼ問題無く動くかと思います。また、今回のバージョンでは、基本的に以前の検証で使っていた、カテゴリ数5とかカテゴリ毎の商品数10個・・といった制限は外してあります（上の方の数字で調整してください）。また、これに伴って乱数のネタ元をnumpyに変更しています。
# coding: utf-8 # # 日本語版BIG DATA Generator # # Python 3版 # # 初期設定 import sys stdout = sys.stdout sys.stdout = stdout import datetime import pymysql.cursors import re import csv import numpy as np from numpy.random import * # テーブル名 Table_Name = "BIGDATA_TEST0_Table" # 書き込み用のカラム設定 DL0 = "ts, " # タイムスタンプ情報 DL1 = "Category, Product, Price, Units, Logistics, " # ビジネス情報 DL2 = "Card, Number, Payment, Tax, " # 支払い情報 DL3 = "User, Zip, Prefecture, Address, Area, Tel, Email, Point" # 顧客情報 # 生成するデータの数 Generate_Data = 1000000 # 途中経過のチェックポイント設定 Check_Point = 10 # 途中経過で生成されたSQL文を表示するか否か（０：表示しない １：表示する） Display_SQL = 0 # CSVデータが5カラム毎のフォーマットなので5個飛びになります（必要に応じて変更） ID_Gap = 5 # 商品データの行数を定義（50個定義の場合は0−49で定義します） Data_Start = 0 # データのスタート行数（原則的に0を指定） Data_End = 49 # 投入データの総数から1を引いた数を指定） # 消費税率の設定（整数でパーセント表記） Tax_Data = 10 # 辞書のID（参照辞書を作成する際に利用） Area_List = 1 Logi_List = 2 # # データをランダムに行番号基準で選択して必要情報を返す # # 読み込んだCSVデータ列からランダムに商品情報を選択してデータを返します。 # 今回の改修で、読み込むCSVデータの行情報に対して、ランダムに0から始まる # データ番号でアクセスする形にしましたので、とりあえず選択対象を作成する！というノリで # CSVファイルを用意されればOKかと。 # def Pick_Up_Item(start, end, Data): RET_Data = [] ID = randint(start,end) * ID_Gap RET_Data.append(Data[ID]) # カテゴリ名 RET_Data.append(Data[ID + 1]) # 商品名 RET_Data.append(Data[ID + 2]) # 価格 RET_Data.append(Data[ID + 3]) # ポイント率 RET_Data.append(Data[ID + 4]) # 販売調整数（リアルっぽく見せる為） return(RET_Data) # # メタデータをCSV形式で読み込んで準備を行う # # CSVのフォーマット カテゴリー、 製品名、価格、 ポイント率、 販売調整係数 # def CSV_Data_Set1(CSV_File): RET_Data = [] with open(CSV_File, 'r', encoding='utf-8-sig') as f: reader = csv.reader(f) i = 0 for row in reader: RET_Data.append(row[i]) # カテゴリ名 RET_Data.append(row[i + 1]) # 商品名 RET_Data.append(row[i + 2]) # 価格 RET_Data.append(row[i + 3]) # ポイント率 RET_Data.append(row[i + 4]) # 販売調整数（リアルっぽく見せる為） return(RET_Data) # # 物流センターと地域名の辞書データをCSVファイルから作成する # # CSVのフォーマット 県名、 地域名、 物流センター名 # def CSV_Data_Set2(CSV_File, List_ID): RET_Data = {} with open(CSV_File, 'r', encoding='utf_8_sig') as f: reader = csv.reader(f) if List_ID == Area_List: RET_Data = {rows[0]:rows[1] for rows in reader} # 地域名辞書の作成 else: RET_Data = {rows[0]:rows[2] for rows in reader} # 配送センター辞書の作成 return(RET_Data) # # 此処からメインの処理になります # try: print("SingleStore上へのデータの生成を開始します。") print ("処理の開始 ： " + datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")) # Fakerの初期化 from faker import Faker fakegen = Faker('ja_JP') Faker.seed(fakegen.random_digit()) # 商品データをCSVファイルから読み込む Product_Data = CSV_Data_Set1('Product_Data.csv') # 地域名・配送センター名辞書をCSVフィルから作成 Area_Data = CSV_Data_Set2('Logi_Data.csv', Area_List) # 地域名辞書 Logi_Data = CSV_Data_Set2('Logi_Data.csv', Logi_List) # 物流センター名辞書 # SingleStoreとの接続 db = pymysql.connect(host = 'xxx.xxx.xxx.xxx', port=3306, user='xxxxxxxx', password='xxxxxxx', db='xxxxxxx', charset='utf8', cursorclass=pymysql.cursors.DictCursor) with db.cursor() as cursor: # 時間情報を生成する起点を確保 dt_now = datetime.datetime.now() # ループカウンターの初期化 Loop_Counter = 0 # 検証データの生成 while Loop_Counter < Generate_Data: # ts用のデータを生成 Sec = fakegen.random_digit() MIL_Sec = fakegen.random_digit() MIC_Sec = fakegen.random_digit() # 現実的なタイムスタンプ情報として利用します（秒単位でデータをズラしてBI等で使い易くする） ts_now = dt_now + datetime.timedelta(seconds=Sec, milliseconds=MIL_Sec, microseconds=MIC_Sec) dt_now = ts_now # 生成データを次回の起点に変更 # データをランダムに選択する(投入データ数は引数で定義可能) Select_Data = Pick_Up_Item(Data_Start, Data_End, Product_Data) # 所得した各情報を設定 Category = Select_Data[0] # カテゴリ名 Product = Select_Data[1] # 商品名 Price = Select_Data[2] # 価格 Point_Rate = Select_Data[3] # ポイント換算率 Real_Rate = Select_Data[4] # 販売数調整係数 # 購入数を設定（家電は原則的に1個単位（乾電池を除く）） if Real_Rate == '1': Units = 1 else: Units = randint(1, Real_Rate) # ポイントの計算を行いデータを設定 Point = int(Price) * Units * int(Point_Rate) / 100 # 支払い情報の設定 if str(fakegen.pybool()) == "True": Card = "現金" else: Card = fakegen.credit_card_provider() Number = fakegen.credit_card_number() if Card == "現金": Number = "N/A" # 支払い総額と消費税の設定 Payment = Units * int(Price) Tax = Payment * Tax_Data / 100 # 購入者情報の設定 User = fakegen.name() Zip = fakegen.zipcode() Address = fakegen.address() Tel = fakegen.phone_number() Email = fakegen.ascii_email() # 都道府県情報の抽出 pattern = u"東京都|北海道|(?:京都|大阪)府|.{2,3}県" m = re.match(pattern , Address) if m: Prefecture = m.group() # 地域名と物流センター名を県名から設定 Area = Area_Data.get(Prefecture) Logistics = Logi_Data.get(Prefecture) # SQLで使用するデータ列の作成 DV0 = str(ts_now)+"','" DV1 = Category + "','" + Product + "','" + str(Price) + "','" + str(Units) + "','" + Logistics + "','" DV2 = Card + "','" + Number + "','" + str(Payment) + "','" + str(Tax) + "','" DV3 = User + "','" + Zip + "','" + Prefecture + "','" + Address + "','" + Area + "','" + Tel + "','" + str(Email) + "','" + str(Point) SQL = "INSERT INTO " + Table_Name +"(" + DL0 + DL1 + DL2 + DL3 + ") VALUES('" + DV0 + DV1 + DV2 + DV3 + "')" # データベースへの書き込み cursor.execute(SQL) db.commit() # コンソールに生成データを表示 if Display_SQL == 1: print (SQL) # ループカウンタの更新 Loop_Counter = Loop_Counter + 1 # データの作成状況を表示 if (Loop_Counter % 10) == 0: print("途中経過： " + str(Loop_Counter) + " 個目のデータ作成を終了") except KeyboardInterrupt: print('!!!!! 割り込み発生 !!!!!') finally: # データベースコネクションを閉じる db.close() # 処理時間を表示して終了 print("今回生成したデータの総数 : " + str(Loop_Counter)) print("SingleStore上への指定されたデータ生成が終了しました。") print ("処理の終了 ： " + datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S"))因みに、データテーブルを作成する部分は以前のものと同じです。
# coding: utf-8 # # 日本語版BIG DATA Generator # # Python 3版 # # # 初期設定 import sys stdout = sys.stdout sys.stdout = stdout import pymysql.cursors import datetime # テーブル名 Table_Name = "BIGDATA_TEST0_Table" # テーブル初期化 Table_Init = "DROP TABLE IF EXISTS " + Table_Name # テーブル定義 #DC0 = "id BIGINT AUTO_INCREMENT, ts TIMESTAMP(6) DEFAULT NOW(), " DC0 = "id BIGINT AUTO_INCREMENT, ts TIMESTAMP(6), " DC1 = "Category VARCHAR(20), Product VARCHAR(20), Price INT, Units INT, Logistics VARCHAR(20), " DC2 = "Card VARCHAR(40), Number VARCHAR(30), Payment INT, Tax INT, " DC3 = "User VARCHAR(20), Zip VARCHAR(10), Prefecture VARCHAR(10), Address VARCHAR(60), Area VARCHAR(10), Tel VARCHAR(15), Email VARCHAR(40), Point INT, " DC4 = "SHARD KEY (Area), PRIMARY KEY(id, Area)" try: print("SingleStore上へのテーブル作成処理を開始") print ("処理の開始 ： " + datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S")) # SingleStoreに接続 db = pymysql.connect(host = 'xxx.xxx.xxx.xxx', port=3306, user='xxxxxxxx', password='xxxxxxxx', db='xxxxxxxx', charset='utf8', cursorclass=pymysql.cursors.DictCursor) # デモ用のテーブルの作成 Table_Create = "CREATE TABLE IF NOT EXISTS " + Table_Name + "(" + DC0 + DC1 + DC2 + DC3 + DC4 + ")" with db.cursor() as cursor: # 既存テーブルの初期化 cursor.execute(Table_Init) db.commit() # 新規にテーブルを作成 cursor.execute(Table_Create) db.commit() except KeyboardInterrupt: # 割り込み処理への対応 print('!!!!! 割り込み発生 !!!!!') finally: # データベースコネクションを閉じる db.close() # 処理時間を表示して終了 print("SingleStore上へのテーブル作成処理が終了") print ("処理の終了 ： " + datetime.datetime.now().strftime("%Y/%m/%d %H:%M:%S"))CSVファイルの状況
CSVファイルをExcel等で適宜作成します。今回のカラム構造は
（A）商材カテゴリ
（B）商品名
（C）価格
（D）ポイント率（整数でパーセント数）
（E）販売調整係数（1回の最大販売数）
になります。この部分はPython内のパラメータと辻褄が合えば、変更利用が可能なように（多分・・（汗））作り変えましたので、いろいろなパターンでお使い頂けるかと思います（それに合わせてSQL定義文も忘れずに変更しておいてください）
地域名とそのエリアを配送するセンター名の参照辞書用CSVも同じ様に作成します。
同様に今回のカラム構造は
（A）県名
（B）地域名
（C）物流センター名
になります。
県名をキーに辞書参照を行う形で処理を行います。こちらも前述の商品系情報と同様に、柔軟な変更が可能ですので、随時必要な形に変更しPython側と辻褄を合わせてお使いください。
実行してみました・・・
これらのファイルを一つのディレクトリに格納して、いつものANACONDA環境で実行した結果が以下の通りです。
今回の纏め
今回は、今まで検証で使ってきたツールの整理整頓を行ってみました。出来るだけ横展開出来るように汎用性を持たせたつもりですが、引き続き改良していきたいと思います。
また特に、前回まで連続してご紹介してきた、MySQL互換で、マシン語レベルでSQL処理を実装している、”インメモリDBであるSingleStore環境での、動作検証やパフォーマンス確認等で使って頂ければ幸いです。
- 投稿日：2021-03-14T12:54:19+09:00
ポートフォリオのAWSネットワーク構成を整理した【Rails + Nginx + Unicorn + MySQL】
Railsアプリポートフォリオのネットワーク構成を頭の整理と備忘を兼ねて整理してみました。
俯瞰的に整理するものなので各リソースやネットワークの細かい設定等には触れません。
初学者なりに自分の言葉で整理したものになっていますので、おかいし点などありましたらご指摘いただけると幸いです。はじめに：参考書籍
AWSのネットワーク構築において以下の本を参考にさせていただきました。
初学者でも大変分かりやすく、かつ内容に従って操作することで本格的でセキュアなネットワークを構築できるハンズオン形式にもなっており、これ無くしてこのネットワーク環境は作れなかったと思います。
まだ発刊されて日も浅い（2021年3月時点）ため、ネット上の記事や古い書籍でありがちな、解説のUIと現在のUIが違って迷う、ということもほぼ無かったのも良かったです。おすすめです！
ネットワーク構成図
開発環境・デプロイ
開発・テストはDockerリモートコンテナ上で実施
ソースコードはGitHubで管理、デプロイはCapistranoで行うIAM
IAM（Identity and Access Management）はAWSへのアクセスを安全に管理するための仕組み
AWSアカウントのデフォルトユーザであるルートユーザは権限が強く、通常の開発等で使うにはリスクが大きい
そのため別途権限を抑えたユーザを作成し、こちらでネットワークの構築を行う
また仮想デバイスを用いたMFA（多要素認証）を導入し、更にセキュアな接続を実現するVPC
ここは私の領域ですよ、と宣言する領域。Virtual Private Cloud
この中にAWSのネットワークを構築していくアベイラビリティゾーン
耐障害性を上げるために、同じ東京リージョンの中でも異なるアベイラビリティゾーンにそれぞれサブネットを構成する
サブネット
VPCのIPアドレスの範囲を分割する範囲。分割する理由は主に以下2点
- 役割の分離：外部に公開するリソースかどうかを区別するため
- 機器の分離：AWS内での物理的な冗長化を行うため
今回は、2つのアベイラビリティゾーンにそれぞれパブリック・プライベートサブネットを用意する
インターネットゲートウェイ
VPCで作成したネットワークとインターネット間の通信を可能にするためのもの
NATゲートウェイ
インターネットゲートウェイを使った通信ではVPCのリソースは外部のネットワークと直接通信を行うため、VPCリソースはパブリックIPを持っている必要がある
しかしパブリックIPを持つ＝インターネットに直接公開されている状態になり、サブネットをパブリックとプライベートに分けた意味が無くなる
プライベートサブネットのリソースは、インターネットに出ていく必要はあってもインターネットから直接アクセスはされたくない
→このような要求を実現するための仕組みがNAT（ネットワークアドレス変換）AWSではこの役割を果たすのがNATゲートウェイになる
ルートテーブル
サブネット同士、あるいはサブネットと各ゲートとの間には、通信をするための経路がまだ無い
そのため、サブネットが別のサブネット内のリソースを見たり、サブネット外のリソースにアクセスができない
サブネット間の通信経路を設定するために、AWSにはルートテーブルという機能がある
今回はサブネットが4つある すべてのサブネットにルートテーブルを作成する必要がある（ただし複数のサブネットで同じルートテーブルを共有することは可能これを踏まえ今回作成するのは以下3つ
- パブリックルートテーブル：パブリックサブネット1，2共有
- 外部とはIGW経由、プライベートサブネットとはLocalターゲットとしてアクセス
- プライベートルートテーブル1：プライベートサブネット1用
- プライベートルートテーブル2：プライベートサブネット2用
- 外部とはNATGW経由、パブリックサブネットとはLocalターゲットとしてアクセス
セキュリティグループ
今のままだとインターネットを通じてどんなアクセスもできてしまう
VPC内のリソースを守るため、外部からのアクセス制限をつける必要がある
→セキュリティグループという機能を用いて実現するセキュリティグループでは、外部からのアクセスを次の2つの概念で制御できる
- ポート番号
- サービスのアクセスで使われる80番（HTTP）、443番（HTTPS）、
- 管理者としてサーバ接続時に使われる22番（SSHなど）
- IPアドレス
今回は以下2つのセキュリティグループが必要
- 全てのリソースに接続するための入り口となる「踏み台サーバ」
- インバウンドルール：SSH接続（22番）のみ
- リクエストや処理を分散する「ロードバランサー」
- インバウンドルール：HTTP（80番）とHTTPS（443番）接続のみ
EC2（踏み台サーバ）
今回は機器の冗長化、負荷分散のために2つのアベイラビリティゾーンにサブネットを構築した
しかしリソースを増やすということはそれだけ侵入の経路が増えセキュリティ上のリスクが高まることになる
そこで全てのリソースに接続するための入り口となる踏み台（bastion）サーバを用意し、
このサーバ経由でないと各リソースに接続できないようにする踏み台サーバは目的のリソースへの通り道となる以外の用途がないため、低スペックでもOK
EC2インスタンス上に構築する
踏み台サーバはパブリックサブネット1に設置し、先程作成した踏み台サーバ用のセキュリティグループを適用する（＝SSH接続のみ許可する。秘密鍵はローカルマシンで保持）EC2（Webサーバ・アプリケーションサーバ）
実際のアプリケーションを稼働させるEC2インスタンス
この中にWebサーバとしての役割を果たすNginx、Rackアプリケーション用サーバとしての役割を果たすUnicorn、アプリ本体であるRailsをインストールする
プライベートサブネット1,2にそれぞれ設置こちらもssh認証の秘密鍵はローカルで持ち、ssh-agentを利用してセキュアに多段接続する（踏み台サーバには秘密鍵をアップしない）
ここで踏み台サーバとWebサーバの違いを整理する
項目 踏み台 Webサーバ 設置サブネット パブリック プライベート パブリックIP 必要 不要 セキュリティグループ デフォルト＋SSH接続 デフォルト＋HTTP, HTTPS接続 誰がいつ接続する？ 管理者が必要に応じて Webサービスのユーザが常時 接続形態 直接接続 間接接続（ロードバランサ経由） ロードバランサー
ユーザが増えてくると1台のWebサーバではリクエストを捌き切れなくなる可能性がある
スケールアウトの方法として用いられるのがロードバランサーで、リクエストの分散を実現できる設定項目は以下
- アベイラビリティゾーン：インターネットゲートウェイへの経路があるサブネット（今回はパブリックサブネット1,2）があるゾーンを指定する必要がある
- セキュリティグループ：デフォルト＋HTTP, HTTPS接続（内部向けと外部向け）
- ターゲットグループ：Webサーバ1, 2、プロトコルはHTTP
データベース
AWSのサービスの一つであるRDSを用いて実現
RDSは以下の4点で構成される
データベースエンジン
→ 今回はMySQLを使用）パラメータグループ
→ 主にデータベースエンジン固有の設定を行うためのものオプショングループ
→ 主にRDS固有の設定を行うためのものサブネットグループ
→ データベースサーバーを複数のアベイラビリティーゾーンに分散させて配置させる時に使われる設定
RDSはサブネットグループを指定することで自動的に複数のアベイラビリティーゾーンにデータベースを作成し、
耐障害性を上げてくれる（マルチAZ）S3
今回のアプリではユーザのアイコンや投稿機能で画像をアップロードすることが可能
その画像を保管する場所として、AWSのストレージサービスであるS3を利用する
S3はVPCの外に配置するものである
そのためIAMでS3にアクセスするためのロールを作成し、EC2に適用することでアプリからS3へのアクセスを実現するAmazon Route 53
Webアプリを公開するためには分かりやすくて覚えやすいドメインが必要
AWSではドメインを取得することができるRoute 53というサービスがある
こちらで希望のドメインを取得することで公開用のURLを発行することができる
（費用は$10~15/年 前後）また、Route 53では他に以下の設定を行う
SSLサーバー証明書の発行（Certificate Manager）
→ ドメインの正しさを保証する証明書の発行を行う。
今回はドメイン検証済み（DV）証明書を使う
発行した証明書を使い、HTTPSのリスナーをロードバランサーに追加するパブリックDNSの設定
→ 踏み台サーバとロードバランサーに対し設定するプライベートDNSの設定
→ 踏み台サーバ、Webサーバ1,2、DBサーバに対し設定するAmazon SES
SES（Simple Email Service）はメールの送受信行う機能を提供するAWSのサービス
今回のアプリではパスワードを忘れた際に再発行するためにメールの送信を行うため用意する
受信に関する設定は不要のため行わない通常のメール送受信においては、以下プロトコルが利用される
SMTP（Simple Mail Transfer Protocol）
→ 送信用のプロトコルPOP3（Post Office Protocol Version 3）
→ 受信用のプロトコル。メールは受信者のローカルに取り込まれるIMAP4（Internet Message Access Protocol Version 4）
→ 受信用のプロトコル。メールはサーバ上に保管されるためインターネットアクセスが必要アプリからメールを送るには、メール送信用のIAMユーザを作成し、Amazon SES API または Amazon SES SMTPインターフェイスで認証する必要がある
今回は通常のメールサーバと同じ方法で実装ができるAmazon SES SMTPインターフェイスを利用しているCloudWatch
AWSで提供されている、各種サーバ等の運用状況を監視するツール
今回はWebサーバ・アプリサーバを包含するEC2インスタンスの死活監視、CPU使用率監視を行っているおわりに
CircleCI/CDを使った自動テスト・自動デプロイも導入したいと考えています。
導入できたらこちらも更新しようと思います。
- 投稿日：2021-03-14T01:08:55+09:00
laravel Seeder（シーダー）を使ってみる
目的
- laravelでSeeder（シーダー）を使う方法をまとめる。
環境
- ハードウェア環境
項目 情報 OS macOS Catalina(10.15.5) ハードウェア MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports) プロセッサ 2 GHz クアッドコアIntel Core i5 メモリ 32 GB 3733 MHz LPDDR4 グラフィックス Intel Iris Plus Graphics 1536 MB
- ソフトウェア環境
項目 情報 備考 PHP バージョン 7.4.8 Homebrewを用いてこちらの方法で導入→Mac HomebrewでPHPをインストールする Laravel バージョン 8.X commposerを用いてこちらの方法で導入→Mac Laravelの環境構築を行う MySQLバージョン 8.0.19 for osx10.13 on x86_64 Homwbrewを用いてこちらの方法で導入→Mac HomebrewでMySQLをインストールする Node.jsバージョン v12.14.1 Homwbrewを用いてこちらの方法で導入→Mac HomebrewでNode.jsをインストールする 情報
limitsというテーブルが存在し、下記のようなカラムの設定になっているとする。
mysql> show full columns from limits; +------------+-----------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +------------+-----------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+ | id | bigint unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | name | varchar(255) | utf8mb4_unicode_ci | NO | | NULL | | select,insert,update,references | 期限 | | created_at | timestamp | NULL | YES | | NULL | | select,insert,update,references | | | updated_at | timestamp | NULL | YES | | NULL | | select,insert,update,references | | | deleted_at | timestamp | NULL | YES | | NULL | | select,insert,update,references | | +------------+-----------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+limitsテーブルのnameカラムが「今日中」「明日中」「今週中」「今月中」「未定」となるレコードを追加したいとする。
今回の方法で紹介するコマンドは特筆しない限り前のコマンドと同じディレクトリで実行するものとする。
方法
アプリ名ディレクトリで下記コマンドを実行してSeederのファイルを作成する。
$ php artisan make:seeder LimitTableSeeder下記コマンドを実行して作成したSeederファイルを開く。
$ vi database/seeders/LimitTableSeeder.php開いたSeederファイルを下記のように修正する。
アプリ名ディレクトリ/database/seeders/LimitTableSeeder.php<?php namespace Database\Seeders; use Illuminate\Database\Seeder; use Carbon\Carbon; use Illuminate\Support\Facades\DB; class LimitTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $limit_names = [ '今日中', '明日中', '今週中', '今月中', '未定', ]; $now = Carbon::now(); foreach ($limit_names as $limit_name) { $limit_info = [ 'name' => $limit_name, 'created_at' => $now, 'updated_at' => $now, ]; DB::table('limits')->insert($limit_info); } } }下記コマンドを実行して作成したSeederを登録するファイルを開く。
$ vi database/seeders/DetabaseSeeder.php下記のように内容を追記する。
アプリ名ディレクトリ/database/seeders/DetabaseSeeder.php<?php namespace Database\Seeders; use Illuminate\Database\Seeder; class DatabaseSeeder extends Seeder { /** * Seed the application's database. * * @return void */ public function run() { // \App\Models\User::factory(10)->create(); $this->call(LimitTableSeeder::class); } }下記コマンドを実行してシーディングの実行を行う。
$ php artisan db:seedMySQLにログインしlimitsテーブルを確認したところ下記のように表示されたため正常にSeederで登録指示したファイルが正常にシーディングされたことがわかる。
mysql> select * from limits; +----+-----------+---------------------+---------------------+------------+ | id | name | created_at | updated_at | deleted_at | +----+-----------+---------------------+---------------------+------------+ | 1 | 今日中 | 2021-03-13 12:00:03 | 2021-03-13 12:00:03 | NULL | | 2 | 明日中 | 2021-03-13 12:00:03 | 2021-03-13 12:00:03 | NULL | | 3 | 今週中 | 2021-03-13 12:00:03 | 2021-03-13 12:00:03 | NULL | | 4 | 今月中 | 2021-03-13 12:00:03 | 2021-03-13 12:00:03 | NULL | | 5 | 未定 | 2021-03-13 12:00:03 | 2021-03-13 12:00:03 | NULL | +----+-----------+---------------------+---------------------+------------+ 5 rows in set (0.00 sec)