MySQL优化笔记整理版.docx

MySQL优化笔记整理版.docx

《MySQL优化笔记整理版.docx》由会员分享，可在线阅读，更多相关《MySQL优化笔记整理版.docx（94页珍藏版）》请在冰豆网上搜索。

MySQL优化笔记整理版

HighPerformanceMemcached

错误！

未找到目录项。

Q:

为什么别人问你MySQL优化的知识总是没有底气.

A:

因为你只是回答一些大而化之的调优原则,

比如:

”建立合理索引”（什么样的索引合理?

）

“分表分库”（用什么策略分表分库?

）

“主从分离”（用什么中间件?

）

并没有从细化到定量的层面去分析.

如qps提高了%N?

有没有减少文件排序?

语句的扫描行数减少了多少?

没有大量的数据供测试,一般在学习环境中,只是手工添加几百上万条数据,

数据量小,看不出语句之间的明确区别.

Q:

如何提高MySQL的性能?

A:

需要优化,则说明效率不够理想.

因此我们首先要做的,不是优化,而是---诊断.

治病的前提,是诊病,找出瓶颈所在.CPU,内存,IO?

峰值,单条语句?

准备环境

1、安装确保以下系统相关库文件

gccgcc-c++autoconfautomakezlib*libxml*ncurses-devellibmcrypt*libtool*（libtool-ltdl-devel*）

#yum–yinstallgccgcc-c++autoconfautomakezlib*libxml*ncurses-devellibmcrypt*libtool*cmake

2、建立mysql安装目录及数据存放目录

#mkdir/usr/local/mysql

#mkdir-p/data/mysql

3、创建用户和用户组

#groupaddmysql

#useradd-gmysqlmysql

4、赋予数据存放目录权限

#chownmysql.mysql–R/data/mysql

二、安装MySQL5.5.35

1、获取解压mysql-5.5.35.tar.gz

在官网或国内镜像下载源码

#wget

#tarzxvfmysql-5.5.35.tar.gz

#cdmysql-5.5.35

2、编译mysql-5.5.35

#cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DMYSQL_UNIX_ADDR=/tmp/mysqld.sock\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci\

-DWITH_EXTRA_CHARSETS:

STRING=utf8,gbk\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_MEMORY_STORAGE_ENGINE=1\

-DWITH_READLINE=1\

-DENABLED_LOCAL_INFILE=1\

-DMYSQL_DATADIR=/data/mysql\

-DMYSQL_USER=mysql\

-DMYSQL_TCP_PORT=3306

#make

#makeinstall

3、复制配置文件

#cpsupport-files/my-f/etc/f

4、初始化数据库

执行前需赋给scripts/mysql_install_db文件执行权限

#chmod755scripts/mysql_install_db

#scripts/mysql_install_db--user=mysql--basedir=/usr/local/mysql/\

--datadir=/data/mysql/

注：

basedir：

mysql安装路径datadir：

数据库文件储存路径

5、设置mysqld的开机启动

#cpsupport-files/mysql.server/etc/init.d/mysql

#chmod755/etc/init.d/mysql

#chkconfigmysqlon

6、为MySQL配置环境变量

将mysql的bin目录加到PATH中，有利于以后管理和维护，在/etc/profile中加入myslq/bin，同时增加两个别名方便操作：

#exportPATH=/usr/local/mysql/bin:

$PATH

#aliasmysql_start="mysqld_safe&"

#aliasmysql_stop="mysqladmin–uroot-pshutdown"

7、启动mysql服务

#/etc/init.d/mysqlstart

启动完成之后用ps-ef|grepmysql命令查看是否启动

8、登陆mysql

#mysql-uroot-p

提示在自行编译mysql,并连接本地机时,常出现找不到mysqld.sock的错误.

[root@lfqbdata]#mysql-uroot

ERROR2002（HY000）:

Can'tconnecttolocalMySQLserverthroughsocket'/tmp/mysqld.sock'

（2）

错误原因:

mysql客户端默认去找/tmp/mysqld.sock做连接,而mysqld.sock有可能不在此处.

比如在/var/lib/mysql/mysql.sock

解决:

1:

mysql-S/sock/path指定真实的路径

mysql-S/var/lib/mysql/mysql.sock

2:

在/tmp下做一个链接,链接到真实sock文件.

#ln/var/lib/mysql/mysql.sock/tmp/mysqld.sock

3:

sock文件在linux环境中连接本地机才能使用,速度比用本机IP要快.

你也可以强行指定用IP来连接.

Sysbench的编译

下载:

解压:

#cd/path/to/sysbench

#./autogen.sh

注:

出现如下错误,则yum安装automake,libtoolize

automake1.10.x（aclocal）wasn'tfound,exiting

libtoolize1.4+wasn'tfound,exiting

出现如下问题,则说明需要安装mysql-devel,即mysql的头文件

drv_mysql.c:

在函数‘mysql_drv_done’中:

drv_mysql.c:

851:

警告：

隐式声明函数‘mysql_library_end’

#../configure--prefix=/usr/local/sysbench--with-mysql-includes=/usr/include/mysql/\

--with-mysql-libs=/usr/lib64/mysql/

#make&&makeinstall

Sysbench测试CPU性能

4线程查找20000以内的素数

[root@localhost~]#/usr/bin/sysbench--test=cpu--cpu-max-prime=20000--num-threads=4run

sysbench0.5:

multi-threadedsystemevaluationbenchmark

Runningthetestwithfollowingoptions:

Numberofthreads:

4

DoingCPUperformancebenchmark

Threadsstarted!

Done.

MaximumprimenumbercheckedinCPUtest:

20000

Testexecutionsummary:

totaltime:

96.7210s

totalnumberofevents:

10000

totaltimetakenbyeventexecution:

114.5518

per-requeststatistics:

min:

2.17ms

avg:

11.46ms

max:

51.54ms

approx.95percentile:

17.27ms

Threadsfairness:

events（avg/stddev）:

2500.0000/6.96

executiontime（avg/stddev）:

28.6380/0.01

注意:

服务器类型,有偏运算型的,有偏存储,所需要的指标不一样.

偏运算的（如视频转码服务器）要求CPU强,而存储则优先选择大容量和快速存储备.

测试的数据,孤立起来看,是没有意义的.

数据要有比较才有意义,比如多台服务器的测试数据,比较CPU性能.

Sysbench测试磁盘IO性能

Sysbench--test=fileio--file-total-size=10Gprepare

解释:

创建10G的内容,供测试用

Sysbench--test=fileio--file-total-size=10G--file-test-mode=rndrwrun

解释:

针对10G文件,做随机读写,测试IO

--file-test-mode还可以为

seqwr：

顺序写入

seqrewq：

顺序重写

seqrd：

顺序读取

rndrd：

随机读取

rndwr：

随机写入

rndrw：

混合随机读写

测试顺序读

[root@localhostsysbench]#./bin/sysbench--test=fileio--file-total-size=10G--file-test-mode=seqrdrun

sysbench0.5:

multi-threadedsystemevaluationbenchmark

Runningthetestwithfollowingoptions:

Numberofthreads:

1

Randomnumbergeneratorseedis0andwillbeignored

Extrafileopenflags:

0

128files,80Mbeach

10Gbtotalfilesize

Blocksize16Kb

PeriodicFSYNCenabled,callingfsync（）each100requests.

Callingfsync（）attheendoftest,Enabled.

UsingsynchronousI/Omode

Doingsequentialreadtest

Threadsstarted!

Operationsperformed:

10000reads,0writes,0Other=10000Total

Read156.25MbWritten0bTotaltransferred156.25Mb（201.09Mb/sec）

12869.92Requests/secexecuted

Generalstatistics:

totaltime:

0.7770s

totalnumberofevents:

10000

totaltimetakenbyeventexecution:

0.7595s

responsetime:

min:

0.01ms

avg:

0.08ms

max:

15.58ms

approx.95percentile:

0.02ms

Threadsfairness:

events（avg/stddev）:

10000.0000/0.00

executiontime（avg/stddev）:

0.7595/0.00

测试随机读

[root@localhostsysbench]#./bin/sysbench--test=fileio--file-total-size=10G--file-test-mode=rndrdrun

sysbench0.5:

multi-threadedsystemevaluationbenchmark

Runningthetestwithfollowingoptions:

Numberofthreads:

1

Randomnumbergeneratorseedis0andwillbeignored

Extrafileopenflags:

0

128files,80Mbeach

10Gbtotalfilesize

Blocksize16Kb

NumberofIOrequests:

10000

Read/WriteratioforcombinedrandomIOtest:

1.50

PeriodicFSYNCenabled,callingfsync（）each100requests.

Callingfsync（）attheendoftest,Enabled.

UsingsynchronousI/Omode

Doingrandomreadtest

Threadsstarted!

Operationsperformed:

10000reads,0writes,0Other=10000Total

Read156.25MbWritten0bTotaltransferred156.25Mb（5.5698Mb/sec）

356.47Requests/secexecuted

Generalstatistics:

totaltime:

28.0530s

totalnumberofevents:

10000

totaltimetakenbyeventexecution:

28.0305s

responsetime:

min:

0.01ms

avg:

2.80ms

max:

76.69ms

approx.95percentile:

11.42ms

Threadsfairness:

events（avg/stddev）:

10000.0000/0.00

executiontime（avg/stddev）:

28.0305/0.00

通过上两例对比,顺序读与随机读之间的速度,差了40倍!

合理的索引+where语句会尽量达成顺序读.

作业:

测试随机写与顺序写的速度差异

Sysbench测试事务性能

#sysbench--test=/path/to/sysbench-source/tests/db/oltp.lua--mysql-table-engine=innodb\

--mysql-user=root--db-driver=mysql--mysql-db=test\

--oltp-table-size=3000

--mysql-socket=/var/lib/mysql/mysql.sockprepare

实测结果:

双核CPU,8G内存,7200转机械硬盘

[root@localhostsysbecn]#./bin/sysbench--test=/path/to/sysbench-source/tests/db/oltp.lua--mysql-table-engine=innodb--mysql-user=root--db-driver=mysql--mysql-db=test--oltp-table-size=3000--mysql-socket=/var/lib/mysql/mysql.sockrun

sysbench0.5:

multi-threadedsystemevaluationbenchmark

Runningthetestwithfollowingoptions:

Numberofthreads:

1

Randomnumbergeneratorseedis0andwillbeignored

Threadsstarted!

OLTPteststatistics:

queriesperformed:

read:

140000

write:

40000

other:

20000

total:

200000

transactions:

10000（30.84persec.）

deadlocks:

0（0.00persec.）

read/writerequests:

180000（555.10persec.）

otheroperations:

20000（61.68persec.）

Generalstatistics:

totaltime:

324.2651s

totalnumberofevents:

10000

totaltimetakenbyeventexecution:

324.2226s

responsetime:

min:

22.56ms

avg:

32.42ms

max:

453.49ms

approx.95percentile:

34.75ms

Threadsfairness:

events（avg/stddev）:

10000.0000/0.00

executiontime（avg/stddev）:

324.2226/0.00

Awk脚本

Awk是一个简便的直译式的文本处理工具.

擅长处理--多行多列的数据

处理过程:

While（还有下一行）{

1:

读取下一行,并把下一行赋给$0,各列赋给$1,$2...$N变量

2:

用指定的命令来处理该行

}

如何处理1行数据?

答:

分2部分,pattern（条件）+action（处理动作）

第1个简单awk脚本

awk‘{printf“%s\n”,$1}’xx.txt//把xx.txt的每一行进行输出

第2个简单awk脚本统计mysql服务器信息

mysqladmin-urootext|awk

'Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf（"%d%d%d\n",q,c,r）}'

mysql性能调优的思路

0:

最好的优化-----不查询!

这不是开玩笑.

如果一台服务器出现长时间负载过高/周期性负载过大,或偶尔卡住

如何来处理?

答:

大的思路--------

是周期性的变化还是偶尔问题?

是服务器整体性能的问题,还是某单条语句的问题?

具体到单条语句,这条语句是在等待上花的时间,还是查询上花的时间.

唯一的办法-----监测并观察服务器的状态.

1:

观察服务器状态,一般用如下2个命令

Showstatus;Showprocesslist;

例:

mysql>showstatus;

#mysqladminext

MySQL周期性波动试验

实验目的:

模拟数据库高低峰时的压力波动,并会观察绘制波动曲线

实验思路:

反复查询数据库并缓存入memcached,缓存定期失效,

观察记录服务器参数,并作图表.

实验准备:

nginx+php+memcached+awk+ab

1:

index.php（随机访问3W条热数据,并储存在memcached中

2:

memcached（储存查询结果）

3:

ab压力测试工具

4:

awk脚本

编译PHP-memcache扩展（此步骤适合任意PHP扩展）

以memcache-2.2.7为例（注意,这是PHP连接memcached的一个扩展）

解压后,假设路径/path/to/memcache

step1:

/path/to/memcached/#/path/to/php/bin/phpize#作用是根据PHP的版本生成编译文件

此步骤后,memcache目录下,产生configure文件

step2:

configure--with-php-config=/path/to/php/bin/php-config

step3:

make&&makeinstall

此步骤编译出一个memcache.so文件

step4:

修改php.ini引入memcache.so

实验步骤:

总数据3W以上,50个并发,每秒请求500-1000次

请求结果缓存在memcache,生命周期为60秒,

（生命周期要结合请求周期来制定,比如3万条数据随机,每秒1000条,30秒能走一遍,生命周期可设为60秒）

观察mysql连接数,每秒请求数的周期变化.

看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.

解决办法:

1:

减少无关请求（业务逻辑层面,暂不讨论,但其实是最有效的手段）

2:

如果请求数是一定的,不可减少的.我们要尽量让请求数平稳,不要有剧烈波动.

很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.

该实际问题最后的解决:

----夜间负载低时,集中失效.

短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午10点左右人多时,缓存已经建立了一部分.白天时,波峰也不剧烈.

或者让缓存的生命周期在一定范围内随机,也可以减缓波峰剧烈的情况

我们把实验中的生命周期由80秒,改为[40-120秒],其他实验条件不变.

得到如下曲线

可以看出,稳定运行后,请求在[1000-1500]之间波动,

而固定缓存周期是,请求在[500-1700]之间波动.

实验附件:

bench.php

php

//30Khotnews

$rangeid=rand（1,30000）+13000000;

$mconn=memcache_connect（'localhost',11211）;

if（（$com=memcache_get（$mconn,$rangeid））===false）{

$conn=mysql_connect（'localhost','root'）;

$sql='usebigdata';

mysql_query（$sql,$conn）;

$sql='setnamesutf8';

mysql_query（$sql,$conn）;

$sql='selectid,name,brieffromlx_