Q&A
現在プログラムの勉強をしている者です。
今回、勉強用に運用していたサイトの表示速度が以前よりも遅くなった為
（以前は1秒未満だったのが2～3秒かかるようになってしまいました。）
初期状態からmysqlに適切なメモリ設定をしたいと思い、
色々とネットで調べてみたのですが疑問が解決できず、質問をさせて頂きました。
【現状】
さくらVPS：
ゾーン：東京第2ゾーン
メモリ：1 GB
ストレージ：SSD 30 GB
CPU：2コア
サーバの種類： MySQL
サーバのバージョン： 5.6.38 - MySQL Community Server (GPL)
プロトコル バージョン: 10
Server version: Apache/2.2.15 (Unix)
PHP のバージョン: 5.6.31
phpMyAdmin
バージョン情報： 4.7.4
1日のPV数：13,000～20,000PV
サイトの内容：
PHP+cronで相互登録ブログの記事・URLを取得してMYSQLを利用してデータベースに保存
PHP+PDOで並び替えを行って1ページあたり25件を表示してます。
データベースに登録されている記事の総数は約195,000件です。
私なりに必要だと思われる個所を調べてみましたが
サーバーについても初心者になったばかりの勉強中の身なので見当違いの事を書いていたら申し訳ないです。
mysqlのCPUが181.3％になっていました。
$ top
load averageの結果です。
# uptime 20:31:58 up 60 days, 19:53, 2 users, load average: 1.65, 1.76, 1.74
swapの確認結果です。
# vmstat 1 | awk '{print strftime("%Y-%m-%d %T ") $0; system("");}' 2017-12-18 20:35:56 procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu----- 2017-12-18 20:35:56 r b swpd free buff cache si so bi bo in cs us sy id wa st 2017-12-18 20:35:56 3 2 41204 208040 69488 275792 0 0 5 116 2 3 25 1 73 1 0 2017-12-18 20:35:57 2 3 41204 207916 69488 275828 0 0 0 0 4427 6797 77 5 16 3 0 2017-12-18 20:35:58 4 4 41204 208032 69488 275828 0 0 0 0 2148 2366 96 4 0 0 0 2017-12-18 20:35:59 3 4 41204 208032 69488 275828 0 0 0 0 2041 1561 94 4 3 0 0 2017-12-18 20:36:00 4 3 41204 207892 69488 275832 0 0 0 0 2196 1988 95 5 0 0 0 2017-12-18 20:36:01 3 1 41204 207892 69496 275828 0 0 0 12 2055 1682 95 4 0 1 0 2017-12-18 20:36:02 2 1 41200 186436 69496 275836 0 0 0 48 3922 10147 88 8 4 1 1 2017-12-18 20:36:03 3 6 41200 177372 70160 275892 0 0 156 3396 5572 7643 83 10 5 3 0
# free total used free shared buffers cached Mem: 1019944 664380 355564 56 12076 252984 -/+ buffers/cache: 399320 620624 Swap: 2097148 55336 2041812
【MYSQLの軽量化対策で以前からやっていた事】
ネットで調べてみた所、mysql5.6はメモリ使用量が多いという事が書いてありまして
参考サイトに書いてあった手順通りに進めて
メモリ使用量を減らす事に成功していました。
参考サイト：
mysql5.6 メモリ使用量を減らす。
それでもサイトが重くなってしまいましたので
今回【mysqlの各種メモリの設定】についても行おうと思い
ネットで色々と調べた結果、複数の参考になりそうなサイトを発見出来ました。
特に参考になったサイト
MySQL最低限のメモリ設定
MySQLメモリ設定を変更して、前後の設定値を比較してみた
5分で出来るMySQLのお手軽チューニング
参考サイトや他のサイトで確認した結果、
メモリ設定に必要だと思われる各種情報の一覧です。
【現状】
mysql> show variables like '%innodb_buffer_pool_size%'; innodb_buffer_pool_size | 134217728 （134M）
mysql> show variables like '%innodb_log_file_size%'; innodb_log_file_size | 50331648 (50M)
mysql> show variables like '%key_buffer_size%'; key_buffer_size | 8388608 (8M)
mysql> show variables like '%read_buffer_size%'; read_buffer_size | 131072 (131k)
mysql> show variables like '%sort_buffer_size%'; sort_buffer_size | 262144(262k)
mysql> show variables like '%read_rnd_buffer_size%'; read_rnd_buffer_size | 262144 (262k)
mysql> show variables like '%query_cache_size%'; query_cache_size | 1048576 (1M)
mysql> show variables like '%thread_cache_size%'; thread_cache_size | 9
mysql> show status like 'Threads_connected'; Threads_connected | 7 (現在接続中のコネクト数)
mysql> select @@GLOBAL.MAX_CONNECTIONS; 151 （最大151）
総メモリー量算出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
実行結果：
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
メモリ使用量 = グローバルバッファ + （スレッドバッファ x スレッド数）
159M+(9M×151)=1,518M
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
ここまでは調べる事が出来たのですが、
調べてみたサイトによっては
MySQLの代表的なストレージエンジンはMyISAMとInnoDBですが、
利用するエンジンによりメモリ（グローバルバッファ）の使い方は異なります（スレッドバッファは共通）。
5.5からは、デフォルトのエンジンがInnoDBになったので、
基本、InnoDBのことだけ考えておけばいいのですが、
システムDBはMyISAMということなので、MyISAMのことも最低限考えておく必要があります。
と書いてあったり
ストレージエンジンはMyISAMを使っています。
と書いてありまして迷っています。
私の場合は「InnoDB」を利用しているという事を教えて頂けました。
【InnoDBの場合の設定】
# vim /etc/my.cnf [mysqld] max_connections = 50 innodb_buffer_pool_size = 256M innodb_log_file_size = 64M read_buffer_size = 1M sort_buffer_size = 1M read_rnd_buffer_size = 4M query_cache_size = 16M thread_cache_size = 16 key_buffer = 32M key_buffer_size = 32M (念の為key_buffer+key_buffer_sizeにも「32M」を設定しています。)
InnoDBの時にもこちらの「key_buffer」に関する2つの設定は必要なのでしょうか？
key_buffer = 32M
key_buffer_size = 32M
初期設定の「8M」のままでもいいのか悩んでおります。
また、別の部分の設定についても不必要な箇所、足りない部分がありましたら
教えて頂けると嬉しいです。
試すのが1番なのですが
ネットでMYSQLのメモリ設定について調べていると「MYSQLの再起動失敗」で数週間以上に渡って
データ復旧出来なかった等の体験談がありまして
調べれば調べるほどに怖くなってしまい、ぜひ経験者様、先輩方のご意見を頂ければ幸いです。
一生懸命調べてみましたが初心者の為、
勘違いや技術力不足による見逃しや間違いに不要な部分もあると思います。
何か間違いやお気付きの点がありましたら教えて頂けると嬉しいです。
どうかよろしくお願いします。
回答1件
0
ベストアンサー
MySQLはテーブルごとにデータベースエンジンが定義されるので
MySQLサーバのストレージエンジン確認方法 などを参考にして確認されるといいと思います。
*意識せずにテーブルを作成すればinnodbになっているはずです。
完全に勘ですが、今回のケースだとメモリサイズの調整よりは処理に時間のかかるクエリが発行されているのではないかなという気がします。
DBが遅い！そんな時・・MySQL スロークエリーからの索引チューニング〜 mysqldumpslowが参考になります。
試すのが1番なのですが
ネットでMYSQLのメモリ設定について調べていると「MYSQLの再起動失敗」で数週間以上に渡って
データ復旧出来なかった等の体験談がありまして
調べれば調べるほどに怖くなってしまい、ぜひ経験者様、先輩方のご意見を頂ければ幸いです。
気軽に試せる実験環境を作りましょう。
サクラにもう一つVPSを借りてもいいですし、起動した時間だけ課金される体系のクラウドなら実験環境なら費用もほとんどかかりません。
開発しているPCのスペックに余裕があれば仮想環境を構築すれば費用はかかりませんしスペックもある程度VPSに似せることが可能です。
投稿2017/12/18 17:39
総合スコア18778
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/12/19 06:52
2017/12/19 07:51
2017/12/19 15:47