DBサーバー建ててみた

はじめまして、grasysの中村です。

今回は、mysqlを入れてDBサーバーを構築する手順とインストール後に設定推奨のサーバーシステム変数についてご紹介します。

デフォルトの状態だと後々困るので、初回導入時点で最低限入れた方が良い設定を紹介することで後々の運用の手助けになれば良いなと考えて執筆いたしました。

mysqlのインストール手順

今回は、下記環境にmysqlをダウンロードする手順を記載します。
OS：CentOS7
実行権限：root
　※rootユーザーが使用できない場合は、以降のコマンドに「sudo」を入れてください

mysqlの構築

下記のコマンドを入力すればダウンロード、インストールが可能です。

まずは、mysqlと競合する「mariadb」がインストールされていないことを確認する。
　※mariadbはmysqlと互換性があるため、mysqlを使いたい場合は事前に削除する必要あり
　　特にAWSには初回にインストールされている可能性が高いです

$ yum list info | grep mariadb

もし、mariadbが入っている場合は、事前にuninstallする必要がある。

$ yum remove mariadb-libs

再度mariadbでgrepをかけて確認

$ yum list info | grep mariadb

ここで何も出てこなかったら、早速install !!

　※もし、まだ出てくるようであれば、「mariadb-libs」のように明示的に指定してuninstallしよう

今回は、最新版でインストール検証したいため、rpm (レポジトリ)はdownloadしない。

　※もし、明示的にバージョン指定したい場合は以下のリンクからrpmをdownloadしよう
　　コマンドも記載しておく(mysql80-community-release-el9-1.noarch.rpmの例)

$ yum localinstall http://dev.mysql.com/get/mysql80-community-release-el9-1.noarch.rpm

installする手順は、一緒なので下記コマンドでmysqlのパッケージがinstallできる

$ yum install mysql-community-server

これでmysql自体のインストールは完了です。

ただ、installしただけでは動いていないため、DBとして動かす必要があります。

下記コマンドを実行します。

$ systemctl start mysqld.service

これでDBサーバーの構築は完了です。お疲れ様でした。

mysqlサーバーのおすすめ初期設定

少しコマンドがわかる人なら疑問に思うかもしれませんが、末尾にある「service」は記載しなくても良いです。

systemctlコマンドが自動で補完してくれるので！

また、少し齧ったことがある方でしたら、同じようなコマンドで「service mysqld start」

あとmysqlの末尾にある「d」について、こちらは「デーモン」の略です。

デーモンは、バックグラウンドで実行してくれるもので、自分たちが操作している裏側で動いてくれるコマンドです。

もし、デーモンがないコマンドの場合は、バックグランドではなくフロント、つまり自分たちの見えるように動いてくれます。

ただし、DBは裏で動いていてほしいので、デーモンコマンドを使うことになります。

ここで「mysql -u root -p」でアクセスしても、パスワードをリセットしていないので、DBを操作することができません。

# mysql --help | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

grepすると、my.cnfは下記にあることがわかります。

・/etc/my.cnf
・/etc/mysql/my.cnf
・/usr/etc/my.cnf
・~/.my.cnf

OSによって場所が変わるので、どこにあるのかはlsなりfind grepなりして探してください。

初期設定では下記のみ記載があります。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

　　※こちらの部分は、ログファイルのみ好きなファイル名に変更できます。
　　　おすすめとして、こちらのログはエラーログなので、error.logなどに変更した方が良い。

初期設定では、これしか書いておらず今後運用していく場合、デフォルト設定では運用上十分ではありません。

そのため、個人的におすすめしたい設定をご紹介します。

# 一般的なログを保存
general_log      = 1
general_log_file = /var/log/mysql/general-query.log
log-error        = /var/log/mysql/error.log
# expire_logs_days = 2
binlog_expire_logs_seconds = 172800 #2日間

# パフォーマンスに及ぼす設定 - 主に同時接続に関連
max_connections   = 8
thread_cache_size = 32

# クエリキャッシュ関連 - 繰り返し同じようなクエリがある場合(8.0.3で設定が削除)
#query_cache_size  = 134217728 # 1024バイトごとに設定 - 128M
#query_cache_limit = 134217728 # 1024バイトごとに設定 - 128M
#query_cache_type  = ON  # ONでquery_cacheを有効化

# innodb関連
innodb_file_per_table   = ON # テーブルを個別に分けることで更新を早くする
innodb_buffer_pool_size = 1073741824 # 出来るだけたくさん - 割り当て可能な物理メモリの8割(1G)
innodb_log_file_size    = 1048576 # buffer_pool_sizeの1/4？ - 1MB以上4G未満 - 1MB
innodb_sort_buffer_size = 67108864 # 64M 

# スロークエリの設定
slow_query_log      = ON
long_query_time     = 1 # sec
slow_query_log_file = /var/log/mysql/slow_query.log

# スレッドバッファ関連（下記合計×スレッド数のメモリが必要になるのであまり大きくしない）
sort_buffer_size     = 33554432 # ソートに全般に利用される - 32M
join_buffer_size     = 16777216 # インデックス無しJOIN - 16M
read_buffer_size     = 16777216 # インデックス無しテーブルスキャン - 16M
read_rnd_buffer_size = 8388608  # インデックス有りソート - 8M

# その他の設定
!includedir /etc/mysql/conf.d/

上記のシステム変数それぞれについて解説していきたい。

# 一般的なログの保存

• general_log
  デフォルトでは0(無効)。デフォルトの場合、実行された正常なクエリがログに記載されない。
  そのため、1(有効)にすることで実行されたクエリを運用中に確認できるので、調査が楽になる。
  ただし、すべてのクエリを吐き出すため、ログローテートやストレージへのログ転送などのライフサイクル設定をしないと、ディスク容量が圧迫されるため注意したい。
• general_log_file
  上記のクエリログを出力する先、特別な環境でない限り一般的には、「/var/log/」配下に設置した方が良い。
  今回はわかりやすいログ構造にするため、さらに「mysql」ディレクトリを噛ませている。
• log-error
  初期設定にもあるシステム変数。通常の命名だとわかりにくいため、「/var/log/mysql/error.log」などのように「error」を書いてある方がわかりやすい。
• expire_logs_days
  こちらは、将来的に非推奨になるためコメントアウトしているが、バイナリログの保存期間を「日にち単位」で設定できるシステム変数。
  後述する「binlog_expire_logs_seconds」の方を公式が推奨しているため、今後はこちらで。
• binlog_expire_logs_seconds
  前述した「expire_logs_days」の秒単位版。
  こちらは、秒単位のため「X(日)*24(時間)*60(分)*60(秒)」での計算した実数値を記載する必要がある。
  注意したいのが、こちらはmysqlに保存されているバイナリファイルのため、ファイルとして出力したログを削除してくれる機能ではない。
  そのため、出力したログファイルのライフサイクルは必ず設定する必要がある。

# パフォーマンスに及ぼす設定 – 主に同時接続に関連

• max_connections = 8
  許可されるクライアントの同時接続数。デフォルト値は151。
  こちらの数値は、DBに接続してくるクライアントの数を下回らないように設定する必要があります。
  Nginxの場合は、「worker_processes * worker_connections」*サーバー台数分
  Apacheの場合は、「MaxClients」*サーバー台数分
  各々の環境によって設定すべき数値は変わると思うので、調整してください。
• thread_cache_size = 32
  mysqlのスレッドをキャッシュしておく最大値。
  mysqlでは、クライアントのリクエストごとにスレッドを使用しているが、このスレッドを用意するのにもサーバーリソースが使用される。
  そのため、現在使用しているDBサーバーでの最大スレッド実行数(max_threadrunning)を設定しておけば、問題なくサーバーを運用できると思われます。
  　※こちらは運用後に実際の数から算出するしか方法がないため、事前に設定するのは難しい

# クエリキャッシュ関連 – 繰り返し同じようなクエリがある場合(8.0.3で設定が削除)

• #query_cache_size
  #query_cache_limit
  #query_cache_type
  こちらに辿り着いた人の中にクエリキャッシュで苦しんでいる人がいるかもしれないので、備忘録として残します。
  知らない人向け↓
  　クエリキャッシュは、繰り返し同じようなクエリが使用された場合、キャッシュしたクエリを返すことが出来る機能。ただし、完全一致ではいけないため大文字小文字などの差分がある場合、別のクエリとして判別するため、使い勝手はあまり良くないです。
  　クエリキャッシュは、メモリを使用してキャッシュをするため、メモリを食います。そのため、本来使用したいリソースを圧迫しクエリキャッシュで動作が逆に重くなることがあるので、mysqlでは非推奨とし8.0.3では削除されました。
  　現在では、クエリキャッシュの代わりにproxysqlなどを使用して別のDBサーバーにクエリキャッシュの機能を追加するか、アプリ側でクエリのキャッシュを作成して保存するような設計にするのが推奨しています。

# innodb関連
mysqlでは使用できるストレージエンジンに、「innodb」と「MyISAM」があります。
今回は、基本的に使用するInnodbの設定値について解説する。

• innodb_file_per_table = ON # テーブルを個別に分けることで更新を早くする
  テーブルをすべて一括で管理するのがデフォルトであるため、更新の際に
• innodb_buffer_pool_size = 1073741824 # 出来るだけたくさん – 割り当て可能な物理メモリの8割(1G)
  　innodbの使用可能なメモリサイズ。設定したい値としては、割り当て可能な物理メモリの8割。
  　こちらは設定すれば、都度テーブルにアクセスするのではなくキャッシュからクエリを実行するため、実行速度が上がるためパフォーマンスが大きく改善します。
  　しかし、設定する値は他で使用するメモリを害するあたいではないため、パフォーマンスが悪くなった場合は、50%近くになるまで都度修正すると良い。
• innodb_log_file_size = 1048576 # buffer_pool_sizeの1/4？ – 1MB以上4G未満 – 1MB
  　書き込み(INSERT とかUPDATE)する際に使用するメモリの最大サイズ。1時間毎に更新されるデータ量を算出した値が適正値。
  　mysqlのコマンド「SHOW ENGINE INNODB STATUS」の「Log sequence number」か、「SHOW GLOBAL STATUS」の「Innodb_os_log_written」を1分間隔で取得し、その差分を求め、60倍して1時間あたりの更新量を求め、システム的にキリのいい数字にすればよろし。
• innodb_sort_buffer_size = 67108864 # 64M
  Innodbのソートに称するためのあらかじめ取っておきたい容量。

# スロークエリの設定

• slow_query_log = ON
  long_query_time = 1 # sec
  「long_query_time」を超えた処理が遅いクエリをログとして書き出してくれる。通常では「slow_query_log 」がOFF。
  こちらをONにしておくことで、処理が遅いクエリを特定し、運用中にインデックスを作成したり、クエリ改善をすることで、パフォーマンス向上の分析に使える。
  　※デフォルトだとOFFなので必ずONにすること。
• slow_query_log_file = /var/log/mysql/slow_query.log
  前述のスロークエリを書き出すファイルを設定する。

# スレッドバッファ関連（下記合計×スレッド数のメモリが必要になるのであまり大きくしない）

• sort_buffer_size = 33554432 # ソートに全般に利用される – 32M
  join_buffer_size = 16777216 # インデックス無しJOIN – 16M
  read_buffer_size = 16777216 # インデックス無しテーブルスキャン – 16M
  read_rnd_buffer_size = 8388608 # インデックス有りソート – 8M
  　上記の設定は、スレッドで使用される設定。コメントにある通りのクエリを実行する際に使用されるので、あまり大きくしないのが望ましい。
  　具体的な数字としては、「上記合計×スレッド数＝環境のメモリの1,2割程度」に収めるのが望ましい。

さいごに

今回は、自分でいちからLinuxサーバーを使用してDBサーバーを建てさせていただきました。

現在は、自分で建てることなくクラウドサービスを使用すれば煩わしい設定などをせずに建てることもできると思います。

ただし、今回した設定のようにクラウド毎にもシステム変数を設定しているので、どのような設定がされているのか見るのも勉強になります。

料金は自分で建てた方が安いので、料金を気になさるのであれば自分で構築した方がおすすめとなります。

また、クラウドサービスにおいて優れている点として「可用性」があるので、こちらについて少し触っておくのも良いと思っております。

またの機会がありましたら次回は可用性について記載させていただければなと思います。

では、よきエンジニアライフを！