Performances MySQL : 500 000 requêtes par seconde avec MySQL 5.7

[Introduction] Cet article fournit les détails et les résultats de référence de MySql5 7 pour atteindre 50 W de requêtes par seconde, expliquant mon exposé précédent sur Mysql Connect. Consultez l'historique des améliorations apportées à MySQL InnoDB. Vous pouvez le trouver facilement. Il n'a jamais été aussi en lecture seule dans les versions stables de MySQL 5 et 6

Cet article fournit les détails et les résultats de référence de l'article "MySql5.7 atteint 50 W de requêtes par seconde", expliquant mon exposé précédent sur Connexion MySQL.

Consultez l'historique des améliorations apportées à MySQL / InnoDB. Vous pouvez le trouver facilement. Dans la version stable de MySQL 5.6, il n'a jamais été aussi rapide en lecture seule. Il est facile à comprendre et offre une bonne évolutivité en lecture seule (RO). J'ai également hâte qu'il atteigne un niveau supérieur en lecture-écriture (RW). (Surtout lorsque la lecture des données est le travail principal de la base de données)

Cependant. Nous sommes également très satisfaits des performances de RO dans MySQL 5.6. Dans la version 5.7, le travail principal se concentre sur la lecture et l'écriture (RW), car le traitement du big data n'a pas encore répondu à nos attentes. Mais RW dépend de RO. Capable d'augmenter à nouveau la vitesse. Grâce à une amélioration continue, l'équipe InnoDB promeut et optimise fortement les performances par seconde de la version 5.7.

Ce qui suit vous l'expliquera dans l'ordre

En fait, il existe les deux façons suivantes de contrôler les liens internes dans MySQL par une charge de travail en lecture seule :

• Utiliser une seule table : MDL, trx_sys et lock_sys (InnoDB)

• Tables multiples : trx_sys et lock_sys (principalement InnoDB)

La charge de travail des tests rapides de plage de table unique est principalement due au verrouillage provoqué par la liaison MDL. Plusieurs tables seront limitées en raison des éléments internes d'InnoDB (différentes tables seront protégées par différents verrous MDL, donc le goulot d'étranglement des liens dans MDL sera réduit dans ce cas). Mais encore une fois, cela dépend de la taille de la charge de travail : une mesure avec plus de travail en lecture seule que d'habitude fonctionnera mieux dans MySQL 5.6 (comme Sysbench OLTP_RO), tandis qu'en même temps, elle fonctionnera mieux dans MySQL 5.6 avec moins de charge de travail. et des requêtes plus rapides (telles que Sysbench Point-Selects (utilisant des clés étrangères pour récupérer un enregistrement) rendront tous les liens difficiles et ne pourront être mesurées qu'en 16 cœurs-HT, et fonctionneront mal en 32 cœurs.. mais quelque chose comme Point- Sélectionnez La charge de travail de test vous permettra de voir les performances maximales possibles avec tous les composants internes de MySQL travaillant ensemble (en commençant par l'analyseur SQL, en terminant par la récupération de la valeur de la ligne)... sur votre version MySQL et votre matériel donné. En fonction de la configuration, cela peut également atteindre le taux maximum de requêtes SQL par seconde (QPS).

Le meilleur résultat que nous avons obtenu sur Mysql5.6 était de 250 000 requêtes par seconde, ce qui était également le meilleur résultat obtenu en utilisant des requêtes d'instructions SQL sur Mysql/InnoDb pendant cette période.

Bien sûr, une vitesse aussi élevée ne peut être atteinte qu'en utilisant la fonction 'transaction en lecture seule' (une nouvelle fonctionnalité sur Mysql5.6 en plus, AUTOCOMMIT=1 doit être utilisé, sinon le CPU) ; sera facilement gaspillé lors du démarrage des transactions, des transactions de validation, vous perdrez en fait les performances globales du système.

Ainsi, la première amélioration introduite sur Mysql5.7 est la « Découverte automatique des transactions en lecture seule » (en fait, chaque transaction InnoDb est considérée comme en lecture seule jusqu'à ce qu'il y ait une déclaration DML avant elle externe) fonction --- , ce qui simplifie grandement la fonction de transaction en lecture seule et fait gagner du temps aux utilisateurs et aux développeurs. Ils n'ont plus à gérer l'utilisation ou non de la fonction de transaction en lecture seule. Cependant, en utilisant cette fonction, vous ne pouvez toujours pas atteindre le taux de requête optimal potentiel par seconde de Mysql, car le temps CPU est toujours perdu dans le processus d'ouverture et de fin des transactions.

En parallèle, Percona utilise différentes solutions pour résoudre le problème de la gestion des "listes de transactions" (TRX-list) et le problème lent des liens d'exclusion mutuelle trx_sys dans InnoDB. La solution de Percona fonctionne bien lors de la gestion de charges élevées de sélections de points avec des transactions, mais MySQL 5.7 fonctionne médiocrement (mais je ne publierai pas les résultats pour 5.7 car son code n'est pas public)... Donc, au moins je peux maintenant en faire comparaisons :

Observations :

• 8 tables dans MySQL5.6, Percona 5.5 et MySQL5.7 Utiliser le même Roint-Select -Test TRX en lecture seule (avec transactions) (résultats 2013.5)

• En même temps, vous pouvez également voir cela dans le même sous le 16 cœurs -Configuration HT, on est encore loin du résultat pic de 250 000/s.

• MySQL5.6 a prolongé le temps de liaison dans l'accès mutuel exclusif trx_sys, et le nombre de requêtes par seconde sera réduit depuis 64 utilisateurs.

• Percona5.5 peut maintenir la charge pendant longtemps, et les requêtes par seconde ne commencent à diminuer qu'à partir de 512 utilisateurs

• Lorsque MySQL 5.7 a été maintenu pendant un certain temps, les requêtes par seconde n'ont toujours pas diminué (pour plus d'utilisateurs simultanés, vous ne pouvez pas le voir sur cette image)...

Cependant, il est clair que si vous souhaitez obtenir le taux de requêtes potentiel maximum par seconde avec MySQL, les transactions doivent être évitées.

Jetons un coup d'œil à notre taux de requêtes maximum par seconde en mai 2013.

Testé sur les mêmes huit tables, mais sans utiliser MySQL 5.6 :

Observation :

• Le test ci-dessus consiste à garder MySQL5.6 toujours exécuté sur 16 cœurs, puis 16 cœurs-HT, 32 cœurs, 32-core-HT.

• Comme vous pouvez le constater, le taux de requêtes maximum par seconde est supérieur à prévu - 275 000 par seconde sur MySQL

• Le résultat maximum a atteint 16-core-HT.

• Cependant, le résultat sur 32-core n'est pas aussi bon que celui sur 16-core-HT (en raison des interruptions de compétition, une configuration avec 2 threads CPU dans le même cœur permet de mieux gérer la concurrence des threads - donc la concurrence réelle est toujours enregistrée sur 16 threads, et non sur 32 cœurs)

Le même test sur MySQL5.7 semble très différent, car la période de temps du lien d'exclusion mutuelle lock_sys dans 5.7 est déjà très faible, et en même temps, le code lié à l'exclusion mutuelle trx_sys obtient également le premier changement :

Résultats d'observation :

• Vous pouvez d'abord voir que les performances du 5.7 sont déjà meilleures que celles du 5.6 sous la même configuration 16 cœurs-HT Après

• , il n'y a pas d'amélioration évidente sous la configuration 32 cœurs !

• Atteint une demande maximale de 350 000/seconde en configuration 32 cœurs-HT !

• À partir du cas de test de charge spécial (agressif) en lecture seule ci-dessus, il est facile de voir que nous obtenons de meilleurs résultats dans 32 cœurs que dans 16, et à en même temps nous n'avons pas activé l'hyperthreading (sur 32-core-HT)... génial ! ;-)

En revanche, force est de constater qu'il y a encore place à l'amélioration. Le conflit sur trx_sys est toujours en cours. Nous n'utilisons pas pleinement la puissance du processeur pour effectuer un travail utile (il y a encore de nombreux cycles de processeur consacrés à la rotation des verrous)... mais les résultats sont bien meilleurs maintenant qu'avant, et bien meilleurs que la version 5.6, il n'y a donc aucune raison de le faire. continuer à exploiter pour s'améliorer Dans cet aspect des performances, nous nous concentrons principalement sur l'amélioration des performances des charges de travail de lecture et d'écriture où nous passions autrefois d'énormes quantités d'espace.

Fin mai, lors de notre session de performance, Sunny a ajouté plusieurs nouveaux changements au conflit de mutex try_sys, et depuis lors, le QPS maximum peut atteindre 375K ! Ce n'est pas une amélioration suffisante des performances pour la version 5.7, n'est-ce pas ? ;-)

En parallèle, nous continuons d'échanger avec l'équipe Percona qui suggère d'autres façons de gérer la liste TRX. Leur solution semble très intéressante, mais sur la 5.5, un tel code ne peut pas montrer des performances supérieures. le nombre de requêtes par seconde (QPS) pouvant être effectuées, et le nombre maximum de requêtes par seconde (QPS) pouvant être effectuées sur un tel code sur 5.6 (Percona Server 5.6 a été testé) ne sera pas supérieur à celui de MySQL 5.6. Cependant, la discussion soulève un point intéressant : quel impact cela a-t-il sur les performances en lecture seule si certaines charges de travail de lecture et d'écriture s'exécutent en même temps ? ...et, même dans les mêmes conditions de test, le code MySQL 5.7 fonctionne toujours bien mieux, l'effet est très évident (vous pouvez voir mon analyse ici, cependant, encore une fois, je ne peux pas afficher 5 pendant ce temps .7, puisque le code n'a pas encore été rendu public - peut-être dans un prochain article)..

Étant donné que cela affecte également toutes les charges de travail pures de lecture et d'écriture, il y avait suffisamment de motivation pour réécrire l'intégralité du code lié à la liste TRX dans comme Sunnys voulait le faire depuis longtemps, mais l'expérience était tout simplement obsessionnelle !

;-)) Jour après jour, nous avons eu le plaisir de voir notre graphique de requêtes par seconde augmenter progressivement, jusqu'à atteindre 440K requêtes pouvant être effectuées par seconde !

Le chiffre des résultats obtenus en sélectionnant 8 tableaux sur la version 2 du jalon de développement 5.7 :

Aucune explication requise ..;-))

Cependant, il y a un léger bizarrerie - nous avons essayé avec Sunny d'analyser tous les goulots d'étranglement et l'impact des changements de code à travers différents outils. Et lors de certains tests, à ma grande surprise, Sunny a observé un nombre de requêtes par seconde plus élevé que moi. Cette "étrangeté" est liée aux facteurs suivants :

• Sous forte charge, le courant. Le code 5.7 s'exécute à proximité de la limite matérielle (principalement le CPU), donc chaque instruction est très importante !

• Si vous utilisez un socket Unix ou un port IP, la différence sera très flagrante !

• Sysbench lui-même utilise 30 % du temps CPU, mais la même charge de test utilisant une ancienne version de Sysbench (avec un chemin de code plus court) n'utilisera que 20 % du temps CPU, le reste 10% sont utilisés sur le serveur MySQL.

• Par conséquent, avec la même charge de test, en utilisant des sockets Unix au lieu des ports IP et en utilisant Sysbench-0.4.8 au lieu de Sysbench-0.4.13, nous obtiendrons toutes les plus de 500 000 requêtes. par seconde ! - C'est plutôt facile, n'est-ce pas ? ;-))

Comparons les différences « avant » et « après »

观察结果：

• 通过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）！