MySQL 성능: MySQL 5.7을 사용하여 초당 500,000개의 쿼리

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

이 기사에서는 "MySql5.7이 초당 50W 쿼리를 달성합니다" 기사의 세부 정보와 벤치마크 결과를 제공하며, MySQL 연결.

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명부터 줄어들기 시작합니다

• MySQL 5.7을 일정 기간 유지한 후에도 초당 요청 수는 줄어들지 않았습니다(동시 사용자 수는 사진에 표시되지 않습니다)...

그러나 MySQL로 초당 최대 잠재적 쿼리 속도를 얻으려면 트랜잭션을 피해야 한다는 것은 분명합니다.

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

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

관찰:

• 위 테스트는 MySQL5.6을 항상 16코어에서 실행한 다음 16코어-HT, 32코어, 32-core-HT.

• 보시다시피 초당 최대 쿼리 속도는 예상보다 높습니다(MySQL

• 최대 결과가 16코어-HT에 도달했습니다.

• 그러나 32코어의 결과는 16코어-HT만큼 좋지 않습니다(경쟁 중단으로 인해, 동일한 코어에 2개의 CPU 스레드가 있는 구성은 스레드 경쟁을 더 잘 관리할 수 있습니다. 따라서 실제 동시성은 여전히 ​​32개 코어가 아닌 16개 스레드에 저장됩니다.

MySQL5.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에서는 이러한 코드가 더 높은 성능을 발휘할 수 없습니다. 수행할 수 있는 초당 쿼리 수(QPS) 및 5.6(Percona Server 5.6은 테스트됨)에서 해당 코드에 대해 수행할 수 있는 최대 초당 쿼리 수(QPS)는 MySQL 5.6보다 크지 않습니다. 그러나 토론에서는 흥미로운 점을 제시합니다. 일부 읽기 및 쓰기 워크로드가 동시에 실행되는 경우 읽기 전용 성능에 어떤 영향을 미칠까요? ...그리고 동일한 테스트 조건에서도 MySQL 5.7 코드는 여전히 훨씬 더 잘 실행되며 그 효과는 매우 분명합니다(여기에서 내 분석을 볼 수 있지만 이번에도 이 기간 동안 5를 표시할 수는 없습니다. .7, 코드가 아직 대중에게 공개되지 않았기 때문에 - 아마도 향후 기사에서)..

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

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

5.7 개발 마일스톤 릴리스 2에서 8개의 테이블을 선택하여 얻은 결과 수:

설명이 필요하지 않습니다..; -))

그러나 약간의 이상한 점이 있습니다. 우리는 Sunny와 함께 다양한 도구를 통해 모든 병목 현상과 코드 변경으로 인한 영향을 분석해 보았습니다. 그리고 일부 테스트에서 놀랍게도 Sunny는 나보다 초당 더 많은 쿼리 수를 관찰했습니다. 이 "이상함"은 다음 요소와 관련이 있습니다.

• 부하가 높을 때 현재 5.7 코드는 하드웨어 제한(주로 CPU)에 가깝게 실행되므로 모든 명령이 매우 중요합니다!

• 유닉스 소켓이나 IP 포트를 사용한다면 차이가 확연히 드러납니다!

• Sysbench 자체는 CPU 시간의 30%를 사용하지만 이전 버전의 Sysbench(더 짧은 코드 경로 포함)를 사용하는 동일한 테스트 로드는 20% %CPU만 사용합니다. 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 &

愿你有所收获， 
-Dimitri                        

 以上就是 MySQL性能：使用 MySQL 5.7 实现每秒 50 万查询的内容，更多相关内容请关注PHP中文网（www.php.cn）！