MySQL5.7을 사용하여 초당 500,000개의 고성능 쿼리 달성

[소개] 이 기사에서는 MySql5 7이 초당 50W 쿼리를 달성하는 세부 정보와 벤치마크 결과를 제공하며 이전에 Mysql Connect에 대해 이야기한 내용을 설명합니다. MySQL InnoDB 개선 내역을 검토하세요. 쉽게 찾을 수 있습니다. MySQL 5 및 6의 안정 버전에서는 읽기 전용이었던 적이 없습니다.

이 기사에서는 Mysql Connect에 대한 이전 강연을 설명하면서 "MySql5.7이 초당 50W 쿼리를 달성합니다" 기사의 세부 정보와 벤치마크 결과를 제공합니다.

MySQL/InnoDB의 개선 내역을 검토하세요. 쉽게 찾을 수 있습니다. MySQL 5.6 안정 버전에서는 읽기 전용에서 이렇게 빠른 적이 없었습니다. 읽기 전용(RO)에서 이해하기 쉽고 확장성이 좋습니다. 읽기+쓰기(RW)에서도 더 높은 수준에 도달할 수 있기를 기대합니다. (특히 데이터를 읽는 것이 데이터베이스의 주요 업무인 경우)

단. MySQL 5.6의 RO 성능에도 매우 만족합니다. 버전 5.7에서는 빅 데이터 처리가 아직 기대에 미치지 못하기 때문에 주요 작업이 읽기+쓰기(RW)에 중점을 두고 있습니다. 그러나 RW는 RO에 의존합니다. 다시 속도를 높일 수 있습니다. 지속적인 개선을 통해 InnoDB 팀은 버전 5.7의 초당 성능을 강력하게 홍보하고 최적화하고 있습니다.

다음에서 순서대로 설명하겠습니다

실제로 MySQL에서 읽기 전용 워크로드의 내부 링크를 제어하는 ​​방법에는 두 가지가 있습니다.

• 단일 테이블 사용: 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코어에서는 성능이 저하됩니다. 포인트 선택 테스트 워크로드를 사용하면 지정된 MySQL 버전 및 지정된 HW에서 모든 MySQL 내부가 함께 작동하여(SQL 파서로 시작하여 행 값 가져오기로 종료) 가능한 최대 성능을 확인할 수 있습니다. 구성에 따라 다름 , 이는 최대 QPS(초당 SQL 쿼리) 속도에 도달할 수도 있습니다.

Mysql5.6에서 얻은 최고의 결과는 초당 250,000개의 쿼리였습니다. 이는 또한 해당 기간 동안 Mysql/InnoDb에서 SQL 문 쿼리를 사용하여 얻은 최고의 결과였습니다.

물론 이러한 빠른 속도는 '읽기 전용 트랜잭션' 기능(Mysql5.6의 새로운 기능)을 통해서만 달성할 수 있습니다. 또한 AUTOCOMMIT=1을 사용해야 합니다. 그렇지 않으면 CPU가 쉽게 낭비됩니다. 트랜잭션을 시작하고 트랜잭션을 제출하면 실제로 시스템의 전반적인 성능이 저하됩니다.

그래서 Mysql5.7에 도입된 첫 번째 개선 사항은 '읽기 전용 트랜잭션 자동 검색'입니다(실제로 모든 InnoDb 트랜잭션은 이 외부에 DML 선언이 없을 때까지 읽기 전용으로 간주됩니다). - -- 이는 크게 단순화됩니다. 읽기 전용 트랜잭션 기능을 사용하여 사용자와 개발자의 시간을 절약해 줍니다. 더 이상 읽기 전용 트랜잭션 기능 사용 여부를 관리할 필요가 없습니다. 그러나 이 기능을 사용하면 트랜잭션의 시작 및 종료 상태를 처리하는 과정에서 CPU 시간이 여전히 낭비되기 때문에 여전히 MySQL의 잠재적인 최적 초당 쿼리 속도를 달성할 수 없습니다.

동시에 Percona는 "트랜잭션 목록" 관리(TRX-list) 문제와 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/s 결과와는 거리가 멀다는 것을 알 수 있습니다.

• MySQL5.6은 trx_sys 상호 배타적 액세스에서 링크 시간을 연장했으며 사용자가 64명이므로 초당 요청 수가 줄어듭니다.

• Percona5.5는 오랫동안 부하를 유지할 수 있으며 초당 요청은 사용자가 512명부터 줄어들기 시작합니다

• MySQL5.7이 일정 기간 유지되면 초당 요청 수에는 여전히 감소가 없습니다(더 많은 동시 사용자에 대해서는 이 그림에서 볼 수 없음)...

그러나 MySQL을 사용하여 초당 최대 잠재적 쿼리 속도를 얻으려는 경우, 거래는 피해야 합니다.

2013년 5월 초당 최대 쿼리 속도가 얼마인지 살펴보겠습니다.

동일한 8개 테이블에서 테스트했지만 MySQL 5.6을 사용하지 않았습니다.

관찰:

• 위 테스트는 MySQL5.6을 항상 16개 코어에서 실행한 다음 16개 코어-HT, 32개 코어, 32개 코어-HT에서 실행하도록 하는 것입니다.

• 보시다시피 최대 초당 쿼리 속도가 예상보다 큽니다. MySQL에서는 초당 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 뮤텍스 경합에 몇 가지 새로운 변경 사항을 추가했으며 그 이후 최대 QPS(초당 쿼리 수)가 375K에 도달했습니다! 5.7의 성능 향상만으로는 충분하지 않죠? ;-)

동시에 TRX 목록을 관리하는 다른 방법을 제안하는 Percona 팀과 계속 의견을 교환하고 있습니다. 그들의 솔루션은 매우 흥미로워 보이지만 5.5에서는 이러한 코드가 더 높은 APS를 표시할 수 없습니다. 5.6(Percona Server 5.6은 테스트됨)에서 이 코드로 수행할 수 있는 초당 최대 쿼리 수(QPS)는 MySQL 5.6보다 크지 않습니다. 그러나 토론에서는 흥미로운 점을 제시합니다. 일부 읽기 및 쓰기 워크로드가 동시에 실행되는 경우 읽기 전용 성능에 어떤 영향을 미칠까요? ...그리고 동일한 테스트 조건에서도 MySQL 5.7 코드는 여전히 훨씬 더 잘 실행되며 그 효과는 매우 분명합니다(여기에서 내 분석을 볼 수 있지만 이번에도 이 기간 동안 5를 표시할 수는 없습니다. .7, 코드가 아직 대중에게 공개되지 않았기 때문에 - 아마도 향후 게시물에 있을 것입니다..

이것은 순수한 읽기 및 쓰기 작업에도 영향을 미치기 때문에 동기가 충분합니다. 전체 TRX 목록 관련 코드를 다시 작성하는 것은 Sunnys가 원했던 것이었습니다. 오랫동안 해왔지만 그 경험은 강박적이었습니다!

;-)) 동일한 32코어 하이퍼 스레드 서버 440K에서 초당 쿼리에 도달할 때까지 날마다 초당 쿼리 그래프가 점차 높아지는 것을 보고 기뻤습니다!

얻은 결과 수 by Select 8 tables on 5.7 Development Milestone Release 2:

설명할 필요가 없습니다...;-))

하지만 조금 이상한 점이 있습니다. - 우리는 모든 병목 현상과 코드 변경의 영향을 분석하려고 했습니다. 다양한 도구를 통해 Sunny와 함께하세요. 그리고 일부 테스트에서 놀랍게도 Sunny는 나보다 초당 더 많은 쿼리 수를 관찰했습니다. 이 "이상함"은 다음 요소와 관련이 있습니다. 하드웨어(주로 CPU)에 영향을 미치므로 모든 명령이 중요합니다!

• Unix 소켓이나 IP 포트를 사용하면 차이가 확연해집니다!

• Sysbench 자체는 CPU 시간의 30%를 사용하지만, 동일한 테스트 로드가 이전 버전의 Sysbench(더 짧은 코드 경로 포함)를 사용하는 경우 CPU의 20%만 사용하고 나머지 10%는 사용합니다. MySQL 서버.

• 따라서 동일한 테스트 로드에서 IP 포트 대신 Unix 소켓을 사용하고 Sysbench-0.4.13 대신 Sysbench-0.4.8을 사용하면 초당 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을 사용하여 초당 500,000개의 고성능 쿼리 달성의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!