MySQL
文章平均质量分 74
胡儿胡儿
好记性不如烂博客
展开
-
理解show engine innodb status之LOG
mysql版本:Ver 5.6.34-debug for Linux on x86_64 (Source distribution)---LOG---Log sequence number 1626037Log flushed up to 1626037Pages flushed up to 1626037Last checkpoint at 16260370原创 2016-11-23 14:30:54 · 1209 阅读 · 0 评论 -
理解mysql字符集(mysql 5.6)
字符集概述和基本指令字符集(character)是一套符号和编码。校对规则(collation)是在字符集内用于比较字符的一套规则,拿mysql官方文档的例子来说:假设我们有一个字母表使用了四个字母:‘A’、‘B’、‘a’、‘b’。我们为每个字母赋予一个数值:‘A’=0,‘B’= 1,‘a’= 2,‘b’= 3。字母‘A’是一个符号,数字0是‘A’的编码,这四个字母和它们的编码组合在一起是原创 2015-12-09 01:49:50 · 902 阅读 · 0 评论 -
理解show engine innodb status之BUFFER POOL AND MEMORY
mysql版本:Ver 5.6.34-debug for Linux on x86_64 (Source distribution)----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 139984896; in additional pool allocat原创 2016-11-23 21:37:16 · 2493 阅读 · 0 评论 -
innodb_autoinc_lock_mode的表现形式和选值参考
前提条件,percona 5.6版本,事务隔离级别为RRmysql> show create table test_autoinc_lock\G*************************** 1. row *************************** Table: test_autoinc_lockCreate Table: CREATE TABLE原创 2016-10-31 22:13:06 · 6009 阅读 · 0 评论 -
innodb_index_stats导入备份数据时报错表主键冲突
故障描述percona5.6,mysqldump全备份,导入备份数据时报错Duplicate entry 'hoc_log99-item_log_27-PRIMARY-n_diff_pfx01' for key 'PRIMARY'故障原因查看了下这个主键应该是mysql系统库下的系统表innodb_index_statsmysql> show create table innodb原创 2016-10-08 18:43:30 · 2131 阅读 · 0 评论 -
理解innodb的锁(record,gap,Next-Key lock)
Record lock单条索引记录上加锁,record lock锁住的永远是索引,而非记录本身,即使该表上没有任何索引,那么innodb会在后台创建一个隐藏的聚集主键索引,那么锁住的就是这个隐藏的聚集主键索引。所以说当一条sql没有走任何索引时,那么将会在每一条聚集索引后面加X锁,这个类似于表锁,但原理上和表锁应该是完全不同的。Gap lock在索引记录之间的间隙中加锁,或者是原创 2016-01-27 21:45:38 · 34929 阅读 · 12 评论 -
故障案例----tokudb启动失败
现象tokudb显示正常关闭2016-10-18 14:22:19 144471 [Note] /opt/udb/program/mysql/percona-5.6.21/bin/mysqld: Normal shutdown..........2016-10-18 14:22:23 144471 [Note] Shutting down plugin 'mysql_old原创 2016-10-18 15:09:27 · 2386 阅读 · 0 评论 -
mysql init_connect的几个要点
init_connect的作用init_connect通常用于:当一个连接进来时,做一些操作,比如设置autocommit为0,比如记录当前连接的ip来源和用户等信息到一个新表里,当做登陆日志信息使用init_connect的注意点1 只有超级账户才可以设置(super_priv权限)2 超级账户无视init_connect设置(即init_connect的设置对来自超级账户的连接原创 2016-08-23 16:53:28 · 5239 阅读 · 0 评论 -
故障案例--binlog_format不为row模式下关于时区设置的一个坑
故障现象有个需求从北美的机房A的DB1为主,北美机房B的DB2为从,建立主从关系。A机房为UTC时区,B机房为PDT时区,结果由于system_time_zone由DB所在的linux时区决定,而这个不可擅自更改,采用了修改time_zone的方式来迂回解决,但是发现主从的时间数据依旧不一致。复现方法============这是主库的测试============mysql> sho原创 2016-07-21 18:21:09 · 1693 阅读 · 0 评论 -
理解mysql的slow log
PS:所有的前提,慢查询时间设置为3s1 log_output参数为TABLE时,慢查询记录到mysql.slow_log表里,但这时这个系统表没有任何索引,我们一般可以在start_time列自行加上索引方便检索,类似这样mysql> show create table mysql.slow_log\G*************************** 1. row ******原创 2016-06-13 12:19:20 · 4350 阅读 · 0 评论 -
故障案例--将存储引擎改为memory时报错the table is full
原创 2016-06-06 10:50:20 · 739 阅读 · 0 评论 -
故障案例--mysql5.6启动失败
现象:之前发生了一次oom,启动失败,错误日志截图如下原因分析初步判定还是内存不足,这个db的内存才600M,缓冲池为300M,将先缓冲池调整到100M后启动成功;接着分析,之前是由于oom导致,启动时算是一个recovery的过程,启动后需要回滚之前的事务,或是commit之前redo提交的事务,这里应该也需要一些内存再接着分析,发现performance_schema为原创 2016-06-01 18:16:08 · 958 阅读 · 0 评论 -
故障案例--多表join引发磁盘空间撑爆
现象:磁盘空间使用率的监控曲线有一个非常陡峭的峰值,达到90%后瞬间下滑问题分析步骤:查看那段时间的慢查询记录,定位到是一个多表的join操作(PS,mysql.slow_log表里的start_time列指的是SQL执行结束的时间)根据SQL的解析计划可知中间结果集非常大,超过了tmp_table_size大小设置,于是在磁盘空间上建立myisam磁盘临时表,重新复现了一遍,确实原创 2016-06-01 17:29:05 · 617 阅读 · 0 评论 -
理解innodb的表空间管理
Advantages of File-Per-Table Mode1 当truncate或者drop一个表时可以释放磁盘空间。如果不是独立表空间,truncate或drop一个表只是在ibdata文件内部释放,实际ibdata文件并不会缩小,释放出来的空间也只能让其他innodb引擎的表使用(有时这会造成磁盘空间容量监控的一个痛点,无法判断它到底有多满)2 独立表空间下,truncate原创 2016-02-22 12:10:50 · 3708 阅读 · 1 评论 -
理解innodb的索引页结构,插入缓冲,自适应哈希索引
Physical Structure of an InnoDB Index所有的innodb索引都是btree索引,索引记录保存在叶子上,默认的索引页大小是16K。当有新的记录插入时,innodb出于对将来的insert和update操作的考虑,会尝试留下1/16的空闲页大小。如果索引记录是完全按照索引记录的大小顺序插入的,那么索引也将填满整个页大小的15/16,如果插入顺序完全随机,那么原创 2016-02-22 10:48:46 · 4353 阅读 · 0 评论 -
innodb压缩
Overview of Table Compression 通过使用压缩技术,一般有两个好处:一是减少磁盘和内存之间的数据传输量从而提升性能,另外一个是减少磁盘和内存空间的使用量。尤其是二级索引意义更大,因为索引数据同样可以被压缩。在针对SSD设备,压缩非常重要,因为SSD通常比较贵,所以需要尽量控制空间的使用。 由于处理器和cache技术发展超过磁盘存储设备技术,许多工作原创 2016-03-29 14:18:41 · 1557 阅读 · 0 评论 -
组复制官方文档翻译(组复制原理)
Group Replication Background(组复制技术原理)创建容错系统的最常见方法是使组件冗余,换句话说,部分组件可以删除,系统应该继续按预期运行。这产生了一系列挑战,将这种系统的复杂性提高到一个完全不同的水平。具体来说,复制的数据库必须处理这样的情况,即它们需要维护和管理几个服务器而不是一个。此外,由于多个服务器组成了一个“组”的概念来相互协同工作,必须处理几个其他经典分布式翻译 2016-12-22 18:21:55 · 3047 阅读 · 0 评论 -
故障案例--mysql5.5分区表的一个坑
故障现象db每隔一段时间就异常重启,查看DB错误日志的错误日志Database was not shut down normally相关的信息,而查看/var/log/message并没有发现什么异常,没有发生OOM。由于每次异常重启的间隔都比较相近,所以怀疑是业务的某个sql引起的,后来经过业务层排查,发现每隔一段时间都会执行一条如下SQL语句select sid as sid,sour...原创 2016-07-21 17:19:21 · 8298 阅读 · 5 评论 -
Gone away故障原因排查
原创 2018-05-04 11:17:20 · 526 阅读 · 0 评论 -
xtrabackup对于flush tables with read lock操作的设置
参数说明版本 percona-xtrabackup-2.4.8-Linux-x86_64 --kill-long-queries-timeout=N 指的是执行flush tables with read lock以后,如果flush操作被卡了N秒,则杀掉卡住它的线程,默认0的情况就是不杀死任何卡住flush的sql,直到该sql执行完成--kill-long-query-type=all|s...原创 2018-03-08 21:29:28 · 2272 阅读 · 0 评论 -
mysql统计信息收集设置
innodb_stats_persistent决定两种收集方式默认为on,指定InnoDB索引统计信息是否保存到磁盘。如果为off,那么在服务器重启或者一些其他操作时会清空之前的统计信息,其实就是清空mysql.innodb_table_stats和mysql.innodb_index_stats表数据。当下一次表访问时再次重新计算,这可能会导致查询执行计划发生变化。可以在创建表之前在全局级别设置...原创 2018-02-08 15:51:51 · 1639 阅读 · 0 评论 -
跨版本导入数据导致mysqld崩溃
现象mysqld突然崩溃,错误日志Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 7f原创 2018-01-29 11:56:31 · 2121 阅读 · 0 评论 -
故障案例--mysql如何定位执行完但未提交的事务内容
问题描述我们经常会碰到这样的情况,某个事务执行完了未提交,后续再来一个DDL和DML操作,导致后面的session要么处于waiting for metalock,要么是锁等待超时。这时我们往往只能找到这个未提交的事务的事务id和session id,但是一般都处于sleep状态,不好分析事务内容到底是什么,所以通常都是粗鲁地kill这个session后解决问题,但是应用层的研发人员往往找原创 2016-07-27 15:37:15 · 5030 阅读 · 0 评论 -
mysql唯一索引的一个小常识--Duplicate entry 'XXX' for key 'XXX'
概述之前一直有个小误区,我以为mysql的唯一索引肯定是区分大小写的,然而实际上utf8字符集下,默认排序规则utf8_general_ci 情况下,是不区分大小写的。而在排序规则utf8_bin下是区分大小写的,这就有可能出现以下情况:之前字段是varchar binary类型,即排序规则为utf8_bin,后来将该字段改回varchar的话,就会导致唯一键冲突错误测试原创 2017-11-10 15:36:42 · 43465 阅读 · 0 评论 -
gtid主从报错When@@SESSION.GTID_NEXT is set to a GTID
故障现象gtid主从报错信息When@@SESSION.GTID_NEXT is set to a GTID, you must explicitly set it to a differentvalue after a COMMIT or ROLLBACK. Please check GTID_NEXT variable manual pagefor detailed explana原创 2017-08-18 17:18:12 · 6280 阅读 · 0 评论 -
sql_log_bin在GTID复制下的一个现象
背景现网环境是M-M GTID+haproxy+组成的高可用需要做一个基于时间点的回档之前的方法:1 新建出另外一组M-M GTID高可用集群。我们将新的集群成为B,旧的集群称为A2 分别导入A的历史备份到B集群的两个实例上,分开导入,导入期间双主同步不关,但会关闭sql_log_bin3 分别导入A的增量binlog到B集群的两个实例上,分开导入,导入期间双主同步不关,但会原创 2017-07-04 17:48:53 · 628 阅读 · 0 评论 -
故障案例---innodb表出现大量的Waiting for table level lock
故障现象show full processlist发现大量的innodb表出现Waiting for table level lock,业务将近不可用原因分析1 一开始当然是认为这是myisam引擎导致的,扫了一圈发现该db下确实有一个myisam表;2 不过故障时无论是show processlist还是innodb_trx等结果看,都没有发现这个myisam表的记录;3原创 2017-06-21 16:23:20 · 21632 阅读 · 2 评论 -
故障案例--寻找瓶颈SQL的一种方法
故障现象DB响应非常慢,连接数暴涨直到打满;任何SQL看起来都是慢查询,都要几十秒以上;show processlist时SQL种类非常多,短时间无法分辨哪个是引起故障的SQL,挑了几个看SQL问题不大;CPU,IO都非常低,看样子无系统瓶颈,也无任何硬件层面的报错;故障原因和定位方法猜测是高并发引起的性能瓶颈,通过show engine innodb status\G结原创 2017-04-11 15:11:27 · 928 阅读 · 0 评论 -
故障案例--通过create server创建federate引擎导致主从失败
故障现象主从同步失败,报错如下Error 'Unknown storage engine 'FEDERATED'' on query. Default database: 'test'. Query: 'create table test_federated(`id` int(20) NOT NULL AUTO_INCREMENT, `name` varchar(32) NOT NUL原创 2017-04-11 14:19:39 · 1093 阅读 · 0 评论 -
主从同步失败---Illegal mix of collations
故障现象:主从同步报错,Error 'Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_unicode_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'hhrchin原创 2017-03-31 15:18:57 · 857 阅读 · 0 评论 -
从最近的mysql故障谈海量数据库的备份系统设计
前言最近炉石传说,gitlab数据库故障在业界传得沸沸扬扬,造成了无法挽回的数据丢失。对于越是大规模的数据库系统, 如何设计一个可靠的备份系统越是至关重要。本文主要以UCloud云数据库产品UDB的备份系统为例,阐述下在海量数据库情况下,备份系统该如何设计,如何确保备份的安全性,如何避免数据库从删库到跑路的窘境。大型备份系统设计大型备份系统设计考虑因素如果一个DBA只需要维原创 2017-02-08 14:51:40 · 3108 阅读 · 1 评论 -
组复制官方文档翻译(group replication operations)
Deploying in Multi-Primary or Single-Primary Mode组复制可以在以下不同模式下运行:single-primary 模式multi-primary 模式默认模式为单主。不可能让组的成员部署在不同的模式,例如一个配置在多主模式,而另一个在单主模式。要在模式之间切换,需要使用不同的配置并重新启动组而不单个成员。无论部署模式如何,组复制不处理客翻译 2017-01-10 14:58:08 · 1157 阅读 · 0 评论 -
innodb_flush_method取值策略和案例
innodb_flush_method的几个典型取值fsync: InnoDB uses the fsync() system call to flush both the data and log files. fsync is thedefault setting.O_DSYNC: InnoDB uses O_SYNC to open and flush the log files原创 2016-11-04 19:05:12 · 3451 阅读 · 0 评论 -
组复制官方文档翻译(Getting Started)
Deploying Group Replication in Single-Primary Mode组中的每个服务器实例可以在独立的物理机器上运行,也可以在同一台机器上运行。本节介绍如何在一台物理机上创建具有三个MySQL Server实例的复制组。这意味着需要三个数据目录,每个MySQL Server实例占用一个,您需要独立配置每个实例。本教程介绍如何使用组复制插件获取和部署MySQL翻译 2016-12-27 19:53:57 · 731 阅读 · 0 评论 -
mysql官方文档阅读笔记 MVCC
InnoDB Multi-VersioningInnodb是一个多版本的存储引擎:它能够保存事务未提交前的数据,用于支持事务的并发操作和回滚。这些信息都被存储在系统表空间的回滚段中(rollback segment)。这个回滚段主要实现两个功能,一是实现事务的回滚操作,另外一个就是一致性非锁定读了。Internal Details of Multi-Versioning在内部实现中,i原创 2016-02-04 10:38:12 · 756 阅读 · 0 评论 -
最简单的qps统计方法
统计最近N秒内的QPS值(包括每秒select,insert等值)mysql> select variable_name,sum(per_sec) as qps from (select straight_join lower(gs0.variable_name) as variable_name, (gs1.variable_value - gs0.variable_value)/5原创 2015-12-03 18:35:32 · 5452 阅读 · 0 评论 -
GTID的常见错误和处理方法
主从同步错误mysql> stop slave;Query OK, 0 rows affected (0.00 sec) mysql> set global sql_slave_skip_counter=1;ERROR 1858 (HY000): sql_slave_skip_counter can not be set when theserver is running wi原创 2015-11-20 12:26:00 · 24604 阅读 · 0 评论 -
MySQL服务器参数优化
配置mysql内存步骤1 确定可以使用的内存上限2 确定每个连接mysql需要多少内存,例如排序缓冲和临时表相对于最坏情况考虑,更好的是观察服务器在真实压力的情况下使用了多少内存,可以观察top命令的virit列3 为操作系统保留内存,至少保留1-2G,建议2G和5%中的较大者4 为缓存分配内存4.1 innodn缓冲池4.2 innodb日志文件盒myisam的操作系原创 2014-08-29 10:32:47 · 977 阅读 · 1 评论 -
含join,order by,group by的复杂语句优化
工作上碰到了几个慢查询,之前在没有SSD的情况下要跑十几个小时,jias原创 2014-09-26 16:12:21 · 4190 阅读 · 0 评论 -
深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE
概念和区别SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁),即在符合条件的rows上都加了共享锁,这样的话,其他session可以读取这些记录,也可以继续添加IS锁,但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。SELECT ... FOR UPDATE 走的是IX锁(意向排它锁),即在符合条件的rows上都加了排原创 2016-01-19 20:30:27 · 41443 阅读 · 13 评论