MySQLの標準の設定を聞かれたときの備忘録 #mysql5.6

タイトル通りですがなんでか違う複数の人から何回も同じこと聞かれるので備忘録しておいて今後はポインタしたい所存です。
知ってる人には新しい情報はないです。ツッコミを入れていただくのはウェルカムです。

（特に希望がないInnoDB前提、GTIDもMTSも無効な5.6前提としてきかれた）とはいえマシンスペックに合わせて初期チューニングは要るし、パッケージやソース同梱のmy.cnfはほとんどの場合そのままではマシンリソースに合わないので絶対にそのまま使わないのを推奨します。
リソースを使い切れないか割り当てすぎてメモリが不足してDB落ちて夜中に障害対応になるのを事前に回避したいし危うい設定すると顧客から瑕疵がどうのまたは悪い評判で仕事がなくなる可能性が生じるのも回避したい所存。

搭載メモリサイズに合わせていじらないとまずいとこが、
innodb_buffer_pool_sizeを相乗りでないDB占有サーバなら搭載メモリの75%に設定、
innodb_io_capacityをディスクのIOPS性能より5.6は若干控えめに設定
スレッドバッファ×max_connections＋グローバルバッファが＋OSが利用するメモリ≒＜搭載メモリ
な感じになるようにmysqltunerに計算してもらいつつ設定
というのが大雑把なセオリー。

mysqltunerの中身抜粋（旧いかもしれないので自分で確認推奨）

#グローバルバッファ
key_buffer_size+max_tmp_table_size+innodb_buffer_pool_size+innodb_additional_mem_pool_size+query_cache_size

#スレッドバッファ×max_connections
per_thread_buffers=read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size
total_per_thread_buffers=per_thread_buffers*max_connections

最も大事なのは割り当てるメモリが絶対に搭載メモリを超えないようにすることです。

またメモリが足らなくなってLinuxのOOM_Killerの餌食になってプロセスが落ちたりしないようにしたい所存です。

OOM_Killerの標的にならないようにする方法もあり、SSHデーモンと同様に以下の様にするとよいです。デフォルトだと0でメモリをアロケートできなくなったら無差別攻撃でプロセス落としだす標的になる模様です。大事な子（プロセス）のスコアは調整しておくとよさそう（起動スクリプトのstartセクションに処理追加。sshdの起動スクリプトを参考にしたらよいと思います）。

# cat /proc/3139/oom_adj 
-17
# cat /proc/3139/oom_score_adj 
-1000

systemdの場合は、ユニットファイルの[Service]セクションに書けばよいようです。
https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

# mkdir /etc/systemd/system/mysqld.service.d/
# cd /etc/systemd/system/mysqld.service.d/
# vi 10-mysqld.conf
[Service]
OOMScoreAdjust=-1000

OSが利用するメモリは最近のサーバはメモリ潤沢だし1GBあればいいんじゃないのという話もありますが、手で測る場合は以下のかんじ。グラフがある人は気にしないでください。（％出してる理由はmysqltuner都合です）

#実際つかってる物理メモリ合計（KB)
RSS_SUM=`ps auxwwf | awk '{FS=" ";total+=$6}; END {print total}'`

##※たとえばmysql以外を出したいときはgrep -v mysql等をawkの前に入れる必要があります。

#搭載されているメモリの合計（KB）
MEM_TOTAL=`cat /proc/meminfo|grep MemTotal:|awk '{print $2}'`

#実際利用している割合を出す（%）
echo "$RSS_SUM / $MEM_TOTAL * 100" |bc -l

最近のmysqlの中の人の奥野先生のパフォチュースライドのP27もお勧めでごさいますです。
https://www.slideshare.net/nippondanji/mysql-57-77003983

innodb_buffer_pool_size – できるだけ大きく（定説はシステムメモリの７ ８割）〜 
innodb_flush_method – O_DIRECT でダブルバッファリングを防ぐ 
innodb_log_file_size – 十分なサイズを確保 
innodb_io_capacity – ディスクの IOPS に合わせる 
innodb_purge_threads, innodb_write_io_threads – 更新が多ければ増やす

sysctl関連
特に要望がなくてDSRとかでないCent6なら私は以下あたりを設定します（マシンイメージ側にあれば二重に設定しなくてOK）

# swap発生しづらくする
vm.swappiness=10
# TCPのfinwaitをリサイクル、タイムアウト値を短くする
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_fin_timeout = 10
# 超えるとsynが捨てられるキュー数の上限を広げる
net.ipv4.tcp_max_syn_backlog = 8192
net.core.somaxconn = 8192
# TCPのキープアライブの間隔やリトライ回数などの調整
net.ipv4.tcp_keepalive_intvl = 3
net.ipv4.tcp_keepalive_probes = 2
net.ipv4.tcp_keepalive_time = 10

recycleは個人的にはバックエンドのDBで同セグのIPが重複しないWEBからしか接続がないなら1でもいいと思っています。（接続元IP重複同時刻通信の片方が握りつぶされる仕様になるので共有Wifiから携帯端末で通信されるようなフロントのサーバが影響を受ける）

RDS(zabbix用)のチューニングの記録
http://qiita.com/smallpalace/items/6697237f97e85d479481

近いスペックのRDSのデフォルト値パクって調整でもいいですがスロークエリログ設定を忘れずにどうぞ。

最後に、初期のサーバパラメータチューニングでしかないですが搭載メモリを超えない範囲なら
見積もりが甘すぎたか人気が出すぎたとかでなければしばらくは平和に運用できるはず。たぶん。
普段からポイントになる指標はグラフ化しとくと傾向を解析するのが楽になるというのはよく聞きます。

スペックについては負荷テストして見積もるか公式サイトの必要スペックを確認するなど。
DB載ってるホストに最低必要なスペックとしては、メモリ8GBくらいからかなあという感覚。
あとメモリ空間を有効活用できない32bitOSは使用禁止などでしょうか。
（物理ならWritebackCacheつきRaidコントローラ、SSD推奨なども。）

設定のセオリーということなのでこのくらいで失礼します。