关于mysql的max_allowed_packet

 Mysql  关于mysql的max_allowed_packet已关闭评论
1月 132017
 

max_allowed_packet 定义的是所允许的单条sql语句的大小。

引用官方的说法:http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_allowed_packet


You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

解释一下:

  1. 该值的类型为integer,允许的最大值为1G (理论上4个字节的表达能力的上限是4G,也或许协议实现上硬编码了吧)
  2. 该变量的默认值为4MB,一般来讲是够的,如果存储大的BLOB列,可能不够,需要修改该配置
  3. 该值总是1KB的整数倍,最小值为1KB;修改的值应该是1024的整数倍,如果不是整数倍,则会按照小于该值的最接近的那个1024的整数倍进行截断处理,并产生1个warning;(注意: 如果设置的值小于1024,则自动调整为1024)如下:

    其实,将max_allowed_packet 设置为比 net_buffer_length 小的意义不大,所以,这里同样会出现一个warning,只是不会强制将max_allowed_packet修改为大于等于net_buffer_length的
    比较专业的设置该值的方法为:
  4. session的max_allowed_packet是不允许修改的,修改了全局配置对当前session也不会生效的,只有重新连接才能看到变化
 Posted by at 下午 12:00

记录一次sql优化

 Mysql  记录一次sql优化已关闭评论
1月 112017
 

一大早到公司,加班一宿的同事就跟我抱怨,说,一个表只有8w条数据,需要更新一列,根据目前进度估算,大概需要3个小时,即使不需要更新,查询每条数据也需要400ms左右;

或许确实配置太低,或许确实表的字段太多,我并没有过于吃惊,但是3小时确实太多,便一起查了一下:

  1. iops并不高,说明内存够用
  2. cpu很高,很可能没有合适的索引,说是用了索引了,还是确认一下吧
  3. 果然搞错了,没有用到索引
  4. 8w条数据,添加索引也应该很快,于是添加了一个索引,耗时不到10s
  5. 执行sql语句,还是很慢,explain显示可能能用到我们刚才添加的索引,但是rows上来看还是全表扫描,为什么?
  6. sql语句大致为: update table1 set bb=1 where aa=123
  7. 现在已经基本可以怀疑aa的类型应该不是int的了,查看表结构,果然aa的类型为varchar
  8. 修改sql语句:update table1 set bb=1 where aa=’123′
  9. 重新开始更新8w条数据,共耗时不到10s

分析:

因为aa是varchar类型,如果要和一个数字比较,势必要对aa进行转换,如此就用不到索引了;这种错误太容易出现了

 Posted by at 上午 9:45

非主流抓包工具

 Linux & Unix, Mysql  非主流抓包工具已关闭评论
7月 252016
 

httpry:

An open-source HTTP packet sniffing tool which captures live HTTP packets with libpcap library, and displays HTTP requests and responses in a human-readable format. It comes with a collection of parsing Perl scripts for mining various information from its standard output.

效果:

安装:

  1. linux下yum可以安装
  2. 其它系统编译安装,源码: https://github.com/jbittel/httpry (从源码来看,虽然是c写的,似乎可以写perl插件)

mysql-sniffer

https://github.com/zorkian/mysql-sniffer  (go实现的,依赖libcap-devel)

 Posted by at 下午 6:36

mysql 数据导入导出

 Mysql  mysql 数据导入导出已关闭评论
7月 042016
 

场景:

mysql导出导入大数据文件时,如果文件很大,导入时最好先把索引都去掉,如果导出时包含表结构,则在一个很大的文件上编辑表结构将非常麻烦,所以正确的做法:

1. 导出表结构(不包含数据)

2. 编辑表结构(去掉索引)

3. 创建表

4. 导出表数据 (不包含表结构)

5. 导入数据

6. 添加索引

 

附:

解压大文件直接到mysql:

如何知道灌到什么程度了?

tar 在解压的时候,使用的也是外部的解压程序(如:gzip),通过管道交互,可以查看gzip读的那个文件读到哪里了,如:

 

 Posted by at 下午 6:56

mysql 之 sql_mode

 Mysql, 默认分类  mysql 之 sql_mode已关闭评论
6月 102016
 

sql_mode 之影响:

  1.  sql_mode=’ansi’; 则当插入时,如果数据超长,则截断插入;有时候,这不是我们想要的

总结:

  • sql_mode=’TRADITIONAL’; 可以避免很多不必要的意外;

    TRADITIONAL 等价于 下面一系列标准的集合
  • 注意: 与innodb_strict_mode的区别

实际应用:

  1. 可以直接在 /etc/my.cnf 中修改sql_mode,如下:(需要重启mysql)
  2. 如果不方便重启mysql,则可以在执行sql语句前进行回话级别的设置,如下:

    (每次连接后都需要执行,比较麻烦;对于长连接的场景,该设置的花费可以忽略)
  3. 设置全局sql_mode:

    注意:
    1) 需要有超级权限
    2) 对当前session无效;要么退出再进来,要么再设置一下当前回话的sql_mode
    3)  该设置重启mysqld就没了,最好在my.cnf 中写一下

参考资料:

 Posted by at 下午 1:57

设置mysql默认字符集和校对规则

 Mysql  设置mysql默认字符集和校对规则已关闭评论
6月 102016
 

在 /etc/my.cnf 中添加:

当然:

  1. 上面修改需要重启
  2. 对已有的表不生效,对于已有的数据库上创建新表是有效的
 Posted by at 下午 1:34

mysql监控管理工具–innotop

 Mysql  mysql监控管理工具–innotop已关闭评论
2月 142016
 

INNOTOP是一个通过文本模式显示MySQL和InnoDB的监测工具。INNOTOP是用PERL语言写成的,这使它能更加灵活的使用在各种操作平台之上,它能详细的的监控出当前MYSQL和INNODB运行的状态,以DBA根据结果,可以合理的优化MYSQL,让MYSQL更稳定更高效的运行。

官方地址: http://innotop.googlecode.com/svn/html/index.html

github地址: https://github.com/innotop/innotop

 

安装: 先试试yum search innotop

 

参考资料: http://blog.csdn.net/wyzxg/article/details/8609981

 

 Posted by at 下午 3:51

关于mysql锁的学习

 Mysql  关于mysql锁的学习已关闭评论
12月 252015
 

锁的类型: 表锁、页锁、行锁、间隙锁【读锁(共享锁)、写锁(排他锁)】、乐观锁、悲观锁(晕,其实没这么多东西)

mysql.INNODB_LOCKS 这张表记录了发生了锁争用的信息;虽然一个事务正在加了一些锁,如果没有其它session等待这些锁的话,这个表里面也是查不到的哦

但是,A表正在事务中被更新时,如果:

lock tables A write;

虽然被阻塞,但是 mysql.INNODB_LOCKS里面却查不到任何记录!  (why: lock tables 不是存储引擎级别的锁)

 

实例分析:

表结构:

sql语句1:

sql语句2:

如果简单认为innodb的锁都是行锁(尤其是select和insert操作的压根儿也不是一行),怎么可能发生死锁呢?

让我们模拟一下,分别在两个session中一行一行地交替执行两个事务,我们发现,确实在insert的时候死锁了

session 1:

 

session 2:

 

如果我们不着急去执行session 2中的insert的语句,而是查询一下存在哪些锁,发现如下:

什么是supremum pseudo-record?

supremum pseudo-record :相当于比索引中所有值都大,但却不存在索引中,相当于最后一行之后的间隙锁

解决办法:

 

参看: http://blog.itpub.net/26250550/viewspace-1070422/

 

问题: 两个select … for update ; 加的是共享锁?否则不能同时两个select语句都能成功执行。还如何查证?

不能简单地说 select … for update 加的是共享锁还是排他锁; 如果select有结果,则加行锁,此时为排他锁; 如果没有结果,则加间隙锁,此时为共享锁(和间隙大小没有关系); 如果没有在事务中,则select for update是不加锁的;

参看: Enabling InnoDB Monitors: https://dev.mysql.com/doc/refman/5.6/en/innodb-enabling-monitors.html

可以查看当前被设置的锁的信息:

部分信息如下:

 

下面是insert 语句等待时的锁情况:

 

学习资料:

http://hedengcheng.com/?p=771    非常不错

 Posted by at 下午 2:44

mysql query cache

 Mysql  mysql query cache已关闭评论
11月 232015
 

Qcache_free_blocks: 表示查询缓存中目前还有多少剩余的blocks,如果该值显示较大,则说明查询缓存中的内存碎片过多了,可能在一定的时间进行整理

Qcache_free_memory: 查询缓存的内存大小,通过这个参数可以很清晰的知道当前系统的查询内存是否够用,是多了,还是不够用,DBA可以根据实际情况做出调整

Qcache_hits: 命中多少

Qcache_inserts: 写入多少

Qcache_lowmem_prunes: 空间不够,挤出多少 (?)

Qcache_not_cached: 有多少查询因不符合条件而没有被写入cache (如:结果集太大、sql条件中使用了函数、select 1等等)

Qcache_queries_in_cache: 当前缓存了多少查询

Qcache_total_blocks:  当前缓存的block数量

 

 

参考:

http://www.111cn.net/database/mysql/44889.htm

http://www.jb51.net/article/58537.htm

 Posted by at 下午 1:21

mysql 之字符乱码

 Mysql  mysql 之字符乱码已关闭评论
11月 112015
 

环境:

mysql数据库表设置字符集为utf8;console终端显示字符集为utf8;如下:

mysql 命令查询的结果显示都是“问号”,解决办法:

 

 Posted by at 下午 2:35