关于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 --versionsysbench 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_64CPU op-mode(s):      32-bit, 64-bitByte Order:          Little EndianCPU(s):              176On-line CPU(s) list: 0-175Thread(s) per core:  2Core(s) per socket:  22Socket(s):           4NUMA node(s):        4Vendor ID:           GenuineIntelBIOS Vendor ID:      IntelCPU family:          6Model:               85Model name:          Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHzBIOS Model name:     Intel(R) Xeon(R) Gold 6238 CPU @ 2.10GHzStepping:            7CPU MHz:             2800.924CPU max MHz:         3700.0000CPU min MHz:         1000.0000BogoMIPS:            4200.00L1d cache:           32KL1i cache:           32KL2 cache:            1024KL3 cache:            30976KNUMA 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,172NUMA 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,173NUMA 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,174NUMA 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,175Flags:               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-networkinglower_case_table_names = 1character-set-server = UTF8MB4skip_name_resolve = 1default_time_zone = "+8:00"#performance setttingslock_wait_timeout = 3600open_files_limit    = 65535back_log = 1024max_connections = 1024max_connect_errors = 1000000table_open_cache = 1024table_definition_cache = 1024thread_stack = 512Ksort_buffer_size = 4Mjoin_buffer_size = 4Mread_buffer_size = 8Mread_rnd_buffer_size = 4Mbulk_insert_buffer_size = 64Mthread_cache_size = 768interactive_timeout = 600wait_timeout = 600tmp_table_size = 32Mmax_heap_table_size = 32M#log settingslog_timestamps = SYSTEMlog_error = error.loglog_error_verbosity = 3slow_query_log = 1log_slow_extra = 1#log_slow_verbosity = FULLslow_query_log_file = slow.loglong_query_time = 0.01log_queries_not_using_indexes = 1log_throttle_queries_not_using_indexes = 60min_examined_row_limit = 0log_slow_admin_statements = 1log_slow_slave_statements = 1log_bin = binlogbinlog_format = ROWsync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 2Gmax_binlog_size = 1Gbinlog_rows_query_log_events = 1binlog_expire_logs_seconds = 604800#binlog_expire_logs_auto_purge = 1binlog_checksum = CRC32gtid_mode = ONenforce_gtid_consistency = TRUE#myisam settingskey_buffer_size = 32Mmyisam_sort_buffer_size = 128M#replication settings#master_info_repository = TABLE#relay_log_info_repository = TABLErelay_log_recovery = 1slave_parallel_type = LOGICAL_CLOCKslave_parallel_workers = 2binlog_transaction_dependency_tracking = WRITESETslave_preserve_commit_order = 1slave_checkpoint_period = 2#innodb settingstransaction_isolation = REPEATABLE-READinnodb_buffer_pool_size = 2Ginnodb_buffer_pool_instances = 8innodb_data_file_path = ibdata1:12M:autoextendinnodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 3innodb_doublewrite_files = 2innodb_max_undo_log_size = 4Ginnodb_io_capacity = 400000innodb_io_capacity_max = 800000innodb_open_files = 65534#本次测试采纳O_DIRECT_NO_FSYNC模式innodb_flush_method = O_DIRECT_NO_FSYNCinnodb_lru_scan_depth = 4000innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_online_alter_log_max_size = 4Ginnodb_print_ddl_logs = 0innodb_status_file = 1innodb_status_output = 0innodb_status_output_locks = 1innodb_sort_buffer_size = 67108864#innodb_thread_concurrency = 176#innodb_spin_wait_delay = 3#innodb_sync_spin_loops = 10#innodb monitor settingsinnodb_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 settingsperformance_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社区助手微信好友，发送验证信息加群。