MySQL のデバッグと最適化に関する 101 のヒントを共有する

MySQL は強力なオープンソース データベースです。データベース駆動型アプリケーションの数が増えるにつれて、MySQL は限界まで押し上げられてきました。 MySQL インストールのチューニングと最適化に関する 101 のヒントを紹介します。いくつかのヒントはインストールに固有のものですが、アイデアは一般的なものです。 MySQL のチューニングと最適化のテクニックをさらに習得できるように、いくつかのカテゴリに分けました。

MySQL サーバーのハードウェアと OS のチューニング:

1. InnoDB ファイル全体をメモリにロードするのに十分な物理メモリがあること。メモリ内のファイルにアクセスする方が、ディスクからアクセスするよりもはるかに高速です。
2. スワップ パーティションの使用は絶対に避けてください。スワップはハード ディスクから読み取られるため、速度が遅くなります。
3. バッテリー駆動の RAM を使用します (注: RAM はランダム アクセス メモリの略です)。
4. 高度な RAID (注: Redundant Arrays of Inexpensive Disks) を使用します (RAID10 以上が望ましい)。
5. RAID5 は避けてください (注: ストレージ パフォーマンス、データ セキュリティ、ストレージ コストを考慮したストレージ ソリューション) – データベースの整合性検証を確実に行うにはコストがかかります。
6. オペレーティング システムとデータのパーティションを論理的にだけでなく物理的にも分離します。オペレーティング システムの読み取りおよび書き込み操作は、データベースのパフォーマンスに影響します。
7. MySQL の一時領域とレプリケーション ログをデータとは別のパーティションに配置する – これにより、データベース バックエンドがディスクを読み書きするときにデータベースのパフォーマンスに影響します。
8. ディスク容量が増えると速度も上がります。
9. より優れた高速ディスク。
10. SATA (注: SATA、つまりシリアル ポート ハード ディスク) の代わりに SAS (注: Serial Attached SCSI) を使用します。
11. 特に RAID 構成では、小さいハード ドライブの方が大きいハード ドライブよりも高速です。
12. バッテリバックアップ式キャッシュ RAID コントローラを使用します。
13. ソフトウェア ディスク アレイの使用は避けてください。
14. データ パーティションには、ソリッド ステート IO カード (ディスク ドライブではない) の使用を検討してください。これらのカードは、ほぼすべてのデータ量に対して 2GB/秒の書き込み速度をサポートできます。
15. Linux で swappiness を 0 に設定する – データベース サーバーにファイルをキャッシュする理由はありません。これはサーバーまたはデスクトップにとって利点です。
16. 可能であれば、noatime と nodirtime を使用してファイル システムをマウントします。データベース ファイルにアクセスするときに変更時刻を更新する必要はありません。
17. XFS ファイルシステムを使用します。これは、多くのジャーナリング オプションを備えた ext3 よりも高速で小さいファイルシステムです。ext3 では、MySQL でダブル バッファリングの問題があることがわかっています。
18. 最高のパフォーマンス基準を実現するために、XFS ファイルシステムのジャーナリングとバッファリング変数を調整します。
19. Linux では、NOOP または DEADLINE IO スケジューラを使用します - NOOP および DEADLINE スケジューラと比較すると、CFQ および ANTICIPATORY スケジューラは非常に低速です。
20. 64 ビット オペレーティング システムを使用する – MySQL の場合、メモリのサポートと使用量が増加します。
21. サーバー上の未使用のパッケージとデーモンを削除して、リソースの使用量を削減します。
22. MySQL を使用するホストと MySQL ホストを 1 つのホスト ファイルに配置します。DNS ルックアップは不要です。
23. MySQL プロセスを強制終了しないでください。データベースと実行中のバックアップ プロセスが破損します。
24. サーバーを MySQL 専用にする – バックグラウンド プロセスやその他のサービスにより、データベースが CPU を使用する時間を短縮できます。

MySQL 設定:

25. 書き込み時には、二重バッファリングを回避するために innodb_flush_method=O_DIRECT を使用します。
26. O_DIRECT と EXT3 ファイルシステムの使用は避けてください。すべての書き込みがシリアル化されます。
27. InnoDB ファイル全体をメモリにロードするのに十分な innodb_buffer_pool_size を割り当てます (ディスクからの読み取りが少なくなります)。
28. innodb_log_file_size パラメータをあまり大きく設定しないでください。これにより、実行速度が向上し、ディスク領域が増えます。ログを多く削除すると、クラッシュ後にデータベースを回復するのにかかる時間が短縮されるため、通常は良いことです。
29. innodb_thread_concurrency と thread_concurrency パラメータを混在させないでください。これら 2 つの値には互換性がありません。
30. max_connections パラメータに非常に小さい数値を割り当てます。接続数が多すぎると RAM が消費され、MySQL サーバーがロックされる可能性があります。
31. 接続を開くときに速度が低下するのを防ぐため、thread_cache を 16 程度の比較的高い数値に保ちます。
32. skip-name-resolve パラメータを使用して DNS ルックアップを削除します。
33. クエリが反復的で、データが頻繁に変更されない場合は、クエリ キャッシュを使用できます。しかし、データが頻繁に変更される場合、クエリ キャッシュを使用するとイライラすることになります。
34. ディスクへの書き込みを防ぐためにtemp_table_sizeの値を増やす
35. ディスクへの書き込みを防ぐためにmax_heap_table_sizeを増やす
36. sort_buffer_size の値を高く設定しすぎないでください。そうしないと、すぐにメモリ不足に陥ります。
37. key_read_requests と key_reads の値に基づいて、key_buffer のサイズを決定します。通常、key_read_requests は key_reads の値よりも大きくする必要があります。そうでないと、key_buffer を効率的に使用できません。
38. innodb_flush_log_at_trx_commit を 0 に設定するとパフォーマンスが向上しますが、デフォルト値 (1) を維持する場合は、データの整合性を確保し、レプリケーションが遅れないようにする必要があります。
39. 構成をテストし、通常の運用に影響を与えずに頻繁に再起動するためのテスト環境が必要です。

MySQL モードの最適化:

40. データベースを整理しておきます。
41. 古いデータのアーカイブ - 返された冗長な行や検索クエリを削除します。
42. データをインデックスします。
43. インデックス、比較、クエリを過度に使用しないでください。
44. TEXT および BLOB データ型を圧縮して、スペースを節約し、ディスクの読み取りを減らします。
45. UTF 8 と UTF16 はどちらも latin1 よりも効率が悪くなります。
46. トリガーは控えめに使用してください。
47. 冗長なデータを最小限に抑える – 不要なデータを繰り返さないでください。
48. 行を拡張する代わりにリンクされたテーブルを使用します。
49. データ型に注意し、実際のデータでは可能な限り小さいデータ型を使用します。
50. クエリで他のデータが頻繁に使用され、BLOB/TEXT データが使用されない場合は、BLOB/TEXT データを他のデータから分離します。
51. テーブルを頻繁にチェックして最適化します。
52. InnoDB テーブルの最適化を頻繁に書き換えます。
53. 列を追加するときにインデックスを削除して再度追加する方が速い場合があります。
54. さまざまなニーズに合わせてさまざまなストレージ エンジンを使用します。
55. ログ テーブルまたは監査テーブルにはアーカイブ ストレージ エンジンを使用します。これらへの書き込みはより効率的です。
56. セッション データは MySQL ではなくキャッシュ (memcache) に保存されます。キャッシュにより値の自動入力が可能になり、MySQL への読み取りや書き込みが困難な時空間データの作成を防ぐことができます。
57. 可変長文字列を保存するときは、CHAR ではなく VARCHAR を使用します。CHAR は固定長ですが、VARCHAR は固定長ではないため、スペースを節約できます (UTF8 はこれの影響を受けません)。
58. 段階的なパターン変更を行う – 小さな変更でも大きな影響を与える可能性があります。
59. 開発環境ですべてのモードをテストし、本番環境の変更を反映します。
60. 設定ファイル内のランダムな値を変更しないでください。悲惨な結果を招く可能性があります。
61. MySQL の設定では、少ないほうが良い場合もあります。
62. 疑問がある場合は、汎用の MySQL 構成ファイルを使用します。

クエリの最適化:

63. スロー クエリ ログを使用して、スロー クエリを見つけます。
64. 実行プランを使用して、クエリが適切に実行されているかどうかを確認します。
65. クエリが最適に実行されているかどうかを常にテストしてください。パフォーマンスは時間の経過とともに変化する可能性があります。
66. テーブル全体に count(*) を使用しないでください。テーブル全体がロックされる可能性があります。
67. 後続の同様のクエリがクエリ キャッシュを使用できるように、クエリに一貫性を持たせます。
68. 適切な場合は、DISTINCT の代わりに GROUP BY を使用します。
69. WHERE、GROUP BY、ORDER BY 句でインデックス付き列を使用します。
70. インデックスはシンプルに保ち、複数のインデックスに同じ列を含めないでください。
71. MySQL は間違ったインデックスを使用する場合があります。この場合は、USE INDEX を使用します。
72. SQL_MODE=STRICT の使用時に問題がないか確認します。
73. レコード数が 5 未満のインデックス フィールドの場合は、UNION で OR ではなく LIMIT を使用します。
74. 更新前の SELECT を回避するには、UPDATE の代わりに INSERT ON DUPLICATE KEY または INSERT IGNORE を使用します。
75. MAX を使用せず、インデックス付きフィールドと ORDER BY 句を使用します。
76. ORDER BY RAND() の使用は避けてください。
77. LIMIT M,N は場合によってはクエリの速度を低下させる可能性があるため、慎重に使用してください。
78. WHERE 句ではサブクエリの代わりに UNION を使用します。
79. UPDATES の場合、排他ロックを防ぐために SHARE MODE を使用します。
80. MySQL を再起動する前に、データがメモリ内にあり、クエリが高速であることを確認するために、データベースをウォームアップすることを忘れないでください。
81. DROP TABLE、CREATE TABLE DELETE FROM を使用して、テーブルからすべてのデータを削除します。
82. データを最小限に抑える 必要なデータを照会するときに、* を使用すると多くの時間がかかります。
83. オーバーヘッドを削減するために、複数の接続ではなく永続的な接続を検討してください。
84. サーバーの負荷も考慮してクエリをベンチマークします。単純なクエリが他のクエリに影響を与えることもあります。
85. サーバーの負荷が増加すると、SHOW PROCESSLIST を使用して、遅いクエリや問題のあるクエリを表示します。
86. 開発環境で生成されたミラーリングされたデータに対して、疑わしいクエリをすべてテストします。

MySQL バックアップ プロセス:

87. セカンダリレプリケーションサーバーからバックアップします。
88. データの依存関係と外部キー制約の不整合を回避するために、バックアップ中にレプリケーションを停止します。
89. MySQL を完全に停止し、データベース ファイルをバックアップします。
90. バックアップに MySQL ダンプを使用する場合は、レプリケーションが中断されないようにバイナリ ログ ファイルもバックアップします。
91. LVM スナップショットを信頼しないでください。これは、将来的に問題を引き起こすデータの不整合を引き起こす可能性があります。
92. データが他のテーブルから分離されている場合は、テーブルごとにデータをエクスポートして、単一テーブルのリカバリを容易にします。
93. mysqldump を使用する場合は –opt を使用してください。
94. バックアップする前にテーブルをチェックして最適化します。
95. インポートを高速化するには、インポート中に外部キー制約を一時的に無効にします。
96. インポートを高速化するために、インポート中の一意性チェックを一時的に無効にします。
97. データ サイズの増加をより適切に監視するために、各バックアップ後にデータベース、テーブル、インデックスのサイズを計算します。
98. 自動スケジュール スクリプトを使用して、レプリケーション インスタンスのエラーと遅延を監視します。
99. 定期的にバックアップを実行します。
100. バックアップを定期的にテストします。
最終 101: MySQL モニタリングの実装: Monitis が世界初の無料オンデマンド MySQL モニタリングを発表。