环境
mysql5.7+centos6+sysbench
常用命令
[root@centos1 ~]# uname -a
Linux 内核名称
centos1 主机名称
2.6.32-431.el6.x86_64 内核版本号
#1 SMP Fri Nov 22 03:15:09 UTC 2013
x86_64 处理器
x86_64 硬件平台类型
x86_64
GNU/Linux 操作系统名称
[root@centos1 ~]# head -n 1 /etc/issue
CentOS release 6.5 (Final) 系统版本
//我的只是一个虚拟机，有些参数可能不正常
[root@centos1 ~]# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 60
model name : Intel(R) Core(TM) i3-4160 CPU @ 3.60GHz
stepping : 3
cpu MHz : 3591.769
cache size : 3072 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc up arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm arat epb xsaveopt pln pts dts fsgsbase bmi1 avx2 smep bmi2 invpcid
bogomips : 7183.53
clflush size : 64
cache_alignment : 64
address sizes : 42 bits physical, 48 bits virtual
power management:查看系统磁盘空间
参数 -b、-k、-m、-g 分别代表以 字节、K字节、M字节、G字节为单位。
[root@centos1 ~]# free -m
total used free shared buffers cached
Mem: 980 349 631 0 23 132
-/+ buffers/cache: 193 787
Linux下清理内存和Cache方法 /proc/sys/vm/drop_caches，默认值是0
To free pagecache:
* echo 1 > /proc/sys/vm/drop_caches
To free dentries and inodes:
* echo 2 > /proc/sys/vm/drop_caches
To free pagecache, dentries and inodes:
* echo 3 > /proc/sys/vm/drop_caches
Swap: 1983 0 1983
SWAP就是LINUX下的虚拟内存分区,它的作用是在物理内存使用完之后,将磁盘空间(也就是SWAP分区)虚拟成内存来使用.它和Windows系统的交换文件作用类似，但是它是一段连续的磁盘空间，并且对用户不可见。
需要注意的是,虽然这个SWAP分区能够作为"虚拟"的内存,但它的速度比物理内存可是慢多了,因此如果需要更快的速度的话,并不能寄厚望于SWAP,最好的办法仍然是加大物理内存.SWAP分区只是临时的解决办法.
交换分区（swap）的合理值一般在内存的2 倍左右，可以适当加大。实际上具体还是以实际应用为准df -h 和df -i显示的占用率差别大
[root@centos1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 28G 4.3G 22G 17% /
tmpfs 491M 0 491M 0% /dev/shm
/dev/sda1 291M 34M 242M 13% /boot
[root@centos1 ~]# df -i
Filesystem Inodes IUsed IFree IUse% Mounted on
/dev/sda2 1823248 109709 1713539 7% /
tmpfs 125551 1 125550 1% /dev/shm
/dev/sda1 76912 38 76874 1% /boot
原因：删除了一些文件，但是运行的程序仍然占用着过期的文件句柄，导致不一样
[root@centos1 ~]# lsof / | grep deleted
mysqld 3123 mysql 4u REG 8,2 0 1446090 /tmp/ibaboxht (deleted)
mysqld 3123 mysql 5u REG 8,2 0 1446092 /tmp/iba9jBR9 (deleted)
mysqld 3123 mysql 6u REG 8,2 0 1446093 /tmp/ib6oOFrQ (deleted)
mysqld 3123 mysql 7u REG 8,2 0 1446095 /tmp/ibbF8cDd (deleted)
mysqld 3123 mysql 11u REG 8,2 0 1446096 /tmp/ibzrMOhU (deleted)查看指定文件大小
[root@centos1 ~]# du -sh /usr/local/mysql/
816M /usr/local/mysql/查看链接的用户数量
[root@centos1 ~]# uptime
05:55:49 up 3:04, 3 users, load average: 0.00, 0.00, 0.00
[root@centos1 ~]# date -s '2016-11-09 13:56:00'
Wed Nov 9 13:56:00 PST 2016
[root@centos1 ~]# clock -w定时任务组件安装
[root@CentOS ~]# yum -y install vixie-cron
[root@CentOS ~]# yum -y install crontabs
说明：
vixie-cron 软件包是 cron 的主程序；
crontabs 软件包是用来安装、卸装、或列举用来驱动 cron 守护进程的表格的程序。sysbench使用
sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况。关于这个项目的详细介绍请看：http://sysbench.sourceforge.net。
它主要包括以下几种方式的测试：
1、cpu性能
2、磁盘io性能
3、调度程序性能
4、内存分配及传输速度
5、POSIX线程性能
6、数据库性能(OLTP基准测试)
安装
文件传输组件安装
sz/rz
yum install lrzsz下载sysbench
yum install -y automake
//下载[libtool](ftp://ftp.gnu.org/gnu/libtool/libtool-1.4.3.tar.gz)
tar -zvxf libtool-1.4.3.tar.gz
./configure --prefix=/usr/local/libtool && make && make install安装sysbench
./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/
make && make install使用
CPU测试
测试素数的加法，所有计算都会采用64位整数
//增加sysbench环境变量
vim /etc/profile
[root@centos1 sysbench]#sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run
libmysqlclient.so.18 not found可是我在mysql/lib下面明明看到这个文件为什么不行呢？详见压测问题动态链接库问题
[root@centos1 sysbench]#sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 4
Random number generator seed is 0 and will be ignored
Doing CPU performance benchmark
Primer numbers limit: 20000
Threads started!
Done.
General statistics:
total time: 20.2200s
total number of events: 10000
total time taken by event execution: 80.8197
response time:
min: 1.88ms
avg: 8.08ms
max: 48.07ms
approx. 95 percentile: 11.54ms
Threads fairness:
events (avg/stddev): 2500.0000/3.39
execution time (avg/stddev): 20.2049/0.01注意: 服务器类型,有偏运算型的,有偏存储, 所需要的指标不一样.偏运算的(如视频转码服务器)要求CPU强,而存储则优先选择大容量和快速存储备.
测试的数据,孤立起来看,是没有意义的.数据要有比较才有意义, 比如多台服务器的测试数据,比较CPU性能.
IO性能测试
测试文件读取速度，这个速度跟mysql数据查询直接挂钩
解释:针对1G文件,做随机读写,测试IO
–file-test-mode 还可以为
seqwr：顺序写入
seqrewq：顺序重写
seqrd：顺序读取
rndrd：随机读取
rndwr：随机写入
rndrw：混合随机读写
[root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G prepare
Creating file test_file.127
1073741824 bytes written in 46.61 seconds (21.97 MB/sec).
//给我创建了127个8M的小文件在sysbench目录下 ls -h
//顺序读
[root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G --file-test-mode=seqrd run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Extra file open flags: 0
128 files, 8Mb each
1Gb total file size
Block size 16Kb
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing sequential read test
Threads started!
Done.
Operations performed: 65536 reads, 0 writes, 0 Other = 65536 Total
Read 1Gb Written 0b Total transferred 1Gb (261.94Mb/sec)
16764.17 Requests/sec executed
General statistics:
total time: 3.9093s
total number of events: 65536
total time taken by event execution: 3.8426
response time:
min: 0.00ms
avg: 0.06ms
max: 35.02ms
approx. 95 percentile: 0.46ms
Threads fairness:
events (avg/stddev): 65536.0000/0.00
execution time (avg/stddev): 3.8426/0.00
//随机读
[root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G --file-test-mode=rndrd run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Extra file open flags: 0
128 files, 8Mb each
1Gb total file size
Block size 16Kb
Number of random requests for random IO: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Done.
Operations performed: 10000 reads, 0 writes, 0 Other = 10000 Total
Read 156.25Mb Written 0b Total transferred 156.25Mb (8.1738Mb/sec)
523.13 Requests/sec executed
General statistics:
total time: 19.1159s
total number of events: 10000
total time taken by event execution: 19.0684
response time:
min: 0.00ms
avg: 1.91ms
max: 57.75ms
approx. 95 percentile: 9.42ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 19.0684/0.00顺序读取262M/S随机读取8M/S，相差32倍！！mysql中合理的索引（后面文章将会跟上解释）加上where语句会尽量达成顺序读.
mysql事务测试
mysql配置文件
[root@centos1 sysbench]# grep -v "^#" /etc/my.cnf | grep -v "^$"
[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
server-id=2
port=3306
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=0
table_cache=256
tmp_table_size=34M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=67M
key_buffer_size=54M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=3M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=105M
innodb_log_file_size=53M
innodb_thread_concurrency=10准备数据，创建测试库test
//sbtest表会自动创建，我准备了500W的测试数据，1G左右数据
[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
FATAL: unable to connect to MySQL server, aborting...
FATAL: error 1045: Access denied for user 'root'@'localhost' (using password: YES)
FATAL: failed to connect to database server!
FATAL: Failed to create test tables
[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 prepare
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Creating table 'sbtest'...
Creating 5000000 records in table 'sbtest'...测试mysql事务性能
[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (36.33 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (690.24 per sec.)
other operations: 20000 (72.66 per sec.)
General statistics:
total time: 275.2683s
total number of events: 10000
total time taken by event execution: 275.0810
response time:
min: 1.90ms
avg: 27.51ms
max: 581.77ms
approx. 95 percentile: 77.63ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 275.0810/0.00第二波
–num-threads=1 表示发起 1个并发连接
–oltp-read-only=off 表示不要进行只读测试，也就是会采用读写混合模式测试
–report-interval=10 表示每10秒输出一次测试进度报告
–percentile=99 表示设定采样比例，默认是 95%，即丢弃1%的长请求，在剩余的99%里取最大值
真实测试场景中，建议持续压测时长不小于1个小时，根据线上环境而论，否则测试数据可能不具参考意义。
[root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 --num-threads=1 --oltp-read-only=off --report-interval=10 --percentile=99 run
sysbench 0.4.12.10: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Random number generator seed is 0 and will be ignored
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Using 1 test tables
Threads started!
--每10秒钟报告一次测试结果，tps、每秒读、每秒写、99%以上的响应时长统计
[ 10s] Intermediate results: 1 threads, tps: 245.887991, reads/s: 3442.431872, writes/s: 1229.439954 response time: 27.147781ms (99%)
[ 20s] Intermediate results: 1 threads, tps: 188.598939, reads/s: 2640.385141, writes/s: 942.994693 response time: 63.163764ms (99%)
[ 30s] Intermediate results: 1 threads, tps: 195.203516, reads/s: 2732.849229, writes/s: 976.017582 response time: 58.050724ms (99%)
[ 40s] Intermediate results: 1 threads, tps: 174.599703, reads/s: 2444.395837, writes/s: 872.998513 response time: 74.445546ms (99%)
Done.
OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000 --其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作，例如COMMIT等)
total: 210000
transactions: 10000 (201.29 per sec.) -- 总事务数(每秒事务数)
deadlocks: 0 (0.00 per sec.)--死锁数
read/write requests: 190000 (3824.60 per sec.)-- 读写总数(每秒读写次数)
other operations: 20000 (402.59 per sec.)
General statistics:
total time: 49.6784s
total number of events: 10000 -- 共发生多少事务数
total time taken by event execution: 49.5798 -- 所有事务耗时相加(不考虑并行因素)
response time: --响应统计
min: 1.94ms
avg: 4.96ms
max: 698.99ms
approx. 99 percentile: 47.36ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 49.5798/0.00个人认为，一个数据库服务器好不好，跟系统要求有直接的关系，能符合业务需求的就是合格的服务器，基准测试只是看看服务器能承受的极限是多少，供系统上线后调优方向参考
扫一扫
2136
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
