GreatSQL vs MySQL性能测试来了,速围观~ - GreatSQL
source link: https://www.cnblogs.com/greatsql/p/16687033.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
- 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性能数据对比
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=48G
TPS | 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=96G
TPS | 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=144G
TPS | 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=192G
TPS | 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 --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
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
Recommend
-
128
围观乌镇大佬:丁磊竟然潮过李彦宏?谁的身材又保持的最好?围观乌镇大佬:丁磊竟然潮过李彦宏?谁的身材又保持的最好?2017-12-03 19:10话说,大周末的,可是苦煞了好多媒...
-
45
的确写Proxy文章很多,那么今天我也不凑字数了,炒两个栗子吧。 一、虚拟属性 const person = { name: 'xiaoyun', province: '江苏省', city: '南京市' } 对于上述对象,我
-
43
吃瓜群众强势围观
-
63
8 月 18 – 19 日,微博和新浪娱乐在北京举办了一场微博粉丝嘉年华,杨洋、马思纯、陈乔恩、王凯、朱一龙、NINEPERCENT、火箭少女等偶像纷纷出席,各家粉丝也从全国各地赶来。
-
48
作者|极客公园 谁是大英雄从《英雄联盟》到《堡垒之夜》,从《战神》到《底特律:变人》,你知道它们的共同点吗?这些不同公司制作的不同游戏几乎找不到任何相似之处,它们有的是网络游戏,有的是单机游戏,有动作类游戏,也有MOBA类游戏,但
-
7
容器技术改变了应用交付、运行的方式,几乎各种Linux环境下的应用程序都可以使用容器来运行。但是否能在容器环境里运行数据库应用,以及数据库应用是否适合在容器里运行,一直都是大家很关注的问题,今天我们就来深入分析一下容器环境运行MySQL数据库的事。...
-
4
有趣的特性:CHECK约束 在MySQL 8.0.16以前, CREATE TABLE允许从语法层面输入下列CHECK约束,但实际没有效果: CHECK (expr) 在 MySQL 8.0.16,CREATE TABLE
-
7
GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。 GreatSQL是MySQL的国产分支版本,使用上与MySQL一致。 一. XDP Socket示例解析 源码参见:
-
6
LIMIT和OFFSET分页性能差!今天来介绍如何高性能分页
-
7
带你轻松玩转MySQL Shell for GreatSQL 作者:KAiTO 2023-08-18 14:39:02 MySQL Shell 是 MySQL 的一个高级客户端和代码编辑器,是第二代 MySQL 客户端。第一代 MySQL 客户端即我们常用的 MySQL 。 1.1...
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK