MySQL の設定とチューニング

はじめに

この実験では、MySQL サーバーを最適なパフォーマンスに設定および調整する方法を学びます。この実験では、SHOW VARIABLES を使用して現在の設定を表示し、InnoDB バッファプールのサイズを調整し、調整後のクエリパフォーマンスを監視し、設定の変更を保存する手順を案内します。

まず、MySQL コマンドラインインターフェイスに接続し、SHOW VARIABLES を使用して、innodb_buffer_pool_size や max_connections に関連する変数を含むさまざまなシステム変数を調べます。これにより、データベースの現在の状態を理解し、潜在的な最適化の対象領域を特定することができます。

Skills Graph

SHOW VARIABLES で現在の設定を表示する

このステップでは、SHOW VARIABLES コマンドを使用して MySQL サーバーの現在の設定を表示する方法を説明します。このコマンドは、データベースの現在の状態を理解し、潜在的な最適化の対象領域を特定するために不可欠です。

SHOW VARIABLES コマンドは、MySQL のシステム変数とその現在の値のリストを表示します。これらの変数は、バッファプールのサイズ、接続制限、文字セットなど、サーバーの動作のさまざまな側面を制御します。

まず、MySQL コマンドラインインターフェイスにアクセスします。これは、ターミナルを開き、次のコマンドを実行することで行えます。

mysql -u root -p

root パスワードの入力を求められます。パスワードを入力して続行します。root パスワードを設定していない場合は、パスワードなしで接続できる場合があります。

MySQL サーバーに接続したら、SHOW VARIABLES コマンドを実行できます。すべての変数を表示するには、次のように入力します。

SHOW VARIABLES;

これにより、変数とそれに対応する値の長いリストが表示されます。出力はかなり多くなる場合があるので、関心のある特定の変数を見つけるために結果をフィルタリングすることをお勧めします。

たとえば、innodb_buffer_pool_size 変数の値を見つけるには、LIKE 句を使用できます。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

このコマンドは、変数名とその現在の値を含む 1 行を返します。

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+--------------------------+-----------+
1 row in set (0.00 sec)

ワイルドカードを使用して、特定のパターンに一致する複数の変数を検索することもできます。たとえば、max_connections に関連するすべての変数を見つけるには、次のコマンドを使用します。

SHOW VARIABLES LIKE 'max_connections%';

これにより、max_connections で始まる変数のリストが返されます。

+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| max_connect_errors                | 100   |
| max_connections                   | 151   |
| max_user_connections              | 0     |
+-----------------------------------+-------+
3 rows in set (0.00 sec)

SHOW VARIABLES の使い方を理解することは、MySQL サーバーの監視と調整に不可欠です。次のステップでは、このコマンドを使用して innodb_buffer_pool_size を調整し、クエリパフォーマンスへの影響を監視します。

InnoDB バッファプールのサイズを調整する

このステップでは、InnoDB のパフォーマンスに重要なパラメータである innodb_buffer_pool_size を調整します。バッファプールは、InnoDB がデータとインデックスページをキャッシュするメモリ領域です。そのサイズを増やすと、特に読み取りが多いワークロードでパフォーマンスが大幅に向上する可能性があります。ただし、サイズを大きく設定しすぎると、メモリが枯渇する可能性があります。

変更を加える前に、システムの利用可能なメモリを理解することが重要です。一般的な推奨事項は、サーバーの RAM の約 70 - 80% を InnoDB バッファプールに割り当てることですが、これは具体的なワークロードやサーバー上で実行されている他のアプリケーションによって異なります。

ここではリソースが制限された LabEx VM 環境にいるため、システムを圧迫することなく設定プロセスを実演できる適切な値にバッファプールのサイズを調整します。デフォルト値（前のステップで確認した値）から 256MB に増やします。

innodb_buffer_pool_size を変更するには、MySQL の設定ファイルを変更する必要があります。このファイルの場所はシステムによって異なりますが、一般的には /etc/mysql/my.cnf または /etc/my.cnf にあります。

まず、設定ファイルの正確な場所を特定しましょう。次のコマンドを試してみることができます。

sudo find / -name my.cnf

このコマンドは、ファイルシステム全体を検索して my.cnf という名前のファイルを探します。Docker コンテナ内にいるため、検索範囲は限られており、比較的すぐにファイルが見つかるはずです。

my.cnf ファイルの場所が特定できたら（この例では /etc/mysql/my.cnf と仮定します）、nano などのテキストエディタを使用して編集できます。

sudo nano /etc/mysql/my.cnf

my.cnf ファイルはセクションに分かれています。[mysqld] セクションを探します。存在しない場合は、追加することができます。[mysqld] セクション内で、innodb_buffer_pool_size パラメータを追加または変更します。

[mysqld]
innodb_buffer_pool_size=256M

変更を保存してテキストエディタを終了します。nano では、Ctrl+X を押し、次に Y を押して変更を確認し、最後に Enter を押して保存します。

設定ファイルを変更した後、変更を有効にするには MySQL サーバーを再起動する必要があります。Docker コンテナ内にいるため、systemctl を使用することはできません。代わりに、mysqladmin コマンドを使用してサーバーを停止し、その後起動します。

まず、MySQL サーバーを停止します。

mysqladmin -u root -p shutdown

root パスワードの入力を求められます。パスワードを入力して続行します。

次に、MySQL サーバーを起動します。通常の環境では systemctl start mysql を使用しますが、LabEx VM の Docker 環境では、systemctl が使用できないため、mysqld_safe コマンドを使用して MySQL サーバーを起動する必要があります。このコマンドは、systemctl が利用できない環境で MySQL サーバーを起動するために設計されています。

sudo mysqld_safe &

コマンドの最後の & は、サーバーをバックグラウンドで実行します。ターミナルにいくつかのログ情報が出力されます。

これで、コマンドラインインターフェイスを使用して MySQL サーバーに再接続します。

mysql -u root -p

そして、innodb_buffer_pool_size が更新されたことを確認します。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

出力に新しい値（268435456、これは 256MB をバイトに換算した値）が反映されているはずです。

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+--------------------------+-----------+
1 row in set (0.00 sec)

おめでとうございます！InnoDB バッファプールのサイズを正常に調整しました。次のステップでは、クエリパフォーマンスを監視して、この変更の影響を確認します。

チューニング後のクエリパフォーマンスを監視する

このステップでは、InnoDB バッファプールのサイズを調整した後のクエリパフォーマンスを監視します。包括的なパフォーマンス分析には、より高度なツールと現実的なワークロードが必要ですが、基本的な手法を使って変更の影響を把握することができます。

この実験では、単純なクエリの実行時間を観察することに焦点を当てます。実際のシナリオでは、mysqldumpslow、Performance Schema、またはサードパーティの監視ソリューションなどのツールを使用して、クエリパフォーマンスを詳細に分析します。

まず、コマンドラインインターフェイスを使用して MySQL サーバーに再接続します。

mysql -u root -p

データベース testdb とその中に mytable というテーブルがあると仮定します。これらがない場合は、次の SQL コマンドで作成できます。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
CREATE TABLE IF NOT EXISTS mytable (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    value INT
);

INSERT INTO mytable (name, value) VALUES
('Alice', 10),
('Bob', 20),
('Charlie', 30),
('David', 40),
('Eve', 50);

では、単純なクエリを実行し、その実行時間を観察しましょう。SELECT 文を使用して mytable のすべての行を取得します。

実行時間を測定するために、BENCHMARK() 関数を使用します。この関数は、指定された式を指定された回数だけ実行し、実行にかかった時間を返します。

SELECT BENCHMARK(100000, (SELECT * FROM mytable));

このコマンドは、SELECT * FROM mytable クエリを 100,000 回実行し、かかった時間を返します。出力は次のようになります。

+---------------------------------------------+
| BENCHMARK(100000, (SELECT * FROM mytable)) |
+---------------------------------------------+
|                                      0.1234 |
+---------------------------------------------+
1 row in set (0.12 sec)

表示される時間（例：0.1234 秒）は、クエリを 100,000 回実行するのにかかった総時間を表します。これは、クエリパフォーマンスを把握する非常に基本的な方法です。

重要な注意事項：

• ウォームアップ： サーバーを再起動した後にこのクエリを最初に実行するとき、データがバッファプールに入っていない可能性があります。データがキャッシュされるため、その後の実行はおそらく高速になります。実行時間を記録する前に、バッファプールがウォームアップするように、クエリを数回実行してください。
• ワークロード： これは小さなテーブルに対する非常に単純なクエリです。バッファプールのサイズの影響は、より大きなテーブルとより複雑なクエリでより顕著になります。
• その他の要因： ディスク I/O、CPU 負荷、ネットワーク遅延など、多くの要因がクエリパフォーマンスに影響を与える可能性があります。

この単純なベンチマークは包括的なパフォーマンス分析を提供しませんが、innodb_buffer_pool_size をチューニングした後のクエリパフォーマンスを監視する基本的な方法を理解するのに役立ちます。実際のシナリオでは、より高度なツールと手法を使用してクエリパフォーマンスを詳細に分析します。

設定変更を保存する

この最後のステップでは、innodb_buffer_pool_size に対して行った設定変更が保存され、サーバーの再起動後も引き続き有効になるようにします。すでに my.cnf ファイルを変更していますが、変更が正しく保存され、サーバーが更新された設定を使用していることを再度確認するのが良い習慣です。

念のため、/etc/mysql/my.cnf ファイル（またはシステム上の適切な場所）を変更して、[mysqld] セクション内に次の行を追加しました。

innodb_buffer_pool_size=256M

変更が保存されていることを確認するには、nano を使用して my.cnf ファイルを再度開くことができます。

sudo nano /etc/mysql/my.cnf

[mysqld] セクション内で innodb_buffer_pool_size パラメータが 256M に設定されていることを確認します。設定されていない場合は、追加してファイルを保存します。

次に、MySQL サーバーが最新の設定を使用していることを確認するために、もう一度再起動します。前と同様に、LabEx VM の Docker 環境にいるため、mysqladmin を使用してサーバーを停止し、mysqld_safe を使用して起動します。

まず、MySQL サーバーを停止します。

mysqladmin -u root -p shutdown

root パスワードの入力を求められます。パスワードを入力して続行します。

次に、MySQL サーバーを起動します。

sudo mysqld_safe &

コマンドの最後の & は、サーバーをバックグラウンドで実行します。

最後に、コマンドラインインターフェイスを使用して MySQL サーバーに再接続します。

mysql -u root -p

そして、innodb_buffer_pool_size が引き続き 256M に設定されていることを確認します。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

次の出力が表示されるはずです。

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+--------------------------+-----------+
1 row in set (0.00 sec)

値が依然として 268435456（これは 256MB をバイトに換算した値）であれば、設定変更が正常に保存され、適用されています。

おめでとうございます！この実験を正常に完了しました。現在の MySQL 設定を表示し、InnoDB バッファプールのサイズを調整し、クエリパフォーマンスを監視し、設定変更を保存する方法を学びました。これらは、MySQL 管理者や開発者にとって不可欠なスキルです。

まとめ

この実験では、SHOW VARIABLES コマンドを使用して MySQL サーバーの現在の設定を表示する方法を学びました。このコマンドは、データベースの現在の状態を理解し、最適化の潜在的な領域を特定するために重要です。

具体的には、MySQL コマンドラインインターフェイスにアクセスし、SHOW VARIABLES コマンドを実行してすべての変数を表示し、LIKE 句を使用して結果をフィルタリングして、innodb_buffer_pool_size のような特定の変数や、max_connections に関連するものなど、特定のパターンに一致する変数を見つける方法を調査しました。