質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

91.06%

  • MySQL

    4949questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • VPS

    282questions

    VPS(バーチャル・プライベート・サーバ)は、仮想化されたサーバをレンタルするサービスで、共有サーバでありながら専門サーバと同等の機能を果たします。物理的な専門サーバより安価で提供できるメリットがあります。

【MYSQL】【さくらVPS】メモリ設定の適切な方法について【メモリ:1 GB SSD利用の場合】

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 132

shimane

score 77

現在プログラムの勉強をしている者です。

今回、勉強用に運用していたサイトの表示速度が以前よりも遅くなった為
(以前は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 | 134217728134M)
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の再起動失敗」で数週間以上に渡って
データ復旧出来なかった等の体験談がありまして
調べれば調べるほどに怖くなってしまい、ぜひ経験者様、先輩方のご意見を頂ければ幸いです。

一生懸命調べてみましたが初心者の為、
勘違いや技術力不足による見逃しや間違いに不要な部分もあると思います。
何か間違いやお気付きの点がありましたら教えて頂けると嬉しいです。
どうかよろしくお願いします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 1

checkベストアンサー

+2

MySQLはテーブルごとにデータベースエンジンが定義されるので
MySQLサーバのストレージエンジン確認方法 などを参考にして確認されるといいと思います。
*意識せずにテーブルを作成すればinnodbになっているはずです。

完全に勘ですが、今回のケースだとメモリサイズの調整よりは処理に時間のかかるクエリが発行されているのではないかなという気がします。
DBが遅い!そんな時・・MySQL スロークエリーからの索引チューニング〜 mysqldumpslowが参考になります。

試すのが1番なのですが
ネットでMYSQLのメモリ設定について調べていると「MYSQLの再起動失敗」で数週間以上に渡って
データ復旧出来なかった等の体験談がありまして
調べれば調べるほどに怖くなってしまい、ぜひ経験者様、先輩方のご意見を頂ければ幸いです。

気軽に試せる実験環境を作りましょう。
サクラにもう一つVPSを借りてもいいですし、起動した時間だけ課金される体系のクラウドなら実験環境なら費用もほとんどかかりません。
開発しているPCのスペックに余裕があれば仮想環境を構築すれば費用はかかりませんしスペックもある程度VPSに似せることが可能です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/19 15:52

    回答有難うございます!

    教えてくださったURLで勉強した結果、ストレージエンジンの確認を行う事が出来ました。
    innodbだという事を知る事が出来ました、有難うございます。

    また、処理に時間のかかるクエリ発行についても教えて頂きまして有難うございます!

    サーバーやMYSQLに関しても初心者でしたので
    まるっきり意識にまったくありませんでした。てっきりメモリ関連の事なのかとばかり。

    現在、こちらも教えて頂いたURLに書いてある通り、
    0.5秒間以上かかっているmysqlをログに出力する設定を行っている最中です。

    結果が分かりましたら、再度こちらにてご報告させて頂きます。

    回答頂きまして本当に有難うございます。
    私もこういう事に気付くことが出来るように
    もっともっと勉強して技術力を少しでも増やしていきたいです。
    大感謝です!

    キャンセル

  • 2017/12/19 16:51

    はい。結果を教えてもらえると嬉しいです!
    (ただ、追加の質問は別途質問を立てた方がいいと思います。)

    MySQLに限らず、チューニングは
    公式ドキュメントや信頼の出来る資料を確認し、
    推測と計測と試行の繰り返しを行わないと勘の域を出ないので、
    設定そのものよりも
    「どうすれば設定したことをテスト/確認出来るか」という観点で手法を調べてみると良いと思います。

    今回のケースであれば、データが増えたらパフォーマンスが落ちたという事であれば、10倍、100倍のデータを準備してみればボトルネックになっている部分がより顕著になり、各チューニングの結果が把握しやすくなると思います。

    そのためにも壊してしまっても大丈夫な環境を作ることをお勧めします。
    (ローカル仮想環境やクラウド環境であればごく簡単にバックアップやスナップショットが取得出来るので、破壊的な実験も気軽にできます。)

    キャンセル

  • 2017/12/20 00:47

    教えて頂いた通りに
    遅延していると思われるMYSQLクエリを調べてみた所
    1つの「like %%」の部分一致を使用している箇所が大量に0.5秒、0.6秒となっていました。
    その部分のlike 部分一致を減らしたりした結果、
    ロードアベレージが0.8~1.0付近
    mysqlのcpuが15%~95%
    サーバー自体のCPUが1750から750付近へと半分以上も減らす事が出来ました!

    本当に本当に助かりました。
    何よりも
    メモリの設定だけでなく
    遅延クエリの発見方法やそれに関連して現在行っているクエリの確認方法だったり
    他にもいろいろとサーバーを運営していく上で役立つ事を知る事が出来ました。

    とても素晴らしい知識を教えて頂けて本当に感謝しています。
    有難うございます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 91.06%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • MySQL

    4949questions

    MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

  • VPS

    282questions

    VPS(バーチャル・プライベート・サーバ)は、仮想化されたサーバをレンタルするサービスで、共有サーバでありながら専門サーバと同等の機能を果たします。物理的な専門サーバより安価で提供できるメリットがあります。