15の便利なMySQL / MariaDBパフォーマンスチューニングと最適化のヒント


MySQLは、強力なオープンソースのリレーショナルデータベース管理システム、つまりRDBMSです。 1995年にリリースされました(20歳)。これは、データベース内のコンテンツを管理するためにおそらく最も一般的な選択肢である構造化照会言語を使用します。 MySQLの最新バージョンは5.6.25で、2015年5月29日にリリースされました。

MySQLの興味深い事実は、名前がMichael Widenius(MySQLの作成者)の娘Myに由来しているという事実です。 MySQLには興味深い事実がたくさんありますが、この記事は、MySQLサーバーの管理に役立ついくつかの便利なプラクティスを紹介することを目的としています。

2009年4月、MySQLプロジェクトはOracleに買収されました。その結果、MariaDBと呼ばれるMySQLコミュニティフォークが作成されました。フォークを作成した主な理由は、General PublicLicenseの下でプロジェクトを無料に保つことでした。

今日、MySQLとMariaDBは、WordPress、Joomla、MagentoなどのWebアプリケーションで使用される最も頻繁に使用されるRDBMSの1つです。

この記事では、MySQL/MariaDBのパフォーマンスを微調整するための基本的でありながら役立つヒントをいくつか紹介します。この記事は、MySQLまたはMariaDBがすでにインストールされていることを前提としていることに注意してください。それでもシステムにそれらをインストールする方法がわからない場合は、こちらの広範なガイドに従うことができます。

  1. Installing LAMP on RHEL/CentOS 7
  2. Installing LAMP on Fedora 22
  3. Setting Up LAMP on Ubuntu 15.04
  4. Installing MariaDB on Debian 8
  5. Install MariaDB on Gentoo Linux
  6. Install MariaDB on Arch Linux

重要:始める前に–この提案を盲目的に受け入れないでください。各MySQLセットアップは一意であり、変更を加える前に追加の検討が必要です。

あなたが知る必要があること:

  1. MySQL/MariaDB configuration file is located in /etc/my.cnf. Every time you modify this file you will need to restart the MySQL service so the new changes can take effect.
  2. For writing this article MySQL version 5.6 has been used as template.

1.テーブルごとのInnoDBファイルを有効にします

まず、InnoDBがストレージエンジンであることを説明することが重要です。 MySQLとMariaDBは、デフォルトのストレージエンジンとしてInnoDBを使用します。以前は、MySQLはデータベーステーブルとインデックスをシステムテーブルスペースに保持するために使用されていました。このアプローチは、唯一の目的がデータベース処理であり、そのストレージディスクが他の目的に使用されていないサーバーを対象としています。

InnoDBはより柔軟なアプローチを提供し、各データベース情報は .ibd データファイルに保持されます。各.ibdファイルは、独自のテーブルスペースを表します。これにより、「TRUNCATE」などのデータベース操作をより高速に完了でき、データベーステーブルを削除または切り捨てるときに未使用の領域を再利用することもできます。

この構成のもう1つの利点は、データベーステーブルの一部を別のストレージデバイスに保持できることです。これにより、ディスクのI/O負荷を大幅に改善できます。

innodb_file_per_tableは、MySQL5.6以降ではデフォルトで有効になっています。 /etc/my.cnfファイルで確認できます。ディレクティブは次のようになります。

innodb_file_per_table=1

2.MySQLデータベースデータを別のパーティションに保存する

注:このセットアップはMySQLでのみ機能し、MariaDBでは機能しません。

特に同じハードドライブ上にある場合、OSの読み取り/書き込みによってMySQLサーバーのパフォーマンスが低下することがあります。代わりに、MySQLサービス用に別のハードドライブ(できればSSD)を使用することをお勧めします。

これを完了するには、新しいドライブをコンピューター/サーバーに接続する必要があります。この記事では、ドライブが/ dev/sdbの下にあると想定します。

次のステップは、新しいドライブを準備することです。

# fdisk /dev/sdb

次に、「n」を押して新しいパーティションを作成します。次に「p」を押して、新しいパーティションをプライマリにします。その後、パーティション番号を1〜4に設定します。その後、パーティションサイズを選択します。ここでEnterキーを押します。次のステップでは、パーティションのサイズを構成する必要があります。

ディスク全体を使用する場合は、もう一度Enterキーを押します。それ以外の場合は、新しいパーティションのサイズを手動で設定できます。準備ができたら、「w」を押して変更を書き込みます。次に、新しいパーティションのファイルシステムを作成する必要があります。これは次の方法で簡単に実行できます。

# mkfs.ext4 /dev/sdb1

次に、新しいパーティションをフォルダにマウントします。フォルダに「ssd」という名前を付けて、ルートディレクトリに作成しました。

# mkdir /ssd/

作成したばかりの新しいパーティションを新しいフォルダにマウントする準備ができました。

# mount /dev/sdb1  /ssd/

/ etc/fstabファイルに次の行を追加することにより、起動時にマウントを実行できます。

/dev/sdb1 /ssd ext3 defaults 0 0

これで、MySQLを新しいディスクに移動する準備が整いました。まず、次のコマンドでMySQLサービスを停止します。

# service mysqld stop

データベースへの書き込みを防ぐために、Apache/nginxも停止することをお勧めします。

# service httpd stop
# service nginx stop

次に、MySQLディレクトリ全体を新しいドライブにコピーします。

# cp /var/lib/mysql /ssd/ -Rp

MySQLデータベースのサイトによっては、これには時間がかかる場合があります。このプロセスが完了したら、MySQLディレクトリの名前を変更します。

# mv /var/lib/mysql /var/lib/mysql-backup

次に、シンボリックリンクを作成します。

# ln -s /ssd/mysql /var/lib/mysql

これで、MySQLとWebサービスを開始する準備が整いました。

# service mysqld start
# service httpd start
# service nginx start

この時点で、MySQLデータベースは新しいドライブからアクセスされます。