MysqlおよびMariadbデータベースを調整および最適化するための13のヒント

MySQLとMariaDBは、Joomla、WordPress、Drupal、Typo 3などのWebサイトホスティングおよびCMSシステムに関して最も広く使用されているリレーショナルデータベース管理システム（RDMS）です。この記事では、 MySQLおよびMariaDBデータベースサーバー。

MySQLデータを別々のパーティションに保存する

最適化と保証の点では、データベースデータを別のボリュームに保存することが常に最善のアイデアです。ボリュームは、SSD、NVMeなどの高速ストレージボリューム専用です。システムに障害が発生した場合でも、データベースは安全になります。パーティションボリュームは高速のストレージボリュームで構成されているため、パフォーマンスが向上します。

MySQL接続の最大数を設定する

MySQL / MariaDBは、命令 max_connectionsを使用しますこれは、サーバーで現在許可されている同時接続の数を指定します。接続が多すぎると、メモリ消費量が多くなり、CPU負荷も高くなります。小規模なWebサイトの場合、接続は100〜200に指定でき、大規模なWebサイトでは500〜800以上が必要になる場合があります。 max_connections SQLクエリを使用して動的に変更できます。この例では、値を200に設定しました。

$ mysql -u root -p

mysql> set global max_connections=200;

出力：

MySQLの低速クエリログを有効にする

実行に非常に長い時間がかかるクエリをログに記録すると、データベースの問題のトラブルシューティングが容易になります。遅いクエリログは、MySQL/MariaDB構成ファイルに次の行を追加することで有効にできます。

slow-query-log=1
slow-query-log-file= /var/lib/mysql/mysql-slow-query.log
long-query-time=1

最初の変数が遅いクエリログを有効にする場合

2番目の変数はログファイルディレクトリを定義します

3番目の変数は、MySQLクエリを完了する時間を定義します

mysql / mariadbサービスを再起動し、ログを監視します

$ systemctl restart mysql

$ systemctl restart mariadb

$ tail -f /var/lib/mysql/mysql-slow-query.log

MySQLで許可される最大パケットを設定します

データはMySQLでパケットに分割されます。 Max_allowed_packetは、送信できるパケットの最大サイズを定義します。 max_allowed_packetの設定が低すぎると、クエリが遅くなりすぎる可能性があります。パケット値を最大のパケットのサイズに設定することをお勧めします。

一時テーブル容量の設定

Tmp_table_sizeは、組み込みメモリテーブルに使用される最大スペースです。テーブルのサイズが指定された制限を超えると、ディスク上のMyISAMテーブルに変換されます。 MySQL / MariaDBでは、構成ファイルに次の変数を追加して、一時テーブルのサイズを設定できます。サーバーでこの値を1GBメモリあたり64Mに設定することをお勧めします。

[mysqld]

tmp_table_size=64M

mysqlサービスを再起動します

$ systemctl restart mysql

$ systemctl restart mariadb

メモリテーブルの最大容量を設定します。

Max_heap_table_sizeは、最大メモリテーブル容量を構成するためにMySQLで使用される変数です。ディスク書き込みを回避するために、最大メモリテーブル容量のサイズは一時テーブル容量と同じにする必要があります。サーバーでこの値をGBメモリあたり64Mに設定することをお勧めします。 MySQL構成ファイルに次の行を追加して、サービスを再起動します。

[mysqld]

max_heap_table_size=64M

変更を適用するには、データベースサーバーを再起動します。

$ systemctl restart mysql

$ systemctl restart mariadb

MySQLのDNS逆引き参照を無効にする

新しい接続が受信されると、MySQL / MariaDBはDNSルックアップを実行して、ユーザーのIPアドレスを解決します。これにより、DNS構成が無効な場合、またはDNSサーバーに問題がある場合に遅延が発生する可能性があります。 DNSルックアップを無効にするには、MySQL構成ファイルに次の行を追加し、MySQLサービスを再起動します。

[mysqld]

skip-name-resolve

サービスを再開します：

$ systemctl restart mysql

$ systemctl restart mariadb

MySQLでSwappinessを使用しないようにする

Linuxカーネルは、システムが物理メモリを使い果たしたときに、メモリの一部を「スワップ」スペースと呼ばれるディスクの特別なパーティションに移動します。この状態では、システムはメモリを解放するのではなく、ディスクに情報を書き込みます。システムメモリはディスクストレージよりも高速であるため、swappinessを無効にすることをお勧めします。次のコマンドを使用して、Swappinessを無効にできます。

$ sysctl -w vm.swappiness=0

出力：

InnoDBバッファープールサイズを増やす

MySQL / MariaDBには、メモリ内のデータをキャッシュしてインデックスを作成するためのバッファプールを備えたInnoDBエンジンがあります。バッファプールは、MySQL/MariaDBクエリを比較的高速に実行するのに役立ちます。 InnoDBバッファープールの適切なサイズを選択するには、システムメモリに関するある程度の知識が必要です。最善のアイデアは、InnoDBバッファープールサイズの値をRAMの80％に設定することです。

例。

システムメモリ=4GB
バッファプールサイズ=3.2GB

MySQL構成ファイルに次の行を追加し、サービスを再起動します

[mysqld]

Innodb_buffer_pool_size 3.2G

データベースを再起動します：

$ systemctl restart mysql

$ systemctl restart mariadb

クエリキャッシュサイズの処理

MySQL / MariaDBのクエリキャッシュディレクティブは、同じデータで繰り返し続けるすべてのクエリをキャッシュするために使用されます。小さなWebサイトの場合は、値を64MBに設定し、時間を増やすことをお勧めします。クエリキャッシュサイズの値をGBに増やすと、データベースのパフォーマンスが低下する可能性があるため、お勧めしません。 my.cnfファイルに次の行を追加します。

[mysqld]

query_cache_size=64M

アイドル状態の接続を確認する

リソースはアイドル状態の接続によって消費されるため、可能であれば、リソースを終了または更新する必要があります。これらの接続は「スリープ」状態のままであり、長期間とどまる可能性があります。次のコマンドを使用して、アイドル状態の接続を確認します。

$ mysqladmin processlist -u root -p | grep “Sleep”

クエリは、スリープ状態にあるプロセスを一覧表示します。通常、PHPでは、mysql_pconnectを使用しているときにイベントが発生する可能性があります。これにより、MySQL接続が開き、クエリが実行され、認証が削除され、接続が開いたままになります。 wait_timeoutを使用するディレクティブを使用すると、アイドル状態の接続を中断できます。 wait_timeoutのデフォルト値は28800秒で、60秒などの最小時間範囲に短縮できます。 my.cnfファイルに次の行を追加します

[mysqld]

wait_timeout=60

MySQLデータベースの最適化と修復

サーバーが予期せずシャットダウンした場合、MySQL/MariaDBのテーブルがクラッシュする可能性があります。コピープロセスの実行中にデータベースにアクセスしたり、ファイルシステムが突然クラッシュしたりするなど、データベーステーブルがクラッシュする理由は他にも考えられます。この状況では、「 mysqlcheck」と呼ばれる特別なツールがあります。」は、データベース内のすべてのテーブルをチェック、修復、および最適化します。

次のコマンドを使用して、修復および最適化アクティビティを実行します。

すべてのデータベースの場合：

$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

特定のデータベースの場合：

$ mysqlcheck -u root -p --auto-repair --check --optimize dbname

dbnameをデータベース名に置き換えます

テストツールを使用してMySQL/MariaDBのパフォーマンスを確認する

MySQL/MariaDBデータベースのパフォーマンスを定期的にチェックすることをお勧めします。これにより、パフォーマンスレポートと改善点を簡単に取得できます。 mysqltunerが最適なツールの中で利用できるツールはたくさんあります。

次のコマンドを実行してツールをダウンロードします

$ wget https://github.com/major/MySQLTuner-perl/tarball/master

ファイルを解凍する

$ tar xvzf master

プロジェクトディレクトリに移動し、次のスクリプトを実行します。

$ cd major-MySQLTuner-perl-7aa57fa

$ ./mysqltuner.pl

出力：

結論

この記事では、さまざまな手法を使用してMySQL/MariaDBを最適化する方法を学びました。読んでいただきありがとうございます。