[原] KVM 环境下MySQL性能对比

2022-03-07

标签（空格分隔）：.0

目录

测试目的

比较MySQL在物理机和KVM环境的性能

压力测试标准

压力测试遵循单一变量的原则，所有的比较都是在只改变一个变量的前提下进行的

测试方法

以物理机MySQL为基准，分别做两个测试

测试IO相关参数(,flush)测试CPU相关参数(NUMA)测试环境

CPU:Intel(R) Xeon(R) CPU E5-2620 v2 @ 2.10GHz X 24
MEM:48G
Disk:SSD 1.3T
System:Ubuntu 14.04.4 LTS
Kernel:3.16.0-30-generic
MySQL:mysql-5.5.31-linux2.6-x86_64
Sysbench:0.4.12
KVM:QEMU emulator version 2.0.0 (Debian 2.0.0+dfsg-2ubuntu1.22)

测试变量

由于相关资料，IO模式可以保证数据的一致性，所以在MySQL环境下，默认只有测试环境

p>

基于开启NUMA的物理机环境，在KVM环境下测试以下变量：

缓存模式下的io(,)KVM主机NUMA影响MySQL性能测试软件环境

配置模板如下（仅列出关键参数）

# The MySQL server
[mysqld]
default-storage-engine = innodb
# MyISAM setup
key_buffer_size = 128M
myisam_sort_buffer_size = 64M
## gloabl config
max_allowed_packet = 16M
max_heap_table_size = 64M
tmp_table_size = 8M
max_connections = 4000
open_files_limit = 6000
table_open_cache = 512
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 256K
sort_buffer_size = 2M
thread_cache_size = 8
query_cache_size = 0
thread_concurrency = 16
# Replication Master setup
log-bin = mysql-bin
relay-log = mysqld-relay-bin
max_binlog_size = 100M
binlog_format = row
binlog_cache_size=32K
thread_stack=262144
auto_increment_increment = 3
auto_increment_offset = 1
# Logging
slow_query_log  = 1
long_query_time = 2
# InnoDB setup
innodb_file_format = Barracuda
innodb_file_per_table
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 16M
innodb_log_buffer_size = 40M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_log_files_in_group=2
innodb_io_capacity=2000
[mysqldump]
quick
extended-insert = false
default-character-set = utf8
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

KVM-qemu的配置如下：

<domain type='kvm'>
	mysql1
	5120
	5120
	4
	
		hvm
		
	
	
		
		
		
	
	
	destroy
	restart
	restart
	
		/usr/bin/kvm-spice
		
			
			
			
		
		
			
			
			
		
		
			
			
		
		
			
		
		
			
		
	

测试基准

测试以物理机的MySQL实例为参考

默认物理机的MySQL使用4G+4Core并关闭NUMA

基准数据

=

OLTP test statistics:
    queries performed:
        read:                            14000028
        write:                           5000010
        other:                           2000004
        total:                           21000042
    transactions:                        1000002 (1375.45 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000038 (26133.48 per sec.)
    other operations:                    2000004 (2750.89 per sec.)
Test execution summary:
    total time:                          727.0382s
    total number of events:              1000002
    total time taken by event execution: 17443.5464
    per-request statistics:
         min:                                  1.78ms
         avg:                                 17.44ms
         max:                               1048.03ms
         approx.  95 percentile:              32.64ms
Threads fairness:
    events (avg/stddev):           41666.7500/646.28
    execution time (avg/stddev):   726.8144/0.00

off= ，使用默认值

OLTP test statistics:
    queries performed:
        read:                            14000028
        write:                           5000010
        other:                           2000004
        total:                           21000042
    transactions:                        1000002 (1390.26 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000038 (26414.92 per sec.)
    other operations:                    2000004 (2780.52 per sec.)
Test execution summary:
    total time:                          719.2920s
    total number of events:              1000002
    total time taken by event execution: 17257.6867
    per-request statistics:
         min:                                  1.78ms
         avg:                                 17.26ms
         max:                               1476.86ms
         approx.  95 percentile:              32.76ms
Threads fairness:
    events (avg/stddev):           41666.7500/709.66
    execution time (avg/stddev):   719.0703/0.00

基准数据分析

以物理机MySQL实例为例，对MySQL性能有一定影响

测试结果为第一次压力测试，在KVM环境（单变量）

简单的虚拟机（kvm）压力测试，=

打开Numa，kvm缓存模式改为

KVM 配置:
    CPU = 4 core
    Mem = 5 G
    MySQL = 4G
    Cache = writethrough
MySQL 配置:
    Mem = 4G
    Innodb_flush_method = O_DIRECT

p>

=

sysbench --test=oltp --oltp-table-size=1000000  --mysql-db=test --max-requests=1000000 --num-threads=24 --mysql-host=192.168.100.244 --mysql-user=test run
OLTP test statistics:
    queries performed:
        read:                            14000042
        write:                           5000015
        other:                           2000006
        total:                           21000063
    transactions:                        1000003 (1041.22 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000057 (19783.20 per sec.)
    other operations:                    2000006 (2082.44 per sec.)
Test execution summary:
    total time:                          960.4138s
    total number of events:              1000003
    total time taken by event execution: 23044.1587
    per-request statistics:
         min:                                  3.43ms
         avg:                                 23.04ms
         max:                                958.60ms
         approx.  95 percentile:              43.71ms
Threads fairness:
    events (avg/stddev):           41666.7917/865.32
    execution time (avg/stddev):   960.1733/0.01

= ()

sysbench 0.4.12:  multi-threaded system evaluation benchmark
OLTP test statistics:
    queries performed:
        read:                            14000042
        write:                           5000015
        other:                           2000006
        total:                           21000063
    transactions:                        1000003 (1025.90 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000057 (19492.01 per sec.)
    other operations:                    2000006 (2051.79 per sec.)
Test execution summary:
    total time:                          974.7614s
    total number of events:              1000003
    total time taken by event execution: 23388.1224
    per-request statistics:
         min:                                  3.75ms
         avg:                                 23.39ms
         max:                               1306.42ms
         approx.  95 percentile:              44.38ms
Threads fairness:
    events (avg/stddev):           41666.7917/863.10
    execution time (avg/stddev):   974.5051/0.01

第一次压力测试总结

从压测报告来看，在开启kvm的前提下，MySQL效率更高

使用kvm，MySQL的性能是物理机的75%左右

p>

纵坐标为总执行时间

IO模式的推荐优化方法

在主机开启的前提下，配置=有效提升MySQL性能

大约 97% 的性能在物理机模式下

第二次压力测试，KVM环境（单变量numa）

简单的虚拟机（kvm）压力测试，打开numa

关闭主机Numa，将kvm缓存模式改为

=

OLTP test statistics:
    queries performed:
        read:                            14000014
        write:                           5000005
        other:                           2000002
        total:                           21000021
    transactions:                        1000001 (1068.76 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000019 (20306.35 per sec.)
    other operations:                    2000002 (2137.51 per sec.)
Test execution summary:
    total time:                          935.6690s
    total number of events:              1000001
    total time taken by event execution: 22450.9403
    per-request statistics:
         min:                                  3.51ms
         avg:                                 22.45ms
         max:                               1170.10ms
         approx.  95 percentile:              41.65ms
Threads fairness:
    events (avg/stddev):           41666.7083/855.51
    execution time (avg/stddev):   935.4558/0.01

=

OLTP test statistics:
    queries performed:
        read:                            14000042
        write:                           5000015
        other:                           2000006
        total:                           21000063
    transactions:                        1000003 (1062.79 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 19000057 (20193.07 per sec.)
    other operations:                    2000006 (2125.59 per sec.)
Test execution summary:
    total time:                          940.9197s
    total number of events:              1000003
    total time taken by event execution: 22577.0003
    per-request statistics:
         min:                                  3.36ms
         avg:                                 22.58ms
         max:                                756.58ms
         approx.  95 percentile:              41.50ms
Threads fairness:
    events (avg/stddev):           41666.7917/943.69
    execution time (avg/stddev):   940.7083/0.01

二次压力测试总结

开启NUMA绑定后，性能下降约3%

CPU优化建议

禁用 NUMA 绑定

为什么不使用多个实例进行高负载压力测试？

测试过程中，实例的CPU可以跑到对应的核上，对应的CPU满载

为什么 NUMA 对性能有如此大的影响？

猜测vCPU的多个线程可能位于不同的CPU节点，导致内存跨节点访问。不清楚vCPU是否会产生这样的调度，但是关闭NUMA不会导致。

有图片解释不同的 kvm 缓存吗？

分类：

技术要点：

相关文章：