MySQL优化笔记整理版Word下载.docx
《MySQL优化笔记整理版Word下载.docx》由会员分享，可在线阅读，更多相关《MySQL优化笔记整理版Word下载.docx（76页珍藏版）》请在冰豆网上搜索。
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'
出现如下问题,则说明需要安装mysql-devel,即mysql的头文件
drv_mysql.c:
在函数‘mysql_drv_done’中:
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
1
Randomnumbergeneratorseedis0andwillbeignored
Extrafileopenflags:
0
128files,80Mbeach
10Gbtotalfilesize
Blocksize16Kb
PeriodicFSYNCenabled,callingfsync（）each100requests.
Callingfsync（）attheendoftest,Enabled.
UsingsynchronousI/Omode
Doingsequentialreadtest
Operationsperformed:
10000reads,0writes,0Other=10000Total
Read156.25MbWritten0bTotaltransferred156.25Mb（201.09Mb/sec）
12869.92Requests/secexecuted
Generalstatistics:
0.7770s
0.7595s
responsetime:
0.01ms
0.08ms
15.58ms
0.02ms
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
NumberofIOrequests:
Read/WriteratioforcombinedrandomIOtest:
1.50
Doingrandomreadtest
Read156.25MbWritten0bTotaltransferred156.25Mb（5.5698Mb/sec）
356.47Requests/secexecuted
28.0530s
28.0305s
2.80ms
76.69ms
11.42ms
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
OLTPteststatistics:
queriesperformed:
read:
140000
write:
40000
other:
total:
200000
transactions:
10000（30.84persec.）
deadlocks:
0（0.00persec.）
read/writerequests:
180000（555.10persec.）
otheroperations:
20000（61.68persec.）
324.2651s
324.2226s
22.56ms
32.42ms
453.49ms
34.75ms
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:
最好的优化-----不查询!
这不是开玩笑.
如果一台服务器出现长时间负载过高/周期性负载过大,或偶尔卡住
如何来处理?
大的思路--------
是周期性的变化还是偶尔问题?
是服务器整体性能的问题,还是某单条语句的问题?
具体到单条语句,这条语句是在等待上花的时间,还是查询上花的时间.
唯一的办法-----监测并观察服务器的状态.
观察服务器状态,一般用如下2个命令
Showstatus;
Showprocesslist;
例:
mysql>
showstatus;
#mysqladminext
MySQL周期性波动试验
实验目的:
模拟数据库高低峰时的压力波动,并会观察绘制波动曲线
实验思路:
反复查询数据库并缓存入memcached,缓存定期失效,
观察记录服务器参数,并作图表.
实验准备:
nginx+php+memcached+awk+ab
index.php（随机访问3W条热数据,并储存在memcached中
memcached（储存查询结果）
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&
此步骤编译出一个memcache.so文件
step4:
修改php.ini引入memcache.so
实验步骤:
总数据3W以上,50个并发,每秒请求500-1000次
请求结果缓存在memcache,生命周期为60秒,
（生命周期要结合请求周期来制定,比如3万条数据随机,每秒1000条,30秒能走一遍,生命周期可设为60秒）
观察mysql连接数,每秒请求数的周期变化.
看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.
解决办法:
减少无关请求（业务逻辑层面,暂不讨论,但其实是最有效的手段）
如果请求数是一定的,不可减少的.我们要尽量让请求数平稳,不要有剧烈波动.
很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.
该实际问题最后的解决:
----夜间负载低时,集中失效.
短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午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（'
'
root'
）;
$sql='
usebigdata'
;
mysql_query（$sql,$conn）;
setnamesutf8'
selectid,name,brieffromlx_comwhereid='
.$rangeid;
$rs
升级会员 