分类目录归档:db

mysqldump error

mysqldump备份有时报错了,内容如下:

mysql@localhost mysql]$ mysqldump --single-transaction -A -uroot -proot123 --master-data=2 > /u01/backup/testbk.sql
Warning: Using a password on the command line interface can be insecure.
Error: Couldn't read status information for table slave_master_info ()
mysqldump: Couldn't execute 'show create table `slave_master_info`': Table 'mysql.slave_master_info' doesn't exist (1146)

错误提示有innodb_index_stats,innodb_table_stats,slave_master_info,slave_relay_log_info,slave_worker_info等几个
使用select语句进行查询时,也会提示这些表不存在

最终解决方法是:删除错误的表后重建

1.查询不可使用的表
show create table mysql.innodb_index_stats;
show create table mysql.innodb_table_stats;
show create table mysql.slave_master_info;
show create table mysql.slave_relay_log_info;
show create table mysql.slave_worker_info;

2.删除上述5张表或有错误的表
drop table mysql.innodb_index_stats;
drop table mysql.innodb_table_stats;
drop table mysql.slave_master_info;
drop table mysql.slave_relay_log_info;
drop table mysql.slave_worker_info;

3.进入data/mysql相应文件夹找到关于5张表的数据文件(.frm,.ibd)并删除
rm -rf innodb_index_stats.*
rm -rf innodb_table_stats.*
rm -rf slave_master_info.*
rm -rf slave_relay_log_info.*
rm -rf slave_worker_info.*

4. 查找表结构
去找$MYSQL_HOME/share/mysql_system_tables.sql,分别search到每一表的建表语句,然后重建设;

mysql innodb_table_stats does not exist

InnoDB: Error: table `mysql`.`innodb_table_stats` does not exist in the InnoDB internal

这个原因很明显 ,是mysql库的innodb_table_stats表损坏了。

再点击 mysql的innodb_index_stats 表,同样损坏。

 

解决方法:

损坏了,当然就是删除重补回来。

1,删除表,进入mysql库,把innodb前缀的表文件删除。

[root@AY140311174146476cc0Z mysql]# pwd
/usr/local/mysql/data/mysql
[root@AY140311174146476cc0Z mysql]# rm -f innodb_*

2,重新执行sql文件语句。当然语句来自安装包。

具体路径,如5.6为例:mysql-5.6.19/scripts/mysql_system_tables.sql

语句贴出来吧

CREATE TABLE IF NOT EXISTS innodb_index_stats (
    database_name           VARCHAR(64) NOT NULL,
    table_name          VARCHAR(64) NOT NULL,
    index_name          VARCHAR(64) NOT NULL,
    last_update         TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    /* there are at least:
    stat_name='size'
    stat_name='n_leaf_pages'
    stat_name='n_diff_pfx%' */
    stat_name           VARCHAR(64) NOT NULL,
    stat_value          BIGINT UNSIGNED NOT NULL,
    sample_size         BIGINT UNSIGNED,
    stat_description        VARCHAR(1024) NOT NULL,
    PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
 
 
 
CREATE TABLE IF NOT EXISTS innodb_table_stats (
    database_name           VARCHAR(64) NOT NULL,
    table_name          VARCHAR(64) NOT NULL,
    last_update         TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    n_rows              BIGINT UNSIGNED NOT NULL,
    clustered_index_size        BIGINT UNSIGNED NOT NULL,
    sum_of_other_index_sizes    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

 

然后,再查询相关的innodb表。看下是否还有错误 。

这个错误到此解决了。

mysql reset slave / reset master

RESET SLAVE

官方的解释如下

1)RESET SLAVE makes the slave forget its replication position in the
master's binary log. This statement is meant to be used for a clean
start: It clears the master info and relay log info repositories,
deletes all the relay log files, and starts a new relay log file. It
also resets to 0 the replication delay specified with the MASTER_DELAY
option to CHANGE MASTER TO. To use RESET SLAVE, the slave replication
threads must be stopped (use STOP SLAVE if necessary).

2)RESET SLAVE does not change any replication connection parameters
such as master host, master port, master user, or master password, which
are retained in memory. This means that START SLAVE can be issued
without requiring a CHANGE MASTER TO statement following

reset slave

其实,它是直接删除master.info和relay-log.info文件,并删除所有的relay log,然后重新生成一个新的relay log,即使relay log中还有SQL没有被SQL线程apply完。

但是RESET SLAVE有个问题,它虽然删除了上述文件,但内存中的change master信息并没有删除,此时,可直接执行start
slave,但因为删除了master.info和relay-log.info,它会从头开始接受主的binlog并应用。(注意:这里所说的从头是说:reset的时候,正在接受的主的binlog,从新接受这个binlog).如果SQL
thread 正在复制临时表的过程中,执行了stop slave ,并且执行了reset slave,这些被复制的临时表将被删除。

reset master 做了什么?

1. reset master 将删除日志索引文件中记录的所有binlog文件,创建一个新的日志文件 起始值从000001 开始,

2. reset master 不能用于有任何slave 正在运行的主从关系的主库。因为在slave 运行时刻 reset master
命令不被支持,reset master 将master 的binlog从000001 开始记录,slave 记录的master log
则是reset master 时主库的最新的binlog,从库会报错无法找的指定的binlog文件。

Mysql Replicate Relay log read failure

一、描述

Mysql主从复制模式中,slave上报错 “relay log read failure”,导致主从同步停止。

mysql> show slave status\G
...
Master_Log_File: dd-bin.002542
Relay_Master_Log_File: dd-bin.002540
Exec_Master_Log_Pos: 950583017
Last_Error: Relay log read failure:...
...

二、分析

      报错信息为从库“无法读取relay log 里的条目”,可能原因为master库的binglog错误,或slave库的中继日志错误。或者为网络问题及bug原因。

      一般是由于网络故障或slave库压力过大,导致relay-log格式错误造成的。找到当前已经同步的时间点,重新设置主从同步,就会产生新的中继日志,恢复正常。

三、问题处理

从"show  slave  status\G"的输出中,找到如下信息:

Relay_Master_Log_File: dd-bin.002540     //slave库已读取的master的binlog

Exec_Master_Log_Pos: 950583017           //在slave上已经执行的position位置点



    停掉slave,以slave已经读取的binlog文件,和已经执行的position为起点,重新设置同步。会产生新的中继日志,问题解决。

(不需要指定host,user,password等,默认使用当前已经设置好的)

mysql>stop slave;

mysql>change master to master_log_file='dd-bin.002540' , master_log_pos=950583017;

mysql>start slave;



四、验证结果

再次查看,错误已经解决,slave 开始追 master 的日志

mysql>show  slave status\G

Exec_Master_Log_Pos: 225927489        //slave上已经执行的position已经变化

Seconds_Behind_Master: 58527            //slave  落后主库的时间,单位秒



过几秒钟,再次查看。离与master同步更近了

mysql>show  slave status\G

Exec_Master_Log_Pos: 307469867

Seconds_Behind_Master: 29570

db

RDS:

Oracle - Business
DB2    - Business
PostgreSQL    - Free
Microsoft SQL Server
MySQL - Free

Embed:

SQLite - Free
Microsoft Access - Free

Document:

MongoDb - Free
CouchDB - Free

Memory/Key Value:

Redis - Free
Memcache - Free
Cassandra - Free

Other:

HBase - Free
Infinite Graph - Free
InfoGrid - Free
Riak - Free

MySQL大数据场景的优化和运维之道

前言

MySQL数据库大家应该都很熟悉,而且随着前几年的阿里的去IOE,MySQL逐渐引起更多人的重视。

MySQL历史

  • 1979年,Monty Widenius写了最初的版本,96年发布1.0

  • 1995-2000年,MySQL AB成立,引入BDB

  • 2000年4月,集成MyISAM和replication

  • 2001年,Heikki Tuuri向MySQL建议集成InnoDB

  • 2003发布5.0,提供了视图、存储过程等功能

  • 2008年,MySQL AB被Sun收购,09年推出5.1

  • 2009年4月,Oracle收购Sun,2010年12月推出5.5

  • 2013年2月推出5.6 GA,5.7开发中

MySQL的优点

  • 使用简单

  • 开源免费

  • 扩展性“好”,在一定阶段扩展性好

  • 社区活跃

  • 性能可以满足互联网存储和性能需求,离不开硬件支持

上面这几个因素也是大多数公司选择考虑MySQL的原因。不过MySQL本身存在的问题和限制也很多,有些问题点也经常被其他数据库吐槽或鄙视

MySQL存在的问题

  • 优化器对复杂SQL支持不好

  • 对SQL标准支持不好

  • 大规模集群方案不成熟,主要指中间件

  • ID生成器,全局自增ID

  • 异步逻辑复制,数据安全性问题

  • Online DDL

  • HA方案不完善

  • 备份和恢复方案还是比较复杂,需要依赖外部组件

  • 展现给用户信息过少,排查问题困难

  • 众多分支,让人难以选择


到了刚才讲的MySQL的优势和劣势,可以看到MySQL面临的问题还是远大于它的优势的,很多问题也是我们实际需要在运维中优化解决的,这也是
MySQL
DBA的一方面价值所在。并且MySQL的不断发展也离不开社区支持,比如Google最早提交的半同步patch,后来也合并到官方主线。
Facebook Twitter等也都开源了内部使用MySQL分支版本,包含了他们内部使用的patch和特性。

数据库开发规范

数据库开发规范定义:开发规范是针对内部开发的一系列建议或规则, 由DBA制定(如果有DBA的话)。

开发规范本身也包含几部分:基本命名和约束规范,字段设计规范,索引规范,使用规范。

规范存在意义

  • 保证线上数据库schema规范

  • 减少出问题概率

  • 方便自动化管理

  • 规范需要长期坚持,对开发和DBA是一个双赢的事情

想想没有开发规范,有的开发写出各种全表扫描的SQL语句或者各种奇葩SQL语句,我们之前就看过开发写的SQL 可以打印出好几页纸。这种造成业务本身不稳定,也会让DBA天天忙于各种救火。

基本命名和约束规范

  • 表字符集选择UTF8 ,如果需要存储emoj表情,需要使用UTF8mb4(MySQL 5.5.3以后支持)

  • 存储引擎使用InnoDB

  • 变长字符串尽量使用varchar varbinary

  • 不在数据库中存储图片、文件等

  • 单表数据量控制在1亿以下

  • 库名、表名、字段名不使用保留字

  • 库名、表名、字段名、索引名使用小写字母,以下划线分割 ,需要见名知意

  • 库表名不要设计过长,尽可能用最少的字符表达出表的用途

字段规范

  • 所有字段均定义为NOT NULL ,除非你真的想存Null

  • 字段类型在满足需求条件下越小越好,使用UNSIGNED存储非负整数 ,实际使用时候存储负数场景不多

  • 使用TIMESTAMP存储时间

  • 使用varchar存储变长字符串 ,当然要注意varchar(M)里的M指的是字符数不是字节数;使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ,这种方式只能存储IPv4,存储不了IPv6

  • 使用DECIMAL存储精确浮点数,用float有的时候会有问题

  • 少用blob text

关于为什么定义不使用Null的原因

* 1.浪费存储空间,因为InnoDB需要有额外一个字节存储

* 2.表内默认值Null过多会影响优化器选择执行计划

关于使用datatime和timestamp,现在在5.6.4之后又有了变化,使用二者存储在存储空间上大差距越来越小 ,并且本身datatime存储范围就比timestamp大很多,timestamp只能存储到2038年

点击查看原图

索引规范

  • 单个索引字段数不超过5,单表索引数量不超过5,索引设计遵循B+ Tree索引最左前缀匹配原则

  • 选择区分度高的列作为索引

  • 建立的索引能覆盖80%主要的查询,不求全,解决问题的主要矛盾

  • DML和order by和group by字段要建立合适的索引

  • 避免索引的隐式转换

  • 避免冗余索引

关于索引规范,一定要记住索引这个东西是一把双刃剑,在加速读的同时也引入了很多额外的写入和锁,降低写入能力,这也是为什么要控制索引数原因。之前看到过不少人给表里每个字段都建了索引,其实对查询可能起不到什么作用。

冗余索引例子

  • idx_abc(a,b,c)

  • idx_a(a) 冗余

  • idx_ab(a,b) 冗余

隐式转换例子

字段:remark varchar(50) NOT Null

MySQL>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127; 1 row in set (0.14 sec)

MySQL>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 1 row in set (0.005 sec)

字段定义为varchar,但传入的值是个int,就会导致全表扫描,要求程序端要做好类型检查

SQL类规范

  • 尽量不使用存储过程、触发器、函数等

  • 避免使用大表的JOIN,MySQL优化器对join优化策略过于简单

  • 避免在数据库中进行数学运算和其他大量计算任务

  • SQL合并,主要是指的DML时候多个value合并,减少和数据库交互

  • 合理的分页,尤其大分页

  • UPDATE、DELETE语句不使用LIMIT ,容易造成主从不一致

数据库运维规范

运维规范主要内容

  • SQL审核,DDL审核和操作时间,尤其是OnlineDDL

  • 高危操作检查,Drop前做好数据备份

  • 权限控制和审计

  • 日志分析,主要是指的MySQL慢日志和错误日志

  • 高可用方案

  • 数据备份方案

版本选择

  • MySQL社区版,用户群体最大

  • MySQL企业版,收费

  • Percona Server版,新特性多

  • MariaDB版,国内用户不多

建议选择优先级为:MySQL社区版 > Percona Server > MariaDB > MySQL 企业版

不过现在如果大家使用RDS服务,基本还以社区版为主

Online DDL问题

原生MySQL执行DDL时需要锁表,且锁表期间业务是无法写入数据的,对服务影响很大,MySQL对这方面的支持是比较差的。大表做DDL对DBA来说是很痛苦的,相信很多人经历过。如何做到Online DDL呢,是不是就无解了呢?当然不是!

点击查看原图

上面表格里提到的 Facebook OSC和5.6 OSC也是目前两种比较靠谱的方案

MySQL 5.6的OSC方案还是解决不了DDL的时候到从库延时的问题,所以现在建议使用Facebook OSC这种思路更优雅

下图是Facebook OSC的思路

点击查看原图

后来Percona公司根据Facebook OSC思路,用perl重写了一版,就是我们现在用得很多的pt-online-schema-change,软件本身非常成熟,支持目前主流版本。

使用pt-online-schema-change的优点有:

  • 1.无阻塞写入

  • 2.完善的条件检测和延时负载策略控制

值得一提的是,腾讯互娱的DBA在内部分支上也实现了Online DDL,之前测试过确实不错,速度快,原理是通过修改InnoDB存储格式来实现。

使用pt-online-schema-change的限制有:

  • 改表时间会比较长(相比直接alter table改表)

  • 修改的表需要有唯一键或主键

  • 在同一端口上的并发修改不能太多

可用性

关于可用性,我们今天分享一种无缝切主库方案,可以用于日常切换,使用思路也比较简单

在正常条件下如何无缝去做主库切换,核心思路是让新主库和从库停在相同位置,主要依赖slave start until 语句,结合双主结构,考虑自增问题。

点击查看原图

MySQL集群方案:

  • 集群方案主要是如何组织MySQL实例的方案

  • 主流方案核心依然采用的是MySQL原生的复制方案

  • 原生主从同步肯定存在着性能和安全性问题

MySQL半同步复制:

现在也有一些理论上可用性更高的其它方案

  • Percona XtraDB Cluster(没有足够的把控力度,不建议上)

  • MySQL Cluster(有官方支持,不过实际用的不多)

点击查看原图

红框内是目前大家使用比较多的部署结构和方案。当然异常层面的HA也有很多第三方工具支持,比如MHA、MMM等,推荐使用MHA

sharding拆分问题

  • Sharding is very complex, so itʼs best not to shard until itʼs obvious that you will actually need to!

  • sharding是按照一定规则数据重新分布的方式

  • 主要解决单机写入压力过大和容量问题

  • 主要有垂直拆分和水平拆分两种方式

  • 拆分要适度,切勿过度拆分

  • 有中间层控制拆分逻辑最好,否则拆分过细管理成本会很高

曾经管理的单表最大60亿+,单表数据文件大小1TB+,人有时候就要懒一些

点击查看原图

上图是水平拆分和垂直拆分的示意图

数据库备份

首先要保证的,最核心的是数据库数据安全性。数据安全都保障不了的情况下谈其他的指标(如性能等),其实意义就不大了。

备份的意义是什么呢?

  • 数据恢复!

  • 数据恢复!

  • 数据恢复!

目前备份方式的几个纬度:

  • 全量备份 VS 增量备份

  • 热备 VS 冷备

  • 物理备份 VS 逻辑备份

  • 延时备份

  • 全量binlog备份

建议方式:

  • 热备+物理备份

  • 核心业务:延时备份+逻辑备份

  • 全量binlog备份

借用一下某大型互联网公司做的备份系统数据:一年7000+次扩容,一年12+次数据恢复,日志量每天3TB,数据总量2PB,每天备份数据量百TB级,全年备份36万次,备份成功了99.9%。

主要做的几点:

  • 备份策略集中式调度管理

  • xtrabackup热备

  • 备份结果统计分析

  • 备份数据一致性校验

  • 采用分布式文件系统存储备份

备份系统采用分布式文件系统原因:

  • 解决存储分配的问题

  • 解决存储NFS备份效率低下问题

  • 存储集中式管理

  • 数据可靠性更好

使用分布式文件系统优化点:

* Pbzip压缩,降低多副本存储带来的存储成本,降低网络带宽消耗

* 元数据节点HA,提高备份集群的可用性

* erasure code方案调研

数据恢复方案


前的MySQL数据恢复方案主要还是基于备份来恢复,可见备份的重要性。比如我今天下午15点删除了线上一张表,该如何恢复呢?首先确认删除语句,然后用
备份扩容实例启动,假设备份时间点是凌晨3点,就还需要把凌晨3点到现在关于这个表的binlog导出来,然后应用到新扩容的实例上,确认好恢复的时间
点,然后把删除表的数据导出来应用到线上。

性能优化

复制优化

MySQL复制:

  • 是MySQL应用得最普遍的应用技术,扩展成本低

  • 逻辑复制

  • 单线程问题,从库延时问题

  • 可以做备份或读复制

问题很多,但是能解决基本问题

点击查看原图

上图是MySQL复制原理图,红框内就是MySQL一直被人诟病的单线程问题

单线程问题也是MySQL主从延时的一个重要原因,单线程解决方案:

  • 官方5.6+多线程方案

  • Tungsten为代表的第三方并行复制工具

  • sharding

点击查看原图

上图是MySQL5.6 目前实现的并行复制原理图,是基于库级别的复制,所以如果你只有一个库,使用这个意义不大

当然MySQL也认识到5.6这种并行的瓶颈所在,所以在5.7引入了另外一种并行复制方式,基于logical timestamp的并行复制,并行复制不再受限于库的个数,效率会大大提升

点击查看原图

上图是5.7的logical timestamp的复制原理图

刚才我也提到MySQL原来只支持异步复制,这种数据安全性是非常差的,所以后来引入了半同步复制,从5.5开始支持

点击查看原图

上图是原生异步复制和半同步复制的区别。可以看到半同步通过从库返回ACK这种方式确认从库收到数据,数据安全性大大提高

在5.7之后,半同步也可以配置你指定多个从库参与半同步复制,之前版本都是默认一个从库

对于半同步复制效率问题有一个小的优化,就是使用5.6+的mysqlbinlog以daemon方式作为从库,同步效率会好很多

关于更安全的复制,MySQL 5.7也是有方案的,方案名叫Group replication 官方多主方案,基于Corosync实现

点击查看原图

主从延时问题

原因:一般都会做读写分离,其实从库压力反而比主库大/从库读写压力大非常容易导致延时。

解决方案:

  • 首先定位延时瓶颈

  • 如果是IO压力,可以通过升级硬件解决,比如替换SSD等

  • 如果IO和CPU都不是瓶颈,非常有可能是SQL单线程问题,解决方案可以考虑刚才提到的并行复制方案

  • 如果还有问题,可以考虑sharding拆分方案

提到延时不得不提到很坑人的Seconds behind master,使用过MySQL的应该很熟悉

这个值的源码里算法

long time_diff= ((long)(time(0) – mi->rli.last_master_timestamp) – mi->clock_diff_with_master);

Secondsbehindmaster来判断延时不可靠,在网络抖动或者一些特殊参数配置情况下,会造成这个值是0但其实延时很大了。通过heartbeat表插入时间戳这种机制判断延时是更靠谱的

复制注意点:

  • Binlog格式,建议都采用row格式,数据一致性更好

  • Replication filter应用

主从数据一致性问题:

  • row格式下的数据恢复问题

InnoDB优化

成熟开源事务存储引擎,支持ACID,支持事务四个隔离级别,更好的数据安全性,高性能高并发,MVCC,细粒度锁,支持O_DIRECT。

主要优化参数:

  • innodbfileper_table =1

  • innodbbufferpool_size,根据数据量和内存合理设置

  • innodbflushlog_attrxcommit= 0 1 2

  • innodblogfile_size,可以设置大一些

  • innodbpagesize

  • Innodbflushmethod = o_direct

  • innodbundodirectory 放到高速设备(5.6+)

  • innodbbufferpool_dump

  • atshutdown ,bufferpool dump (5.6+)

    点击查看原图

 

上图是5.5 4G的redo log和5.6 设置大于4G redo log文件性能对比,可以看到稳定性更好了。innodblogfile_size设置还是很有意义的

InnoDB比较好的特性:

  • Bufferpool预热和动态调整大小,动态调整大小需要5.7支持

  • Page size自定义调整,适应目前硬件

  • InnoDB压缩,大大降低数据容量,一般可以压缩50%,节省存储空间和IO,用CPU换空间

  • Transportable tablespaces,迁移ibd文件,用于快速单表恢复

  • Memcached API,full text,GIS等

InnoDB在SSD上的优化:

  • 在5.5以上,提高innodbwriteiothreads和innodbreadiothreads

  • innodbiocapacity需要调大

  • 日志文件和redo放到机械硬盘,undo放到SSD,建议这样,但必要性不大

  • atomic write,不需要Double Write Buffer

  • InnoDB压缩

  • 单机多实例

也要搞清楚InnoDB哪些文件是顺序读写,哪些是随机读写

随机读写:

  • datadir

  • innodbdata file_path

  • innodbundo directory

顺序读写:

  • innodbloggrouphomedir

  • log-bin

InnoDB VS MyISAM:

  • 数据安全性至关重要,InnoDB完胜,曾经遇到过一次90G的MyISAM表repair,花了两天时间,如果在线上几乎不可忍受

  • 并发度高

  • MySQL 5.5默认引擎改为InnoDB,标志着MyISAM时代的落幕

TokuDB:

  • 支持事务 ACID 特性,支持多版本控制(MVCC)

  • 基于Fractal Tree Index,非常适合写入密集场景

  • 高压缩比,原生支持Online DDL

  • 主流分支都支持,收费转开源 。目前可以和InnoDB媲美的存储引擎

目前主流使用TokuDB主要是看中了它的高压缩比,Tokudb有三种压缩方式:quicklz、zlib、lzma,压缩比依次更高。现在很多使用zabbix的后端数据表都采用的TokuDB,写入性能好,压缩比高。

下图是我之前做的测试对比和InnoDB

点击查看原图

点击查看原图

上图是sysbench测试的和InnoDB性能对比图,可以看到TokuDB在测试过程中写入稳定性是非常好的。

tokudb存在的问题:

  • 官方分支还没很好的支持

  • 热备方案问题,目前只有企业版才有

  • 还是有bug的,版本更新比较快,不建议在核心业务上用

比如我们之前遇到过一个问题:TokuDB的内部状态显示上一次完成的checkpoint时间是“Jul 17 12:04:11 2014”,距离当时发现现在都快5个月了,结果堆积了大量redo log不能删除,后来只能重启实例,结果重启还花了七八个小时

MySQL优化相关的case

Query cache,MySQL内置的查询加速缓存,理念是好的,但设计不够合理,有点out。

锁的粒度非常大MySQL 5.6默认已经关闭

When the query cache helps, it can help a lot. When it hurts, it can hurt a lot.明显前半句已经没有太大用处,在高并发下非常容易遇到瓶颈。

关于事务隔离级别 ,InnoDB默认隔离级别是可重复读级别,当然InnoDB虽然是设置的可重复读,但是也是解决了幻读的,建议改成读已提交级别,可以满足大多数场景需求,有利于更高的并发,修改transaction-isolation。

点击查看原图

点击查看原图

上图是一个比较经典的死锁case,有兴趣可以测试下

关于SSD

关于SSD,还是提一下吧。某知名大V说过“最近10年对数据库性能影响最大的是闪存”,稳定性和性能可靠性已经得到大规模验证,多块SATA SSD做Raid5,推荐使用。采用PCIe SSD,主流云平台都提供SSD云硬盘支持。

最后说一下大家关注的单表60亿记录问题,表里数据也是线上比较核心的。

先说下当时情况,表结构比较简单,都是bigint这种整型,索引比较多,应该有2-3个,单表行数60亿+,单表容量1.2TB左右,当然内部肯定是有碎片的。

形成原因:历史遗留问题,按照我们前面讲的开发规范,这个应该早拆分了,当然不拆有几个原因:

  1. 性能未遇到瓶颈 ,主要原因

  2. DBA比较“懒“

  3. 想看看InnoDB的极限,挑战一下。不过风险也是很大的,想想如果在一个1.2TB表上加个字段加个索引,那感觉绝对酸爽。还有就是单表恢复的问题,恢复时间不可控。

我们后续做的优化 ,采用了刚才提到的TokuDB,单表容量在InnoDB下1TB+,使用Tokudb的lzma压缩到80GB,压缩效果非常好。这样也解决了单表过大恢复时间问题,也支持online DDL,基本达到我们预期。

今天讲的主要针对MySQL本身优化和规范性质的东西,还有一些比较好的运维经验,希望大家能有所收获。今天这些内容是为后续数据库做平台化的基础。我今天分享就到这里,谢谢大家。

QA

Q1:use schema;select * from table; 和select * from schema.table;两种写法有什么不一样吗?会对主从同步有影响吗?

对于主从复制来说执行效率上差别不大,不过在使用replication filter时候这种情况需要小心,应该要使用ReplicateWildIgnoreTable这种参数,如果不使用带wildignore,第一种方式会有问题,过滤不起作用。

Q2:对于用于MySQL的ssd,测试方式和ssd的参数配置方面,有没有好的建议?主要针对ssd的配置哈

关于SATA SSD配置参数,建议使用Raid5,想更保险使用Raid50,更土豪使用Raid 10

点击查看原图

上图是主要的参数优化,性能提升最大的是第一个修改调度算法的

Q3:数据库规范已制定好,如何保证开发人员必须按照规范来开发?

关于数据库规范实施问题,也是有两个方面吧,第一、定期给开发培训开发规范,让开发能更了解。第二、还是在流程上规范,比如把我们日常通用的建表和字段策略固化到程序,做成自动化审核系统。这两方面结合 效果会比较好。

Q4:如何最大限度提高innodb的命中率?

这个问题前提是你的数据要有热点,读写热点要有交集,否则命中率很难提高。在有热点的前提下,也要求你的你的内存要足够大,能够存更多的热点数据。尽量不要做一些可能污染bufferpool的操作,比如全表扫描这种。

Q5:主从复制的情况下,如果有CAS这样的需求,是不是只能强制连主库?因为有延迟的存在,如果读写不在一起的话,会有脏数据。

如果有CAS需求,确实还是直接读主库好一些,因为异步复制还是会有延迟的。只要SQL优化的比较好,读写都在主库也是没什么问题的。

Q6:关于开发规范,是否有必要买国标?

这个国标是什么东西,不太了解。不过从字面看,国标应该也是偏学术方面的,在具体工程实施时候未必能用好。

Q7:主从集群能不能再细化一点那?不知道这样问合适不?

看具体哪方面吧。主从集群每个小集群一般都是采用一主多从方式,每个小集群对应特定的一组业务。然后监控备份和HA都是在每个小集群实现。

Q8:如何跟踪数据库table某个字段值发生变化?

追踪字段值变化可以通过分析row格式binlog好一些。比如以前同事就是通过自己开发的工具来解析row格式binlog,跟踪数据行变化。

Q9:对超大表水平拆分,在使用MySQL中间件方面有什么建议和经验分享?

对于超大表水平拆分,在中间件上经验不是很多,早期人肉搞过几次。也使用过自己研发的数据库中间件,不过线上应用的规模不大。关于目前众多的开源中间件里,360的atlas是目前还不错的,他们公司内部应用的比较多。

Q10:我们用的MySQL proxy做读负载,但是少量数据压力下并没有负载,请问有这回事吗?

少量数据压力下,并没有负载 ,这个没测试过,不好评价

Q11:对于binlog格式,为什么只推荐row,而不用网上大部分文章推荐的Mix ?

这个主要是考虑数据复制的可靠性,row更好。mixed含义是指如果有一些容易导致主从不一致的SQL ,比如包含UUID函数的这种,转换为row。既然要革命,就搞的彻底一些。这种mix的中间状态最坑人了。

Q12: 读写分离,一般是在程序里做,还是用proxy ,用proxy的话一般用哪个?

这个还是独立写程序好一些,与程序解耦方便后期维护。proxy国内目前开源的比较多,选择也要慎重。

Q13: 我想问一下关于mysql线程池相关的问题,什么情况下适合使用线程池,相关的参数应该如何配置,老师有这方面的最佳实践没有?

线程池这个我也没测试过。从原理上来说,短链接更适合用线程池方式,减少建立连接的消耗。这个方面的最佳配置,我还没测试过,后面测试有进展可以再聊聊。

Q14: 误删数据这种,数据恢复流程是怎么样的(从库也被同步删除的情况)?


你删除数据的情况,如果只是一张表,单表在几GB或几十GB。如果能有延时备份,对于数据恢复速度是很有好处的。恢复流程可以参考我刚才分享的部分。目前
的MySQL数据恢复方案主要还是基于备份来恢复
,可见备份的重要性。比如我今天下午15点删除了线上一张表,该如何恢复呢。首先确认删除语句,然后用备份扩容实例启动,假设备份时间点是凌晨3点。就还
需要把凌晨3点到现在关于这个表的binlog导出来,然后应用到新扩容的实例上。确认好恢复的时间点,然后把删除表的数据导出来应用到线上。

Q15: 关于备份,binlog备份自然不用说了,物理备份有很多方式,有没有推荐的一种,逻辑备份在量大的时候恢复速度比较慢,一般用在什么场景?

物理备份采用xtrabackup热备方案比较好。逻辑备份一般用在单表恢复效果会非常好。比如你删了一个2G表,但你总数据量2T,用物理备份就会要慢了,逻辑备份就非常有用了

 

 

 

 

摘自:http://sanwen8.cn/p/10913Vm.html

msmq,rabbitmq,activemq,zeromq

摘自网络

RabbitMQ是实现AMQP(高级消息队列协议)的消息中间件的一种,最初起源于金融系统。

一些概念:

channel:通道,amqp支持一个tcp连接上启用多个mq通信通道,每个通道都可以被作为通信流。

producer:生产者,是消息产生的源头。

exchange:交换机,可以理解为具有路由表的路由规则。

queues:队列,装载消息的缓存容器。

consumer:消费者,连接到队列并取走消息的客户端。

核心思想:在RabbitMQ中,生产者从不直接将消息发送给队列。

事实上,有些生产者甚至不知道消息是否被送到某个队列中去了。生产者只负责将消息送给交换机,而交换机确切地知道什么消息应该送到哪。

bind:绑定,实际上可以理解为交换机的路由规则。每个消息都有一个称为路由键的属性(routing key),就是一个简单的字符串。一个绑定将【交换机,路由键,消息送达队列】三者绑定在一起,形成一条路由规则。

exchange type:交换机类型:

fanout:不处理路由键,转发到所有绑定的队列上

direct:处理路由键,必须完全匹配,即路由键字符串相同才会转发

topic:路由键模式匹配,此时队列需要绑定要一个模式上。符号“#”匹配一个或多个词,符号“*”匹配不多不少一个词。因此“audit.#”能够匹配到“audit.irs.corporate”,但是“audit.*” 只会匹配到“audit.irs”

网络获取得一些综合的测试结果:

点击查看原图

 

 rabbitmq原型图

点击查看原图

 

 

 

 

MySQL数据库命名规范及约定

一、【操作规范】
1. 如无备注,则表中的第一个id字段一定是主键且为自动增长;
2. 如无备注,则数值类型的字段请使用UNSIGNED属性;
3. 如无备注,排序字段order_id在程序中默认使用降序排列;
4. 如无备注,所有字段都设置NOT NULL,并设置默认值;
5. 如无备注,所有的布尔值字段,如is_hot、is_deleted,都必须设置一个默认值,并设为0;
6. 所有的数字类型字段,都必须设置一个默认值,并设为0;
7. 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预设的长度;
8. 建表时将数据字典中的字段中文名和属性备注写入数据表的备注中(“PK、自动增长”不用写);
9. 如无说明,建表时一律采用innodb引擎;

二、【常用表名约定】
0. 说明:表前缀用项目名称首字母缩写;所以表名都小写,单词之间用下划线分开,单词都用单数形式
1. user – 用户
2. category – 分类
3. goods – 商品、产品等一切可交易网站的物品都用此命名
4. good_gallery – 物品的相册
5. good_cate – 物品的分类,除了单独作为表名,其他地方分类单词一律用缩写cate
4. attr – 属性
5. article – 文章、新闻、帮助中心等以文章形式出现的,一般都用此命名
6. cart – 购物车
7. feedback – 用户反馈
8. order – 订单
9. site_nav – 包括页头和页尾导航
10. site_config – 系统配置表
11. admin – 后台用户 【RBAC标准表】
12. role – 后台用户角色【RBAC标准表】
13. access – 后台操作权限,相当于action【RBAC标准表】
14. role_admin – 后台用户对应的角色【RBAC标准表】
15. access_role – 后台角色对应的权限【RBAC标准表】
16. 待续

三、【常用列名约定】
1. 表名_id – 通常用作外键命名
2. cid –
特殊的编号,带有元数据,方便关联查询,你可以把它理解成类别(层次)编号。举个例子,产品在分类时,往往需要将其归类到子分类下,相应的字段中也一般只
记录子分类的id,这时若需要知道该产品属于哪个主分类,就需要通过子分类信息再查询到主分类信息,这是比较麻烦的,cid字段就是要解决这个问题。一般
的站点几十个分类肯定是够用了,所以这里假设某一主分类的cid为11,则子分类的cid从1101开始编号,处理时只需截取前两位数值便可知道该产品属
于哪一个主分类了。
3. add_time – 添加时间、上架时间等
4. last_time – 最后操作时间,如登录、修改记录
5. expire_time – 过期时间
6. name – 商品名称、商家名称等,不要跟title混用,title只用于文章标题、职称等
7. price – 价格
8. thumb – 只要是列表页面中的窗口图,一律用此命名
9. image_src – 相册中的图片地址一律用此命名,不要出现各种img,image,img_url,thumb_url等
10. head_thumb – 用户头像, 虽然有点长,一定要遵守。不要出现上述情况
11. image_alt – 相册中图片的alt属性
12. desc – 描述、简介,比如goods_desc,不要出现goods_txt这种
13. details – 详情、文章内容等
14. order_id – 排序
15. telephone – 座机号码
16. mobile – 手机号码
17. phone – 当不区分手机和座机时,请用phone命名
18. address – 地址,单独出现不要用addr缩写,组合出现时需用缩写,比如mac地址,mac_addr
19. zipcode – 邮编
20. region – 地区,大的区域,比如记录杭州市、温州市等
21. area – 区域,小的,比如上城区,江干区等
22. avg_cost – 人均消费
23. 待续

四、【数据表字段设计范例】

分类表(t_category

字段名

列名

类型

属性备注

说明

流水号 id int(10) PK、自动增长
特殊编号 cid varchar(4) 第一个主分类为11、第一个子分类为1101,类推,仅支持二级分类
名称 name varchar(10) 页面中需注明输入不超过10个字
父分类 pid int(10)
统计量 count int(10)
是否热门 is_hot tinyint(1)
首页显示 is_index tinyint(1)
排序 order_id int(10)

ibdata1 & mysql-bin

ibdata1和mysql-bin
问题:磁盘空间报警,经查发现ibdata1和mysql-bin日志占用空间太多(其中ibdata1超过120G,mysql-bin超过80G)
原因:ibdata1是存储格式,在INNODB类型数据状态下,ibdata1用来存储文件的数据和索引,而库名的文件夹里的那些表文件只是结构而已。
innodb存储引擎有两种表空间的管理方式,分别是:
1)共享表空间(可拆分为多个小的表空间文件),这个是我们目前多数数据库使用的方法;
2)独立表空间,每一个表有一个独立的表空间(磁盘文件)
对于两种管理方式,各有优劣,具体如下:
①共享表空间:
优点:可以将表空间分成多个文件存放到不同的磁盘上(表空间文件大小不受表大小的限制,一个表可以分布在不同步的文件上)。
缺点:所有数据和索引存放在一个文件中,则随着数据的增加,将会有一个很大的文件,虽然可以把一个大文件分成多个小文件,但是多个表及索引在表空间中混合存储,这样如果对于一个表做了大量删除操作后表空间中将有大量空隙。对于共享表空间管理的方式下,一旦表空间被分配,就不能再回缩了。当出现临时建索引或是创建一个临时表的操作表空间扩大后,就是删除相关的表也没办法回缩那部分空间了。
②独立表空间:在配置文件(my.cnf)中设置: innodb_file_per_table
特点:每个表都有自已独立的表空间;每个表的数据和索引都会存在自已的表空间中。
优点:表空间对应的磁盘空间可以被收回(Drop table操作自动回收表空间,如果对于删除大量数据后的表可以通过:alter table tbl_name engine=innodb;回缩不用的空间。
缺点:如果单表增加过大,如超过100G,性能也会受到影响。在这种情况下,如果使用共享表空间可以把文件分开,但有同样有一个问题,如果访问的范围过大同样会访问多个文件,一样会比较慢。如果使用独立表空间,可以考虑使用分区表的方法,在一定程度上缓解问题。此外,当启用独立表空间模式时,需要合理调整innodb_open_files参数的设置。
解决:
1)ibdata1数据太大:只能通过dump,导出建库的sql语句,再重建的方法。
2)mysql-bin Log太大:
①手动删除:
删除某个日志:mysql>PURGE MASTER LOGS TO ‘mysql-bin.010′;
删除某天前的日志:mysql>PURGE MASTER LOGS BEFORE ’2010-12-22 13:00:00′;
②在/etc/my.cnf里设置只保存N天的bin-log日志
expire_logs_days = 30 //Binary Log自动删除的天数

MySql ibdata1文件

MySql innodb如果是共享表空间,ibdata1文件越来越大,达到了30多个G,对一些没用的表进行清空:

truncate table xxx;

然后optimize table xxx; 没有效果

因为对共享表空间不起作用。

mysql ibdata1存放数据,索引等,是MYSQL的最主要的数据。

如果不把数据分开存放的话,这个文件的大小很容易就上了G,甚至几十G。对于某些应用来说,并不是太合适。因此要把此文件缩小。

无法自动收缩,必须数据导出,删除ibdata1,然后数据导入,比较麻烦,因此需要改为每个表单独的文件。

解决方法:数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。

步骤如下:

 

1)备份数据库

备份全部数据库,执行命令

#mysqldump -q -uroot -ppassword --add-drop-table --all-databases >/home/backup/all.sql

做完此步后,停止数据库服务。

#service mysqld stop

 

2)找到my.ini或my.cnf文件

linux下执行 

# /usr/libexec/mysqld --verbose --help | grep -A 1 'Default options'

Default options are read from the following files in the given order:

/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 

windows环境下可以:

mysqld --verbose --help > mysqlhelp.txt

notepad mysqlhelp.txt

在里面查找Default options,可以看到查找my.ini的顺序,以找到真实目录

 

3)修改mysql配置文件

打开my.ini或my.cnf文件

[mysqld]下增加下面配置

innodb_file_per_table=1

验证配置是否生效,可以重启mysql后,执行

#service mysqld restart

#mysql -uroot -ppassword

mysql> show variables like '%per_table%';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| innodb_file_per_table | ON    |

+-----------------------+-------+

1 row in set (0.00 sec)





mysql> 



看看innodb_file_per_table变量是否为ON

 

4)删除原数据文件

删除原来的ibdata1文件及日志文件ib_logfile*,删除/var/lib/mysql目录下的应用数据库文件夹(mysql文件夹不要删)

 

5)还原数据库

启动数据库服务

从命令行进入MySQL Server

还原全部数据库,执行命令

#service mysqld start

#mysql -uroot -pocs < /home/backup/all.sql 





经过以上几步后,可以看到新的ibdata1文件就只有几十M了,数据及索引都变成了针对单个表的小ibd文件了,它们在相应数据库的文件夹下面。





# ll

total 295028

drwx------  2 mysql mysql     36864 Apr 22 14:16 glpi

drwx------  2 mysql mysql     36864 Feb 15 13:45 glpi-1

-rw-rw----  1 mysql mysql  10485760 Apr 22 14:27 ibdata1

-rw-rw----. 1 mysql mysql 270532608 Apr 22 14:14 ibdata1-1

-rw-rw----  1 mysql mysql   5242880 Apr 22 14:27 ib_logfile0

-rw-rw----. 1 mysql mysql   5242880 Apr 22 14:14 ib_logfile0_bak

-rw-rw----  1 mysql mysql   5242880 Apr 22 14:28 ib_logfile1

-rw-rw----. 1 mysql mysql   5242880 Apr 21 22:50 ib_logfile1_bak

drwx------  2 mysql mysql      4096 Apr 22 14:16 mrbs

drwx------  2 mysql mysql      4096 Apr 14 12:05 mrbs-1

drwx------. 2 mysql mysql      4096 Apr 22 14:16 mysql

srwxrwxrwx  1 mysql mysql         0 Apr 22 14:16 mysql.sock

drwx------  2 mysql mysql     12288 Apr 22 14:16 ocsweb

drwx------  2 mysql mysql     12288 Nov 16  2011 ocsweb-1





# ll mrbs

total 808

-rw-rw---- 1 mysql mysql     61 Apr 22 14:16 db.opt

-rw-rw---- 1 mysql mysql  10492 Apr 22 14:16 mrbs_area.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_area.ibd

-rw-rw---- 1 mysql mysql   9264 Apr 22 14:16 mrbs_entry.frm

-rw-rw---- 1 mysql mysql 131072 Apr 22 14:16 mrbs_entry.ibd

-rw-rw---- 1 mysql mysql   9442 Apr 22 14:16 mrbs_repeat.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_repeat.ibd

-rw-rw---- 1 mysql mysql   8888 Apr 22 14:16 mrbs_room.frm

-rw-rw---- 1 mysql mysql 114688 Apr 22 14:16 mrbs_room.ibd

-rw-rw---- 1 mysql mysql   8688 Apr 22 14:16 mrbs_users.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_users.ibd

-rw-rw---- 1 mysql mysql   8658 Apr 22 14:16 mrbs_variables.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_variables.ibd

-rw-rw---- 1 mysql mysql   8738 Apr 22 14:16 mrbs_zoneinfo.frm

-rw-rw---- 1 mysql mysql  98304 Apr 22 14:16 mrbs_zoneinfo.ibd





# ll mrbs-1

total 88

-rw-rw---- 1 mysql mysql    61 Apr 14 12:05 db.opt

-rw-rw---- 1 mysql mysql 10492 Apr 14 12:05 mrbs_area.frm

-rw-rw---- 1 mysql mysql  9264 Apr 14 12:05 mrbs_entry.frm

-rw-rw---- 1 mysql mysql  9442 Apr 14 12:05 mrbs_repeat.frm

-rw-rw---- 1 mysql mysql  8888 Apr 14 12:05 mrbs_room.frm

-rw-rw---- 1 mysql mysql  8688 Apr 14 12:05 mrbs_users.frm

-rw-rw---- 1 mysql mysql  8658 Apr 14 12:05 mrbs_variables.frm

-rw-rw---- 1 mysql mysql  8738 Apr 14 12:05 mrbs_zoneinfo.frm