MySQL5.7 を使用して 1 秒あたり 500,000 の高性能クエリを実現

[はじめに] この記事では、MySql Connect に関する以前の話を説明しながら、1 秒あたり 50W のクエリを達成する MySql5 7 の詳細とベンチマーク結果を説明します。 MySQL InnoDB の改善の歴史を確認してください。簡単に見つけることができます。 MySQL 5 と 6 の安定版ではこれほど読み取り専用になったことはありません

この記事では、MySql Connect に関する以前の話を説明した記事「MySql5.7 は 1 秒あたり 50W のクエリを達成」の詳細とベンチマーク結果を提供します。

MySQL/InnoDB の改善の歴史を確認してください。簡単に見つけることができます。 MySQL 5.6 安定版では、読み取り専用でこれほど高速になりました。読み取り専用 (RO) では理解しやすく、優れたスケーラビリティを備えています。リード+ライト（RW）においても、より高いレベルに達することを期待しています。 (特にデータの読み取りがデータベースの主な仕事である場合)

ただし。また、MySQL 5.6 の RO のパフォーマンスにも非常に満足しています。バージョン 5.7 では、ビッグ データの処理がまだ期待に応えていないため、主な作業は読み取り+書き込み (RW) に集中しています。ただし、RW は RO に依存します。再び速度を上げることができる。 InnoDB チームは継続的な改善を通じて、バージョン 5.7 の 1 秒あたりのパフォーマンスを強力に推進および最適化しています。

以下、順番に説明します

実際、読み取り専用ワークロードで MySQL の内部リンクを制御するには 2 つの方法があります:

• 単一テーブルを使用する: MDL、trx_sys、lock_sys (InnoDB)

• 複数のテーブル: trx_sys および lock_sys (主に InnoDB)

非常に高速な単一テーブル範囲テストのワークロードは、主にロックを引き起こす MDL リンクによるものです。 InnoDB の内部構造により、複数のテーブルが制限されます (異なるテーブルは異なる MDL ロックによって保護されるため、この場合、MDL のリンク ボトルネックが軽減されます)。ただし、これもワークロードのサイズによって異なります。通常よりも読み取り専用の作業が多い測定では、MySQL 5.6 (Sysbench OLTP_RO など) でのパフォーマンスが向上しますが、同時に、ワークロードが少ない MySQL 5.6 でのパフォーマンスも向上します。そして、より高速なクエリ (Sysbench Point-Selects (レコードをフェッチするために外部キーを使用) など) はすべてのリンクを難しくし、16 コア HT でのみ測定でき、32 コアではパフォーマンスが低下します。しかし、Point-Select のようなものはすべて、 「テスト ワークロードにより、すべての MySQL 内部機能が連携して (SQL パーサーから始まり、行値のフェッチで終了) 可能な最大のパフォーマンスを確認できます...」を選択します。指定された MySQL バージョンと指定された HW 構成に応じて、これはまた、1 秒あたりの最大 SQL クエリ (QPS) レートに達する場合もあります。

Mysql5.6 で得られた最高の結果は 1 秒あたり 250,000 クエリでした。これは、その期間中に Mysql/InnoDb で SQL ステートメント クエリを使用して得られた最高の結果でもありました。

もちろん、このような高速性は「読み取り専用トランザクション」機能 (Mysql5.6 の新機能) を使用することによってのみ実現できます。さらに、AUTOCOMMIT=1 を使用する必要があります。そうしないと、CPU が無駄に消費されやすくなります。トランザクションを開始したり、トランザクションを送信したりすると、実際にはシステム全体のパフォーマンスが低下します。

Mysql5.7 で導入された最初の改善は、「読み取り専用トランザクションの自動検出」機能です (実際には、この外部に DML 宣言があるまで、すべての InnoDb トランザクションは読み取り専用とみなされます) - --、これは非常に重要です読み取り専用トランザクション機能が簡素化され、ユーザーと開発者は読み取り専用トランザクション機能を使用するかどうかを管理する必要がなくなりました。ただし、この関数を使用しても、トランザクションの開始および終了ステータスのプロセスで CPU 時間が依然として無駄になるため、Mysql の潜在的な最適な 1 秒あたりのクエリ レートを達成することはできません。

同時に、Percona はさまざまなソリューションを使用して、「トランザクション リスト」管理 (TRX リスト) の問題と、InnoDB の trx_sys 相互排他リンクの遅い問題を解決します。 Percona のソリューションは、トランザクションによる高負荷の Point-Select を処理する場合に良好なパフォーマンスを発揮しますが、MySQL 5.7 のパフォーマンスは平凡です (ただし、5.7 のコードは公開されていないため、結果は公開しません)。つまり、少なくともいくつかのことを実行できるようになりました。比較:

観察結果:

• MySQL5.6、Percona 5.5、MySQL5.7 の 8 つのテーブルに対する同じ Roint-Select-TRX 読み取り専用テスト (トランザクションあり) (2013.5 月次結果)

• 同時に、同じ 16 コア HT 構成では、ピークの 250,000/秒の結果にはまだ程遠いことがわかります。

• MySQL5.6 では、trx_sys 相互排他アクセスのリンク時間が延長され、1 秒あたりのリクエスト数が 64 ユーザーから減少します。

• Percona5.5 は長時間負荷を維持でき、1 秒あたりのリクエストはユーザーが 512 人になった時点でのみ減少し始めます

• MySQL5.7 が一定期間維持されると、 1 秒あたりのリクエスト数はまだ減少していません (同時ユーザー数が多い場合、この図ではわかりません)...

ただし、MySQL で 1 秒あたりの潜在的な最大クエリ レートを取得したい場合は、次のことが明らかです。取引は避けてください。

2013 年 5 月の 1 秒あたりの最大クエリ レートを見てみましょう。

同じ 8 つのテーブルでテストしましたが、MySQL5.6 は使用しませんでした:

観察:

• 上記のテストは、MySQL5.6 を常に 16 コアで実行し、次に 16 コア - HT、32 コア、32 コア - HT を維持することです。

• ご覧のとおり、最大1 秒あたりのクエリ レートは予想よりも高く、MySQL では 1 秒あたり 275,000 です

• 最大結果は 16 コア - HT に達しました。

• しかし、32 コアでの結果は 16 コアほど良くありません。 core-HT (競合割り込みのため、同じコア内に 2 つの CPU スレッドがある構成はスレッド競合をより適切に管理できます。そのため、実際の同時実行性は 32 コアではなく 16 スレッドで保存されます)

MySQL 5.7 での同じテスト5.7 の lock_sys 相互排他リンクの期間はすでに非常に短く、trx_sys 相互排他関連のコードも最初の変更を受けるため、見た目は大きく異なります。 :

まず、次のことができます。同じ 16 コア HT 構成では、5.7 のパフォーマンスが 5.6 よりも優れていることがわかります

その後、32 コア構成では明らかな違いはありません。

32 コア HT 構成で最大リクエスト 350,000/秒に到達しました。

上記の特別な (積極的な) 読み取り専用負荷テスト ケースから、16 コアよりも 32 コアの方が良い結果が得られることが簡単にわかりますが、まだハイパースレッディングを有効にしていません (32 コア -HT) ...いいね！ ;-)

• 一方で、まだ改善の余地があることも明らかです。 trx_sys に関する競合はまだ進行中です。有用な作業を行うために CPU パワーを十分に活用していない (ロックのローテーションにまだ多くの CPU サイクルが費やされている)...しかし、結果は以前よりもはるかに良くなり、5.6 よりもはるかに優れているため、そうする理由はありません。パフォーマンスを向上させるためにマイニングを続ける この側面のパフォーマンスでは、かつては膨大なスペースを費やしていた読み取りおよび書き込みワークロードのパフォーマンスを向上させることに主に焦点を当てています。

5 月末のパフォーマンス セッション中に、Sunny は try_sys ミューテックス競合にいくつかの新しい変更を追加し、それ以来、1 秒あたりの最大可能クエリ数 (QPS) が 375K に達しました。これは 5.7 からのパフォーマンスの向上としては十分ではありませんね。 ;-)

同時に、TRX リストを管理する他の方法を提案する Percona チームと意見交換を続けていますが、5.5 では、そのようなコードはより高い APS を表示できません。 5.6 (Percona Server 5.6 はテスト済み) 上のこのコードによって実行できる 1 秒あたりの最大クエリ数 (QPS) は、MySQL 5.6 より大きくなることはありません。ただし、この議論では興味深い点が浮かび上がってきます。それは、読み取りと書き込みのワークロードが同時に実行されている場合、読み取り専用のパフォーマンスにどのような影響があるのでしょうか? ...そして、同じテスト条件下であっても、MySQL 5.7 コードは依然として大幅に改善されており、その効果は非常に明白です (ここで私の分析をご覧いただけますが、繰り返しになりますが、今回は 5 を示すことはできません。7、コードはまだ公開されていないため、おそらく将来の投稿になるでしょう)..

これは純粋な読み取りおよび書き込みワークロードにも影響するため、十分な動機があります。TRX リスト関連のコード全体を書き直すことは、Sunnys が望んでいたことでした。長い間続けてきましたが、その経験はまさに強迫観念に他なりませんでした。

;-)) 毎日、同じ 32 コアのハイパースレッド サーバー上で 1 秒あたりのクエリ数が 440K に達するまで、1 秒あたりのクエリ数のグラフが徐々に高くなっていくのを見てうれしく思いました!

取得された結果の数by 5.7 開発マイルストーン リリース 2 で 8 つのテーブルを選択:

説明する必要はありません...;-))

しかし、少し奇妙なことがあります - すべてのボトルネックとコード変更の影響を分析しようとしましたさまざまなツールを通じて Sunny と連携します。そしていくつかのテストでは、驚いたことに、Sunny は私よりも 1 秒あたりのクエリ数が多いことを観察しました。この「奇妙さ」は次の要因に関連しています:

高負荷下では、現在 5.7 のコードは限界近くで実行されていますハードウェア (主に CPU) に影響するため、すべての命令が重要です。

• Unix ソケットまたは IP ポートを使用する場合、違いは非常に明らかです。

• Sysbench 自体は CPU 時間の 30% を使用しますが、同じテスト負荷で古いバージョンの Sysbench (コード パスが短い) を使用する場合、CPU の 20% のみが使用され、残りの 10% は CPU 時間の 30% を使用します。 MySQLサーバー。

• つまり、同じテスト負荷の下で、IP ポートの代わりに Unix ソケットを使用し、Sysbench-0.4.13 の代わりに Sysbench-0.4.8 を使用すると、1 秒あたり 500K を超えるクエリが得られることになります !-それは簡単ですそうだね？ ;-))

• 「前」と「後」の違いを比較してみましょう

  

  观察结果：

  • 通过Sysbench降低了CPU的使用率。

  • 在MySQL服务器上具有更高的CPU可用性。

  • 我们实现了50万每秒查询。

  还有什么呢？

  我可能只提到：kudos Sunny和整个MySQL的开发团队；

  让我们看一下现在选择8张表工作负载的情况下的最大每秒查询。

  • MySQL-5.7.2 (DMR2)

  • MySQL-5.6.14

  • MySQL-5.5.33

  • Percona Server 5.6.13-rc60.5

  • Percona Server 5.5.33-rel31.1

  • MariaDB-10.0.4

  • MariaDB-5.5.32

  每个引擎都在以下配置下进行测试：

  • CPU taskset: 8核-HT,16核，16核-HT,32核，32核-HT

  • 并发会话数：8，16，32 ... 1024

  • InnoDB自旋等待延时：6，96

  最好的结果是来自任意两个特定的组合间的比较。通过对数据库引擎的比较，我得到了下面的一个图表，这个图表我在以前的文章中已经提到过了。

  下面是一些评论：

  • 对Mysql5.7的巨大差距结果不需要做过多的评论，因为这是很明显的。

  • 那么，有趣的是基于MySQL5.5的代码库引擎没有任何的接近MySQL5.6的结果。

  • 这已经证实了在使用MySQL5.6的代码库引擎之后，Percona Server达到了MySQL5.6的水平，然而MariaDB-10仍然还在探索的路上。

  • 因此，毫无疑问，MySQL5.6是代码的基石!

  • MySQL5.7是在MySQL5.6基础上的再一次优化扩展。

  具有什么样的扩展性呢？

  

  答案是简单的：MySQL5.7是唯一在此基础上进行扩展的。

  如果使用ip端口和一个重量级的Sysbench-0.4.13，会得到如下的结果：

  

  QPS只是稍微的略低一点，但是总体的趋势是完全一样的。

  可扩展性也是非常的相似：

  

   

  更多的结果将会出来，敬请期待；

  注意：对一个单表绑定过多的工作负载是不好的：

  • 减少InnoDB间的争论使得其他的争论更加的明显。

  • 当负载是绑定在一张单表上时候，MDL的争论将变得更加主导。

  • 这是预期希望的，我们在下一个DMRS上将保持不变。

   

  还有很多挑战摆在我们面前;-)

  作为参考，我上述测试的硬件配置信息如下：

  • Server : 32cores-HT (bi-thread) Intel 2300Mhz, 128GB RAM

  • OS : Oracle Linux 6.2

  • FS : 启用"noatime,nodiratime,nobarrier"挂载的EXT4

  
  my.conf:

  max_connections=4000
   key_buffer_size=200M
   low_priority_updates=1
   table_open_cache = 8000
   back_log=1500
   query_cache_type=0
   table_open_cache_instances=16
  
  # files
   innodb_file_per_table
   innodb_log_file_size=1024M
   innodb_log_files_in_group = 3
   innodb_open_files=4000
  
  # buffers
   innodb_buffer_pool_size=32000M
   innodb_buffer_pool_instances=32
   innodb_additional_mem_pool_size=20M
   innodb_log_buffer_size=64M
   join_buffer_size=32K
   sort_buffer_size=32K
  
  # innodb
   innodb_checksums=0
   innodb_doublewrite=0
   innodb_support_xa=0
   innodb_thread_concurrency=0
   innodb_flush_log_at_trx_commit=2
   innodb_max_dirty_pages_pct=50
   innodb_use_native_aio=1
   innodb_stats_persistent = 1
   innodb_spin_wait_delay= 6 / 96
  
  # perf special
   innodb_adaptive_flushing = 1
   innodb_flush_neighbors = 0
   innodb_read_io_threads = 4
   innodb_write_io_threads = 4
   innodb_io_capacity = 4000
   innodb_purge_threads=1
   innodb_adaptive_hash_index=0
  
  # monitoring
   innodb_monitor_enable = '%'
   performance_schema=OFF

  如果你需要的话，Linux Sysbench的二进制版本在这里：

  • Sysbench-0.4.13-lux86

  • Sysbench-0.4.8-lux86

  使用UNIX socket来运行Point-Selects测试的Sysbench命令如下（在parallel中启动8个进程）：

  LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.8 --num-threads=$1 --test=oltp --oltp-table-size=10000000 \
          --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \
          --max-requests=0 --max-time=$2 --mysql-socket=/SSD_raid0/mysql.sock \
          --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
          --mysql-table-engine=INNODB  --db-driver=mysql \
          --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
          --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
          --oltp-read-only=on run  > /tmp/test_$n.log &

  使用IP端口来运行Point-Selects测试的Sysbench命令如下（在parallel中启动8个进程）：

  LD_PRELOAD=/usr/lib64/libjemalloc.so /BMK/sysbench-0.4.13 --num-threads=$1 --test=oltp --oltp-table-size=10000000 \
          --oltp-dist-type=uniform --oltp-table-name=sbtest_10M_$n \
          --max-requests=0 --max-time=$2 --mysql-host=127.0.0.1 --mysql-port=5700 \
          --mysql-user=dim --mysql-password=dim --mysql-db=sysbench \
          --mysql-table-engine=INNODB  --db-driver=mysql \
          --oltp-point-selects=1 --oltp-simple-ranges=0 --oltp-sum-ranges=0 \
          --oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-skip-trx=on \
          --oltp-read-only=on run  > /tmp/test_$n.log &

以上がMySQL5.7 を使用して 1 秒あたり 500,000 の高性能クエリを実現の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。