mysql资源评估_Mysql_数据库评估

实际上还有一类数字类型是通过二进制格式以字符串来存放的，如，DECIMAL(DEC)[(M[,D])]，NUMERIC[(M[,D])]，由于其存放长度主要通过其定义时候的M所决定，M定义多大，则实际就有多长。M代表整个位数的长度，D代表小数殿后的位数，默认M为10，D为0。一般考虑到这种数据完全可以变化形式以整数存放，所以这种数据类型()一般不用。但是，在mysql中float、double(或real)是浮点数，decimal(或numberic)是定点数。浮点数(float、double)存在误差问题；对货币等对精度敏感的数据，应该用定点数表示或存储；编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

另外IP地址字段的话，尽量设置为UNSIGNED INT(32无符号整形)，查询时可以使用把一个字符串IP转成一个整形，并使用把一个IP转换成字符串IP。

时间存储格式，常用的是DATETIME，DATE，TIMESTAMP这三种，从存储空间看TIMESTAMP最少，4个字节，而其他两种都要8个字节，多了一倍。如果有需要存放早于1970年之前的时间，则要使用DATETIME类型，假如不需要，则最好使用TIMESTAMP来减少存储空间的占用。

字符存储类型：

mysql5.0.3之后的表示，VARCHAR[(M)]表示的字节数，最大限制和字符集有关，如果是gbk编码，最大长度为(65535-1-2)/2=32766，减1的原因是实际行存储从第二个字节开始，减2的原因是varchar头部的2个字节表示长度，除2因为是gbk编码；如果是utf8编码，最大长度为(65535-1-2)/3=21844。字段尽量选择固定长度。另外MySQL 5.0之后版本存储VARCHAR类型的时候会保留末尾空格，CHAR[(M)]都是以空格填补剩余的空间，所以，如果要保留结尾空格的话，一定要使用VARCHAR。

另外不要使用数字作为ENUM枚举常量！

尽量用char(1)来代替bit(1)，尽量避免程序错误！

MyISAM表的话，尽量使用char()代替varchar()；对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列(所有数据行都使用指向数据列值的头指针)，主要的性能因素是数据行使用的存储总量，建议使用varcahr()，可以减少存储空间。

对于BLOB和TEXT字段，如果可以，就把这些字段分拆到另外一个表里面去。

4字符集选择

mysql的字符集包括字符集(CHARACTER)和校对规则(COLLATION)两个概念。字符集是用来定义mysql存储字符串的方式，校对规则是定义了比较字符串的方式。

UTF-8：是用以解决国际上字符的一种多字节编码，它对英文使用8位(即一个字节)，中文使用24位(三个字节)来编码。UTF-8包含全世界所有国家需要用到的字符，是国际编码，通用性强。

GBK： 是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的，即不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1。GBK包含全部中文字符，是国家编码，通用性比UTF8差，不过UTF8占用的数据库比GBK大。

为了避免所有乱码问题，应该采用UTF-8，将来要支持国际化也非常方便UTF-8可以看作是大字符集，它包含了大部分文字的编码。

5命名规范

1、数据库和表名应尽可能和所服务的业务模块名一致；

2、服务于同一子模块的一类表尽量以子模块名(或部分单词)为前缀或后缀；

3、表名应尽量包含与所存放数据相对应的单词；

4、字段名称也尽量保持和实际数据相对应

5、索引名称尽量包含所有的索引键字段名或者缩写，且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致，且尽量包含一个类似于idx或者ind之类的前缀或者后缀，以表名其对象类型是索引，同时还可以包含该索引所属表的名称；

6、约束等其他对象也应该尽可能包含所属表或其他对象的名称，以表名各自关系。

7、同一个SQL语句中必须统一大小写，不允许SELECT * FROM my_table

WHERE MY_TABLE.col=1;

6索引的设计

说明：MyISAM的主键索引(聚集索引)和普通索引(非聚集索引)；InnoDB中索引分为聚集索引形式的主键索引和非聚集索引形式的普通索引。

·频繁的作为条件进行查询的字段应创建索引

·唯一性太差的字段不要创建索引

·频繁更新的字段不适合创建索引

·能建立组合索引的尽量建立组合索引

·索引键长不能过长(最大限制为1000个字节)

·join条件字段类型不一致的时候mysql无法使用索引

·如果一个字段设计成用来存储URL，则可以新增一个被索引的url_crc列，使用CRC32做哈希

·如果对较长的字段使用索引，可以考虑前缀索引(注意索引唯一性)

·避免使用重复索引(可以使用pt-duplicate-key-checker来检查重复索引)

·统计未使用的索引(可以使用pt-index-usage来查看)

·只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列

7外键问题

InnoDB中定义外键时需要注意的地方：

InnoDB有问题的方式

InnoDB正常的方式

Create

Table: CREATE TABLE `person` (

`id` smallint(5) unsigned NOT NULL

AUTO_INCREMENT,

`name` char(60) NOT NULL,

PRIMARY KEY (`id`)

)

ENGINE=InnoDB

DEFAULT CHARSET=utf8

CREATE

TABLE shirt (

id

SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

style

ENUM('t-shirt', 'polo', 'dress') NOT NULL,

color

ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

owner

SMALLINT UNSIGNED NOT NULL REFERENCES person(id),

PRIMARY

KEY (id)

)

ENGINE=InnoDB

DEFAULT CHARSET=utf8;

CREATE

TABLE person2 (

id

SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

name

CHAR(60) NOT NULL,

PRIMARY

KEY (id)

)

ENGINE=InnoDB

DEFAULT CHARSET=utf8;

CREATE

TABLE shirt2 (

id

SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

style

ENUM('t-shirt', 'polo', 'dress') NOT NULL,

color

ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,

owner

SMALLINT UNSIGNED NOT NULL,

PRIMARY

KEY (id),

FOREIGN

KEY(owner) REFERENCES person2(id)

)

ENGINE=InnoDB

DEFAULT CHARSET=utf8;

mysql>

show create table shirt\G

***************************

1. row ***************************

Table: shirt

Create

Table: CREATE TABLE `shirt` (

`id` smallint(5) unsigned NOT NULL

AUTO_INCREMENT,

`style` enum('t-shirt','polo','dress') NOT

NULL,

`color`

enum('red','blue','orange','white','black') NOT NULL,

`owner` smallint(5) unsigned NOT NULL,

PRIMARY KEY (`id`)

)

ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

1

row in set (0.00 sec)

mysql>

show create table shirt2\G

***************************

1. row ***************************

Table: shirt2

Create

Table: CREATE TABLE `shirt2` (

`id` smallint(5) unsigned NOT NULL

AUTO_INCREMENT,

`style` enum('t-shirt','polo','dress') NOT

NULL,

`color`

enum('red','blue','orange','white','black') NOT NULL,

`owner` smallint(5) unsigned NOT NULL,

PRIMARY KEY (`id`),

KEY `owner` (`owner`),

CONSTRAINT `shirt2_ibfk_1`

FOREIGN KEY (`owner`) REFERENCES `person2` (`id`)

)

ENGINE=InnoDB DEFAULT CHARSET=utf8

1

row in set (0.00 sec)

Show create table的时候，表shirt并没有出现外键定义！也相当于没有定义外键！在删除父表的时候，不会报错！

这样定义的外键就会有CONSTRAINT语句，外键约束在起作用，当删除父表数据时，会报错：ERROR 1451 (23000): Cannot delete or update a parent row: a

foreign key constraint fails (`test`.`shirt2`, CONSTRAINT `shirt2_ibfk_1`

FOREIGN KEY (`owner`) REFERENCES `person2` (`id`))

alter

table shirt2 drop FOREIGN KEY shirt2_ibfk_1;

alter

table shirt2 add constraint shirt2_ibfk_1 FOREIGN KEY (owner) REFERENCES

person2(id) ON DELETE CASCADE ON UPDATE CASCADE;

7准实时性数据处理

如系统当前在线人数，论坛系统当前总帖数、回帖数等，多条件大结果集查询页面的总结果数以及

总页数，某些虚拟积分的top n排名等等。这些统计的计算都会涉及到大量的数据，同时也需要大量的计算资源，访问频率也都非常的高。如果都通过实时统计，恐怕只要数据量稍微大一些，都会带来非常大的硬件资源开销。但在短时间内的不够精确，又并不会带来太大用户体验的降低。所以完全可以通过定时任务程序(如cront跑一个脚本定时的去算统计数据)，没隔一定时间段进行一次统计后存放在专门设计的统计表中。这样，在统计数据需要展示的时候，我们只需要从统计好的结果数据中取出即可。

8垂直分割需求

示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，除了个人信息外，并不需要经常读取或是改写这个字段。那么，可以把这个字段设计到另外一张表中。这样的表有更好的性能。

示例二：你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

另外，你需要注意的是，这些被分出去的字段所形成的表，确保不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是指数级的下降。

9水平拆分需求

譬如DNWeb的eventlog表，里面的log类型分为很多种，而且这个表又比较大，所以这个时候最好做水平拆分，将像登陆记录，统计在线人数等类型分离出去。

10适度冗余的设计

被频繁引用且只能通过join 2张(或者更多)大表的方式才能得到的独立小字段，适合作为一个冗余字段，但要保证数据的一致性！

第三章

数据库安全审核

1账户和权限

User

From Host

Administrative Roles

ga2

192.168.0.132

root

Localhost

执行：SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query

FROM mysql.user;

(可以根据MySQL Workbench这个软件来看)

第四章

MySQL优化

(1)二进制的发行版包括RPM等包装好的特定二进制版本：

好处：

a)通过非常简单的安装方式快速完成MySQL的部署；

b)安装版本是经过比较完善的功能和性能测试的编译版本；

c)所使用的编译参数更具通用性的，且比较稳定；

不足：存储引擎的种类，字符集的选择，安装目录，等都是定制好的(后期也可以更改)，一台server上只能安装一个MySQL。

补充RPM默认安装路径：

/usr/bin

客户端程序和脚本、比如mysqladmin mysqldump等命令

/var/lib/mysql

数据目录

/usr/share/mysql

mysql.server命令及配置文件,错误消息和字符集文件

/etc/rc.d/init.d/

启动脚本文件mysql的目录

/usr/share/sql-bench

基准测试程序

(2)源码编译安装：

好处：

a)针对自己的硬件平台选用合适的编译器来优化编译后的二进制代码；

b)根据不同的软件平台环境调整相关的编译参数；

c)针对我们特定应用场景选择需要什么组件不需要什么组件；

d)根据我们的所需要存储的数据内容选择只安装我们需要的字符集；

e)同一台主机上面可以安装多个MySQL；

不足：

a)对编译参数的不够了解造成编译参数使用不当可能使编译出来的二进制代码不够稳定；

b)对自己的应用环境把握失误而使用的优化参数可能反而使系统性能更差；

c)还有一个并不能称之为隐患的小问题就是源码编译安装将使安装部署过程更为复杂，所花费的时间更长

(mysql5.5以后是通过cmake来编译的)安装cmake-2.8.4.tar.gz

# yum install make gcc gcc-c++ ncurses-devel

openssl-devel bison cmake -y

或者

# tar zxvf cmake-2.8.4.tar.gz

# cd cmake-2.8.4

# ./bootstrap

# gmake

# gmake install

# ./configure

# make && make install

添加mysql用户

查看是否有mysql用户及用户组

# cat /etc/passwd查看用户列表

# cat /etc/group查看用户组列表

如果没有就创建

# groupadd mysql

# useradd -g mysql mysql

修改/usr/local/mysql权限

# chown -R mysql:mysql /usr/local/mysql

获取解压mysql-5.6.15

#wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.15.tar.gz

# tar zxvf mysql-5.6.15.tar.gz

# cd mysql-5.6.15

编译mysql-5.6.15

cmake \

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \

-DSYSCONFDIR=/etc \

-DWITH_MYISAM_STORAGE_ENGINE=1 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1\

-DWITH_MEMORY_STORAGE_ENGINE=1 \

启用readline库支持(提供可编辑的命令行)

-DWITH_SSL=system \  #启用ssl库支持(安全套接层)

-DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock

\  #指定sock路径

-DMYSQL_TCP_PORT=3306 \

-DMYSQL_USER=mysql \  #指定mysql用户(默认为mysql)

-DENABLED_LOCAL_INFILE=1 \  #启用本地数据导入支持

-DWITH_DEBUG=0\  #禁用debug(默认为禁用)

禁用Profiling分析(默认为开启)

-DEXTRA_CHARSETS=all \  #启用额外的字符集类型(默认为all)

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci

# make && make install

复制配置文件

# cp support-files/my-medium.cnf /etc/my.cnf

初始化数据库

# chmod 755 scripts/mysql_install_db

# scripts/mysql_install_db --user=mysql

--basedir=/usr/local/mysql/ \

--datadir=/usr/local/mysql/data/

设置mysqld的开机启动

# cp support-files/mysql.server

/etc/init.d/mysql

# chmod 755 /etc/init.d/mysql

# chkconfig mysql on

设置PATH

# export PATH=/usr/local/mysql/bin:$PATH

让PATH生效

#source

/etc/profile

2 MySQL日志设置优化

首先看下BINLOG(做主从的时候要开启)(默认情况下，系统仅仅打开错误日志log_error)：

·binlog_cache_size：在事务过程中容纳二进制日志SQL语句的缓存大小，是为每个客户端分配的。可以从binlog_cache_use和binlog_cache_disk_use来判断binlog_cache_size的设置是否合理。

·max_binlog_cache_size：指所有binlog能够使用的最大cache内存大小。默认即可

·max_binlog_size：binlog日志的最大值，一般设置为512M或者1G，最大为1G。

·sync_binlog：默认0，表示当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让filesystem自行决定什么时候同步，或者cache满了之后才同步到磁盘。这个时候性能是最好的，但也是风险较大的。设置为1的时候，故障时最多只丢失binglog_cache中未完成的一个事务，是最安全但是性能损耗较大。对于高并发事务系统来说，sync_binlog保持默认即可。

·binlog_do_db：设置哪些数据库需要记录binlog；最好不要使用此设置

·binlog_ignore_db：设置哪些数据库不需要记录binlog；最好不要使用此设置

例如：

binlog_cache_size = 4M

##max_binlog_cache_size

= 16EB ##默认即可

max_binlog_size

= 512M

sync_binlog

= 0

SLOW QUERY LOG相关参数及使用建议(初期临时可以开启，动态变量可以直接关闭set global slow_query_log=OFF;)：slow_query_log_file

= /var/log/mylogs/slowquery.log

long_query_time

= 1

log-queries-not-using-indexes

QueryCache的缺点(所以一般禁用query_cache)：后端的任何一个表的任何一条数据发生变化后，就会将与该表有关的query的cache全部失效。query语句的hash运算以及hash查找资源CPU消耗；query cache中缓存的是result set，而不是数据页

QueryCache相关参数：

·have_query_cache：是否支持Query Cache；

·query_cache_limit：存放的单条query最大的result set，默认1M；

·query_cache_min_res_unit：query cache每个result set存放的最小内存大小，默认4K；

·query_cache_size：系统中用于query cache内存的大小；默认1048576B=1024K

·query_cache_type：是否启用Query Cache；(>= 5.6.8，默认禁止)

·query_cache_wlock_invalidate：针对MyISAM存储引擎，设置当有write lock在某个table上面的时候，读请求时要等write lock释放资源之后再查询，还是允许直接从query cache中读取结果，默认为false(可以直接从query cache中取出结果)；

Query Cache命中率=

Qcache_hits / ( Qcache_hits + Qcache_inserts )；

例如：query_cache_type

= 0 ##禁止Query Cache，MySQL>= 5.6.8，默认禁止

query_cache_limit

= 1M

query_cache_min_res_unit

= 4K

query_cache_size

= 1M

query_cache_wlock_invalidate

= 0 ##默认0，可以直接从query cache中取出结果

4 MySQL Server其他常用优化

网络连接与连接线程：

·max_connections：整个MySQL允许的最大连接数；一般来说500到800左右是一个比较合适的参考值，默认为151，可以依照Max_used_connections

/ max_connections * 100% ≈ 85%。

·net_buffer_length：网络包传输中，传输消息之前的net buffer初始化大小；系统默认大小为16KB，一般来说可以满足大多数场景

·max_allowed_packet：在网络传输中，一次传消息输量的最大值；这个参数与net_buffer_length相对应，只不过是net buffer的最大值。系统默认值为16MB，最大值是1GB，必须设定为1024的倍

数，单位为字节。当与大的BLOB字段一起工作，可以适当增加。

·back_log：在MySQL的连接请求等待队列中允许存放的最大连接请求数。(MySQL>= 5.6.6)默认为-1 (autosized)，初始为

50 + (max_connections / 5)，封顶为900

·thread_cache_size：Thread Cache池中应该存放的连接线程数。一般来说可能50到100之间应该就可以了。默认为-1 (autosized)，初始为 8 + (max_connections / 100)，封顶为100

·thread_stack：每个连接线程被创建的时候，MySQL给他分配线程使用的堆大小。使用系统的默认值(256KB)基本上可以所有的普通应用环境。

Threads_Cache_Hit =

(Connections - Threads_created) / Connections * 100%

Table Cache相关的优化：

·table_open_cache：设置系统中Cache的打开表文件描述符的数量，默认2000,(MySQL>= 5.6.12, autosized)。

Sort Buffer和Join Buffer：

·join_buffer_size：当Join是ALL，index，rang或者index_merge的时候使用的Buffer；一般设置为1-2M即可。

·sort_buffer_size：系统中对数据进行排序的时候使用的Buffer；一般设置设置为2-4M即可。

例如：max_connections

= 800

net_buffer_length = 16K

max_allowed_packet = 32M

back_log = -1 ##默认为-1

(autosized)，初始为

50 + (max_connections / 5)，封顶为900

thread_cache_size = -1 ##默认为-1 (autosized)，初始为

8 + (max_connections / 100)，封顶为100

thread_stack

= 256K

table_open_cache

= 2000 ##默认2000，MySQL>= 5.6.12,autosized

join_buffer_size

= 2M

sort_buffer_size

= 4M

5 MyISAM常用优化

索引缓存优化：

·key_buffer_size：索引缓存大小；一般来说，如果我们的MySQL是运行在32位平台纸上，此值建议不要超过2GB大小。如果是运行在64位平台纸上则不用考虑此限制，但也最好不要超过4GB。(只适用于MyISAM

Server)

·key_buffer_block_size，索引缓存中的Cache Block Size，默认即可；

Key_buffer_UsageRatio = (1 -

Key_blocks_used/(Key_blocks_used + Key_blocks_unused)) *100%

Key_Buffer_Read_HitRatio = (1 -

Key_reads/Key_read_requests) * 100%

Key_Buffer_Write_HitRatio

= (1 - Key_writes/Key_Write_requests) * 100%

表读取缓存优化：

·read_buffer_size，以Sequential

Scan方式扫描表数据时候使用的Buffer；每个Thread进行Sequential Scan的时候都会产生该Buffer，所以在设置的时候尽量不要太高，避免因为并发太大造成内存不够。系统默认为128KB，最大为2GB，设置的值必须是4KB的倍数，否则系统会自动更改成小于设置值的最大的4KB的倍数。可以尝试适当调大此参数看是否能够改善全表扫描的性能，一般设置为2MB即可。

·read_rnd_buffer_size，进行Random

Scan的时候使用的Buffer；read_rnd_buffer_size的默认值256KB，最大值为4G。一般来说，read_rnd_buffer_size值的适当调大，对提高ORDER BY操作的性能有一定的效果。一般设置为read_rnd_buffer_size

= 8M

并发优化：

·concurrent_insert：提高INSERT操作和SELECT之间的并发处理，使二者尽可能并行。大部分情况下concurrent_insert的值都被设置为1，当表中没有删除记录留下的空余空间的时候都可以在尾部并行插入。这其实也是MyISAM的默认设置。如果我们的系统主要以写为主，尤其是有大量的INSERT的时候。为了尽可能提高INSERT的效率，我们可以将concurrent_insert设置为2，也就是告诉MyISAM，不管在表中是否有删除行留下的空余空间，都在尾部进行并发插入，使INSERT和SELECT能够互不干扰。默认设置AUTO，保留默认设置即可。

例如：key_buffer_size

= 512M##此值只对MyISAM表有效

read_buffer_size = 2M

read_rnd_buffer_size = 8M

concurrent_insert = AUTO

Innodb缓存相关优化：

·innodb_buffer_pool_size：设置Innodb最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是缓存用户表及索引数据的最主要缓存空间，对Innodb整体性能影响也最大。一般为设置Innodb的Buffer Pool设置为整个系统物理内存的50%～80%之间

·innodb_log_buffer_size：这个参数就是用来设置Innodb的Log Buffer大小的，系统默认值为1MB。Log Buffer的主要作用就是缓冲Log数据，提高写Log的IO性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话，8MB以内的大小就完全足够了。

·innodb_additional_mem_pool_size：是用于存放Innodb的字典信息和其他一些内部结构所需要的内存空间。一个常规的几百个Innodb表的MySQL，如果不是每个表都是上百个字段的话，20MB内存已经足够了。

·innodb_doublewrite：Innodb在将数据同步到数据文件进行持久化之前，首先会将需要同步的内容写入存在于表空间中的系统保留的存储空间，也就是被我们称之为Double Write Buffer的地方，然后再将数据进行文件同步。默认ON即可。

·innodb_adaptive_hash_index：浅显一点就是给Buffer Pool中的数据做的索引，保持默认即可。

事务优化：

·innodb_flush_log_at_trx_commit：一般来说，如果完全不能接受数据的丢失，那么我们肯定会通过牺牲一定的性能来换取数据的安全性，选择设置为1。而如果我们可以丢失很少量的数据(比如说1秒之内)，那么我们可以设置为0。当然，如果大家觉得我们的OS足够稳定，主机硬件设备，而且主机的供电系统也足够安全，我们也可以将innodb_flush_log_at_trx_commit设置为2让系统的整体性能尽可能的高。(说明：如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！)

其他方面优化：

·innodb_flush_method：如果磁盘是通过RAID卡做了硬件级别的RAID，建议可以使用O_DIRECT，可以一定程度上提高IO性能。

·innodb_thread_concurrency：主要控制Innodb内部的并发处理线程数量的最大值，默认为0，完全让Innodb自己根据实际需要创建并行线程，而且在不少场景下设置为0还是一个非常不错的选择，尤其是当系统写IO压力较大的时候。过高的值可能导致线程的互斥。

例如：innodb_buffer_pool_size = 4G ##物理内存的50%～80%，假设为物理内存为8G

innodb_log_buffer_size = 8M

innodb_additional_mem_pool_size

= 20M

innodb_flush_log_at_trx_commit

= 2

innodb_flush_method

= O_DIRECT

innodb_thread_concurrency = 0

7数据存储优化

分散IO提升磁盘响应：

可以通过innodb_log_group_home_dir参数来指定Innodb日志存放位置，同时再通过设置数据文件位置innodb_data_home_dir参数来告诉Innodb我们希望将数据文件存放在哪里。

详细参照：MySQL_InnoDB_MyISAM优化及监控.docx

第五章MySQL监控

1使用pt-mysql-summary查看服务器配置

这个工具在percona-toolkit-2.2.5-2.noarch.rpm中(下面有介绍)，使用方法如下：

/usr/bin/pt-mysql-summary

--user=root --password=root --save-samples=/usr/tuzhen/pt-mysql-summary-2

对MySQL配置有个全面的了解。

2关注连接数

如果连接数达到了最大连接数，那不管有多少资源，用户都会阻塞在外面。

修改mysql最大连接数：

打开my.cnf，修改max_connections=100(默认为100)。

检查下最大的过往使用连接数，这个值在max_connections的85%左右是比较合适的，如果过高则是max_connections过少或者系统负荷过高了。

3show full

processlist

显示所有进程

mysql>

show full processlist;

+—–+——+———–+——+———+——+——-+———————–+

|

Id  | User | Host      | db

| Command | Time | State | Info                  |

+—–+——+———–+——+———+——+——-+———————–+

|

629 | root | localhost | NULL | Query

|    0 | NULL  | show full processlist |

|

633 | root | localhost | NULL | Sleep

|   11 |       | NULL                  |

+—–+——+———–+——+———+——+——-+———————–+

2

rows in set (0.00 sec)

如果正在运行的语句太多，运行时间太长，表示MySQL效率有问题。必要的时候可以将对应的进程kill掉。

mysql>

kill 633;

Query

OK, 0 rows affected (0.00 sec)

关注TIME参数，看看正在运行的用户进程有多少是长时间占用的，具体分析下。

4使用mysqlreport关注Connections和Threads

__

Connections _________________________________________________________

Max

used            6 of  500

%Max:   1.20

Total         914.64k     0.1/s

__

Threads _____________________________________________________________

Running             2 of    3

Cached              3 of    8

%Hit:  64.38

Created       325.79k     0.0/s

Slow                0       0/s

5关注系统锁情况

可以通过Cacti或者MySQLReport来监控，也可以show status like ‘%lock%’;

使用mysqlreport关注Table Locks，InnoDB Lock。

6关注慢查询日志

在需要是可以将慢查询日志打开:

在mysql配置文件my.cnf中增加

log-slow-queries=/var/lib/mysql/slowquery.log

(指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log)

long_query_time=2

(记录超过的时间，默认为10s，设置为0时，记录所有的查询)

log-queries-not-using-indexes

(log下来没有使用索引的query,可以根据情况决定是否开启)

log-long-format

(如果设置了，所有没有使用索引的查询也将被记录)

分析慢查询日志：

·返回平均查询时间最长的前十条语句

/usr/local/mysql/bin/mysqldumpslow  -s at -t 10

/usr/local/mysql/var/slow_queries.log

·返回查询次数最对的前十条语句

/usr/local/mysql/bin/mysqldumpslow

-s c -t 10 /usr/local/mysql/var/slow_queries.log;

·返回查询记录数最多的前十条语句

/usr/local/mysql/bin/mysqldumpslow

-s r -t 10 on;

7整理索引碎片

尽量不要让表的行数过大，有索引的尽量控制在1千万以内，没有索引的尽量控制在500万行。

8使用percona-toolkit-2.2.5-2.noarch.rpm

先开启慢查询日志，再利用这个套件里面的 /usr/bin/pt-query-digest命令去查看整个服务器的慢查询情况。(F:\百度云盘\百度云\MySQL\08_服务器测试\02_pt-query-digest)，作用=mysqldumpslow

9利用SHOW PROFILE单个语句进行分析

步骤大概如下：

--开启(这里开启的是会话级别的)

mysql>

set prfiling=1;

--查询语句

mysql>

select * from test001 where id=2;

--查看profiles

mysql>

show profiles;

--查看第一个profile

mysql>

show profile for query 1;

--按每个步骤消耗的时间排序，进一步查看

mysql>

set @query_id=1;

mysql>

SELECT STATE,SUM(DURATION) AS Total_R,

->

ROUND(

->

100*SUM(DURATION)/

->

(SELECT SUM(DURATION)

->

FROM INFORMATION_SCHEMA.PROFILING

->

WHERE QUERY_ID=@query_id

->

),2) AS Pct_R,

->

COUNT(*) AS Calls,

->

SUM(DURATION) / COUNT(*) AS "R/Call"

-> FROM INFORMATION_SCHEMA.PROFILING

-> WHERE QUERY_ID=@query_id

-> GROUP BY STATE

-> ORDER BY Total_R DESC;

10主从状态监控

在从服务器上运行shell脚本：#!/bin/sh

master_ip="10.1.51.178"

slave_ip="10.1.51.179"

mstool="/usr/local/mysql/bin/mysql

-h 10.1.51.178 -ureplication –p******* -P 3307"

sltool="/usr/local/mysql/bin/mysql

-hlocalhost -uroot  -P 3307"

declare

-a slave_stat

slave_stat=($($sltool

-e "show slave status\G"|grep Running |awk '{print $2}'))

if

[ "${slave_stat[0]}" = "Yes" -a

"${slave_stat[1]}" = "Yes" ]

then

echo "--OK-- slave is running"

declare -a slave_stat_2

slave_stat_2=($($sltool -e "show

slave status\G"|grep "Seconds_Behind_Master" |awk '{print

$2}'))

echo "Seconds_Behind_Master :

${slave_stat_2[0]}"

declare -a slave_stat_3

slave_stat_3=($($sltool -e "show

slave status\G"|grep "_Master_Log_Pos" |awk '{print $2}'))

echo "Read_Master_Log_Pos :

${slave_stat_3[0]}"

echo "Exec_Master_Log_Pos :

${slave_stat_3[1]}"

exit 0

else

echo "Critical slave is error"

exit 2

fi

[root@localhost

tuzhen]# sh slave_monitor.sh

--OK--

slave is running

Seconds_Behind_Master

: 0

Read_Master_Log_Pos

: 181751522

Exec_Master_Log_Pos

: 181751522