mysql性能优化学习与实战

环境

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

个人认为,一个数据库服务器好不好,跟系统要求有直接的关系,能符合业务需求的便是合格的服务器,基准测试只是看看服务器能承受的极限是多少,供系统上线后调优方向参考.