5分で出来るMySQLのお手軽チューニング
こちらのエントリーで書いているのですが、開発用のVPS鯖がメモリ使い過ぎで重くなっていて、mysqlに問題がありそうだったのでサクっと調整しました。
その手順を残しておきます。
MySQLのチューニング項目について説明
MySQLのメモリに関する設定項目はたくさんあります。
それぞれの項目に関しての詳しい情報は下記が参考になります。
http://trackback.blogsys.jp/livedoor/klab_gijutsu2/50860867
ただ、これらを全て把握するのは大変だと思うので、簡単に分類すると以下の2種類があります。
- グローバルバッファ
- スレッドバッファ
で、MySQLが使うメモリ量は下記になるわけです。
グローバルバッファ + ( スレッドバッファ * コネクション数 )
それらを踏まえた上で具体的なチューニングに入ります。
MySQLで使っているメモリ量を調べる
こんな便利なものを作ってくれた方がいました。
http://blog.mogmet.com/calculate-mysql-total-memory-sql/trackback/
【MySQL】総メモリー使用量を算出するSQL作ってみた
SQL部分を引用させてもらうと
select @@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH as GLOBAL_BUFFER_SIZE, @@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE as THREAD_BUFFER_SIZE, @@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE, (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb, (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb, (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.INNODB_ADDITIONAL_MEM_POOL_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb \G
となっており、これを実行すると以下のようになります。
*************************** 1. row *************************** GLOBAL_BUFFER_SIZE: 159399936 THREAD_BUFFER_SIZE: 9306112 TOTAL_MEMORY_SIZE: 1564622848 TOTAL_MEMORY_SIZE_kb: 1527952.0000 TOTAL_MEMORY_SIZE_mb: 1492.14062500 TOTAL_MEMORY_SIZE_gb: 1.457168579102 1 row in set (0.38 sec)
この場合、一見GLOBAL_BUFFER_SIZEが大きいように見えますが、問題なのはスレッドバッファです。
何故ならスレッドバッファが使うメモリサイズは 【スレッドバッファ * コネクション数】となるためです。
ちなみにコネクション数を確認するにはmysqlのコンソールで下記を実行します。
mysql> select @@GLOBAL.MAX_CONNECTIONS; +--------------------------+ | @@GLOBAL.MAX_CONNECTIONS | +--------------------------+ | 151 | +--------------------------+ 1 row in set (0.00 sec)
151...
9306112 * 151 = 1405222912 byte = 1340.125MB
で、スレッドバッファに1340.125MBも使ってます。
MySQLの設定を修正
何が問題なのかわかったので実際にMySQLの設定を修正していきます。
『/etc/my.cnf』に下記の設定を書きます。
[mysqld]
max_connections = 31
今回はスレッド数が多すぎたので max_connections を 151ー＞31 と設定しました。
31という値については各環境で適当に設定してください。
グローバルバッファも設定したい場合は
[mysqld]
max_connections = 31
innodb_buffer_pool_size = 256MB
のように innodb_buffer_pool_size を設定します。
こちらの256MBという値についても各環境で適当に設定してください。
本当はグローバルバッファの設定には他の設定項目もありますが、大部分を占めるのが innodb_buffer_pool_size なので、シンプルに設定するためにこの項目だけにしています。
これだけでチューニングは完了です。
あとはmysqlをリスタートすれば設定が反映されます。
service mysql restart
設定後のメモリ使用量を確認
上のほうで紹介した長い長いSQLを実行して設定後のメモリ使用量を確認した結果、以下のようになりました。
*************************** 1. row *************************** GLOBAL_BUFFER_SIZE: 293617664 THREAD_BUFFER_SIZE: 9306112 TOTAL_MEMORY_SIZE: 582107136 TOTAL_MEMORY_SIZE_kb: 568464.0000 TOTAL_MEMORY_SIZE_mb: 555.14062500 TOTAL_MEMORY_SIZE_gb: 0.542129516602 1 row in set (0.00 sec)
トータルメモリ使用量が0.5GBほどにまで下がってます。めでたい。
※メモリは使う量が少なければ少ないほどいいわけではなく、（swapしない範囲で）割り当てが多ければ多いほどパフォーマンスは向上します。今回はApache等も同時に動かしている鯖なので、mysqlのメモリ使用量を抑えたい目的で設定しています。そこは各自の環境に合わせて設定してください。
まとめ
- max_connections
- innodb_buffer_pool_size
を調整すればそこそこいい感じになる！
もっと調整していきたい！という方はここから他の細かい設定項目を調整していけばいいんじゃないでしょうか。
近状 20140118
最近ブログ書いてないからなんか書く。
とりあえず頭の整理がてら最近抱えてる仕事をつらつら書こうかなと。
大きなものをあげると以下の３つ。
- 合コン斡旋サービス作る
- 居酒屋ホームページ作る
- GoogleAnalyticsのログをいい感じに使う
これらについて詳しく書いていくと
合コン斡旋サービス作る
詳しいサービス内容はリリースしたらブログに書くとして、先月中頃に「一緒になんかサービス立ち上げようぜ！」って誘いを頂いたのでとりあえずWeb側のシステム作ってる。
Webシステムは8割方完成（プログラム・デザイン共に）
レスポンシブデザインで構築してるからスマホ用ページを作る手間がなくていい感じ。
Ajaxも容赦なく使って、操作してて楽しいしいい感じ。
あとはサービスの説明とか求人とかの文言どうしようかってところ。
そのへんは打ち合わせしつつ詰めていきたい。
あー、あとドメインも取ってないし本番鯖も用意してない。とりあえずAWS使う予定。
あと実際に運用するようになったら誰がどんな事すんのとか、結構勢いで進めてきたけどこのサービスほんとに大丈夫なの？とか、、スピード感持って進めていきたい所だけど、ここからが意外と長いんだろうなぁ。
それと合コン斡旋について思考実験繰り返しているうちにもっと良さそうなビジネスモデル浮かんできてそっちにときめいたりもしちゃってる。
軸がブレてあっち行ったりこっち行ったりで前に進まないような事にはならないよう注意しつつ、そのへん含めて次回の打ち合わせで提案してみよう。
問題はまだまだあるし、これから大きなトラブルに直面する事もあるだろうけど何かサービス運用してみたいと思ってたから、これはやってて結構楽しい。
どうにかしよう。
居酒屋ホームページ作る
請負でホームページ制作。
請負の仕事とかあまりノリ気ではなかったけれど、昔からの知り合いの頼みだったので引き受けた。
これに関してはお金稼ぎたいというよりも無事に終わってくれればそれでいいかなと思う。
作業としても目新しいモノも無くてそんな楽しくなさそうだし、自分の時間をあまり割きたくないのでcrowdworksにでも投げてみようかとも考えてる。
追記 - 居酒屋ホームページは結局自分でサクッと作ってS3に静的コンテンツとして上げて公開した。制作費ももらったけど写真撮影用にデジタル一眼を買ったので大した利益にはなってない。
まあ、長く使えるものだし（震え声）
ローカルストレージを使った問い合わせフォームを作った
ちょっと必要になったのでローカルストレージを使ってメアド入力が不要な問い合わせ機能を作った。
今までのような
- ユーザーがメールアドレスを入力して問い合わせし、お店の人がそのメアド宛に返答する。
という流れではなく
- ブラウザ上で問い合せを送ってお店の人がそこに返答する。
といった、LINEのような掲示板のようなチャットのような流れになっています。
あくまでデモ版なので2重投稿対策などは行っておりません。
（このまま運用で使うのはオススメしません）
肝心のユーザーID周りは、
まだ生成されていなければ一意のIDを生成 → ローカルストレージにぶっこむ
となっています。
抽選してばばーん！と結果がでるルーレット作った
会社のLT大会で必要になりそうだったので作った。
ルーレット
http://nickworks.net/products/roulette/
一応Githubにもコード上げておいた。
nickworks-net/roulette · GitHub
もし必要な人がいたらぜひ使ってください。
よし寝よう。
nginxでバーチャルホスト設定
前回nginxとFastCGIいれたけどバーチャルホストの設定もしたかったので設定した。
前回の記事：CentOS6.4にnginxとFastCGI入れる - わーくあうと！
設定ファイルを作る
vhost.confというファイルを作って
vi /etc/nginx/conf.d/vhosts.conf
中身はこんな感じにした
# バーチャルホスト用の設定.
# 基本的には /var/www/nginx/vhosts/ の下にホスト名のディレクトリ作るだけでOK.
server {
listen 8080;
server_name ~^(.*)?;
#charset koi8-r;
#access_log /var/log/nginx/log/host.access.log main;
# ↓ホントはホスト毎にアクセスログ分けたかったが $host が効かないようなので諦める。
# mainのログフォーマットに $http_host を付けたからgrepすれば見れるし。
#access_log /var/www/nginx/vhosts/$host/logs/access_log main;
root /var/www/nginx/vhosts/$host/html;
location / {
index index.html index.htm index.php;
}
#error_page 404 /404.html;
# redirect server error pages to the static page /50x.html
error_page 500 502 503 504 /50x.html;
location = /50x.html {
#root /var/www/nginx/html;
}
# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
#
location ~ \.php$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_param SCRIPT_FILENAME /var/www/nginx/vhosts/$host$fastcgi_script_name;
include fastcgi_params;
}
}
これであとは /var/www/nginx/vhosts/ の下にホスト名のディレクトリ作っていけばバーチャルホスト増やせる。
例）/var/www/nginx/vhost/hoge.domain.com
うん、シンプル。
CentOS6.4にnginxとFastCGI入れる
借りてるVPS鯖にnginxとFastCGIいれたのでメモっておく
FastCGI入れる
php-fastcgiの起動スクリプトを作成
# vi /etc/rc.d/init.d/php-fastcgi
で
#!/bin/sh # # spawn-fcgi Start and stop FastCGI processes # # chkconfig: - 80 20 # description: Spawn FastCGI scripts to be used by web servers # Source function library. . /etc/rc.d/init.d/functions RETVAL=0 SPAWNFCGI="/usr/bin/spawn-fcgi" PHPFCGI="/usr/bin/php-cgi" FCGIPORT="9000" FCGIADDR="127.0.0.1" PHP_FCGI_CHILDREN=5 PHP_FCGI_MAX_REQUESTS=1000 ALLOWED_ENV="PATH USER" USER=nginx GROUP=nginx PIDFILE=/var/run/phpfcgi.pid ALLOWED_ENV="$ALLOWED_ENV PHP_FCGI_CHILDREN PHP_FCGI_MAX_REQUESTS FCGI_WEB_SERVER_ADDRS" case "$1" in start) PHPFCGI_START=$"Starting ${NAME} service: " echo -n $PHPFCGI_START # clean environment E= for i in $ALLOWED_ENV; do E="$E $i=${!i}"; done daemon $SPAWNFCGI -a ${FCGIADDR} -p ${FCGIPORT} -u ${USER} -g ${GROUP} -P ${PIDFILE} -C ${PHP_FCGI_CHILDREN} -f ${PHPFCGI} RETVAL=$? ;; stop) echo -n "Stopping php-fcgi: " killproc -p $PIDFILE phpfcgi echo RETVAL=$? ;; *) echo "Usage: $0 {start|stop}" exit 1 esac exit $RETVAL
これをコピペする。
nginx入れる
nginx公式リポジトリを追加
# rpm -ivh http://nginx.org/packages/centos/6/noarch/RPMS/nginx-release-centos-6-0.el6.ngx.noarch.rpm
ドキュメントルートにするディレクトリ作成
# mkdir /var/www/nginx # mkdir /var/www/nginx/html
設定ファイル編集
/etc/nginx/conf.d/default.conf
デフォルトで
server {
listen 80;
server_name localhost;
#charset koi8-r;
#access_log /var/log/nginx/log/host.access.log main;
location / {
root /usr/share/nginx/html;
index index.html index.htm;
}
#error_page 404 /404.html;
# redirect server error pages to the static page /50x.html
#
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /usr/share/nginx/html;
}
# proxy the PHP scripts to Apache listening on 127.0.0.1:80
#
#location ~ \.php$ {
# proxy_pass http://127.0.0.1;
#}
# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
#
#location ~ \.php$ {
# root html;
# fastcgi_pass 127.0.0.1:9000;
# fastcgi_index index.php;
# fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
# include fastcgi_params;
#}
# deny access to .htaccess files, if Apache's document root
# concurs with nginx's one
#
#location ~ /\.ht {
# deny all;
#}
}
↑こうなってる内容を
server {
listen 8080;
server_name localhost;
#charset koi8-r;
#access_log /var/log/nginx/log/host.access.log main;
root /var/www/nginx/html;
location / {
#root /var/www/nginx/html;
index index.html index.htm index.php;
}
#error_page 404 /404.html;
# redirect server error pages to the static page /50x.html
#
error_page 500 502 503 504 /50x.html;
location = /50x.html {
#root /var/www/nginx/html;
}
# proxy the PHP scripts to Apache listening on 127.0.0.1:80
#
#location ~ \.php$ {
# proxy_pass http://127.0.0.1;
#}
# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
#
location ~ \.php$ {
#root html;
fastcgi_pass 127.0.0.1:9000;
#fastcgi_index index.php;
#fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
# deny access to .htaccess files, if Apache's document root
# concurs with nginx's one
#
#location ~ /\.ht {
# deny all;
#}
}
↑このように編集した。
CentOS6.4のPHPを5.4にアップグレードしようとしてやめた話
借りてるVPS鯖のPHPのバージョンが5.3（普通にyumで入れた）で、せっかくなので5.4にでもしようかなと下記の記事を参考に入れてた
■CentOS6.4にサードパーティレポジトリを追加して、PHP5.4とかいれてた - otukutunの日記
要はremiやらepelやらのサードパーティリポジトリ追加してそこから入れるんだけど、よくよく考えたらあとからphpのモジュール追加するときも
yum install --enablerepo=remi php-なんちゃら
とかしないとだめなんだよなぁと思ってやっぱ5.3に戻した。
後々絶対忘れる自信があったので。。
デフォルトで使うリポジトリをサードパーティ製のものにすれば解決っちゃ解決だけどそれも微妙だし。
というお話でした。
pm2のインストールでこける件
node.jsをデーモン化するにはforeverよりpm2のほうが高機能でいいよという噂を耳にしたのでpm2を入れようとしたら失敗した。
$npm install -g pm2 〜省略〜 > usage@0.3.9 install /root/.nvm/v0.10.22/lib/node_modules/pm2/node_modules/usage > node-gyp rebuild gyp ERR! build error gyp ERR! stack Error: not found: make gyp ERR! stack at F (/root/.nvm/v0.10.22/lib/node_modules/npm/node_modules/which/which.js:43:28) gyp ERR! stack at E (/root/.nvm/v0.10.22/lib/node_modules/npm/node_modules/which/which.js:46:29) gyp ERR! stack at /root/.nvm/v0.10.22/lib/node_modules/npm/node_modules/which/which.js:57:16 gyp ERR! stack at Object.oncomplete (fs.js:107:15) gyp ERR! System Linux 2.6.32-358.2.1.el6.x86_64 gyp ERR! command "node" "/root/.nvm/v0.10.22/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild" gyp ERR! cwd /root/.nvm/v0.10.22/lib/node_modules/pm2/node_modules/usage gyp ERR! node -v v0.10.22 gyp ERR! node-gyp -v v0.11.0 gyp ERR! not ok npm ERR! usage@0.3.9 install: `node-gyp rebuild` npm ERR! Exit status 1 npm ERR! npm ERR! Failed at the usage@0.3.9 install script. npm ERR! This is most likely a problem with the usage package, npm ERR! not with npm itself. npm ERR! Tell the author that this fails on your system: npm ERR! node-gyp rebuild npm ERR! You can get their info via: npm ERR! npm owner ls usage npm ERR! There is likely additional logging output above. npm ERR! System Linux 2.6.32-358.2.1.el6.x86_64 npm ERR! command "/root/.nvm/v0.10.22/bin/node" "/root/.nvm/v0.10.22/bin/npm" "install" "-g" "pm2" npm ERR! cwd /var/www/chat-server npm ERR! node -v v0.10.22 npm ERR! npm -v 1.3.14 npm ERR! code ELIFECYCLE npm http 200 https://registry.npmjs.org/keypress npm http GET https://registry.npmjs.org/keypress/-/keypress-0.1.0.tgz npm http 200 https://registry.npmjs.org/keypress/-/keypress-0.1.0.tgz npm ERR! npm ERR! Additional logging details can be found in: npm ERR! /var/www/chat-server/npm-debug.log npm ERR! not ok code 0
こんなエラー。
makeが無いとか言ってるけど、まぁとりあえず Development Tools 入れてみるかーと思い以下をインストール
$ yum groupinstall "Development tools"
それで
$ npm install -g pm2
したらインストール成功した。
CentOSにnode.jsをnvmでインストール
自分の開発VPS鯖にnvmでnode.js入れたけど忘れそうなのでメモっておく。
nvmのインストール
$ git clone git://github.com/creationix/nvm.git ~/.nvm $ source ~/.nvm/nvm.sh
nodeのインストール
$ nvm install v0.10.22
確認
$ which node /root/.nvm/v0.10.22/bin/node $ which npm /root/.nvm/v0.10.22/bin/npm
