通过InnoDB监控状态分析锁占用

一、表结构

1
2
3
4
5
6
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `stage` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_b` (`stage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

二、表数据

1
2
3
4
5
6
7
8
+------+---------+
|   id |   stage |
|------+---------|
|    1 |       1 |
|    4 |       4 |
|    9 |       9 |
|   15 |      15 |
+------+---------+

三、简单实例

1. 事务执行时序表

事务(ID:41114)，在索引idx_b值为4上加排他锁。

T1(41114)
begin;
select * from t where stage = 4 for update;

2. 查看InnoDB运行状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
---TRANSACTION 41114, ACTIVE 41 sec
4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 39, OS thread handle 0x700000a83000, query id 36756 localhost ::1 root cleaning up
TABLE LOCK table `test`.`t` trx id 41114 lock mode IX
RECORD LOCKS space id 245 page no 4 n bits 80 index `idx_b` of table `test`.`t` trx id 41114 lock_mode X
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 4; hex 80000004; asc     ;;

RECORD LOCKS space id 245 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 41114 lock_mode X locks rec but not gap
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000004; asc     ;;
 1: len 6; hex 00000000a08b; asc       ;;
 2: len 7; hex dd000001fa0110; asc        ;;
 3: len 4; hex 80000004; asc     ;;

RECORD LOCKS space id 245 page no 4 n bits 80 index `idx_b` of table `test`.`t` trx id 41114 lock_mode X locks gap before rec
Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000009; asc     ;;

3. 解读InnoDB运行状态

事务T1总共加了4把锁：1把表，3把行锁。
表锁类型：IX

行锁根据索引划分为加在聚集索引 PRIMARY 上的行锁和加在普通索引 idx_b 上的行锁。
加在聚集索引上的行锁1把，是一个排他的记录锁；
加在普通索引上的行锁2把，1把是next-key lock，锁住的范围为(1, 4]；1把是gap lock，锁住的范围为(4, 9)。

数据存储

阅读全文 >>

开启InnoDB监控

当前mysql版本：5.6.27

一、背景

在mysql处理死锁问题时，由于show engine innodb status输出来的死锁日志无任务事务上下文，并不能很好地诊断相关事务所持有的所有锁信息，包括：锁个数、锁类型等。

于是，需要能查看到更详细的事务锁占用情况。

二、INNODB监控机制(InnoDB Monitors)

mysql提供一套INNODB监控机制，用于周期性(每15钞)输出INNODB运行相关状态(INNODB运行状态、表空间状态、表状态等)到mysqld服务标准错误输出。另外，INNODB标准监控和锁监控，也可以通过命令：show engine innodb status输出到控制台。
此部分内容一般输出到mysql error log里(查找日志位置，参见“补充知识”)。

官方说明(详见参考文档1)如下：

1
When you enable InnoDB monitors for periodic output, InnoDB writes their output to the mysqld server standard error output (stderr). In this case, no output is sent to clients. When switched on, InnoDB monitors print data about every 15 seconds. Server output usually is directed to the error log (see Section 5.4.2, “The Error Log”). This data is useful in performance tuning. On Windows, start the server from a command prompt in a console window with the --console option if you want to direct the output to the window rather than to the error log.

该类监控机制默认是关闭状态，分析问题需要查看监控日志时再开启。
建议分析问题后，将监控关闭；否则，每15秒输出一次INNODB运行状态信息到错误日志，会使用日志变得特别大。

三、开启状态监控

INNODB监控机制目前主要提供如下四类监控：

  • 标准监控(Standard InnoDB Monitor)：监视活动事务持有的表锁、行锁；事务锁等待；线程信号量等待；文件IO请求；buffer pool统计信息；InnoDB主线程purge和change buffer merge活动。
  • 锁监控(InnoDB Lock Monitor)：提供额外的锁信息。
  • 表空间监控(InnoDB Tablespace Monitor)：显示共享表空间中的文件段以及表空间数据结构配置验证。
  • 表监控(InnoDB Table Monitor)：显示内部数据字典的内容。

关于四类监控开启与关闭方法，一言以蔽之，主要是通过创建系统可识读的特殊表名来完成。特别地，除表空间(InnoDB Tablespace Monitor)监控和表监控(InnoDB Table Monitor)外，其他二类监控还可能通过修改系统参数来完成。
基于系统表的方式和基于系统参数的方式，只要使用二者其中一种方式开启监控即可。

1. 标准监控(Standard InnoDB Monitor)

基于系统表：innodb_monitor

mysql会通过检查是否存在名为innodb_monitor的数据表，来判断是否开启标准监控，并打印日志。
需要开启，则创建表；需要关闭，则删除表。

1
2
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_monitor;

基于系统参数：innodb_status_output

自mysql 5.6.16版本之后，可以通过设置系统参数(innodb_status_output)的方式开启或者关闭标准监控。

1
2
set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output=OFF;
数据存储

阅读全文 >>

mysql前缀索引大小限制

一、背景

更改数据表存储引擎由MyISAM修改为INNODB，提示如下错误：
ERROR 1071: Specified key was too long; max key length is 767 bytes

二、问题描述

今有一张数据表如下，其结构如下：

1
2
3
4
5
6
7
8
CREATE TABLE IF NOT EXISTS `base_info`
(
  `id` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ID',
  `name` varchar(256) DEFAULT NULL COMMENT '名称',
  `urls` varchar(1000) DEFAULT NULL COMMENT '链接',
  `partition_month` varchar(256) NOT NULL DEFAULT '' COMMENT '快照月份',
  PRIMARY KEY (`id`,`partition_month`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='基础信息表';

由于基于此表可能会用到事务。因此，MyISAM不满足需求，需要将表ENGINE修改为INNODB。
具体SQL语句如下：

1
2
ALTER TABLE `base_info`
ENGINE = InnoDB;

三、解决方案

1. 原因分析

不同的数据表存储引擎限制的索引长度不一。
数据表存储引擎MyISAM最大索引长度为 1000B，而INNODB默认最大索引长度为767B。

上述问题涉及的primary key中partition_month长度为 256 * 3B = 768B > 767B。

2. 问题处理

修改索引字段长度，并指定用于索引建立的前缀长度。
目前能想到的问题处理方案有如下两种：

基于业务场景

根据业务需求调整不合理的字段类型或者长度。本案例就属于这类情况。

从业务角度来看，partition_month值形如：201607。
因此，可以将字段类型调整长度为int类型或者将partition_month字符长度设置成 char(6) 即可。

1
2
3
ALTER TABLE `test`.`base_info`
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`, `partition_month`(6));

基于数据区分度

根据具体的数据区分度来设置前缀索引长度。

假设需要为urls建立索引，可以事先查看当前字段应用不同前缀长度的区分度。
如下是取当前urls前缀长度为1000、100、125、150、200时的数据区分度：

1
2
3
4
5
6
select count(distinct urls)/count(*) as urls_full, count(distinct left(urls,100))/count(*) as urls_100, count(distinct left(urls,125))/count(*) as urls_125, count(distinct left(urls,150))/count(*) as urls_150, count(distinct left(urls,200))/count(*) as urls_200 from base_info;
+----------+----------+----------+----------+----------+
| urls_full | urls_100 | urls_125 | urls_150 | urls_200 |
+----------+----------+----------+----------+----------+
|   0.9859 |   0.9858 |   0.9858 |   0.9859 |   0.9859 |
+----------+----------+----------+----------+----------+

从结果可以看出，当urls取前缀长度为150时，其区分度与完整长度一致。

数据存储

阅读全文 >>

mysql insert锁机制

一、前言

上周遇到一个因insert而引发的死锁问题，其成因比较令人费解。
于是想要了解一下insert加锁机制，但是发现网上介绍的文章比较少且零散，挖掘过程比较忙乱。

本以为只需要系统学习一个较完全的逻辑，但是实际牵扯很多innodb锁相关知识及加锁方式。我好像并没有那么大的能耐，把各种场景的加锁过程一一列举并加之分析；亦没有太多的精力验证网上的言论的准确性。

只好根据现在了解的内容，参考官方文档，说说自己当前的理解。
本文仅供参考，如有误导，概不负责。

二、现场状态

不同的mysql版本，不同的参数设置，都可能对加锁过程有影响。
分析加锁机制还是应当尽可能多地列举一下关键参数，例如：当前mysql版本、事务隔离级别等。
如下，仅仅只列出个别比较重要的参数。

1.数据库版本

1
2
3
4
5
6
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.27    |
+-----------+

2. 数据库引擎

1
2
3
4
5
6
7
8
mysql> show variables like '%engine%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+

注：InnoDB支持事务，Myisam不支持事务；InnoDB支持行锁和表锁；Myisam不支持行锁。

3. 事务隔离级别

1
2
3
4
5
6
mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+-----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation  |
+-----------------------+------------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ        | REPEATABLE-READ |
+-----------------------+------------------------+-----------------+

注：几种事务隔离级别：READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

4. 查看gap锁开启状态

1
2
3
4
5
6
mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF   |
+--------------------------------+-------+

innodb_locks_unsafe_for_binlog：默认值为0，即启用gap lock。
最主要的作用就是控制innodb是否对gap加锁。
但是，这一设置变更并不影响外键和唯一索引（含主键）对gap进行加锁的需要。
开启innodb_locks_unsafe_for_binlog的REPEATABLE-READ事务隔离级别，很大程度上已经蜕变成了READ-COMMITTED。

参见官方文档[^1]：

By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.

Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

The effect of enabling innodb_locks_unsafe_for_binlog is similar to but not identical to setting the transaction isolation level to READ COMMITTED.

5. 查看自增锁模式

1
2
3
4
5
6
mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+

innodb_autoinc_lock_mode有3种配置模式：0、1、2，分别对应”传统模式”, “连续模式”, “交错模式”。[^8]
传统模式：涉及auto-increment列的插入语句加的表级AUTO-INC锁，只有插入执行结束后才会释放锁。这是一种兼容MySQL 5.1之前版本的策略。
连续模式：可以事先确定插入行数的语句(包括单行和多行插入)，分配连续的确定的auto-increment值；对于插入行数不确定的插入语句，仍加表锁。这种模式下，事务回滚，auto-increment值不会回滚，换句话说，自增列内容会不连续。
交错模式：同一时刻多条SQL语句产生交错的auto-increment值。

由于insert语句常常涉及自增列的加锁过程，会涉及到AUTO-INC Locks加锁过程。
为了分步了解insert加锁过程，本文暂不讨论任何涉及自增列的加锁逻辑。
这一参数设置相关内容可能会出现在我的下一篇文章里。

数据存储

阅读全文 >>

request entity too large

一、问题描述

拍照上传图片时，系统提示: request entity too large，错误码为413。

二、问题分析

翻阅http状态码查看413解释如下:

413 Request entity too large
The request is larger than what the server is able to process.

由此可知，应该是上传照片超过服务器的限制。

三、解决方案

1. 解除域名服务器限制

一般情况下，域名服务器选用apache和nginx比较多。

apache

对apache服务器而言，相关参数为: LimitRequestBody
默认值为0，表示最大2GB。

官方解释如下:

1
2
3
4
5
6
7
Description:	Restricts the total size of the HTTP request body sent from the client
Syntax:	LimitRequestBody bytes
Default:	LimitRequestBody 0
Context:	server config, virtual host, directory, .htaccess
Override:	All
Status:	Core
Module:	core
后端开发

阅读全文 >>

XtraFinder in 10.11

一、背景

MAC系统升级到10.11后，系统提示XtraFinder不可用。

二、解决方案

XtraFinder需要注入自己的代码到系统Finder处理过程。
而由于MAC 10.11开始系统默认加入了 SIP(System Integrity Protection) 机制，

1. 关闭SIP

重启mac，在开机启动画面长按Command + R，进入恢复模式。
在顶部实用工具菜单，打开Terminal。
输入：csrutil enable --without debug，即可。

2. 恢复SIP

进入恢复模式，打开Terminal。
输入：csrutil clear，即可。

三、参考文档

工具软件

中止错误的多行SQL语句执行

一、背景

经常会出现在命令行编写多行SQL过程中，发现语法有问题。
此时，需要中止错误的SQL语句，取消SQL执行。

二、实例

编写多行SQL，中途发现语法错误，可以通过\c来中止。
如若SQL内容较多，可以通过\p打印出已经编写的SQL语句。

1
2
3
4
5
6
7
mysql> select 123
    -> \p
--------------
select 123
--------------

    -> \c
数据存储

阅读全文 >>

xml.etree.ElementTree解析xml

本文将介绍如何使用python基本类库xml.etree.ElementTree解析xml文档。

一、前言

1. xml解析支持

python标准库提供三种方法解析XML: SAX、DOM以及ElementTree。

SAX (simple API for XML )

python 标准库包含SAX解析器，SAX用事件驱动模型，通过在解析XML的过程中触发一个个的事件并调用用户定义的回调函数来处理XML文件。

DOM(Document Object Model)

将XML数据在内存中解析成一个树，通过对树的操作来操作XML。

ElementTree(元素树)

ElementTree就像一个轻量级的DOM，具有方便友好的API。代码可用性好，速度快，消耗内存少。

2. ElementTree介绍

xml.etree.ElementTree模块提供了一个轻量级、Pythonic的API，同时还有一个高效的C语言实现，即xml.etree.cElementTree。
与DOM相比，ET的速度更快，API使用更直接、方便。
与SAX相比，ET.iterparse函数同样提供了按需解析的功能，不会一次性在内存中读入整个文档。ET的性能与SAX模块大致相仿，但是它的API更加高层次，用户使用起来更加便捷。
因此，使用python解析xml时，推荐使用ElementTree这种方式。

系统脚本

阅读全文 >>

python 520

一、前言

最近跟老婆大人都在学习python。
正值一年一度的5月20号之际，给老婆的礼物除了发520的红包外，就是这个老婆让我帮她完成的小小python程序了。

二、代码

1
2
3
4
5
6
7
8
9
10
11
#!/usr/bin/env python
#coding=utf8


if __name__ == '__main__':
    while True:
        s = raw_input("请输入甜言蜜语：")
        if s == 'exit' or s == 'quit':
            break;

        print '爱的回应：六点就走，回家陪你。'

三、运行截图

![520运行图]http://o7kubqw1j.bkt.clouddn.com//images/article/python 520/520_run.png)
![520运行图]http://o7kubqw1j.bkt.clouddn.com//images/article/python 520/520_run.png)

系统脚本

Python菜鸟教程学习笔记 - 条件语句

一、前言

python里的真为True，假为False，空为None.
Python程序语言指定任何非0和非空（None）条件判断结果为True，0 或者None条件判断结果为False。

二、条件判断形式

条件判断形式为：

1
2
3
4
5
6
7
8
if 判断条件1:
    执行语句1……
elif 判断条件2:
    执行语句2……
elif 判断条件3:
    执行语句3……
else:
    执行语句4……

注：别忘了if, elif, else关键字开头行尾的冒号。

系统脚本

阅读全文 >>

|