MySQL优化笔记整理版.docx

1、MySQL优化笔记整理版High Performance Memcached错误！未找到目录项。Q: 为什么别人问你MySQL优化的知识 总是没有底气.A: 因为你只是回答一些大而化之的调优原则, 比如:”建立合理索引”(什么样的索引合理?)“分表分库”(用什么策略分表分库?)“主从分离”(用什么中间件?)并没有从细化到定量的层面去分析.如qps提高了%N? 有没有减少文件排序?语句的扫描行数减少了多少?没有大量的数据供测试,一般在学习环境中,只是手工添加几百上万条数据,数据量小,看不出语句之间的明确区别.Q: 如何提高MySQL的性能?A: 需要优化,则说明效率不够理想. 因此我们首先要做的

2、,不是优化,而是-诊断. 治病的前提,是诊病,找出瓶颈所在. CPU,内存,IO? 峰值,单条语句? 准备环境1、安装确保以下系统相关库文件gcc gcc-c+ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool*(libtool-ltdl-devel*)# yum y install gcc gcc-c+ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool* cmake2、 建立mysql安装目录及数据存放目录# mkdir /usr/l

3、ocal/mysql# mkdir -p /data/mysql3、 创建用户和用户组# groupadd mysql# useradd -g mysql mysql4、 赋予数据存放目录权限# chown mysql.mysql R /data/mysql二、安装MySQL 5.5.351、 获取解压mysql-5.5.35.tar.gz在官网或国内镜像下载源码# wget # tar zxvf mysql-5.5.35.tar.gz# cd mysql-5.5.352、 编译mysql-5.5.35# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysq

4、l -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

5、-DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306# make# make install3、 复制配置文件# cp support-files/my-f /etc/f4、 初始化数据库执行前需赋给scripts/mysql_install_db文件执行权限# chmod 755 scripts/mysql_install_db# scripts/mysql_install_db -user=mysql -basedir=/usr/local/mysql/ -datadir=/data/mysql/注：basedir：mysql安装路径 datadir：数据库文件

6、储存路径5、 设置mysqld的开机启动# cp support-files/mysql.server /etc/init.d/mysql# chmod 755 /etc/init.d/mysql# chkconfig mysql on6、 为MySQL配置环境变量将mysql的bin目录加到PATH中，有利于以后管理和维护，在/etc/profile中加入myslq/bin，同时增加两个别名方便操作：# export PATH=/usr/local/mysql/bin:$PATH# alias mysql_start=mysqld_safe &# alias mysql_stop=mysql

7、admin u root -p shutdown7、 启动mysql服务# /etc/init.d/mysql start启动完成之后用ps -ef |grep mysql 命令查看是否启动8、 登陆mysql#mysql -uroot -p提示 在自行编译mysql,并连接本地机时,常出现找不到mysqld.sock的错误.rootlfqb data# mysql -urootERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysqld.sock (2)错误原因: mysql客户端默认去找

8、/tmp/mysqld.sock 做连接,而mysqld.sock有可能不在此处.比如在 /var/lib/mysql/mysql.sock解决: 1: mysql -S /sock/path 指定真实的路径 mysql -S /var/lib/mysql/mysql.sock2: 在/tmp下做一个链接,链接到真实sock文件.# ln /var/lib/mysql/mysql.sock /tmp/mysqld.sock3: sock文件在linux环境中连接本地机才能使用,速度比用本机IP要快. 你也可以强行指定用IP来连接.Sysbench的编译下载:解压: #cd /path/to/s

9、ysbench# ./autogen.sh注:出现如下错误,则yum安装automake,libtoolizeautomake 1.10.x (aclocal) wasnt found, exitinglibtoolize 1.4+ wasnt found, exiting出现如下问题,则说明需要安装mysql-devel,即mysql的头文件drv_mysql.c: 在函数mysql_drv_done中:drv_mysql.c:851: 警告：隐式声明函数mysql_library_end#./configure -prefix=/usr/local/sysbench -with-mysql

10、-includes=/usr/include/mysql/ -with-mysql-libs=/usr/lib64/mysql/#make & make installSysbench测试CPU性能4线程查找20000以内的素数rootlocalhost # /usr/bin/sysbench -test=cpu -cpu-max-prime=20000 -num-threads=4 runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number

11、of threads: 4Doing CPU performance benchmarkThreads started!Done.Maximum prime number checked in CPU test: 20000Test execution summary: total time: 96.7210s total number of events: 10000 total time taken by event execution: 114.5518 per-request statistics: min: 2.17ms avg: 11.46ms max: 51.54ms appro

12、x. 95 percentile: 17.27msThreads fairness: events (avg/stddev): 2500.0000/6.96 execution time (avg/stddev): 28.6380/0.01注意: 服务器类型,有偏运算型的,有偏存储, 所需要的指标不一样.偏运算的(如视频转码服务器)要求CPU强,而存储则优先选择大容量和快速存储备.测试的数据,孤立起来看,是没有意义的.数据要有比较才有意义, 比如多台服务器的测试数据,比较CPU性能.Sysbench测试磁盘IO性能Sysbench -test=fileio -file-total-size=1

13、0G prepare解释: 创建10G的内容,供测试用Sysbench -test=fileio -file-total-size=10G -file-test-mode=rndrw run解释:针对10G文件,做随机读写,测试IO-file-test-mode 还可以为seqwr：顺序写入seqrewq：顺序重写seqrd：顺序读取rndrd：随机读取rndwr：随机写入rndrw：混合随机读写测试顺序读rootlocalhost sysbench# ./bin/sysbench -test=fileio -file-total-size=10G -file-test-mode=seqrd

14、run sysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredExtra file open flags: 0128 files, 80Mb each10Gb total file sizeBlock size 16KbPeriodic FSYNC enabled, calling fsync() each 100

15、 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing sequential read testThreads started!Operations performed: 10000 reads, 0 writes, 0 Other = 10000 TotalRead 156.25Mb Written 0b Total transferred 156.25Mb (201.09Mb/sec)12869.92 Requests/sec executedGeneral statisti

16、cs: total time: 0.7770s total number of events: 10000 total time taken by event execution: 0.7595s response time: min: 0.01ms avg: 0.08ms max: 15.58ms approx. 95 percentile: 0.02msThreads fairness: events (avg/stddev): 10000.0000/0.00execution time (avg/stddev): 0.7595/0.00测试随机读rootlocalhost sysbenc

17、h# ./bin/sysbench -test=fileio -file-total-size=10G -file-test-mode=rndrd run sysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredExtra file open flags: 0128 files, 80Mb each10Gb tota

18、l file sizeBlock size 16KbNumber of IO requests: 10000Read/Write ratio for combined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random read testThreads started!Operations performed: 10000 re

19、ads, 0 writes, 0 Other = 10000 TotalRead 156.25Mb Written 0b Total transferred 156.25Mb (5.5698Mb/sec) 356.47 Requests/sec executedGeneral statistics: total time: 28.0530s total number of events: 10000 total time taken by event execution: 28.0305s response time: min: 0.01ms avg: 2.80ms max: 76.69ms

20、approx. 95 percentile: 11.42msThreads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 28.0305/0.00通过上两例对比, 顺序读与随机读之间的速度,差了40倍!合理的索引+where语句会尽量达成顺序读.作业:测试随机写与顺序写的速度差异Sysbench测试事务性能 #sysbench -test=/path/to/sysbench-source/tests/db/oltp.lua -mysql-table-engine=innodb -mysql

21、-user=root -db-driver=mysql -mysql-db=test -oltp-table-size=3000 -mysql-socket=/var/lib/mysql/mysql.sock prepare实测结果: 双核CPU,8G内存,7200转机械硬盘rootlocalhost sysbecn# ./bin/sysbench -test=/path/to/sysbench-source/tests/db/oltp.lua -mysql-table-engine=innodb -mysql-user=root -db-driver=mysql -mysql-db=test

22、 -oltp-table-size=3000 -mysql-socket=/var/lib/mysql/mysql.sock runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredThreads started!OLTP test statistics: queries performed: read: 14

23、0000 write: 40000 other: 20000 total: 200000 transactions: 10000 (30.84 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 180000 (555.10 per sec.) other operations: 20000 (61.68 per sec.)General statistics: total time: 324.2651s total number of events: 10000 total time taken by event execu

24、tion: 324.2226s response time: min: 22.56ms avg: 32.42ms max: 453.49ms approx. 95 percentile: 34.75msThreads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 324.2226/0.00Awk脚本Awk是一个简便的直译式的文本处理工具.擅长处理-多行多列的数据处理过程: While(还有下一行) 1:读取下一行,并把下一行赋给$0,各列赋给$1,$2.$N变量 2: 用指定的命令来处理该

25、行如何处理1行数据?答: 分2部分, pattern (条件) + action(处理动作)第1个简单awk脚本awk printf “%sn” , $1 xx.txt / 把xx.txt的每一行进行输出第2个简单awk脚本 统计mysql服务器信息mysqladmin -uroot ext|awk Queries/q=$4/Threads_connected/c=$4/Threads_running/r=$4ENDprintf(%d %d %dn,q,c,r)mysql性能调优的思路0: 最好的优化-不查询!这不是开玩笑.如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住如何来处

26、理?答:大的思路-是周期性的变化还是偶尔问题?是服务器整体性能的问题, 还是某单条语句的问题?具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间.唯一的办法-监测并观察服务器的状态.1:观察服务器状态, 一般用如下2个命令Show status; Show processlist;例: mysql show status; #mysqladmin ext MySQL周期性波动试验实验目的: 模拟数据库高低峰时的压力波动,并会观察绘制波动曲线实验思路: 反复查询数据库并缓存入memcached, 缓存定期失效,观察记录服务器参数,并作图表.实验准备: nginx+php+memca

27、ched+awk+ab1: index.php (随机访问3W条热数据,并储存在memcached中2: memcached (储存查询结果)3: ab 压力测试工具4: awk脚本编译PHP-memcache扩展 (此步骤适合任意PHP扩展)以memcache-2.2.7为例(注意,这是PHP连接memcached的一个扩展)解压后,假设路径/path/to/memcachestep1 : /path/to/memcached/# /path/to/php/bin/phpize #作用是根据PHP的版本生成编译文件此步骤后,memcache目录下,产生configure文件step2: co

28、nfigure -with-php-config =/path/to/php/bin/php-configstep3: make & make install此步骤编译出一个memcache.so文件step4: 修改php.ini引入memcache.so实验步骤: 总数据3W以上,50个并发,每秒请求500-1000次请求结果缓存在memcache,生命周期为60秒,(生命周期要结合请求周期来制定,比如3万条数据随机,每秒1000条,30秒能走一遍,生命周期可设为60秒)观察mysql连接数,每秒请求数的周期变化.看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.解决办法: 1

29、: 减少无关请求(业务逻辑层面,暂不讨论,但其实是最有效的手段)2: 如果请求数是一定的,不可减少的. 我们要尽量让请求数平稳,不要有剧烈波动.很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.该实际问题最后的解决:-夜间负载低时,集中失效. 短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午10点左右人多时,缓存已经建立了一部分. 白天时,波峰也不剧烈. 或者让缓存的生命周期在一定范围内随机,也可以减缓波峰剧烈的情况我们把实验中的生命周期由80秒,改为40-120秒,其他实验条件不变.得到如下曲线可以看出,稳定运行后,请求在1000-1500之间波动,而固定缓存

30、周期是,请求在500-1700之间波动.实验附件:bench.php?php/ 30K hot news$rangeid = rand(1,30000)+13000000;$mconn = memcache_connect(localhost,11211);if( ($com = memcache_get($mconn,$rangeid) = false) $conn = mysql_connect(localhost,root); $sql = use bigdata; mysql_query($sql,$conn); $sql = set names utf8; mysql_query($sql,$conn); $sql = select id,name,brief from lx_