Mysql 学习笔记

一、工具：

1）慢查询分析工具：(mk-query-digest)

语法：mk-query-digest slow-query.log

下载地址：

 

2)多线程备份工具：(mydumper)

语法：mydumper --help

下载地址：

 

3)mysql热备份工具：(xtrabachkup and xtrabackup-manager)

语法：xtrabachkup --help

下载地址：

下载地址：

 

4)监视TCP请求响应时间：(tcprstat)

语法：tcprstat -uroot -p -t 1 -n 0

下载地址：

 

5)实时监控工具：(mycheckpoint)and innotop

语法：

下载地址： 

下载innoto:wget

[root@monitor ~]# tar zxvf innotop-1.8.0.tar.gz

[root@monitor ~]# cd innotop-1.8.0

[root@monitor innotop-1.8.0]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Warning: prerequisite 2.1 not found. --缺少Term::ReadKey模块，必须：DBI BDB::mysql Time::HiRes
Writing Makefile for innotop

[root@monitor innotop-1.8.0]# perl -MCPAN -e shell
Terminal does not support AddHistory.

cpan shell -- CPAN exploration and modules installation (v1.7602)
ReadLine support available (try 'install Bundle::CPAN')

cpan> install Term::ReadKey

cpan> install DBI

cpan> install DBD::mysql 

&编译安装和二进制安装mysql，需要连接以下库文件:

ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib64/libmysqlclient.so.18 

ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib64/libmysqlclient.so 

&报以下错误：

/usr/bin/ld: cannot find -lmysqlclien   

ln -s /usr/local/mysql/lib/libmysqlclient.so /usr/lib64/libmysqlclient.so解决

[root@monitor innotop-1.8.0]# perl Makefile.PL
Writing Makefile for innotop
[root@monitor innotop-1.8.0]# make install
cp innotop blib/script/innotop
/usr/bin/perl "-MExtUtils::MY" -e "MY->fixin(shift)" blib/script/innotop
Manifying blib/man1/innotop.1
Installing /usr/share/man/man1/innotop.1
Installing /usr/bin/innotop
Writing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/innotop/.packlist
Appending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod
&mysql 授权: grant process on *.* ;  没有这个权限会报错：Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

 

6)性能压力测试工具：(hammerora)

语法：图形介面

下载地址：

 

二、理解查询缓存：

1）查看是否启用查询缓存，可以通过2个参数：query_cache_type and query_cache_size,其中任何一个参数设置为0都意味着关闭查询缓存功能，但是正确的设置推荐query_cache_type=0;

0 -- 不启用查询缓存

1 -- 启用查询缓存

 

2）配置：

   query_cache_size:根据实际生产环境和应用经验设置（默认值：64M)

   query_cache_limit:限制查询缓存区最大能缓存的查询记录集(默认值：1M，建议修改为16k~1024k)

   query_cache_min_res_unit:设置查询缓存分配内存的最小单位，要适当地设置此参数，可以做到减少内存块的申请和分配次数，但是设置过大可能导致内存磁片数值上升(默认值：4k，建议设置为1k~16k)

   query_cache_wlock_invalidate:该参数主要涉及myisam引擎，若一个客户端对某表加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放(默认值：0,也即允许)

 

3)维护：

 查询缓存区碎片整理：flush query cache

 清空查询缓存的数据：reset query cache or flush tables

 

4)性能监控：

  查询缓存内存碎片率=Qcache_free_blocks/Qcache_total_blocks * 100%

  查询缓存命率=(Qcache_hits - Qcache_inserts)/Qcache_hits * 100%

  查询缓存内存使用率=(query_cache_size - Qcache_free_memory)/query_cache_size * 100%

  Qcache_lowmem_prunes:该参数值对于检测查询缓存区的内存大小设置是否合适，有非常关键性的作用，其代表的意义为：查询缓存因内存不足而不得不从查询缓存区删除的查询缓存信息。

  query_cache_min_res_unit:内存块分配的最小单元非常重要，设置过大可能增加内存碎片的概率发生，太小又可能增加内存分配的消耗，为此在系统平稳运行一个阶段性后，可参考公式的计算值：查询缓存最小内存块=(query_cache_size - Qcache_free_memory)/Qcache_queries_in_cache

  query_cache_size:如何判断query_cache_size是否设置过小，依然也只有一个预设置一个值，推荐为：32M~128M之间的区域，待系统平稳运行一个时间段（至少1周），并且观察这周内的相关状态值：

1). Qcache_lowmem_prunes;

2). 命中率;

3). 内存使用率;

若整个平稳运行期间监控获得的信息，命中率高于80%，内存使用率超过80%,并且Qcache_lowmem_prunes的值不停地增加，而且增加的数值还较大，则说明我们为查询缓冲区分配的内存过小，可以适当地增加查询缓存区的内存大小;

若是整个平稳运行期间监控获得的信息，命中率低于40%，Qcache_lowmem_prunes的值也保持一个平稳状态，则说明我们的查询缓冲区的内存设置过大，或者说业务场景重复执行一样的查询语句的概率低，同时若还监测到一定量的freeing items,那么必须考虑把查询缓存的内存减小，甚至关闭查询缓存功能.

 

日志管理：

1)运行时动态修改binlog的格式：

  --基于SQL语句的复制(statement-based replication, SBR)

  --基于行的复制(row-based replication, RBR)

  --基于混合模式复制(mixed-based replication, MBR)

  mysql>set global binlog_format = "ROW";

  mysql>set global binlog_format = "MIXED";

  mysql>set global binlog_format = "STATEMENT";