MySQLパフォーマンスチューニングの10の方法

詳細
MYSQLは、WEBバックエンドデータベースが最も流行しているはずです.WEB開発言語は最近急速に発展しており、PHP、Ruby、Python、Javaにはそれぞれ特徴があり、NOSQLは最近ますます多く取り上げられているが、ほとんどのアーキテクチャ師がMYSQLをデータストレージに選んだと信じている.
MYSQLはこのように便利で安定しているので、WEBプログラムを開発するときにあまり考えられません.最適化を考えてもプログラムレベルです.たとえば、リソースを消費しすぎるSQL文を書かないでください.しかし、それ以外にも、システム全体に最適化できる点がたくさんあります.
1.適切なストレージエンジンの選択:InnoDB
データテーブルが読み取り専用または全文検索に使用されない限り(全文検索といえばMYSQLは使用されないと信じています)、InnoDBをデフォルトで選択する必要があります.
自分でテストしたとき、MyISAMはInnoDBよりも速いことに気づくかもしれません.これは、MyISAMはインデックスのみをキャッシュし、InnoDBはデータとインデックスをキャッシュし、MyISAMはトランザクションをサポートしていないからです.でもinnodbを使うとflush_log_at_trx_commit=2は、近い読み取り性能(100倍の差)を得ることができる.
1.1既存のMyISAMデータベースをInnoDBに変換する方法:

mysql -u [USER_NAME] -p -e "SHOW TABLES IN [DATABASE_NAME];" | tail -n +2 | xargs -I '{}' echo "ALTER TABLE {} ENGINE=InnoDB;" > alter_table.sql
perl -p -i -e 's/(search_[a-z_]+ ENGINE=)InnoDB//1MyISAM/g' alter_table.sql
mysql -u [USER_NAME] -p [DATABASE_NAME] < alter_table.sql

 
1.2テーブルごとにInnoDB FILEを作成する:

innodb_file_per_table=1

これによりibdata 1ファイルが大きすぎず、制御を失うことが保証されます.特にmysqlcheck-o–all-databasesを実行する場合.
2.メモリからデータを読み取ることを保証し、データをメモリに保存する
2.1十分大きいinnodb_buffer_pool_size
innodb_にデータを完全に保存することを推奨します.buffer_pool_size、すなわちinnodb_をストレージ量で計画するbuffer_pool_sizeの容量.これにより、メモリからデータを完全に読み取り、ディスク操作を最小限に抑えることができます.
2.1.1 innodb_の決定方法buffer_pool_sizeは十分大きく、データはハードディスクではなくメモリから読み込まれますか?
方法1

mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 129037 |
| Innodb_buffer_pool_pages_dirty   | 362    |
| Innodb_buffer_pool_pages_flushed | 9998   |
| Innodb_buffer_pool_pages_free    | 0      |  !!!!!!!!
| Innodb_buffer_pool_pages_misc    | 2035   |
| Innodb_buffer_pool_pages_total   | 131072 |
+----------------------------------+--------+
6 rows in set (0.00 sec)

Innodb_の検出buffer_pool_pages_freeが0の場合、buffer poolがすでに使用されていることを示し、innodb_を大きくする必要がある.buffer_pool_size
InnoDBの他のパラメータ:

innodb_additional_mem_pool_size = 1/200 of buffer_pool
innodb_max_dirty_pages_pct 80%

方法2
またはiostat-d-x-k 1コマンドを使用して、ハードディスク(HDD)の動作を確認します.
2.1.2計画のためにサーバに十分なメモリがあるか
echo 1>/proc/sys/vm/drop_の実行Cachesはオペレーティングシステムのファイルキャッシュを消去し、本当のメモリ使用量を見ることができます.
2.2データ予熱
デフォルトでは、innodb_にキャッシュされるのは、あるデータが1回だけです.buffer_pool.そのため、データベースが起動したばかりで、データの予熱を行い、ディスク上のすべてのデータをメモリにキャッシュする必要があります.データ予熱は読み取り速度を向上させることができる.
InnoDBデータベースでは、以下の方法でデータの予熱を行うことができます.
1.次のスクリプトをMakeSelectQueriesToLoadとして保存します.sql

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb
;

2.実行

mysql -uroot -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql

3.データベースを再起動するたびに、またはライブラリ全体のバックアップ前に予熱が必要な場合に実行します.

mysql -uroot < /root/SelectQueriesToLoad.sql > /dev/null 2>&1

2.3 SWAPにデータを保存しない
専用MYSQLサーバの場合はSWAPを無効にし、共有サーバの場合はinnodb_を決定します.buffer_pool_sizeは十分大きいです.あるいは固定メモリ空間をキャッシュしmemlock命令を使用します.
3.定期的にデータベースの再構築を最適化する
mysqlcheck-o–all-databasesはibdata 1を増大させ、真の最適化はデータテーブル構造の再構築だけです.

CREATE TABLE mydb.mytablenew LIKE mydb.mytable;
INSERT INTO mydb.mytablenew SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

4.ディスク書き込みの削減
4.1十分な書き込みキャッシュinnodb_の使用log_file_size
ただし1 Gのinnodb_を使うとlog_file_size、サーバがオンの場合、リカバリに10分かかります.
推奨innodb_log_file_sizeは0.25*innodb_に設定buffer_pool_size
4.2 innodb_flush_log_at_trx_commit
このオプションは、ディスクの書き込み操作と密接に関連しています.
innodb_flush_log_at_trx_commit=1は、書き込みディスクinnodb_を変更するたびにflush_log_at_trx_commit=0/2/秒ディスクへの書き込み
アプリケーションが高いセキュリティ(金融システム)に関係していない場合や、インフラストラクチャが十分に安全である場合、トランザクションが小さい場合は、0または2でディスクの操作を低減できます.
4.3デュアルライトバッファの回避

innodb_flush_method=O_DIRECT

5.ディスクの読み書き速度の向上
RAID 0は、特にEC 2のような仮想ディスク(EBS)を使用する場合、ソフトRAID 0を使用することが重要です.
6.インデックスの活用
6.1既存のテーブル構造とインデックスの表示

SHOW CREATE TABLE db1.tb1/G

6.2必要なインデックスの追加
インデックスは、検索エンジン用のインデックスの並べ替えなど、クエリーの速度を向上させる唯一の方法です.
インデックスの追加は、スロー・クエリー・ログやクエリー・ログ、EXPLAINコマンドでクエリーを分析するなど、クエリーに基づいて決定する必要があります.

ADD UNIQUE INDEX
ADD INDEX

6.2.1たとえば、ユーザー検証テーブルの最適化:
索引の追加

ALTER TABLE users ADD UNIQUE INDEX username_ndx (username);
ALTER TABLE users ADD UNIQUE INDEX username_password_ndx (username,password);

サーバを再起動するたびにデータの予熱を行う

echo “select username,password from users;” > /var/lib/mysql/upcache.sql

起動スクリプトをmyに追加します.cnf

[mysqld]
init-file=/var/lib/mysql/upcache.sql

6.2.2自動インデックス付きフレームまたは自動分割テーブル構造を使用するフレーム
たとえば、Railsのようなフレームワークでは、インデックスが自動的に追加され、Drupalのようなフレームワークでは、テーブル構造が自動的に分割されます.あなたが開発した初期に正しい方向を示します.だから、あまり経験が豊富でない人は最初から0から構築することを求めているが、実際にはよくないやり方だ.
7.分析クエリー・ログと遅いクエリー・ログ
すべてのクエリーを記録します.これは、ORMシステムまたはクエリー文を生成するシステムで役立ちます.

log=/var/log/mysql.log

本番環境で使用しないでください.そうしないと、ディスク容量がいっぱいになります.
実行時間が1秒を超えるクエリを記録します.

long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log

8.メモリディスクを使用する急進的な方法
現在、インフラストラクチャの信頼性は非常に高く、例えばEC 2はサーバハードウェアのダウンタイムをほとんど心配しない.しかもメモリは本当に安くて、数十Gメモリのサーバーを簡単に買えて、メモリディスクを使って、定期的にディスクにバックアップすることができます.
MYSQLディレクトリを4 Gのメモリディスクに移行

mkdir -p /mnt/ramdisk
sudo mount -t tmpfs -o size=4000M tmpfs /mnt/ramdisk/
mv /var/lib/mysql /mnt/ramdisk/mysql
ln -s /tmp/ramdisk/mysql /var/lib/mysql
chown mysql:mysql mysql

9.NOSQLでMYSQLを使う
B-TREEは依然として最も効率的なインデックスの1つであり、すべてのMYSQLは依然として時代遅れではない.
HandlerSocketでMYSQLのSQL解析層をスキップすると、MYSQLは本当にNOSQLになります.
10.その他
単一クエリーは最後にLIMIT 1を追加し、全テーブルスキャンを停止します.
非「インデックス」データを分離します.たとえば、大きな文章を分離して保存し、他の自動クエリーに影響を与えません.
MYSQLに内蔵されている関数は使用しません.内蔵関数はクエリーキャッシュを確立しません.
PHPの接続速度は非常に速く、すべての接続プールを使用しないことができます.そうしないと、接続数を超える可能性があります.もちろんプールPHPプログラムを接続しなくても
接続数がいっぱいになった場合は@ignore_user_abort(TRUE);
ドメイン名ではなくIPを使用してデータベースパスを作成し、DNS解析の問題を回避