新着記事

Viewing posts for the category MySQL

そうだ、mysql8.0サーバーを構築してみよう

ども、お久しぶりです。

オンプレのインフラ構築を担当することが多い四斗邊です。

今回は社内の要望でmysql8.0サーバーを社内ネットワーク上に構築する!というのが目標です。

結構ありふれたネタだと思いますが、社内に向けた手順書のような形で残しておきたかったので今回記事しました。

PC構成


  • CPU intel i5-12400 6コア12スレッド 2.5GHz

  • メモリー CT2K32G4SFD832A メモリー32GB 1枚(レイテンシーは特に考えていない)

  • ベアボーンキット DeskMini B660

  • SSD WDS100T3X0E 1TB

これで10万円を切った構成になっています。
安くてそれなりに社内サーバーとしてはそこそこパワーがある構成にしてみました。
一番の特徴はなによりもベアボーンキットなのでコンパクトなのがいい。
個人的にはCPUはAMD派ですが、2022年8月上旬ではintelが安くてちょうどいいかんじでした。
家庭用なので消耗とか気になりますが、4年ぐらいは耐えてくれてると信じたい。

OSソフトウェア構成


OS ubuntu 22.04
DB mysql8.0.30

ubuntu以外も候補はありますが、構築経験があるのでこちらかなと。

mysqlインストール手順


*注意事項
手順の前にsshとかOS周りの設定は一通りしております。今回の手順はmysqlインストールに特化したものとご了承ください。

mysql-serverのインストール

$sudo apt install mysql-server

mysqlにログインする。

$sudo mysql
#ユーザー確認
mysql >select user,host from mysql.user;

インストールしているときに、すでにrootユーザーは作られているので、以下のコマンドでパスワードを改変する。hogeの部分は任意なパスワードに変える。

mysql > USE mysql; 
mysql >ALTER USER 'root'@'localhost' identified BY 'hoge';
一応権限の確認は以下で確認できる
mysql > show grants for 'root'@'localhost';

後々構築した後に気づいた点で、localhostになっているので、リモート時にはrootユーザーに入れないことがあった。

やっぱりここはrootユーザーのhost=%を作るに限る。(セキュアな面ではもうちょっとhost部分は制限したほうがいいかも)

mysql > CREATE USER 'root'@'%' IDENTIFIED BY 'hoge';
mysql > GRANT ALL ON *.* TO 'root'@'%';

これでリモート上でもrootで入れる。

文字のエンコードはutf-8に設定したかったので、my.cnfを以下のようにいじる。参考にしたURL↓

https://qiita.com/is0me/items/12629e3602ebb27c26a4

まずはmy.cnfの場所の特定。どうやら/etc/my.cnfが先で、次に/etc/mysql/my.cnfの順番で読み込まれるようだ。

mysql --help | grep my.cnf 
#以下結果 
order of preference,my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf

/etc/mysql/my.cnfの中身を見ると以下のような記載がありました。

!includedir /etc/mysql/conf.d/ 
!includedir /etc/mysql/mysql.conf.d/

/etc/mysql/conf.d/のディレクトリに入れると反映されるのでは?ということで、ここにmy.cnfを設置してみる。

保存するその前に、一旦、サーバー上でmysqlが止まっているか確認する。

mysqlが止まっているか以下でわかる。(すでに稼働している場合は周りに影響しないか注意してください。)

$sudo service mysql stop
#以下の内容を入れて/etc/mysql/conf.d/my.cnfに保存する。 
[mysqld]
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
#更新したら以下でリスタートする。
service mysql restart

あとは、リモート上でも接続できるようにサーバー上でリッスンを設定する。今回は、mysqlのポート3306をリッスンさせる。

mysqlインストール時にすでにポートはリッスンしているので、bind-addressとmysqlx-bind-addressのアドレスを変更するようです。

/etc/mysql/mysql.conf.d/mysqld.cnf
#bind-address = 127.0.0.1
#mysqlx-bind-address = 127.0.0.1

更新したら以下でリスタートする。

service mysql restart

そうすると、自動的に全てのアドレスから全てのアドレス:3306でリッスンできる。

sudo ss -talpn 
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 151 *:3306 *:* users:(("mysqld",pid=3822,fd=23))
ただ、この設定は少し強引すぎる。

社内サーバー用とかで事前に外部からのアクセスを制限できているなど限定した場面で活用するのが望ましい。というか一応ufwも確認する。

sudo ufw allow 22 #ssh 
sudo ufw allow 3306 #mysql
sudo ufw enable
sudo ufw status numbered
To Action From -- ------ ----
[ 1] 22 ALLOW IN Anywhere
[ 2] 3306 ALLOW IN Anywhere
[ 3] 22 (v6) ALLOW IN Anywhere (v6)
[ 4] 3306 (v6) ALLOW IN Anywhere (v6)

これでmysqlとsshのみ通るようにポートは開けることが確認できた。

ここまでで一通りのmysqlサーバの完成となる。実際に自身のPC上で確認してみてください。

以下のコマンドで入れたら成功です。

(もちろんローカル上にもmysqlは入れてるよね?)

mysql -h XXXX.example.co.jp -u root -p
パスワードを入力:hoge

できましたかね?これであなたもmysqlサーバー構築者だ、おめでとう!

できない人は個別のトラブルシューティングに従って解決してください。

結構最低構成でできたはずなので、時間はかからないのかなと思っています。

みなさん試してみてください。

新卒1ヶ月目の業務内容

4/1から新卒でエンジニアとして入社した高津です。
今回は新卒で入社1ヶ月でどのような業務を行ったのか紹介していきたいと思います。
TORICOにエンジニアとして入社を検討している人に少しでも参考になれば幸いです。

主な業務内容

自分はコーマス開発部で、漫画全巻ドットコムの開発をメインでやっています。

簡単なバグ(UI)の修正漫画全巻ドットコムのリニューアルの大きく2つに分けられます。

簡単なバグ(UI)の修正

こちらは数時間で終わるような簡単なUIの修正(改修)で入社して3日後にはプルリクエストを出していました。

  • アイコン(font-awesome)が正しく表示出来るようにする
  • 個人情報同意フォームの改修
  • 電話番号記入欄のに数字が4文字入るようにする
    などの業務を行いました。

漫画全巻ドットコムのリニューアル

漫画全巻ドットコムは10年以上続く歴史のあるサービスで最初はPHPで作られていました。
メンテナンス性に問題があったのでDjango+Nuxt.jsにリプレイスしています。
今回自分は電子新着ページ電子割引ページをリニューアルしました。(ブログを書いている時点で実装は終わっていますがレビューが終わってないので本番にはまだ反映されていません)
Nuxt(フロントエンド)は先輩方が作ってくれた雛形を軽く修正して利用出来るのでDjnago(バックエンド)の実装がメインでした。
今回はその中でも難しかったポイントをいくつか列挙したいと思います。

キャッシュを効かす

同じ値を取得して返すだけなのに毎回SQLを叩くのは無駄なのでkye-value型のNoSQLであるredius(メモリ)に一定時間値を保管し、値がキャッシュされていなければSQL等を叩く処理を行います。(keyは引数、valueは返り値で保存)
ページ単位(view)単位でキャッシュする方法とクラスメソッド単位でキャッシュする方法の2パターンあります。
カテゴリー別の作品数を取得する処理はページ間(異なるurl)でも共通したしょりなのでクラスメソッド単位でキャッシュする必要があり、少々手こずりました。

SQLの実行回数(IO)を極力少なくしパフォーマンスをあげる

今回一番苦戦しましたポイントです。
ただ実装するだけであればすぐ終わったのですが、最初の実装ではSQLを12回叩いてしまっていたのでリファクタリングする必要がありました。(俗に言うN+1問題が発生していました)
こちらはやり方を先輩方にご教示頂き、MySQLにだけサポートされているconcat関数をraw_queryで使い1回で取得することに成功しました。
実際には以下のようなSQLに落ち着きました。

select GROUP_CONCAT(sample_id) AS ids, group_type from
dtb_sample WHERE aggregate_type=
'%s' AND product_type = 2
group by group_type ORDER BY sort_key;

テスト

TORICOの開発ではただ動くものを作るだけでなくその後の保守運用のことも考慮して単体テスト、統合テストも書くように徹底されています。
特にDB設計が少々複雑なこともありテストデータを作るところはかなり苦戦しました。

具体的には以下のようなことをテストしました。

  • 各URLにgetして正しい値やstatusが返ってくるか
  • 各メソッドのすべての条件分岐において正しく動作するかどうか
これらのテストを書くことでテストのしずらいメソッドが見つかり、それをリファクタリングすることで保守性の高いコードに改善出来ます。

また、仕様が分からない人がみても理解できるようにWhy「なぜこの処理を書くのか?」を極力書くように意識しました。

まとめ

如何でしたでしょうか?

今現在23卒のエントリーを受け付けています。
少しでも興味を持ってくれた人は是非応募して頂けると幸いです。

新卒エンジニアが最初の半年に任された業務

今年の春に入社しました、情報システム部の清瀬です。

私がTORICOに入社からそろそろ半年が経過しようとしています。このタイミングで、この半年どういった業務を担当してきたのかを記事にしようと思います。TORICOに興味をもっている方にTORICOのエンジニア業務がどんなものなのか、知ってもらえればと思います。

社内アプリへの権限付与の自動化

torico-id のホームページ

TOIRCOには TORICO-ID というログインを共通化できるアプリがあります。マンガ全巻ドットコム や マンガ展 と連携させることでログインやアカウント作成の簡略化ができます。このアプリは10近くある社内アプリにも使われています。社員全員がボタン一つで簡単にログインできるのですが、問題点が一つありました。それは、Admin サイトへの権限の問題です。

TORICO の社員と一般のユーザーを分けるフラグはあります。そのため、社員にのみ権限を与えることは今までもできていました。しかし、全社員に全ての社内アプリのAdmin権限を付与するわけにはいきません。そのため今までは情報システム部が手動で権限の付与をしていました。

そこで部署ごとに権限を付与するサイトを事前に決めておき、権限の付与の自動化を行えるようにしました。作成したモデルは具体的には

  • Website
    • 権限を与えるアプリのモデル
    • 部署とM2Mの関係
    • django-allauth の Application と 1:N の関係
  • ClientGroup
    • ログイン成功時にユーザーに与える、クライアント側の Django の Group のモデ
    • django-allauth の Application と 1:N の関係
  • Department
    • 部署・部門のモデル
    • Website と M2M の関係
    • ClientGroup と M2M の関係
    • User と M2M の関係

の3つです。TORICO では Mezzanin という Django の CMS を使用しているため、Mezzanine の権限も付与できるように Website を Application と 1:N の関係にしています。

あとは Department に Application と ClientGroup を結びつけておくことで、どの部署にどの権限を付与するのかを自動で判別できるようになりました。TORICO は現在拡大期であり毎月のように新しく入社される方がいるため、この機能は非常に役立っていると個人的には思います。また、エンジニアとしても予想外のメリットがありました。それは新しいプロジェクトのローカル環境を作成した際に、いちいち SQLクライアントツールで自分のアカウントにスタッフ権限を付与しなくて済むことです。特に情報システム部は担当するアプリが多いため、その一手間を省けるのは非常に便利です。

古いシステムの Django 化

長年TORICOを支えてきてくれたシステムですが、その中の幾つかを Django に移行するお手伝いもしました。元のコードを読みながら、その機能を Django で再現するという業務でした。元のコードは PHP で書かれているため、PHP と Python を比較するような作業で、個人的にはとても楽しかったです。なにより、古いシステムを新しいシステムに置き換えることで、一つ一つの処理が非常に効率が良くなり、格段にレスポンスを早くすることができました。あらためてフレームワークの凄さを目の当たりできてよかったです。TORICO にはまだ古いシステムが残っているので、来期も移行の業務を積極的にやっていきたいと思っています。

MySQL 5.6 を 5.7 にバージョンアップする

RDS の MySQL5.6 のサポート期限が8月末(延長されて22/3/1まで)までであるということで、MySQL のパージョンアップもさせていただきました。手順としては、

  1. dev 環境のバージョンアップを行い、全ての機能が使えるかテストする
  2. 1 で問題あった機能を修正する
  3. スナップショットでバックアップをとる
  4. 修正したコードをデプロイして、本番のバージョンアップを行う

今回のバージョンアップでは1の部分で問題がありました。対象のアプリでは、ログイン時に MySQL の OLD_PASSWORD というハッシュ関数を使っていたのですが、それがサポートされなくなりました。そのため、代替となる関数をアプリ側で作成しなければなりませんでした。幸い、既に再現をしてくれているコードがあったため、それを拝借するだけで問題はありませんでした。ただ、ログインというアプリの基幹機能の修正だったため、本番へのデプロイは非常に緊張しました。

また、本番のバージョンアップ時にオプションの設定を忘れるというミスをしてしまいました。古いものが引き継がれると勘違いしていたことが原因です。その結果タイムゾーンの設定が狂ってしまい、表示されるべきコンテンツが表示されないという状況になってしまいました。普段からお世話になっている先輩に手助けしていただいたため無事に解決できましたが、もう少しで大惨事となるところでした。今後はもっと慎重に、情報集めからテストまでを行わなければいけないなと反省しています。

最後に

今回は、私がこの半年で任せていただいた主要な業務をいくつか紹介させていただきました。以上に取り上げたもの以外にも、

  • 新機能の追加
    • 5件
  • Django 化
    • 2件
  • DB・ネットワーク
    • 2件
  • セキュリティ(別ブログに記載)
    • 10件
  • コードの軽微な修正
    • 16件

と、様々な業務を体験できました。今回はシステムに関係する業務だけを取り上げましたが、他にも壁にドリルで穴を開けたり社内サーバーを移動させたりと、普段はできない業務も経験させていただきました。TORICO ではフルスタックであることが求められるため、業務も多岐にわたります。そこに魅力を感じた方は、ぜひ TORICO に応募してみてください。

Django の ORマッパーで生成されたSQLを実行前に置換し、JOIN時のインデックスを強制する

Django には、便利な ORマッパーが搭載されており、SQLを一切書かずともRDBの操作が行えます。

今回、SQL実行時に思ったようにインデックスが使用されず、パフォーマンスが出ない問題がありました。

これは、生SQLを実行した場合も同様にインデックスが使われなかったため、Django の問題ではなく、実行計画が最適に作成されなかったというSQL上の問題(というか仕様?)です。

SQL内にインデックスヒントを強引に書き込むことでインデックスを強制した所、正常なパフォーマンスとすることができたので、その方法を書きます。

MySQLの強制インデックス

今回、SQLを発行したページは Django の Admin 内で、子モデルの Admin ページから、親モデルを select_related して、リスト表示するというコードとなっていました。

@admin.register(models.Child)
class ChildAdmin(admin.ModelAdmin):
    ...
list_select_related = (
    'parent',
)

DB の種類は MySQL です。発行される SQL は以下のようになります。

SELECT *
FROM child
INNER JOIN parent
ON child.title_id = parent.title_id
ORDER BY child.id
LIMIT 100

※ 実際には SELECT 句の中は各フィールドが明示的に書き出されます

実際にこのSQLをSQLコンソールから実行してみると、50秒ほどかかりました。

(child は約100万レコード、parent は約40万レコードほどの分量です。)

予想より多くの時間がかかっており、テーブルフルスキャンが発生している印象です。

EXPLAIN してみると、possible_keys に title_id というキーを認識しているものの、実際にはキーが使用されると判断されない状況でした。

EXPLAIN 結果

※ 実際のテーブル名は child, parent ではないため、画像を加工しています。

ためしに、認識して使用されていないキー title_id の使用を強制し、SQLを発行してみます。

SELECT *
FROM child
  INNER JOIN parent
  FORCE INDEX (title_id)
  ON child.title_id = parent.title_id
ORDER BY child.id
LIMIT 100

FORCE INDEX (title_id) を追加しています。

すると、50ミリ秒 ほどでSQLを完了することができました。今回はインデックスが活用されています。

EXPLAIN で見ても、key が使われています。

解決へのアプローチ

インデックスが使われない問題は MySQL のサーバ側にあります。インデックスの再構築などを行うことで改善する可能性はあります。

ただし、今回は MySQL サーバへの変更などは行わず、コード上の変更により、 インデックス強制の SQL をなんとか発行する方針としました。

Django の SQL 生成のフック

Django は、搭載されている OR マッパーで SQL を組み上げます。その中には、テーブルジョインする際にインデックスを指定するオプションはありません

そこで、生成されたSQL を発行前にフックし、何らかの処理(置換)を行うことで、インデックス強制をさせることを考えました。

Python は、変数のほかクラス・関数などあらゆるものがファーストクラスオブジェクトであり、使用時に上書きや代入することができます。

特に、コア機能のメソッドを外部から上書きすることで動作を変更させる手法はパッチ、もしくはモンキーパッチと言われ、フレームワークやライブラリの主要機能を一時的に書き換えることも容易に行えます。

今回は、Djangoで生成されるSQL部分をパッチすることで、SQLの置換が行えそうです。

動作の安定性を失ったり(特にスレッド安全性など)、セキュリティ上の問題も生みやすく、動作を追うことが困難になるため、安直に行うべきものではありませんが、課題の解決やビジネス成長につながるのであれば選択肢として考えることはできます。Pythonは、速度など多くの犠牲のもと、柔軟な拡張性を獲得していると言えます。

SQL生成の箇所

Django のORマッパーを追っていくと、

  1. まず Model.objectsQuerySet を生成する
  2. QuerySetQuery を作成する
  3. Query が、 SQLCompier (django.db.models.sql.compiler.SQLCompiler) を作り、 as_sql メソッドを使ってSQL を組み上げる

という動作をします。

Model > QuerySet > Query > SQLCompiler

的な感じです。(記号はイメージです。has a 関係のようなものを表しているつもりですが、厳密ではありません)

SQLCompiler.as_sql を読んでみると、200行ほどからなるそこそこ分量のあるメソッドで、その中には JOIN 時にインデックス強制をできそうなフックポイントなどは無いため、簡単に処理は追加できません。

今回は、この as_sql を一旦どこかに退避し、新しくSQL置換機能をもった as_sql を勝手に作って SQLCompler の as_sql として置き換え、その勝手に作った as_sql の中で、もともとの as_sql を呼ぶことで、機能の拡張を行うものとします。

パッチのコード

具体的なコードとしては

def patch_sql_transformer():
    """
    queryset.query に sql_transformer というメソッドを付与すると、
    生成済み SQL をその sql_transformer で処理(置換など)して返す
    """
    from django.db.models.sql.compiler import SQLCompiler
    o_as_sql = SQLCompiler.as_sql

    def _decorate_as_sql(self, *args, **kwargs):
        sql, params = o_as_sql(self, *args, **kwargs)
        transformer = getattr(self.query, 'sql_transformer', None)
        if transformer:
            sql = transformer(sql)
        return sql, params

    SQLCompiler.as_sql = _decorate_as_sql

このようなコードとしました。このコードを、アプリの起動時のどこかで1回だけ実行することで、as_sql の機能が書き換わります。

機能としては、クエリセット内の Query のインスタンスに、 sql_transformer という関数がついていれば(つけていれば)、生成された SQL をその関数を通してから実行します。

今回、sql_transformer として実行したい関数は下記のものです。

def _transform_sql(sql):
    return sql.replace(
    'INNER JOIN `parent` ON',
        'INNER JOIN `parent` FORCE INDEX (title_id) ON')

近距離パワー型のアプローチですが、INNER JOIN 句を文字列置換して、強制的にインデックスを使わせます。

今回は Admin ページ内だけでこれを適用したかったため、このメソッドを ModelAdmin の get_queryset 内で作り、Query に仕込みます。

@admin.register(models.Child)
class ChildAdmin(admin.ModelAdmin):
   ...
    def get_queryset(self, request):
        qs = super().get_queryset(request)

        def _transform_sql(sql):
            return sql.replace(
                'INNER JOIN `parent` ON',
                'INNER JOIN `parent` FORCE INDEX (title_id) ON')
        qs.query.sql_transformer = _transform_sql
        return qs
 
    list_select_related = (
        'parent',
    )
...

これにより、インデックスが強制指定で使われるようになり、速度を改善させることができました。

なぜ Query にメソッドを作るのか?

SQL の生成時は

Model > QuerySet > Query > SQLCompiler

とモデルが関係していますが、今回、なぜ半端な位置の Querysql_transformer を生やす、というデザインにしたかというと、それ以外に適した場所がなかったからです。

クエリセットを生成中の、QuerySet や、 SQLCompiler は、クエリセットをチェーンする時

例えば

qs = MyModel.objects.filter(active=True)
qs = qs.filter(item_type=xxx)
qs = qs.order_by('-id')
qs = qs.limit(100)

のようなコードの場合 (実際、Django の ModelAdmin の中では上記相当のコードが発行されています)、毎回 queryset インスタンスが作り直されます。

なので、queryset インスタンスになにかフック用の関数を付与したとしても、SQLCompiler の SQL 構築まではインスタンスが無くなり、メソッドも失われてしまいます。同様に、SQLCompiler になにか関数を作っても、クエリセットが作り直される時に消えます。

では、その中間にある Query はというと、QuerySet のコンストラクト引数として常に引き渡され、場合によっては deepcopy され、インスタンスのプロパティや変数などは大元の QuerySet がすべて消えるまで生存しており、かつ SQLCompiler の第一引数でもあるためアクセスが容易ですので、今回は Query のインスタンスを用いて関数の引き回しをすることにしました。

Search