欢迎您光临本站,如有问题请及时联系我们。

互联网金融公司在分布式数据库的运维实践

  1、业务上子查询SQL过多,需要大量改写为join关联查询语句,开发需要更改代码

  在MariaDB5.3版本里,就已经对子查询进行了优化,并采用semijoin半连接方式将SQL改写为了表关联join,从而提高了查询速度。


  通常情况下,我们希望由内到外,即先完成内表里的查询结果,然后驱动外查询的表,完成最终查询,但是MySQL5.5会先扫描外表中的所有数据,每条数据将会传到内表中与之关联,如果外表很大的话,那么性能上将会很差。


  MariaDB


  案例:MySQL5.5的子查询执行计划,是将in重写为exists


  我们看一下这两个执行计划,当外表比较大时,第一行会扫描5000071行,改为exists写法,它的执行计划和in是完全一样的。如果你外表比较大的话,查询性能会是非常差的。


  案例:MariaDB10.0的子查询执行计划,是将in/exists重写为join


  MariaDB10.0相当于MySQL5.6版本,这里In和exists,它会直接重写为join关联查询,这里有三个不同的写法,执行计划是完全一样的。改写join以后是由小表关联大表,可以看下扫描的行数为10行,执行效率就是非常快的。


  2、由于数据量上TB,直接升级MySQL5.6,不能平滑升级,需要进行一次mysqldump再导入,耗费过多的时间。


  MySQL


  以MySQL5.5版本为例,若要升级到MySQL5.6,需要进行一次全库mysqldump导出再导入,当数据库很大时,比如100GB,升级起来会非常困难。但如果升级为MariaDB10,会非常轻松,按照官方文档阐述,只需把MySQL卸载掉,并用MariaDB启动,然后通过mysql_upgrade命令升级即可完成。


  MariaDB跟MySQL在绝大多数方面是兼容的,对于前端应用(比如PHP、Perl、Python、Java、.NET、MyODBC、Ruby、MySQLCconnector)来说,几乎感觉不到任何不同。


  升级到MariaDB10注意事项

  MariaDB


  在处理内部的临时表,MariaDB5.5/10.0用Aria引擎代替了MyISAM引擎,这将使某些GROUPBY和DISTINCT请求速度更快,因为Aria有比MyISAM更好的缓存机制。如果你的临时表很多的话,要增加aria_pagecache_buffer_size参数的值(缓存数据和索引),默认是128MB(而不是tmp_table_size参数)。如果你没有MyISAM表的话,建议把key_buffer_size调低,例如64KB,仅仅提供给MySQL库里面的系统表使用。


  官方推荐使用jemalloc内存管理器获取更好的性能。


  jemalloc


  Jemalloc内存管理器性能


  上图是官方的压力测试报告,可以看出Jemalloc内存管理器的性能是最好的。


  这是之前我给MariaDB作者写的一封信,他回答,升级到MariaDB是没有问题的,现在很多大公司都用MariaDB,例如Google、Wikipedia。主要原因我总结如下:


  在Oracle控制下的MySQL有两个问题:


  MySQL核心开发团队是封闭的,完全没有Oracle之外的成员参加。很多高手即使有心做贡献,也没办法做到。


  MySQL新版本的发布速度,在Oracle收购Sun之后大为减缓。


  这里再说一下MariaDB企业版和社区版的区别:


  企业版更注重bug的修复,社区版则对新功能更新比较快。MariaDB社区版和企业版的源代码都是开源的,并且所有功能都是免费开放,不用担心功能上有阉割,但甲骨文MySQL企业版延伸套件采取封闭源代码且需要付费。


  此外,MariaDB相比MySQL拥有更多的功能、更快、更稳定、BUG修复更快。


  3、解决复制延迟,开启多线程并行复制(MariaDB10.0.X基于表)


  金融公司对数据一致性要求较高,主从同步延迟问题是不能接受的。MySQL5.6由于是基于库级别的并行复制,在实际生产中用处并不大,而只有5.7才支持基于表的并行复制。MariaDB的并行复制有两种实现模式:


  第一种:Conservativemodeofin-orderparallelreplication(保守模式的顺序并行复制)


  MariaDB10通过基于表的多线程并行复制技术,如果主库上1秒内有10个事务,那么合并一个IO提交一次,并在binlog里增加一个cid=XX标记,当cid的值是一样的话,Slave就可以进行并行复制,通过设置多个sql_thread线程实现。


  Slave


  上述cid为630的事务有2个,表示组提交时提交了2个事务,假如设置slave_parallel_threads=24(并行复制线程数,根据CPU核数设置),那么这2个事务在slave从库上通过24个sql_thread线程进行并行恢复。只有那些被自动确认为不会引起冲突的事务才会被并行执行,以确保从库上事务提交和主库上事务提交顺序一致。这些操作完全是透明的,无须DBA干涉。


  如果想控制binlog组提交数量,可以通过下图两个参数设置。


  binlog


  第二种模式:Out-of-orderparallelreplication(无序并行复制)


  设置SETSESSIONgtid_domain_id=99具有不同gtid_domain_id域识别符可并行复制,生产使用场景通常是用在增加索引、增加字段上。


  实现无序并行复制,需要把GTID开启才可以实现,执行上图所示的命令。


  多线程并行复制—压力测试


  我们可以看到,随着并行复制线程的增加,slave从库的TPS每秒写入速度接近主库。


  4、前期公司大数据部门刚起步,未成熟,需要借助多源复制技术(汇总前面多个业务库),提供给BI部门、产品PO、金融分析师BA/MA进行分析。


  (注:这个功能只有MySQL5.7才有,2015年7月未GA)


  适用场景:实现数据分析部门的需求,将多个系统的数据汇聚到一台服务器上进行OLAP分析计算。


  MariaDB10多源复制的搭建方法如下。


  https://mariadb.com/kb/en/mariadb/multi-source-replication/


  ①创建通道


  SET@@default_master_connection=${connect_name};


  ②建立同步复制


  CHANGEMASTER${connect_name}TO


  MASTER_HOST=’192.168.1.10′,MASTER_USER=’repl’,MASTER_PASSWORD=’repl’,MASTER_PORT=3306,MASTER_LOG_FILE==’mysql-bin.000001′,MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10;


  ③启动


  STARTSLAVE${connect_name};


  STARTALLSLAVES;


  ④停止


  STOPSLAVE${connect_name};


  STOPALLSLAVES;


  ⑤查看状态


  SHOWSLAVE${connect_name}STATUS;


  SHOWALLSLAVESSTATUS;


  ⑥清空同步信息和日志


  RESETSLAVE${connect_name}ALL;


  ⑦刷新Relaylogs


  FLUSHRELAYLOGS${connect_name};


  5、MariaDBColumnStore(InfiniDB4.6.2)数据仓库,用于大数据离线分析计算


  数据


  第五个原因就是数据量逐日增长,在InnoDB里进行复杂SQL查询分析是一件非常痛苦的事情,后来我选择了MariaDBColumnStore数据仓库,专为分布式大规模并行处理MassivelyParallelProcessing(MPP)设计的列式存储引擎,用它做大数据离线分析OLAP系统,借助ETL工具canal,实现抽取binlog并解析为原生态SQL文件入库到Columnstore里。


  Columnstore技术特性


  标准SQL协议支持Navicat/SQLyog/WebSQL等客户端工具

  数据分布式存储(本地化)ShardNothing架构

  分布式并行计算任务并行执行

  横向扩展

  Columnstore技术架构


  Columnstore技术架构


  UM模块:SQL协议接口,接收客户端连接访问,推送SQL请求给PM性能模块代为执行,最后收集性能模块的处理结果做数据汇总,并返回给客户端最终查询结果。

  PM模块:负责数据的列式存储,处理查询请求,将数据提取到内存中计算。

  安装、使用及测试请参考我之前写的文章:《MariaDBColumnStore初探:安装、使用及测试》


  6、审计日志AuditLog


  互联网金融公司对数据很敏感,业务从库提供给开发等人员使用。DBA通过审计日志记录他们操作的结果。


  安装审计AuditPlugin插件:


  AuditPlugin


  MariaDB审计日志参数:


  server_audit_events=‘CONNECT,QUERY,TABLE’


  server_audit_logging=ON


  server_audit_incl_users=‘hechunyang’


  server_audit_excl_users=‘sys_pmm,nagios’


  server_audit_file_rotate_size=10G


  server_audit_file_rotations=500


  server_audit_file_path=/data/audit/server_audit.log


  日志


  将审计日志抽到表里,用PHP展示出来分析。


  PHP


  本节小结


  由于MySQL功能上迭代速度太慢,移步MariaDB后,撑过了业务发展高峰期2015-2016年。


  借助《高性能三》一书的原话:


  MariaDB和Percona有什么不同?


  高可用架构当时选型有两个方案,一个是MHA,一个是PXC,为什么没有选择PXC呢?有以下几个不可抗力因素:


  (1)网络抖动或者机房被ARP攻击,导致NODE节点失联,出现了脑裂,怎么处理?最悲剧的是三份节点都同时写,而且还没复制过来,到底以哪份数据为准?


  (2)硬盘坏了一块,导致RAID10性能下降,会导致集群限流,限流的参数是wsrep_provider_options=gcs.fc_limit:待执行队列长度超过该值时,flowcontrol被触发,默认是16。此时正处于促销活动情形,由于PXC的性能取决于最弱的一个NODE节点,数据库连接数很容易被打满,直接挂了。


  (3)业务如果有大事务,超过了wsrep_max_ws_rows、wsrep_max_ws_size这两个值,节点之间无法复制,造成数据不一致,怎么办?


  由于集群是乐观锁并发控制,事务冲突的情况会在commit阶段发生。如果有两个事务在集群中不同的节点上对同一行写入并提交,失败的节点将回滚,应用端JAVA/PHP返回报错,直接影响用户体验。


  可参考Percona之前分享的PPT——巨大的潜力在PXC架构,貌似解决了一致性的问题,但距离成熟还有一段距离。


  下图是GroupReplication以及GaleraCluster集群触发限流后,性能影响甚大。


  在没有流量控制的情况下,Writer会在有限的时间内处理大量行(来自8个客户端,8个线程,50个并发批量插入)。随着流量控制,情况急剧变化。Writer需要很长时间才能处理明显更小的行数/秒。总之,性能显著下降。


  GroupReplication:TheSweetandtheSour


  (4)最主要的因素——性能问题


  由于PXC/MariaDBGaleraCluster自身不支持VIP功能,MariaDB的解决方案是用MaxScale做七层负载均衡Proxy,由于本身性能就不如主从复制,再过一层代理,性能就更差。可参考下图官方的解决方案。


  GaleraCluster整体架构图如下:


  GaleraCluster


  信任Percona专业团队的选择


  生产数据库HA架构


  HA架构


  MHA管理多组集群(多实例)


  我们公司目前为一主带三从(其中一个从库是做的延迟复制12小时,用pt-slave-delay工具实现),高可用架构采用开源MHA+半同步复制semireplication。


  延迟复制的目的怕万一开发手抖,或者代码写了一个BUG,或者把一个表给删了,通过延迟还能回来。


  上面是一个监控图,报错的就是延时复制从库。


  生产库MariaDB开启的参数


  sync_binlog=1


  innodb_flush_log_at_trx_commit=1


  innodb_support_xa=1(事务的两阶段提交)


  MHA架构和MMM架构有什么区别呢?最大的区别在于:MHA会把丢失的数据,在每个Slave节点上补齐。下面通过一幅图来了解它的工作原理。


  MHA架构


  我们可以看到,当master宕机时,MHA管理机会试图scp丢失的那一部分binlog,然后把该binlog拷贝到最新的slave机器上,补齐差异的binlog并应用。当最新的slave补齐数据后,把它的relay-log拷贝到其他的slave上,识别差异并应用。至此,整个恢复过程结束,从而保证切换后的数据是一致的。


  再通过下图,可以更容易去理解整个恢复过程。


  MHA架构注意事项

  1、防止网络抖动误切换,造成数据不一致


  其实现原理为:投票机制,当监控管理机无法ping通和无法连接MySQL主库,会试图从监控备机上去ping和连接MySQL主库,只有双方都连接失败,才认定MySQL主库宕机。假如有一方可以连接MySQL主库,都不会切换。


  参数:


  secondary_check_script=/usr/local/bin/masterha_secondary_check


  -s192.168.111.76-s192.168.111.79–user=root


  –master_host=QCZJ-dbm


  –master_ip=192.168.111.77–master_port=3306


  从切换日志里看,它先试图用从库111.76和111.79,去同时ping111.77主库,两个都ping不通的话,才认定主库宕机,此时才可以进行故障切换。如果有一个从库能ping通主库都不会进行故障切换。


  需要留意的地方:由于masterha_secondary_check脚本写死了端口,所以要手工修改ssh端口


  $ssh_user=“root”unless($ssh_user);


  $ssh_port=62222unless($ssh_port);


  $master_port=3306unless($master_port);


  2、VIP没有采用keepalived,就是怕网络抖动问题。


  这里我修改了以下两个脚本,自带VIP,大家可以下载试用。


  master_ip_failover_script=/usr/local/bin/master_ip_failover


  master_ip_online_change_script=/usr/local/bin/master_ip_online_change


  红色的部分是修改的地方。


  ——————————————————————————-#HardcodestuffnowuntilthenextMHAreleasepassesSSHinfoinhere


  MHA::ManagerUtil::exec_ssh_cmd($new_master_ip,‘62222’,“ipaddradd192.168.111.83/32devem2;arping-q-c2-U-Iem2192.168.111.83”,undef);


  ——————————————————————————-


  数据库架构演进

  随着网站壮大,数据库架构一般会经历如下演进:


  数据库架构


  为什么要分库分表?(性能+存储扩容)


  单个库数据容量太大,单个DB存储空间不够

  单个库表太多,查询的时候,打开表操作也消耗系统资源

  单个表容量太大,查询的时候,扫描行数过多,磁盘IO大,查询缓慢

  单个库能承载的访问量有限,再高的访问量只能通过分库分表实现

  针对爬虫业务,并发读写频率很高且对事务要求性不高,没有联表关联查询,那么就不需要考虑放入MySQL里,直接存入NOSQL——MongoDB里更适合。


  利用MongoDB自身的Auto-Sharding分片技术实现,通过这种技术可以使我们非常方便的扩展数据,从而不用让开发更改一行代码即可轻松实现数据拆分。


  我们这里做了分布式,集群总共是9台机器分两组Shard,两个Shard组来做的。通过这个自动分片,解决了开发不用改变原代码了,减少日常工作。


  片键的选择


  Hashbasedpartitioning可以确保数据平均分布,但是这样会导致经过哈希处理的值在各个数据块和shard上随机分布,进而使制定的范围查询rangequery不能定位到某些shard而是在每个shard上进行遍历查询。鉴于业务的实际情况,没有范围查询,我们是以userId(查询最频繁的)字段做的Hash拆分。


  再说说片键的注意事项。


  第一,在对文档个别字段update时,如果query部分没有带上shardkey,性能会很差,因为mongos需要把这条update语句派发给所有的shard实例,跨多个网络性能就会下降。

  第二,当update的upsert参数为true时,query部分必须带上shardkey,否则语句执行出错。例:db.t1.update({},{cid:7,name:”D”},{upsert:1})

  第三,shardkey的值不能被更改。

  最后再说一下数据均衡Balance注意事项。


  内部分裂并自动balance,一旦发生数据迁移会造成整个系统的吞吐量急剧下降。为了应对Sharding迁移的不确定性,我们可以强制指定Sharding迁移的时间点,具体迁移时间点依据业务访问的低峰期。


  我们的流量低峰期是在凌晨1点到6点,那么我们可以在这段时间内设置窗口期开启Sharding迁移功能,允许数据的迁移,其他的时间不进行数据的迁移,从而做到对Sharding迁移的完全掌控,避免掉未知时间Sharding迁移带来的一些风险。


  设置窗口期命令:


  useconfig


  db.settings.update({_id:“balancer”},{$set:{activeWindow:{start:“1:00”,stop:“6:00”}}},true)


  数据均衡Balance监控图–PerconaPMM

  监控


  观察getmore黄颜色曲线,1:00-6:00点时间段正是做数据迁移。


  如果不设置窗口期,以我们7200转的sas硬盘,在早高峰做数据迁移,定将影响业务稳定。


  参考我之前写的PMM监控搭建使用文章:《安利一款运维杀手锏,让监控部署不再尴尬!》


  爬虫整体入库架构图

  架构


  新增数据先写入数据库WiredTiger里,然后马上更新到In-Memory引擎(inMemorySizeGB=180G),读取时优先在In-Memory内存中读取,如果数据不在则从后端WiredTiger里取数。In-Memory中的热数据失效时间为一天,等待下次读取时再加载。


  缓存失效时间设置


  在创建索引时,需要指定过期时间,参考画红色线部分,过期后集合里的这个文档就会自动删除。这里有一个注意事项就是:字段必须是时间类型的。


  写关注(WriteConcern)


  1、MongoDB默认为异步复制,本地写完后即返回客户端请求。


  2、可以通过驱动设置为:


  <?php


  //Settingw=majorityforupdate:


  $collection->update($someDoc,$someUpdates,array(“w”=>


  “majority”,”j”=>true));


  ?>


  意思为同步复制机制,主库数据写入内存后,还要确保Journal重做日志刷入磁盘,并保证已复制到从节点后,才会返回更新成功,将请求返回给客户端。


  读写分离


  MongoDB的Java驱动,默认读写是在Primary主节点上,如果想读Secondary从节点,需要通过设置驱动实现。


  节点动态扩容&一致性哈希算法


  节点扩容过程为:数据1、2在节点A上,数据3、4在节点C上。如果增加一个节点B,数据1、2还在A上,只需要把数据3迁到B上,数据4仍在C上,所以只是部分数据迁移,并不是整体数据迁移,这样避免了雪崩的现象。


  延迟复制节点的必要性

  原因:


  1、开发代码有BUG或DBA手抖,一瞬间让你的业务回到解放前


  2、过TB数据备份恢复问题


  MariaDB10.2才支持延迟复制(MySQL5.6早已支持),固需要借助PerconaPT工具实现


  shell>perl/usr/local/bin/pt-slave-delay-S/tmp/mysql.sock–userroot


  –password123456–delay43200–log/root/delay.log–daemonize


  注:单位秒,43200秒等于12小时


  MongoDB3.2延迟复制实现


  Primary>rs.add({host:


  “qianzhan_delay.mongodb.dc.puhuifinance.com:27017”,priority:0,hidden:1,slaveDelay:43200,votes:0})


  注:


  priority权重设置为0,永远不能切为Primary

  hidden设置为隐藏节点

  slaveDelay延迟时间,单位秒,43200秒等于12小时

  votes取消投票资格


  用PerconaMongoDB替换原生版——热备份功能

  PerconaMongoDB3.2版本默认支持WiredTiger引擎的在线热备份,解决了官方版只能通过mongodump逻辑备份这一缺陷。恢复很简单,把备份目录里的数据文件直接拷贝到你的dbpath下,然后启动MongoDB即可。


  参考文献:


  https://www.percona.com/doc/percona-server-for-mongodb/LATEST/hot-backup.html#hot-backup


  注:PerconaserverMongodb3.2.10有一个bug


  directoryperdb=true


  wiredTigerDirectoryForIndexes=true


  这两个参数必须注销掉,否则备份失败。


  这是我提交的bug地址,https://jira.percona.com/browse/PSMDB-123


  Percona采纳了该bug,并在3.2.12版本里修复。


  https://www.percona.com/doc/percona-server-for-mongodb/3.2/release_notes/3.2.12-3.2.html


  PerconaMongoDB3.2HotBackupPerlScripts


  使用说明:请在本地admin数据库,以管理员身份运行createBackup命令,并指定备份目录。


  自动备份脚本


  #perl-MCPAN-e“installMongoDB”


  #!/usr/bin/perl

  useMongoDB;


  useFile::Path;


  usePOSIXqw(strftime);


  my$mc=MongoDB::MongoClient->new(


  host=>“mongodb://localhost:37019/”,


  username=>“admin”,


  password=>“123456”,


  );


  my$db=$mc->get_database(“admin”);


  $year=strftime“%Y”,localtime;


  $month=strftime“%m”,localtime;


  $time=strftime“%Y-%m-%d-%H-%M-%S”,localtime;


  $BAKDB=“yourdb”;


  $BAKDIR=“/data/bak/hcy/$year/$month/$BAKDB_$time”;


  my$user=getpwnam“mongodb”ordie“baduser”;


  my$group=getgrnam“mongodb”ordie“badgroup”;


  mkpath($BAKDIR)ordie“目录已存在.$!”;


  chown$user,$group,$BAKDIR;


  my$cmd=[


  createBackup=>1,


  backupDir=>$BAKDIR


  ];


  $db->run_command($cmd);


  if($!==0){


  print“backupissuccess.”;


  }else{


  print“backupisfailure.”;


  }


  MongoDB慢查询邮件报警并自动KILLPerlScripts


  MongoDB


  通过查看当前操作db.currentOp(),大于指定执行时间,发邮件报警,并通过db.killOp(opid)杀掉进程。


  Oplog盖子集合(CappedCollections)注意事项(可以理解为MySQLBinlog)

  默认剩余空间的5%


  当你搭建副本集的时候,一定要把Oplog设置得比较大,默认是剩余磁盘空间的5%,我们线上设置为100G。Oplog跟binlog存储方式不太一样,binlog是写满一个文件会再生成一个新的文件继续写,而Oplog则是覆盖写。我们看上图,从库挂掉以后再次加入集群时,它会先发送一个位置点给主库,比如现在发送一个位置点是27,主库有的话会把27之后的数据推过来。如主库没有会告知从库我这里没有找到,从库会把本地数据全部删除,从主库上全量抽数据,学名为initialsync。


  神器!MongoDB语法在线生成器

  http://www.querymongo.com/可以将SQL语法转换成MongoDB语法,例子:


  MySQL分库分表中间件选择


  MariaDBSpider分库分表存储引擎

  https://mariadb.com/kb/en/mariadb/spider-storage-engine-overview/


  MySQL


  Spider是MariaDB内置的一个可插拔用于MariaDB/MySQL数据库分片的存储引擎,充当应用服务器和远程后端DB之间的代理(中间件),它可以轻松实现MySQL的横向和纵向扩展,突破单台MySQL的限制,支持范围分区、列表分区、哈希分区,支持XA分布式事务,支持跨库join。通过Spider,您可以跨多个数据库后端有效访问数据,让您的应用程序一行代码不改,即可轻松实现分库分表!


  开发无需调整代码,应用层跟访问单机MySQL一样。

  DBA部署简单,由于MariaDB10默认已经捆绑了Spider引擎,无需编译安装。

  支持标准SQL语法,存储过程,函数,跨库Join,没有Atlas那么多的限制。

  后端DB可以是任一版本,MySQL/MariaDB/Percona

  无维护成本

  生产成熟案例-腾讯公司


  这个是它的整体的架构图,应用程序连接Spider,Spider充当中间件代理,将客户端查询的请求,按照事先定义好的分片规则,分发给后端数据库,之后返回的数据汇总在Spider内存里做聚合,最终返回客户端请求,对于应用程序而言是透明的。


  性能压力测试sysbench

  性能压力


  在我的压测结果上,分表的性能会降低70%,垂直拆分性能会降低40%,性能损耗的原因是在分布式场景下,要保证2PC一致性和可用性读写的表现就差,另外就是跨多个网络传输这两方面引起的。


  在生产环境中,我通过Spider实现了表的垂直拆分,没有做分库分表。


  使用场景介绍


  (架构图)


  1、交易流水表我是半年一切表,老表改名,再创新一张新表,然后通知开发手工改代码里的SQL,用unionall的方式关联查询。如:select*fromt1whereapply_no=‘XXXX’unionallselect*fromt1_20170630whereapply_no=‘XXXX’


  2、由于历史表没有写操作,只有用户的查询,且查询频率并不是很高,将历史表移到备份机,再通过spider做一个映射(软连接)实现表的垂直拆分,解决磁盘空间扩展问题。


  3、实施这个方案,选择Spider引擎是有优势的:


  SQL解析和查询优化是个非常复杂且很难做好的工作,其它替代产品都是自己实现,由于复杂性,这些产品都带来了一些限制,比如不支持存储过程、函数、视图等,给使用和实施带来了困难。而作为一个存储引擎,这些工作都由MariaDB自身完成了,可以方便地将大表做分布式拆分,它的好处是对业务方使用是透明的,SQL语法没有任何限制,在不改变现有DB架构的方案中,侵入性最小。


  提升性能的关键


  optimizer_switch=‘engine_condition_pushdown=on’


  引擎下推,查询推送到后端数据库,将查询结果返回给Spider做聚合,类似Map-Reduce。早期的版本是从后端拉取所需的数据到本地临时表,然后再做处理。


  Spider引擎安装

  shell>mysql-uroot-p</usr/local/mysql/share/install_spider.sql


  SELECTengine,support,transactions,xaFROM


  information_schema.engines;


  Spider引擎使用

  定义后端服务器和数据库名字


  这个是定义后端服务器和数据库名字。这里后端服务器的名字为backend1,数据库名字为test,主机IP地址为192.168.143.205,用户名为user_readonly,密码为123456,端口为3306。


  注:如配置错误,可直接DROPSERVERbackend1;重新创建即可。


  垂直拆分(映射、软连接)


  这个是定义垂直拆分,也就是映射和软连接,做一个超链接。Spider自身不保存数据,只保存路由信息。这里通过设置COMMENT注释来调用后端的表,然后你就可以查看sbtest表了,是不是很简单?


  MariaDB10.3&SpiderGA


  参考https://mariadb.org/embrace-community-fly-open-source-dream/


  监控慢SQL—PerconaQueryAnalytics


  慢查询监控也是用的是Percona来做,这里是集成了可视化平台。


  MySQL慢查询邮件报警并自动KILL


  (PerconaPT-kill精简版)


  多增加发送kill掉后的慢SQL邮件报警功能


  注:官方原版默认被kill掉的SQL不会发邮件出来,这会造成不能及时通知开发,对排查问题带来困惑。


  下一代关系型数据库NewSQL


  最后说一下下一代关系型数据库NewSQL:CockroachDB和TiDB。


  CockroachDB是一个分布式SQL数据库。其主要设计目标是扩展性、强一致性和生存性(CockroachDB蟑螂数据库由此得名)。CockroachDB的目标是容忍磁盘、机器、机架,甚至数据中心故障,在无需人工干预的情况下,最小化这些延迟中断的影响。CockroachDB各节点是对等的,设计目标是同质化部署(一个二进制包),最小化配置,也不需要外部依赖项。CockroachDB集群中的每个节点都可以扮演一个客户端SQL网关角色,SQL网关将客户端SQL语句转换成KV操作,分发到所需的节点执行并返回结果给客户端。其设计灵感,来自谷歌Spanner和F1论文。


  https://github.com/cockroachdb/cockroach


  https://www.cockroachlabs.com/docs/stable/


  区别

  TiDB的SQL解析协议是基于MySQL,而CockroachDB是基于PostgreSQL。


  内部架构体系

  CockroachDB采用分层架构,其最高抽像层为SQL层,CockroachDB直接通过SQL层提供熟悉的关系概念,如:模式schema、表table、列column和索引index,接下来SQL层依赖于分布式KV存储,该存储管理range处理的细节以提供一个单一全局KV存储的抽象。分布式KV存储与任意数量的CockroachDB物理节点通信,每个物理节点包含一个或者多个存储。


  特点

  在原有NoSQL数据库(FacebookRocksDB)基础上,增加了分布式事务,解决了数据强一致性。

  支持传统SQL语法,封装了一层PostgreSQL协议。

  采用MongoDB的Raft协议做故障切换(大多数投票机制),默认3个节点挂1个节点不影响业务读写。

  节点动态热扩容,节点间的数据自动迁移。

  内部自动分裂数据块(达到64M),自动balance均衡(数据迁移)。

  全同步机制(强一致性),数据写入必须至少2个副本(默认3个副本)落地,客户端才可以返回提交成功请求。

  任意一个节点支持读写操作。

  使用场景


  这个是Percona之前在这个文章里做的评测


  What’sNextforSQLDatabases?


  性能上,不及MySQL,生产环境主库替代MySQL为时尚早,其工业品质和MySQL尚有差距。


  BothCockroachDBandTiDB,atthemomentofthiswriting,stillhaveroughedgesandcan’tbeusedinseriousdeployments(frommyexperience).Iexpectbothprojectswillmakeabigprogressin2017.


  译:不能用于严重部署(根据我的经验),我预计这两个项目将在2017年取得重大进展。


  不能用于OLAP和重度数据分析

  JOIN关联查询性能较差

  比较适合的场景,就是订单历史流水表,物流历史表,论坛帖子历史表这种,低并发简单SQL读写,通过CockroachDB自动扩容

  我们公司现在把历史表导入到CockroachDB里面,配合大数据部门,让他们从这里直接抽数据。


  性能差的原因


  一个是全同步机制,强一致性,数据至少写入两个节点才可以。

  第二就是默认序列化,事务只能一个一个执行,不能并行执行。

  第三分布式事务提交,需要跨多个网络,网络IO开销大。

  Thelongtransactions(let’ssaychanging100000ormorerows)alsowill


  beproblematic.Thereisjusttoomuchnetworkround-tripsand


  housekeepingworkoneachnode,makinglongtransactionsanissuefor


  distributedsystems.


  What’sNextforSQLDatabases?


  译:大事务(例如更改10万行或更多)也是有问题的。每个节点都有太多的网络往返,使得长时间的大事务成为分布式系统的一个瓶颈。


  默认三个副本,每个节点都可以读写:


  运维部署


  参数解释:


  –cache为内存缓存的数据,通常为物理内存的70%

  –join为加入群集节点

  部署非常简单,只需要添加节点,数据会自动迁移扩容。


  CockroachDB客户端PosticoforMac


  命令行工具


  #psql-h192.168.1.1-Udev-p26257–password


  自带监控http://192.168.155.46:8080


  这个是自带的监控平台,可以看到运行情况。


  从MySQL迁移历史数据到CockroachDB

  由于采用PostgreSQL协议,MySQL表结构语法会报错,需要微调。

  不支持COMMENT注释,需注销掉。

  AUTO_INCREMENTPRIMARYKEY主键自增,需改成SERIAL

  int(11)改为int,没有tinyint,用smallint代替

  不支持double,用decimal代替

  不支持`反引号,需注销掉

  创建表结构时,不支持写二级索引,需要单独用命令创建

  默认UTF-8字符集

  timestamp默认UTC格林威治时间

  更多请参考https://www.cockroachlabs.com/docs/stable/data-types.html


  1、导出MySQL表结构


  #mysqldump–xml–compacttestt1>t1_schema.sql


  2、转成PostgreSQL表结构


  #phpconvertor.php-it1_schema.sql-ot1_schema.sql.pg


  https://github.com/mihailShumilov/mysql2postgresql


  3、导出MySQL数据


  #mysqldump–single-transaction–compact


  –default-character-set=utf8–set-charset-c-t-q–extended-insert


  -uroot-p123456–compatible=postgresqltestt1>t1.sql


  4、如果SQL文件里有转义符,需要进行一次格式化,PostgreSQL在反斜杠转义符之前需要添加’E’前缀。


  #sed-i“s/,’/,e’/g”t1.sql


  导入到CockroachDB里


  #psql-h192.168.155.249-Uroot-p26257-dtest<t1.sql


  开启慢查询

  #定义慢SQL执行时间


  >SETCLUSTERSETTINGsql.trace.txn.enable_threshold=‘1s’;


  #开启慢日志记录


  >SETCLUSTERSETTINGsql.trace.log_statement_execute=true;


  效果如下:


  1.1新版本特性


  1、支持查看SQL运行状态,类似MySQLshowprocesslist命令


  2、支持kill慢SQL线程id,类似MySQLkillthread_id


  执行时间超过10秒的select查询全部干掉


  CANCELQUERY(SELECTquery_idFROM[SHOWCLUSTERQUERIES]WHEREstart<(now()–INTERVAL’10seconds’)ANDquery~*‘select’);


  开发手册

  1、下载PostgrepSQL驱动


  https://jdbc.postgresql.org/


  2、连接CockroachDB范例ForJDBC


来源:本文由E8运维原创撰写,欢迎分享本文,转载请保留出处和链接!