1.结论先行无论ibp（innodb_buffer_pool_size）是试学否充足，MySQL的试学性能都远不如GreatSQL。MySQL的试学性能平均约为GreatSQL的70%（最高84.5%，最低61.7%）。试学在ibp充分的试学情况下，随着并发数的试学增加，MySQL并没有表现出该有的试学性能提升，反倒掉头向下，试学可见还是试学不够稳定。在ibp不够的试学情况下，IT技术网GreatSQL开启thread pool性能有所提升；当ibp充足的试学情况下，区别就不大了。试学MySQL vs GreatSQL性能数据对比 48G 96G 144G 192G avg MySQL vs GreatSQL 0.6173 0.735 0.721 0.8449 0.7295 各数据库版本分别为 MySQL 8.0.30 MySQL Community Server - GPL GreatSQL 8.0.25-16 GreatSQL,试学 Release 16, Revision 8bb0e5af297 2.测试结果数据2.1 ibp=48GTPS 8th 16th 32th 64th 128th GreatSQL-thdpool 969.16 1324.21 1661.57 2007.98 2331.4 GreatSQL 873.06 1146.85 1371.34 1509.8 1699.19 MySQL 686.14 846.5 915.15 1073.95 1439.29 P.S，后缀加上 thdpool 表示启用了thread pool。试学 QPS 8th 16th 32th 64th 128th GreatSQL-thdpool 19383.2 26484.14 33231.49 40159.56 46627.89 GreatSQL 17461.16 22937.14 27426.87 30196.02 33983.78 MySQL 13722.8 16929.94 18303.03 21479 28785.7 
2.2 ibp=96GTPS 8th 16th 32th 64th 128th GreatSQL-thdpool 1074.57 1407.54 1706.35 2206.06 2810.39 GreatSQL 1013.2 1198.5 1546.53 2033.04 2419.47 MySQL 751.7 986.11 1218.87 1778.67 2065.69 QPS 8th 16th 32th 64th 128th GreatSQL-thdpool 21491.46 28150.73 34127 44121.2 56207.88 GreatSQL 20264.04 23969.97 30930.56 40660.83 48389.42 MySQL 15034.11 19722.27 24377.47 35573.39 41313.8 
2.3 ibp=144GTPS 8th 16th 32th 64th 128th GreatSQL-thdpool 1059.46 1422.72 1853.24 2710.31 3481.66 GreatSQL 857.28 1327.67 1767.78 2660.8 3148.06 MySQL 857.05 1149.79 2038.3 2516.41 2510.15 QPS 8th 16th 32th 64th 128th GreatSQL-thdpool 21189.17 28454.3 37064.79 54206.13 69633.25 GreatSQL 17145.52 26553.48 35355.47 53215.89 62961.17 MySQL 17140.96 22995.73 40765.95 50328.29 50202.93 
2.4 ibp=192GTPS 8th 16th 32th 64th 128th GreatSQL 1406.86 1316.02 2144.17 4114.55 3310.67 GreatSQL-thdpool 1391.2 1247.93 2085.81 4053.76 3113.97 MySQL 1367.31 2629.75 2940.51 2687.48 2797.06 QPS 8th 16th 32th 64th 128th GreatSQL 28137.19 26320.43 42883.45 82291 66213.47 GreatSQL-thdpool 27823.9 24958.68 41716.16 81075.21 62279.48 MySQL 27346.18 52595.01 58810.18 53749.63 55941.29 
2.5 GreatSQL不同ibp下的试学数据GreatSQL TPS 8th 16th 32th 64th 128th QPS 8th 16th 32th 64th 128th GreatSQL-thdpool(48G) 969.16 1324.21 1661.57 2007.98 2331.4 GreatSQL-thdpool(48G) 19383.2 26484.14 33231.49 40159.56 46627.89 GreatSQL(48G) 873.06 1146.85 1371.34 1509.8 1699.19 GreatSQL(48G) 17461.16 22937.14 27426.87 30196.02 33983.78 GreatSQL-thdpool(96G) 1074.57 1407.54 1706.35 2206.06 2810.39 GreatSQL-thdpool(96G) 21491.46 28150.73 34127 44121.2 56207.88 GreatSQL(96G) 1013.2 1198.5 1546.53 2033.04 2419.47 GreatSQL(96G) 20264.04 23969.97 30930.56 40660.83 48389.42 GreatSQL-thdpool(144G) 1059.46 1422.72 1853.24 2710.31 3481.66 GreatSQL-thdpool(144G) 21189.17 28454.3 37064.79 54206.13 69633.25 GreatSQL(144G) 857.28 1327.67 1767.78 2660.8 3148.06 GreatSQL(144G) 17145.52 26553.48 35355.47 53215.89 62961.17 GreatSQL(192G) 1406.86 1316.02 2144.17 4114.55 3310.67 GreatSQL(192G) 28137.19 26320.43 42883.45 82291 66213.47 GreatSQL-thdpool(192G) 1391.2 1247.93 2085.81 4053.76 3113.97 GreatSQL-thdpool(192G) 27823.9 24958.68 41716.16 81075.21 62279.48 
2.6 MySQL不同ibp下的数据MySQL TPS 8th 16th 32th 64th 128th QPS 8th 16th 32th 64th 128th MySQL(48G) 686.14 846.5 915.15 1073.95 1439.29 MySQL(48G) 13722.8 16929.94 18303.03 21479 28785.7 MySQL(96G) 751.7 986.11 1218.87 1778.67 2065.69 MySQL(96G) 15034.11 19722.27 24377.47 35573.39 41313.8 MySQL(144G) 857.05 1149.79 2038.3 2516.41 2510.15 MySQL(144G) 17140.96 22995.73 40765.95 50328.29 50202.93 MySQL(192G) 1367.31 2629.75 2940.51 2687.48 2797.06 MySQL(192G) 27346.18 52595.01 58810.18 53749.63 55941.29 
测试环境&测试模式3.1 测试工具sysbench 复制/usr/local/bin/sysbench --versionsysbench 1.1.01.2. 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.001.2.3.4.5. 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 90 L0A019TAHR Dell Express Flash CD5 3.84T SFF 12.86 TB / 3.84 TB 512 B + 0 B 1.1.11.2.3.4. 文件系统、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 bfq1.2.3.4.5. CPU 复制Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bitByte Order: Little Endian CPU(s): 176On-line CPU(s) list: 0-175Thread(s) per core: 2Core(s) per socket: 22Socket(s): 4NUMA node(s): 4Vendor ID: GenuineIntel BIOS Vendor ID: Intel CPU family: 6Model: 85Model name: Intel(R) Xeon(R) Gold 6238 CPU @ 2.10 GHz BIOS Model name: Intel(R) Xeon(R) Gold 6238 CPU @ 2.10 GHz Stepping: 7CPU MHz: 2800.924CPU max MHz: 3700.0000CPU min MHz: 1000.0000BogoMIPS: 4200.00L1d cache: 32 K L1i cache: 32 K L2 cache: 1024 K L3 cache: 30976 K 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,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_capabilities1.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29. 3.4 数据库配置选项参数 复制[mysqld]skip- networking lower_case_table_names = 1character-set-server = UTF8MB4 skip_name_resolve = 1default_time_zone = "+8:00" #performance setttings lock_wait_timeout = 3600open_files_limit = 65535back_log = 1024max_connections = 1024max_connect_errors = 1000000table_open_cache = 1024table_definition_cache = 1024thread_stack = 512 K sort_buffer_size = 4 M join_buffer_size = 4 M read_buffer_size = 8 M read_rnd_buffer_size = 4 M bulk_insert_buffer_size = 64 M thread_cache_size = 768interactive_timeout = 600wait_timeout = 600tmp_table_size = 32 M max_heap_table_size = 32 M #log settings log_timestamps = SYSTEM log_error = error.loglog_error_verbosity = 3slow_query_log = 1log_slow_extra = 1#log_slow_verbosity = FULL slow_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 = ROW sync_binlog = 1binlog_cache_size = 4 M max_binlog_cache_size = 2 G max_binlog_size = 1 G binlog_rows_query_log_events = 1binlog_expire_logs_seconds = 604800#binlog_expire_logs_auto_purge = 1binlog_checksum = CRC32 gtid_mode = ONenforce_gtid_consistency = TRUE #myisam settings key_buffer_size = 32 M myisam_sort_buffer_size = 128 M #replication settings #master_info_repository = TABLE#relay_log_info_repository = TABLErelay_log_recovery = 1slave_parallel_type = LOGICAL_CLOCK slave_parallel_workers = 2binlog_transaction_dependency_tracking = WRITESET slave_preserve_commit_order = 1slave_checkpoint_period = 2 #innodb settings transaction_isolation = REPEATABLE- READ innodb_buffer_pool_size = 2 G innodb_buffer_pool_instances = 8innodb_data_file_path = ibdata1:12M: autoextend innodb_flush_log_at_trx_commit = 1innodb_log_buffer_size = 32 M innodb_log_file_size = 2 G innodb_log_files_in_group = 3innodb_doublewrite_files = 2innodb_max_undo_log_size = 4 G innodb_io_capacity = 400000innodb_io_capacity_max = 800000innodb_open_files = 65534 #本次测试采用O_DIRECT_NO_FSYNC模式 innodb_flush_method = O_DIRECT_NO_FSYNC innodb_lru_scan_depth = 4000innodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_online_alter_log_max_size = 4 G innodb_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 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%=onperformance_schema_instrument = %lock%=on #thread pool，需要开启thread pool时才取消下面两行注释 #thread_handling = pool-of-thread#thread_pool_stall_limit = 501.2.3.4.5.6.7.8.9.10.11.12.13.14.15.16.17.18.19.20.21.22.23.24.25.26.27.28.29.30.31.32.33.34.35.36.37.38.39.40.41.42.43.44.45.46.47.48.49.50.51.52.53.54.55.56.57.58.59.60.61.62.63.64.65.66.67.68.69.70.71.72.73.74.75.76.77.78.79.80.81.82.83.84.85.86.87.88.89.90.91.92.93.94.95.96.97.98.99.100.101.102.103.104.105.106.107.108.109.110.111.112.113.114.115.116.117.118.119.120.121.122.123.企商汇 |
喜欢
讨厌