关于mysql:GreatSQL-vs-MySQL性能测试来了速围观

• GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
• GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。

1. 论断后行

1. 无论 ibp（innodb_buffer_pool_size）是否短缺，MySQL 的性能都远不如 GreatSQL。
2. MySQL 的性能均匀约为 GreatSQL 的 70%（最高 84.5%，最低 61.7%）。
3. 在 ibp 充沛的状况下，随着并发数的减少，MySQL 并没有体现出该有的性能晋升，反倒掉头向下，可见还是不够稳固。
4. 在 ibp 不够的状况下，GreatSQL 开启 thread pool 性能有所晋升；当 ibp 短缺的状况下，区别就不大了。

MySQL vs GreatSQL 性能数据比照

各数据库版本别离为

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

2. 测试后果数据

2.1 ibp=48G

P.S，后缀加上 thdpool 示意启用了thread pool。

2.2 ibp=96G

2.3 ibp=144G

2.4 ibp=192G

2.5 GreatSQL 不同 ibp 下的数据

2.6 MySQL 不同 ibp 下的数据

测试环境 & 测试模式

3.1 测试工具

sysbench

/usr/local/bin/sysbench --version
sysbench 1.1.0

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

[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
[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
[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
[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
[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

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

• 文件系统、ioscheduler

$ df -hT | grep nvme1
/dev/nvme1n1p1 xfs       3.5T  2.9T  706G  81% /data_nvme1n1p1

$ cat /sys/block/nvme1n1/queue/scheduler
[none] mq-deadline kyber bfq

• CPU

Architecture:        x86_64
CPU op-mode(s):      32-bit, 64-bit
Byte Order:          Little Endian
CPU(s):              176
On-line CPU(s) list: 0-175
Thread(s) per core:  2
Core(s) per socket:  22
Socket(s):           4
NUMA node(s):        4
Vendor ID:           GenuineIntel
BIOS Vendor ID:      Intel
CPU family:          6
Model:               85
Model name:          Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHz
BIOS Model name:     Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHz
Stepping:            7
CPU MHz:             2800.924
CPU max MHz:         3700.0000
CPU min MHz:         1000.0000
BogoMIPS:            4200.00
L1d cache:           32K
L1i cache:           32K
L2 cache:            1024K
L3 cache:            30976K
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
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
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
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
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 数据库配置选项参数

[mysqld]
skip-networking
lower_case_table_names = 1
character-set-server = UTF8MB4
skip_name_resolve = 1
default_time_zone = "+8:00"

#performance setttings
lock_wait_timeout = 3600
open_files_limit    = 65535
back_log = 1024
max_connections = 1024
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
thread_stack = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M

#log settings
log_timestamps = SYSTEM
log_error = error.log
log_error_verbosity = 3
slow_query_log = 1
log_slow_extra = 1
#log_slow_verbosity = FULL
slow_query_log_file = slow.log
long_query_time = 0.01
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin = binlog

binlog_format = ROW
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G
binlog_rows_query_log_events = 1
binlog_expire_logs_seconds = 604800
#binlog_expire_logs_auto_purge = 1
binlog_checksum = CRC32
gtid_mode = ON
enforce_gtid_consistency = TRUE

#myisam settings
key_buffer_size = 32M
myisam_sort_buffer_size = 128M

#replication settings
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
relay_log_recovery = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 2
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1
slave_checkpoint_period = 2

#innodb settings
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_doublewrite_files = 2
innodb_max_undo_log_size = 4G
innodb_io_capacity = 400000
innodb_io_capacity_max = 800000
innodb_open_files = 65534
#本次测试采纳 O_DIRECT_NO_FSYNC 模式
innodb_flush_method = O_DIRECT_NO_FSYNC
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 0
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_sort_buffer_size = 67108864
#innodb_thread_concurrency = 176
#innodb_spin_wait_delay = 3
#innodb_sync_spin_loops = 10

#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
innodb_monitor_enable = "module_adaptive_hash"

#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#thread pool，须要开启 thread pool 时才勾销上面两行正文
#thread_handling = 'pool-of-thread'
#thread_pool_stall_limit = 50

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区：

欢送来 GreatSQL 社区发帖发问
https://greatsql.cn/

技术交换群：

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。