MySQL の設定とチューニング

はじめに

この実験 (Lab) では、MySQL サーバーの設定とパフォーマンスチューニングの基本を学びます。まず現在のサーバー設定を確認し、次にパフォーマンスに重要なパラメータである innodb_buffer_pool_size を変更して、変更がどのように適用されるかを確認します。

この実験では、MySQL の設定ファイルを編集し、変更を適用するためにサーバーを再起動し、新しい設定が有効になっていることを確認する手順を説明します。最後に、MySQL の組み込みプロファイラーを使用してクエリパフォーマンスを分析する基本的な方法を学びます。これにより、さまざまなワークロードに合わせてデータベースを最適化するための基礎が得られます。

現在の MySQL 設定の表示

変更を加える前に、MySQL サーバーの現在の構成を理解することが重要です。このステップでは、MySQL に接続し、重要なパフォーマンス変数である innodb_buffer_pool_size の値を確認します。

まず、デスクトップからターミナルを開きます。

root ユーザーとして MySQL サーバーに接続します。この実験 (Lab) 環境では、パスワードなしで接続するために sudo を使用できます。

sudo mysql -u root

接続すると、MySQL プロンプト (mysql>) が表示されます。

システム変数は MySQL サーバーの動作を制御します。innodb_buffer_pool_size 変数は、InnoDB テーブルのデータとインデックスをキャッシュするために割り当てられるメモリ量を決定します。適切にサイズ設定されたバッファプールは、良好なパフォーマンスに不可欠です。

SHOW VARIABLES コマンドと LIKE 句を使用して、この変数の現在の値を見つけます。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

以下のような出力が表示され、バイト単位のデフォルト値が示されます。次のステップで変更するため、この値をメモしておいてください。

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

現在の構成を確認したので、MySQL シェルを終了できます。

exit

MySQL 設定ファイルの変更

MySQL の設定は、現在のセッションで一時的に変更することも、構成ファイルで永続的に変更することもできます。サーバーの再起動後も変更を有効にするには、構成ファイルを編集する必要があります。このステップでは、/etc/mysql/my.cnf を編集して innodb_buffer_pool_size を増やします。

sudo 権限を使用して、nano テキストエディタで MySQL 構成ファイルを開きます。

sudo nano /etc/mysql/my.cnf

下にスクロールして [mysqld] セクションを見つけます。このセクションには、MySQL サーバーデーモンに固有の設定が含まれています。バッファプールのサイズを 256 メガバイトに設定するために、[mysqld] の見出しの下に次の行を追加します。

innodb_buffer_pool_size=256M

[mysqld] セクションは、以下のようになります。

[mysqld]
innodb_strict_mode=OFF
init_connect='SET NAMES utf8'
character-set-server = utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size=256M

これで、ファイルを保存して nano を終了します。Ctrl+X を押し、変更を確認するために Y を入力し、ファイルに書き込むために Enter を押します。

構成ファイルへの変更は、MySQL サーバーが再起動された後にのみ有効になります。service コマンドを使用して再起動します。

sudo service mysql restart

これで構成が永続的に更新されました。次のステップでは、変更が有効になっていることを確認します。

設定変更の確認

構成ファイルを変更し、サーバーを再起動した後、新しい設定が正しく適用されたことを確認する必要があります。このステップでは、MySQL に再接続し、innodb_buffer_pool_size 変数を再度確認します。

MySQL サーバーに接続します。

sudo mysql -u root

次に、SHOW VARIABLES コマンドを再度実行して、新しい値を確認します。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

出力には、バイト単位の新しい値が表示されるはずです。MySQL は自動的に 256M (256 メガバイト) を 268435456 バイトに変換します。

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

この値をステップ 1 でメモした値と比較することで、構成変更が成功し、現在有効になっていることを確認できます。

これで、MySQL シェルを終了できます。

exit

クエリパフォーマンスの分析

クエリパフォーマンスを向上させるためにサーバー変数のチューニングが行われます。詳細な分析は複雑ですが、MySQL に組み込まれているクエリプロファイラを使用して、クエリ実行時間の基本的な測定を行うことができます。このステップでは、テストテーブルを作成し、データを挿入し、簡単なクエリを分析します。

まず、MySQL サーバーに接続します。

sudo mysql -u root

testdb という名前の新しいデータベースを作成し、それに切り替えます。

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

次に、サンプルデータを格納するための employees という名前のテーブルを作成します。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INT
);

employees テーブルにいくつかのレコードを挿入します。

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 60000),
('Bob', 'Engineering', 80000),
('Charlie', 'Sales', 65000),
('David', 'Marketing', 55000);

次に、セッションでクエリプロファイラを有効にします。これにより、後続のクエリのパフォーマンスデータが記録されます。

SET profiling = 1;

分析したいクエリを実行します。例えば、「Sales」部門のすべての従業員を検索してみましょう。

SELECT * FROM employees WHERE department = 'Sales';

パフォーマンス結果を表示するには、SHOW PROFILES コマンドを使用します。これにより、プロファイリングを有効にしてから実行したクエリとその期間が一覧表示されます。

SHOW PROFILES;

出力は以下のようになり、各クエリの期間が秒単位で表示されます。

+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00038500 | SELECT * FROM employees WHERE department = 'Sales'    |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

この Duration 列は、クエリパフォーマンスのベースラインを提供します。実際のシナリオでは、このツールを使用して、はるかに大きなデータセットで構成変更の前後のクエリ速度を比較します。

プロファイラを使用してクエリを正常に分析しました。これで、MySQL シェルを終了できます。

exit;

まとめ

この実験では、MySQL サーバーの設定とチューニングの基本的なプロセスを学びました。innodb_buffer_pool_size のようなシステム変数を検査して、現在のサーバー構成を表示する方法を実践しました。

MySQL 構成ファイル (my.cnf) を変更して永続的な変更を行い、サーバーを再起動して適用し、新しい設定が有効であることを確認する実地経験を積みました。最後に、MySQL に組み込まれているクエリプロファイラを使用してクエリ期間を測定する、基本的なパフォーマンス分析手法を紹介しました。

これらの基本的なスキルは、健全でパフォーマンスの高い MySQL データベースを維持する責任を持つすべての開発者または管理者にとって不可欠です。