mysqlチューニングの概要

1 はじめに

CPU の周波数やメモリのサイズについては触れません (これはインデックスと同じくらい重要ですが、この記事の内容ではありません) )、およびハードディスクのシーク時間。 mysql のチューニングを考えると、少なくとも Explain 実行プラン、遅い SQL ログ、古いプロファイル コマンド、新しい Performance_schema パフォーマンス ビュー、現在のトランザクションの関連テーブル、および information_schema 内のメモリ使用量情報、および show エンジンの innodb ステータスの診断情報について知っておく必要があります。 metrix の一部の tps、qps、iops インジケーターとして。 (関連する推奨事項: 「MySQL チュートリアル 」)

上記はチューニング用に準備されたいくつかのツールであり、データベースは高可用性を実現するために大小さまざまな機能を提供します。大きなものは次のとおりです。 、グループ レプリケーション、パーティション、ファイル リンク: つまり、ログ ログとデータ ファイルをそれぞれ異なるハード ディスクに配置できます。小規模なものには、列の計算、列のハッシュの計算、インデックスのマージ、インデックスのプッシュダウン、MRR、BKA、ルーズ インデックスおよびその他のアルゴリズム、フィル ファクターなどが含まれます。

もちろん、ビュー インデックスや分散パーティション ビューはなく、結合はネストされたもののみをサポートします。これは mysql の欠点です。SQL サーバーの結合アルゴリズムは、loop while hash の 3 つのタイプをサポートし、速度が大幅に向上します。の参加です。 MySQL には、パフォーマンスを向上させる多くの関数が付属していません。静的テーブルなど、他の関数は経験に基づいています。サブクエリでは関数を使用しないでください。サブクエリを結合クエリに変えるようにしてください。文字列以外のカラムと BLOB カラムは常に他の数値よりも優れています。または、時間列が遅いです。結合 |order by|group では、ハードディスク上に一時テーブルを生成することを許可してはなりません。もちろん、これはメモリ、狭いテーブルと広いテーブルの設計などに関係します。もちろん、最終的には、あなたのビジネスの種類によって異なります。

最適化を開始するには 2 つの方法があります。1 つは実行時、つまり実行中のサーバー上で最適化する方法で、もう 1 つは開発プロセス中に行う方法です。どちらを使用する場合でも、performance_schema が必要になります。

2 Performance_schema の説明

パフォーマンス ビューはすべてのデータベースにあり、SQL サーバーは dm_* で始まる一連のメモリ テーブルです。そして、mysql は パフォーマンス_schema ライブラリ内のさまざまなテーブルです。最初に入り口のテーブルを見てみましょう:

SELECT * FROM setup_timers; -- 计时定义表
select * from setup_actors; -- 那些用户需要收集信息
select * from Setup_objects; -- 那些对象需要收集信息，比如mysql表，
select * from setup_consumers; -- 那些仪器的分类需要收集
select * from setup_instruments; -- 收集仪器，每一个功能点都会有仪器的事件，开始和结束，然后开启那个仪器，就会收集那个仪器的数据

最初に、次の方法を見てみましょう。 Performance_schema スイッチをオンにします:

show variables like 'performance_schema' -- 这是一个read only变量

OFF の場合は、構成ファイルでオンにする必要があります。

以下では、これらのエントリ テーブルを 1 つずつ紹介します。

1、setup_actors テーブル

はすべてのユーザーが収集できます。

2，Setup_objects

テーブルやトリガーなど、これらのオブジェクトは収集できます。 。 2 つの列コントロールをオフにする場合、有効フィールドと時間フィールドは [いいえ] に設定されます。これはこれらのテーブルの場合です。

3 setup_consumers

イベントの分類、ステージはステップ、ステートメントはサーバー 実行手順や結果はプロファイルと同じですが、プロファイル方式は後で削除されるため推奨しません。トランザクションはトランザクションなどのイベントコレクションです。

4 setup_instruments

これは、次のようなメインのイベント監視ツールです:

5 最後のステップは setup_timers です。これは、次のように、これらの機器カテゴリの時間タイプを定義するために、performance_timers とともに使用されます:

CYCLE: CPU クロック, TIMER_FREQUENCYは何秒あるか、TIMER_RESOLUTIONは毎回どのくらい増えるか、最後に取得頻度です。

Three は、performance_schema を使用して、priofile データを取得します。

関連するインストゥルメントを開きます:

上記のインストゥルメント分類表を見てみましょう setup_consumers の情報と stage に関する行がすべて NO である場合は、YES に変更する必要があります。同時に、ステートメント監視テーブルの情報も取得する必要があるため、ステートメントをオンにするには:

UPDATE setup_consumers SET ENABLED = 'YES'
       WHERE NAME LIKE '%stage%';
UPDATE setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%statements%';

次に、ステージ楽器をオンにします

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
       WHERE NAME LIKE '%stage/%';   -- 开启所有执行步骤的监控
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

実行は sql

 select * from quartz.TestOne

このステートメントのクエリ ID をクエリします:

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%quartz%';

那么id就是509

然后执行性能监控表：

SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration
       FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=509

 

内容和老版本的profile结果一样。

主要看下stage/sql/Sending data这一行，这一行是主要io相关的事件，一般情况下，sql慢了，而这一行数值比较大，那肯定硬盘读数据慢了或者有锁冲突。

那么就是用error log，有死锁，mysql会将死锁信息打入error日志，show engine innodb status只是全局的一些信息，如果要想看详细的再去监控对应的instrument。

而且目前mysql8多支持NOWAIT和skiplocked两个语句，用法还是select.. from 表明 for update/for nowait等，非常灵活的解决了死锁的处理方式，当然你也可以让其事务隔离级别为脏读级别，但是并不能解决更多的业务类型，设置死锁超时也是一个可行的办法。

以上がmysqlチューニングの概要の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。