GreatSQL vs MySQL性能测试来了，速围观~

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
  
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
  

1.结论先行

• 无论ibp（innodb_buffer_pool_size）是否充足，MySQL的性能都远不如GreatSQL。
  
• MySQL的性能平均约为GreatSQL的70%（最高84.5%，最低61.7%）。
  
• 在ibp充分的情况下，随着并发数的增加，MySQL并没有表现出该有的性能提升，反倒掉头向下，可见还是不够稳定。
  
• 在ibp不够的情况下，GreatSQL开启thread pool性能有所提升；当ibp充足的情况下，区别就不大了。
  

MySQL vs GreatSQL性能数据对比
48G96G144G192GavgMySQL  vs GreatSQL0.61730.7350.7210.84490.7295各数据库版本分别为

• MySQL 8.0.30 MySQL Community Server - GPL
  
• GreatSQL 8.0.25-16 GreatSQL, Release 16, Revision 8bb0e5af297
  

2.测试结果数据

2.1 ibp=48G

TPS8th16th32th64th128thGreatSQL-thdpool969.161324.211661.572007.982331.4GreatSQL873.061146.851371.341509.81699.19MySQL686.14846.5915.151073.951439.29P.S，后缀加上 thdpool 表示启用了thread pool。
QPS8th16th32th64th128thGreatSQL-thdpool19383.226484.1433231.4940159.5646627.89GreatSQL17461.1622937.1427426.8730196.0233983.78MySQL13722.816929.9418303.032147928785.7
2.2 ibp=96G

TPS8th16th32th64th128thGreatSQL-thdpool1074.571407.541706.352206.062810.39GreatSQL1013.21198.51546.532033.042419.47MySQL751.7986.111218.871778.672065.69QPS8th16th32th64th128thGreatSQL-thdpool21491.4628150.733412744121.256207.88GreatSQL20264.0423969.9730930.5640660.8348389.42MySQL15034.1119722.2724377.4735573.3941313.8
2.3 ibp=144G

TPS8th16th32th64th128thGreatSQL-thdpool1059.461422.721853.242710.313481.66GreatSQL857.281327.671767.782660.83148.06MySQL857.051149.792038.32516.412510.15QPS8th16th32th64th128thGreatSQL-thdpool21189.1728454.337064.7954206.1369633.25GreatSQL17145.5226553.4835355.4753215.8962961.17MySQL17140.9622995.7340765.9550328.2950202.93
2.4 ibp=192G

TPS8th16th32th64th128thGreatSQL1406.861316.022144.174114.553310.67GreatSQL-thdpool1391.21247.932085.814053.763113.97MySQL1367.312629.752940.512687.482797.06QPS8th16th32th64th128thGreatSQL28137.1926320.4342883.458229166213.47GreatSQL-thdpool27823.924958.6841716.1681075.2162279.48MySQL27346.1852595.0158810.1853749.6355941.29
2.5 GreatSQL不同ibp下的数据

GreatSQLTPS8th16th32th64th128thQPS8th16th32th64th128thGreatSQL-thdpool(48G)969.161324.211661.572007.982331.4GreatSQL-thdpool(48G)19383.226484.1433231.4940159.5646627.89GreatSQL(48G)873.061146.851371.341509.81699.19GreatSQL(48G)17461.1622937.1427426.8730196.0233983.78GreatSQL-thdpool(96G)1074.571407.541706.352206.062810.39GreatSQL-thdpool(96G)21491.4628150.733412744121.256207.88GreatSQL(96G)1013.21198.51546.532033.042419.47GreatSQL(96G)20264.0423969.9730930.5640660.8348389.42GreatSQL-thdpool(144G)1059.461422.721853.242710.313481.66GreatSQL-thdpool(144G)21189.1728454.337064.7954206.1369633.25GreatSQL(144G)857.281327.671767.782660.83148.06GreatSQL(144G)17145.5226553.4835355.4753215.8962961.17GreatSQL(192G)1406.861316.022144.174114.553310.67GreatSQL(192G)28137.1926320.4342883.458229166213.47GreatSQL-thdpool(192G)1391.21247.932085.814053.763113.97GreatSQL-thdpool(192G)27823.924958.6841716.1681075.2162279.48
2.6 MySQL不同ibp下的数据

MySQLTPS8th16th32th64th128thQPS8th16th32th64th128thMySQL(48G)686.14846.5915.151073.951439.29MySQL(48G)13722.816929.9418303.032147928785.7MySQL(96G)751.7986.111218.871778.672065.69MySQL(96G)15034.1119722.2724377.4735573.3941313.8MySQL(144G)857.051149.792038.32516.412510.15MySQL(144G)17140.9622995.7340765.9550328.2950202.93MySQL(192G)1367.312629.752940.512687.482797.06MySQL(192G)27346.1852595.0158810.1853749.6355941.29
测试环境&测试模式

3.1 测试工具

sysbench

1. /usr/local/bin/sysbench --version
2. sysbench 1.1.0

复制代码

P.S，该版本是楼方鑫修改后的，增加了99.9%的RT统计值，例如：

1. [ 1s ] thds: 128 tps: 10285.06 qps: 208112.71 (r/w/o: 145769.21/41646.36/20697.15) lat (ms,99%,99%,99.9%): 24.83/24.83/28.67 err/s: 0.00 reconn/s: 0.00
2. [ 2s ] thds: 128 tps: 9968.88 qps: 199013.18 (r/w/o: 139399.13/39676.28/19937.76) lat (ms,99%,99%,99.9%): 20.00/20.00/24.38 err/s: 0.00 reconn/s: 0.00
3. [ 3s ] thds: 128 tps: 10214.11 qps: 204613.28 (r/w/o: 143162.59/41022.47/20428.22) lat (ms,99%,99%,99.9%): 19.29/19.29/23.10 err/s: 0.00 reconn/s: 0.00
4. [ 4s ] thds: 128 tps: 10227.68 qps: 204402.77 (r/w/o: 143127.62/40819.79/20455.37) lat (ms,99%,99%,99.9%): 17.95/17.95/20.00 err/s: 0.00 reconn/s: 0.00
5. [ 5s ] thds: 128 tps: 10466.08 qps: 209233.51 (r/w/o: 146497.06/41804.30/20932.15) lat (ms,99%,99%,99.9%): 19.29/19.29/21.11 err/s: 0.00 reconn/s: 0.00

复制代码

3.2 测试模式

• 利用sysbench生成64个表，每个表1250万条记录。
  
• 数据库总大小约191G。
  
• sysbench采用 oltp_read_write 模式。
  
• innodb_flush_method = O_DIRECT_NO_FSYNC。
  
• GreatSQL在需要时才开启thread pool，MySQL不支持thread pool。
  
• 默认关闭InnoDB PQ。
  
• 因为没有额外测试机，所以采用本地socket方式连接，顺便关闭网络监听设置。
  
• 测试资源有限，所以只测试单机模式，没有开启MGR。
  

3.3 测试机硬件配置

• 最大物理内存：376G，但数据库分配IBP时分别为48G、96G、144G、192G，没有将物理内存全部耗尽。
  
• 磁盘：Dell NVMe SSD
  

1. $ nvme list | grep nvme1
2. /dev/nvme1n1          90L0A019TAHR        
3. Dell Express Flash CD5 3.84T SFF         1     
4. 2.86  TB /   3.84  TB    512   B +  0 B   1.1.1

复制代码

• 文件系统、ioscheduler
  

1. $ df -hT | grep nvme1
2. /dev/nvme1n1p1 xfs       3.5T  2.9T  706G  81% /data_nvme1n1p1
4. $ cat /sys/block/nvme1n1/queue/scheduler
5. [none] mq-deadline kyber bfq

复制代码

• CPU
  

1. Architecture:        x86_64
2. CPU op-mode(s):      32-bit, 64-bit
3. Byte Order:          Little Endian
4. CPU(s):              176
5. On-line CPU(s) list: 0-175
6. Thread(s) per core:  2
7. Core(s) per socket:  22
8. Socket(s):           4
9. NUMA node(s):        4
10. Vendor ID:           GenuineIntel
11. BIOS Vendor ID:      Intel
12. CPU family:          6
13. Model:               85
14. Model name:          Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHz
15. BIOS Model name:     Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHz
16. Stepping:            7
17. CPU MHz:             2800.924
18. CPU max MHz:         3700.0000
19. CPU min MHz:         1000.0000
20. BogoMIPS:            4200.00
21. L1d cache:           32K
22. L1i cache:           32K
23. L2 cache:            1024K
24. L3 cache:            30976K
25. NUMA node0 CPU(s):   0,4,8,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68,72,76,80,84,88,92,96,100,104,108,112,116,120,124,128,132,136,140,144,148,152,156,160,164,168,172
26. NUMA node1 CPU(s):   1,5,9,13,17,21,25,29,33,37,41,45,49,53,57,61,65,69,73,77,81,85,89,93,97,101,105,109,113,117,121,125,129,133,137,141,145,149,153,157,161,165,169,173
27. NUMA node2 CPU(s):   2,6,10,14,18,22,26,30,34,38,42,46,50,54,58,62,66,70,74,78,82,86,90,94,98,102,106,110,114,118,122,126,130,134,138,142,146,150,154,158,162,166,170,174
28. NUMA node3 CPU(s):   3,7,11,15,19,23,27,31,35,39,43,47,51,55,59,63,67,71,75,79,83,87,91,95,99,103,107,111,115,119,123,127,131,135,139,143,147,151,155,159,163,167,171,175
29. Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc cpuid aperfmperf pni pclmulqdq dtes64 monitor ds_cpl smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch cpuid_fault epb cat_l3 cdp_l3 invpcid_single intel_ppin ssbd mba ibrs ibpb stibp ibrs_enhanced fsgsbase tsc_adjust bmi1 avx2 smep bmi2 erms invpcid cqm mpx rdt_a avx512f avx512dq rdseed adx smap clflushopt clwb intel_pt avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 xsaves cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts pku ospke avx512_vnni md_clear flush_l1d arch_capabilities

复制代码

3.4 数据库配置选项参数

1. [mysqld]
2. skip-networking
3. lower_case_table_names = 1
4. character-set-server = UTF8MB4
5. skip_name_resolve = 1
6. default_time_zone = "+8:00"
8. #performance setttings
9. lock_wait_timeout = 3600
10. open_files_limit    = 65535
11. back_log = 1024
12. max_connections = 1024
13. max_connect_errors = 1000000
14. table_open_cache = 1024
15. table_definition_cache = 1024
16. thread_stack = 512K
17. sort_buffer_size = 4M
18. join_buffer_size = 4M
19. read_buffer_size = 8M
20. read_rnd_buffer_size = 4M
21. bulk_insert_buffer_size = 64M
22. thread_cache_size = 768
23. interactive_timeout = 600
24. wait_timeout = 600
25. tmp_table_size = 32M
26. max_heap_table_size = 32M
28. #log settings
29. log_timestamps = SYSTEM
30. log_error = error.log
31. log_error_verbosity = 3
32. slow_query_log = 1
33. log_slow_extra = 1
34. #log_slow_verbosity = FULL
35. slow_query_log_file = slow.log
36. long_query_time = 0.01
37. log_queries_not_using_indexes = 1
38. log_throttle_queries_not_using_indexes = 60
39. min_examined_row_limit = 0
40. log_slow_admin_statements = 1
41. log_slow_slave_statements = 1
42. log_bin = binlog
44. binlog_format = ROW
45. sync_binlog = 1
46. binlog_cache_size = 4M
47. max_binlog_cache_size = 2G
48. max_binlog_size = 1G
49. binlog_rows_query_log_events = 1
50. binlog_expire_logs_seconds = 604800
51. #binlog_expire_logs_auto_purge = 1
52. binlog_checksum = CRC32
53. gtid_mode = ON
54. enforce_gtid_consistency = TRUE
56. #myisam settings
57. key_buffer_size = 32M
58. myisam_sort_buffer_size = 128M
60. #replication settings
61. #master_info_repository = TABLE
62. #relay_log_info_repository = TABLE
63. relay_log_recovery = 1
64. slave_parallel_type = LOGICAL_CLOCK
65. slave_parallel_workers = 2
66. binlog_transaction_dependency_tracking = WRITESET
67. slave_preserve_commit_order = 1
68. slave_checkpoint_period = 2
70. #innodb settings
71. transaction_isolation = REPEATABLE-READ
72. innodb_buffer_pool_size = 2G
73. innodb_buffer_pool_instances = 8
74. innodb_data_file_path = ibdata1:12M:autoextend
75. innodb_flush_log_at_trx_commit = 1
76. innodb_log_buffer_size = 32M
77. innodb_log_file_size = 2G
78. innodb_log_files_in_group = 3
79. innodb_doublewrite_files = 2
80. innodb_max_undo_log_size = 4G
81. innodb_io_capacity = 400000
82. innodb_io_capacity_max = 800000
83. innodb_open_files = 65534
84. #本次测试采用O_DIRECT_NO_FSYNC模式
85. innodb_flush_method = O_DIRECT_NO_FSYNC
86. innodb_lru_scan_depth = 4000
87. innodb_lock_wait_timeout = 10
88. innodb_rollback_on_timeout = 1
89. innodb_print_all_deadlocks = 1
90. innodb_online_alter_log_max_size = 4G
91. innodb_print_ddl_logs = 0
92. innodb_status_file = 1
93. innodb_status_output = 0
94. innodb_status_output_locks = 1
95. innodb_sort_buffer_size = 67108864
96. #innodb_thread_concurrency = 176
97. #innodb_spin_wait_delay = 3
98. #innodb_sync_spin_loops = 10
100. #innodb monitor settings
101. innodb_monitor_enable = "module_innodb"
102. innodb_monitor_enable = "module_server"
103. innodb_monitor_enable = "module_dml"
104. innodb_monitor_enable = "module_ddl"
105. innodb_monitor_enable = "module_trx"
106. innodb_monitor_enable = "module_os"
107. innodb_monitor_enable = "module_purge"
108. innodb_monitor_enable = "module_log"
109. innodb_monitor_enable = "module_lock"
110. innodb_monitor_enable = "module_buffer"
111. innodb_monitor_enable = "module_index"
112. innodb_monitor_enable = "module_ibuf_system"
113. innodb_monitor_enable = "module_buffer_page"
114. innodb_monitor_enable = "module_adaptive_hash"
116. #pfs settings
117. performance_schema = 1
118. #performance_schema_instrument = '%memory%=on'
119. performance_schema_instrument = '%lock%=on'
121. #thread pool，需要开启thread pool时才取消下面两行注释
122. #thread_handling = 'pool-of-thread'
123. #thread_pool_stall_limit = 50

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！