GreatSQL vs MySQL 性能测试来了，速围观~
作者：Java-fenn
- 2022 年 9 月 12 日 湖南
本文字数：3998 字
阅读完需：约 13 分钟
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 性能数据对比
各数据库版本分别为
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 = binlog
binlog_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
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 3
Java-fenn
关注
需要Java资料或者咨询可加我v ： Jimbye 2022.08.16 加入
还未添加个人简介
评论