MySQL-Leistung: 500.000 Abfragen pro Sekunde mit MySQL 5.7

[Einführung] Dieser Artikel enthält die Details und Benchmark-Ergebnisse von MySql5 7, um 50-W-Abfragen pro Sekunde zu erreichen, und erläutert meinen früheren Vortrag über Mysql Connect. Sehen Sie sich den Verlauf der Verbesserungen an MySQL InnoDB an. Sie können es leicht finden. In den stabilen Versionen von MySQL 5 und 6 war es noch nie so schreibgeschützt.

Dieser Artikel enthält die Details und Benchmark-Ergebnisse des Artikels „MySql5.7 erreicht 50 W Abfragen pro Sekunde“ und erläutert meinen früheren Vortrag MySQL Connect.

Sehen Sie sich den Verlauf der Verbesserungen an MySQL / InnoDB an. Sie können es leicht finden. In der stabilen Version von MySQL 5.6 war es im schreibgeschützten Modus noch nie so schnell. Es ist leicht zu verstehen und weist eine gute Skalierbarkeit im schreibgeschützten Modus auf. Ich freue mich auch darauf, dass es beim Lesen und Schreiben (RW) ein höheres Niveau erreicht. (Besonders wenn das Lesen von Daten die Hauptaufgabe der Datenbank ist)

Allerdings. Auch mit der Leistung von RO in MySQL 5.6 sind wir sehr zufrieden. In der Version 5.7 liegt der Schwerpunkt der Arbeit auf dem Lesen+Schreiben (RW), da die Verarbeitung von Big Data noch nicht unseren Erwartungen entspricht. Aber RW ist auf RO angewiesen. Kann die Geschwindigkeit wieder erhöhen. Durch kontinuierliche Verbesserung fördert und optimiert das InnoDB-Team die Leistung pro Sekunde von Version 5.7 stark.

Im Folgenden wird es Ihnen der Reihe nach erklärt

Tatsächlich gibt es die folgenden zwei Möglichkeiten, interne Links in MySQL durch schreibgeschützte Arbeitslast zu steuern:

• Einzelne Tabelle verwenden: MDL, trx_sys und lock_sys (InnoDB)

• Mehrere Tabellen: trx_sys und lock_sys (hauptsächlich InnoDB)

Die Arbeitsbelastung beim schnellen Testen einzelner Tabellenbereiche ist hauptsächlich auf Sperren zurückzuführen, die durch MDL-Verknüpfungen verursacht werden. Aufgrund der InnoDB-Interna ist die Anzahl mehrerer Tabellen begrenzt (verschiedene Tabellen werden durch unterschiedliche MDL-Sperren geschützt, sodass der Link-Engpass in MDL in diesem Fall verringert wird). Aber auch hier kommt es auf die Größe der Arbeitslast an – eine Messung mit mehr schreibgeschützter Arbeit als üblich wird in MySQL 5.6 eine bessere Leistung erbringen (z. B. Sysbench OLTP_RO), während sie gleichzeitig in MySQL 5.6 mit weniger Arbeitslast eine bessere Leistung erbringt und schnellere Abfragen (wie z. B. Sysbench Point-Selects (mit Fremdschlüsseln zum Abrufen eines Datensatzes) machen alle Links schwierig und können nur in 16-Core-HT gemessen werden und funktionieren in 32-Core schlecht. Aber alles wie Point- Wählen Sie „Die Test-Workload“ ermöglicht es Ihnen, die maximal mögliche Leistung zu sehen, wenn alle MySQL-Interna zusammenarbeiten (beginnend mit dem SQL-Parser, endend mit dem Abrufen des Zeilenwerts) … auf Ihrer gegebenen MySQL-Version und gegebenen HW. Abhängig von der Konfiguration, dies kann auch die maximale Rate für SQL-Abfragen pro Sekunde (QPS) erreichen.

Das beste Ergebnis, das wir auf Mysql5.6 erzielten, waren 250.000 Abfragen pro Sekunde, was auch das beste Ergebnis war, das in dieser Zeit mit SQL-Anweisungsabfragen auf Mysql/InnoDb erzielt wurde.

Eine solch hohe Geschwindigkeit kann natürlich nur erreicht werden, wenn die Funktion „schreibgeschützte Transaktion“ verwendet wird (eine neue Funktion in Mysql5.6). Außerdem muss AUTOCOMMIT=1 verwendet werden, andernfalls wird die CPU belastet Es wird leicht verschwendet, Transaktionen zu starten und zu begehen, wodurch tatsächlich die Gesamtleistung des Systems verloren geht.

Die erste in Mysql5.7 eingeführte Verbesserung ist also die Funktion „Automatische Erkennung von schreibgeschützten Transaktionen“ (eigentlich gilt jede InnoDb-Transaktion als schreibgeschützt, bis davor eine DML-Deklaration vorliegt) Extern) Funktion --- Dies vereinfacht die schreibgeschützte Transaktionsfunktion erheblich und spart Benutzern und Entwicklern Zeit. Sie müssen nicht mehr entscheiden, ob sie die schreibgeschützte Transaktionsfunktion verwenden möchten. Mit dieser Funktion können Sie jedoch immer noch nicht die potenziell optimale Abfragerate pro Sekunde von MySQL erreichen, da immer noch CPU-Zeit beim Öffnen und Beenden des Status von Transaktionen verschwendet wird.

Gleichzeitig verwendet Percona verschiedene Lösungen, um das Problem der Verwaltung von „Transaktionslisten“ (TRX-Liste) und das langsame Problem der gegenseitigen Ausschlusslinks von trx_sys in InnoDB zu lösen. Die Lösung von Percona schneidet gut ab, wenn es um die Verarbeitung hoher Mengen an Point-Selects mit Transaktionen geht, aber MySQL 5.7 schneidet mittelmäßig ab (aber ich werde die Ergebnisse für 5.7 nicht veröffentlichen, da der Code nicht öffentlich ist) ... Also kann ich jetzt zumindest einiges tun Vergleiche:

Beobachtungen:

• 8 Tabellen in MySQL5.6, Percona 5.5 und MySQL5.7 Verwenden Sie die gleiche Roint-Select -TRX-Lesetest (mit Transaktionen) (Ergebnisse von 2013,5)

• Gleichzeitig können Sie das auch im gleichen Unter 16-Kern sehen -HT-Konfiguration sind wir noch weit vom Spitzenergebnis von 250.000/s entfernt.

• MySQL5.6 hat die Verbindungszeit im gegenseitigen exklusiven Zugriff von trx_sys verlängert und die Anzahl der Anfragen pro Sekunde wird seit 64 Benutzern reduziert.

• Percona5.5 kann die Last lange aufrechterhalten und die Anfragen pro Sekunde beginnen erst bei 512 Benutzern zu sinken

• Auch wenn MySQL 5.7 eine Zeit lang gewartet wurde, sind die Anfragen pro Sekunde immer noch nicht zurückgegangen (bei mehr gleichzeitigen Benutzern ist es auf diesem Bild nicht zu sehen)...

Es ist jedoch klar, dass Transaktionen vermieden werden sollten, wenn Sie mit MySQL die maximale potenzielle Abfragerate pro Sekunde erreichen möchten.

Werfen wir einen Blick auf unsere maximale Abfragerate pro Sekunde im Mai 2013.

Getestet an denselben acht Tabellen, jedoch ohne Verwendung von MySQL 5.6:

Beobachtung:

• Der obige Test besteht darin, MySQL5.6 immer auf 16 Kernen auszuführen, dann 16 Kern-HT, 32 Kerne, 32-Core-HT

• Wie Sie sehen, ist die maximale Abfragerate pro Sekunde höher als erwartet – 275.000 pro Sekunde bei MySQL

• Das maximale Ergebnis wurde bei 16-Core-HT erreicht.

• Allerdings ist das Ergebnis auf 32-Core nicht so gut wie das auf 16-Core-HT (aufgrund von Wettbewerbsunterbrechungen, Eine Konfiguration mit 2 CPU-Threads im selben Kern kann den Thread-Wettbewerb besser bewältigen – die tatsächliche Parallelität wird also immer noch auf 16 Threads und nicht auf 32 Kernen gespeichert)

Derselbe Test auf MySQL5.7 sieht ganz anders aus, da der Zeitraum des gegenseitigen Ausschlusslinks lock_sys in 5.7 bereits sehr gering ist und der Code für den gegenseitigen Ausschluss trx_sys auch die erste Änderung erhält:

Beobachtung Ergebnisse:

• Zuerst sieht man, dass die Leistung von 5.7 bereits besser ist als die von 5.6 unter derselben 16-Core-HT-Konfiguration. Nach

• , es gibt keine offensichtliche Verbesserung unter der 32-Kern-Konfiguration!

• Eine maximale Anfrage von 350.000/Sekunde in der 32-Core-HT-Konfiguration erreicht!

• Aus der speziellen (aggressiven) Leselasttestsituation oben ist leicht ersichtlich, dass wir in 32 Kernen bessere Ergebnisse erzielen als in 16, und zwar bei Gleichzeitig haben wir Hyper-Threading nicht aktiviert (auf 32-Core-HT) ... großartig! ;-)

Andererseits ist klar, dass es noch Raum für Verbesserungen gibt. Der Streit um trx_sys dauert noch an. Wir nutzen die CPU-Leistung nicht vollständig, um nützliche Arbeit zu leisten (es werden immer noch viele CPU-Zyklen für die Sperrenrotation aufgewendet) ... aber die Ergebnisse sind jetzt viel besser als zuvor und viel besser als 5.6, es gibt also keinen Grund dazu Setzen Sie das Mining fort, um uns zu verbessern. In diesem Aspekt der Leistung konzentrieren wir uns hauptsächlich auf die Verbesserung der Leistung von Lese- und Schreib-Workloads, bei denen wir früher viel Platz verbraucht haben.

Ende Mai, während unserer Performance-Sitzung, fügte Sunny mehrere neue Änderungen zum try_sys-Mutex-Konflikt hinzu, und seitdem kann der maximale QPS 375.000 erreichen! Das ist keine ausreichende Leistungsverbesserung gegenüber 5.7, oder? ;-)

Gleichzeitig tauschen wir weiterhin Meinungen mit dem Percona-Team aus, das andere Möglichkeiten zur Verwaltung der TRX-Liste vorschlägt. Ihre Lösung sieht sehr interessant aus, aber auf 5.5 kann ein solcher Code keine höhere Leistung zeigen Die Anzahl der Abfragen pro Sekunde (QPS), die ausgeführt werden können, und die maximale Anzahl der Abfragen pro Sekunde (QPS), die für solchen Code unter 5.6 (Percona Server 5.6 wurde getestet) ausgeführt werden können, werden nicht größer sein als unter MySQL 5.6. Die Diskussion wirft jedoch einen interessanten Punkt auf: Welche Auswirkungen hat es auf die Nur-Lese-Leistung, wenn einige Lese- und Schreib-Workloads gleichzeitig ausgeführt werden? ...und selbst unter den gleichen Testbedingungen läuft der MySQL 5.7-Code immer noch deutlich besser, der Effekt ist sehr offensichtlich (meine Analyse können Sie hier sehen, allerdings kann ich in dieser Zeit wiederum keine 5.7 anzeigen, da sein Code noch nicht für die Öffentlichkeit freigegeben wurde – vielleicht in einem zukünftigen Artikel). die Art und Weise, die Sunnys schon lange machen wollte, aber die Erfahrung war geradezu obsessiv!

;-)) Tag für Tag waren wir erfreut, dass unser Abfrage-pro-Sekunde-Diagramm allmählich höher wurde, bis wir auf demselben 32-Kern-Hyper-Threaded-Server 100 % erreichten Abfragen können pro Sekunde durchgeführt werden!

Die Anzahl der Ergebnisse, die durch Auswahl von 8 Tabellen in 5.7 Development Milestone Release 2 erhalten wurden:

Keine Erklärung erforderlich ..; -))

Es gibt jedoch eine kleine Kuriosität: Wir haben mit Sunny versucht, alle Engpässe und die Auswirkungen von Codeänderungen mithilfe verschiedener Tools zu analysieren. Und in einigen Tests beobachtete Sunny zu meiner Überraschung eine höhere Anzahl von Abfragen pro Sekunde als ich. Diese „Seltsamkeit“ hängt mit folgenden Faktoren zusammen:

Unter hoher Last der Strom 5.7-Code läuft nahe an der Hardwaregrenze (hauptsächlich der CPU), daher ist jede Anweisung sehr wichtig!
Wenn Sie einen Unix-Socket oder IP-Port verwenden, wird der Unterschied sehr offensichtlich sein!
Sysbench selbst nutzt 30 % der CPU-Zeit, aber die gleiche Testlast mit einer älteren Version von Sysbench (mit einem kürzeren Codepfad) verbraucht nur 20 % der CPU, den Rest 10 % werden auf dem MySQL-Server verwendet.
Daher erhalten wir bei gleicher Testlast, Verwendung von Unix-Sockets anstelle von IP-Ports und Verwendung von Sysbench-0.4.8 anstelle von Sysbench-0.4.13 alle über 500.000 Abfragen pro Sekunde! - Ganz einfach, nicht wahr? ;-))
Vergleichen wir die „Vorher“- und „Nachher“-Unterschiede

观察结果：

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