mysql数据库迁移,如何构建高性能MySQL

mysql数据库迁移，如何构建高性能MySQL？

测试场景MySQL 5.7.12主要测试不同刷盘参数对性能的影响, 使用以下三个场景:sync_binlog=1, innodb_flush_log_at_trx_commit=1, 简写为b1e1 (binlog-1-engine-1)sync_binlog=0, innodb_flush_log_at_trx_commit=1, 简写为b0e1sync_binlog=0, innodb_flush_log_at_trx_commit=0, 简写为b0e0

MySQL 环境搭建使用 MySQL sandbox, 对应三个场景的启动参数如下:1. ./start --sync-binlog=1 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=12. ./start --sync-binlog=0 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=13. ./start --sync-binlog=0 --log-bin=bin --server-id=5712 --gtid-mode=ON --enforce-gtid-consistency=1 --log-slave-updates=1 --innodb-flush-log-at-trx-commit=0

压力生成使用sysbench:

mysql -h127.0.0.1 -P5712 -uroot -pmsandbox -e"truncate table test.sbtest1"; /opt/sysbench-0.5/dist/bin/sysbench --test=/opt/sysbench-0.5/dist/db/insert.lua --mysql-table-engine=innodb --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=msandbox --mysql-port=5712 --oltp-table-size=1000000 --mysql-db=test --oltp-table-name=stest --num-threads=1 --max-time=30 --max-requests=1000000000 --oltp-auto-inc=off --db-driver=mysql run

性能观测工具使用systemtap(简称stap), version 2.7/0.160

基准

在没有观测压力的情况下, 对三种场景分别进行基准测试, 用以矫正之后测试的误差:

场景sysbench事务数b1e167546b0e1125699b0e0181612

火焰图与offcpu火焰图

火焰图是Brendan Gregg首创的表示性能的图形方式, 其可以直观的看到压力的分布. Brendan提供了丰富的工具生成火焰图.

火焰图比较b0e1和b0e0

使用stap脚本获取CPU profile, 并生成火焰图(火焰图生成的命令略, 参看Brendan的文档)

stap脚本

global tids probe process("/home/huangyan/sandboxes/5.7.12/bin/mysqld").function("mysql_execute_command") { if (pid() == target() && tids[tid()] == 0) { tids[tid()] = 1; } } global oncpu probe timer.profile { if (tids[tid()] == 1) { oncpu[ubacktrace()] <<< 1; } } probe timer.s(10) { exit(); } probe end { foreach (i in oncpu+) { print_stack(i); printf("\t%d\n", @count(oncpu[i])); } }

注意:1. 脚本只抓取MySQL的用户线程的CPU profile, 不抓取后台进程.2. 脚本只抓取10s, 相当于对整个sysbench的30s过程进行了短期抽样.

b0e1生成的火焰图

性能

在开启观测的情况下, 观察性能:

场景sysbench事务数b0e1119274b0e0166424

分析

在生成的火焰图中, 可以看到:

在b0e1场景中, _ZL27log_write_flush_to_disk_lowv的占比是12.93%, 其绝大部分时间是用于将innodb的log刷盘.在b0e0场景中, _ZL27log_write_flush_to_disk_lowv的开销被节省掉, 理论上的事务数比例应是1-12.93%=87.07%, 实际事务数的比例是119274/166424=71.67%, 误差较大

误差较大的问题, 要引入offcpu来解决.

offcpu

在之前的分析中我们看到理论和实际的事务数误差较大. 考虑_ZL27log_write_flush_to_disk_lowv的主要操作是IO操作, IO操作开始, 进程就会被OS进行上下文切换换下台, 以等待IO操作结束, 那么只分析CPU profile就忽略了IO等待的时间, 也就是说_ZL27log_write_flush_to_disk_lowv的开销被低估了.

offcpu也是Brendan Gregg提出的概念. 对于IO操作的观测, 除了CPU profile(称为oncpu时间), 还需要观测其上下文切换的代价, 即offcpu时间.

修改一下stap脚本可以观测offcpu时间. 不过为了将oncpu和offcpu的时间显示在一张火焰图上作对比, 我对于Brendan的工具做了微量修改, 本文将不介绍这些修改.

stap脚本

global tids probe process("/home/huangyan/sandboxes/5.7.12/bin/mysqld").function("mysql_execute_command") { if (pid() == target() && tids[tid()] == 0) { tids[tid()] = 1; } } global oncpu, offcpu, timer_count, first_cpu_id = -1; probe timer.profile { if (first_cpu_id == -1) { first_cpu_id = cpu(); } if (tids[tid()] == 1) { oncpu[ubacktrace()] <<< 1; } if (first_cpu_id == cpu()) { timer_count++; } } global switchout_ustack, switchout_timestamp probe scheduler.ctxswitch { if (tids[prev_tid] == 1) { switchout_ustack[prev_tid] = ubacktrace(); switchout_timestamp[prev_tid] = timer_count; } if (tids[next_tid] == 1 && switchout_ustack[next_tid] != "") { offcpu[switchout_ustack[next_tid]] <<< timer_count - switchout_timestamp[next_tid]; switchout_ustack[next_tid] = ""; } } probe timer.s(10) { exit(); } probe end { foreach (i in oncpu+) { print_stack(i); printf("\t%d\n", @sum(oncpu[i])); } foreach (i in offcpu+) { printf("---"); print_stack(i); printf("\t%d\n", @sum(offcpu[i])); } }

注意: timer.profile的说明中是这样写的:

Profiling timers are available to provide probes that execute on all CPUs at each system tick.

也就是说在一个时间片中, timer.profile会对每一个CPU调用一次. 因此代码中使用了如下代码, 保证时间片技术的正确性:

if (first_cpu_id == cpu()) { timer_count++; }

b0e1生成的带有offcpu的火焰图

性能

由于变更了观测脚本, 需要重新观测性能以减小误差:

场景sysbench事务数b0e1105980b0e0164739

分析

在火焰图中, 可以看到:1. _ZL27log_write_flush_to_disk_lowv的占比为31.23%2. 理论上的事务数比例应是1-31.23%=68.77%, 实际事务数的比例是105980/164739=64.33%, 误差较小.

观测误差的矫正

在比较b0e1和b0e0两个场景时, 获得了比较好的结果. 但同样的方法在比较b1e1和b0e1两个场景时, 出现了一些误差.

误差现象

b1e1的火焰图如图:

其中_ZN13MYSQL_BIN_LOG16sync_binlog_fileEb(sync_binlog的函数)占比为26.52%.

但性能差异为:

场景sysbench事务数b1e153752b0e1105980

理论的事务数比例为1-26.52%=73.48%, 实际事务数比例为53752/105980=50.71%, 误差较大.

分析压力分布

首先怀疑压力转移, 即当sync_binlog的压力消除后, 服务器压力被转移到了其它的瓶颈上. 但如果压力产生了转移, 那么实际事务数比例应大于理论事务数比例, 即sync_binlog=0带来的性能提升更小.

不过我们还是可以衡量一下压力分布, 看看b1e1和b0e1的压力有什么不同, 步骤如下:

修改stap脚本, 在b1e1中不统计sync_binlog的代价. 生成的火焰图表示消除sync_binlog代价后, 理论上的服务器压力类型.与b0e1产生的火焰图做比较.

stap脚本

只修改了probe end部分, 略过对my_sync堆栈的统计:

probe end { foreach (i in oncpu+) { if (isinstr(sprint_stack(i), "my_sync")) { continue; } print_stack(i); printf("\t%d\n", @sum(oncpu[i])); } foreach (i in offcpu+) { if (isinstr(sprint_stack(i), "my_sync")) { continue; } printf("---"); print_stack(i); printf("\t%d\n", @sum(offcpu[i])); } }

结果

b1e1, 理论的服务器压力图:

b0e1, 实际的服务器压力图:

可以看到, 压力分布是非常类似, 即没有发生压力分布.

BTW: 这两张图的类似, 具有一定随机性, 需要做多次试验才能产生这个结果.

分析

既然理论和实际的压力分布类似, 那么可能发生的就是压力的整体等比缩小. 推测是两个场景上的观测成本不同, 导致观测影响到了所有压力的观测.

观察stap脚本, 其中成本较大的是ctxswitch, 即上下文切换时的观测成本.

上下文切换的观测成本

如果 “上下文切换的观测成本影响场景观测的公平性” 这一结论成立, 那么我们需要解释两个现象:1. b1e1和b0e1的比较, 受到了上下文切换的观测成本的影响2. b0e1和b0e0的比较, 未受到上下文切换的观测成本的影响

假设上下文切换的观测成本正比于上下文切换的次数, 那么我们只需要:1. 观测每个场景的上下文切换次数2. 对于b1e1和b0e1的比较, 由上下文切换次数计算得到理论的降速比, 与实际的降速比进行比较3. 对于b0e1和b0e0的比较, 由上下文切换次数计算得到是否会带来降速.

stap脚本

在probe scheduler.ctxswitch和probe end 增加了 ctxswitch_times 相关的内容:

global ctxswitch_times probe scheduler.ctxswitch { ctxswitch_times++; ... } probe end { ... printf("ctxswitch_times=%d\n", ctxswitch_times); }

结果

场景sysbench事务数上下文切换次数sync_binlog占比b1e15535282637036.80%b0e1105995693383–b0e0162709675092–

分析结果:1. b1e1与b0e1的比较1. 理论降速比: 693383/826370 = 83.90%2. 实际降速比: (实际的事务数比例/由sync_binlog占比推算的理论的事务数比例) = (55352/105995)/(1-36.80%) = 0.5222/0.6320 = 82.63%3. 误差很小. 即b1e1与b0e1的比较中, 理论值和实际值的误差来自于: IO操作的减少导致上下文切换的数量减小, 使得两个场景的观察成本不同.2. b0e1与b0e0的比较: 上下文切换次数相近, 即两个场景的观察成本相同.

实验结果符合之前的分析.

结论利用火焰图, 可以快速诊断出MySQL服务器级别的性能瓶颈, 做出合理的参数调整对于IO类型的操作的观测, 需要考虑oncpu和offcpu两种情况由于观测手段中使用了上下文切换作为观测点, 那IO操作数量的不同, 会引起上下文切换次数的不同, 从而引起观测误差.

goldendb与mysql的区别？

GoldenDB和MySQL是两种不同的数据库系统，具有一些区别。1. 数据库类型：GoldenDB是一种NoSQL类型的数据库，而MySQL是一种关系型数据库（RDBMS）。2. 数据模型：GoldenDB使用文档模型（Document Model）存储数据，数据以类似于JSON的BSON（Binary JSON）格式进行存储。而MySQL使用表格模型（Table Model）来存储数据，数据以行和列的形式组织。3. 数据结构：GoldenDB支持灵活的动态模式，即数据的结构可以根据需要动态进行更改。MySQL则具有严格的预定义模式，数据结构需要在创建表时定义，并且字段类型和长度需要提前确定。4. 扩展性：GoldenDB具有良好的水平扩展性，可以通过添加节点来增加数据存储和读写能力。MySQL则可以通过增加硬件资源来提高性能，但扩展性相对较差。5. 查询语言：GoldenDB使用类似于MongoDB的查询语言（如使用find、aggregate等命令），支持面向文档的查询。MySQL使用SQL查询语言，以表为单位进行查询和操作。6. 数据一致性：GoldenDB采用最终一致性的模型，默认情况下提供“读写分离”，从节点上的数据更改可能存在一定的延迟。MySQL则提供强一致性，可以保证读取的数据是最新的。需要根据具体的应用场景和需求来选择使用GoldenDB还是MySQL。如果需要使用灵活的数据模型和高度可扩展性，GoldenDB可能是一个更好的选择；而如果需要严格的事务控制和强一致性，MySQL可能更适合。

想用MongoDB取代MySQL可以吗？

脱离业务场景，空谈技术架构都是耍流氓。

我们公司同一个项目就同时在用Mysql和MongoDB，希望通过下面介绍可以帮助你真正了解到Mysql和MongoDB优劣对比及实际业务应用场景。

数据库人气排行

以下来自最新的db-engines的数据库人气排行榜

接下来我们看一下前十名的趋势变化图：

关系数据库前10名如下：

文档数据库前10名如下：

通过上面可以看出MongoDB虽说分数一直保持着稳定上升的趋势，但和 Mysql相比依然有一定的差距。不过，MongoDB 在2018年的表现是非常不错的，至少一直都在进步，这个表现也是 MongoDB 独一份。

数据结构

MySQL：MySQL将数据存储在表中，并使用结构化查询语言（SQL）访问数据。MySQL使用模式来定义数据库结构，要求表中的所有行具有相同的结构，并且值由特定的数据类型表示。

MongoDB：在MongoDB中，数据存储在类似JSON的文档中，这些文档可以有不同的结构。为了提高查询速度，MongoDB可以将相关数据存储在一起，这些数据可以使用MongoDB查询语言访问。

在MongoDB中，文档能够拥有自己独特的结构。新字段可以随时添加并包含任何类型的值。

优缺点

MySQL是关系型数据库。

优势：

在不同的引擎上有不同的存储方式。

查询语句是使用传统的sql语句，拥有较为成熟的体系，成熟度很高。

开源数据库的份额在不断增加，mysql的份额页在持续增长。

缺点：

在海量数据处理的时候效率会显著变慢。

Mongodb是非关系型数据库(nosql )，属于文档型数据库。

存储方式：虚拟内存+持久化。

查询语句：是独特的Mongodb的查询方式。

适合场景：事件的记录，内容管理或者博客平台等等。

架构特点：可以通过副本集，以及分片来实现高可用。

数据处理：数据是存储在硬盘上的，只不过需要经常读取的数据会被加载到内存中，将数据存储在物理内存中，从而达到高速读写。

成熟度与广泛度：新兴数据库，成熟度较低，Nosql数据库中最为接近关系型数据库，比较完善的DB之一，适用人群不断在增长。

优点：

快速！在适量级的内存的Mongodb的性能是非常迅速的，它将热数据存储在物理内存中，使得热数据的读写变得十分快。高扩展性，存储的数据格式是json格式！

缺点：

事务关系支持薄弱。这也是所有NoSQL数据库共同的缺陷，不过NoSQL并不是为了事务关系而设计的，具体应用还是根据需求。而且开发文档不是很完全、完善。

应用场景

关系型数据库适合存储结构化数据，如用户的帐号、地址

1）这些数据通常需要做结构化查询，比如join，这时候，关系型数据库就要胜出一筹

2）这些数据的规模、增长的速度通常是可以预期的

3）事务性、一致性

NoSQL适合存储非结构化数据，如文章、评论：

1）这些数据通常用于模糊处理，如全文搜索、机器学习

2）这些数据是海量的，而且增长的速度是难以预期的，

3）根据数据的特点，NoSQL数据库通常具有无限（至少接近）伸缩性

4）按key获取数据效率很高，但是对join或其他结构化查询的支持就比较差

什么场景MongoDB更适用

更高的写入负载

默认情况下，MongoDB更侧重高数据写入性能，而非事务安全，MongoDB很适合业务系统中有大量“低价值”数据的场景。但是应当避免在高事务安全性的系统中使用MongoDB，除非能从架构设计上保证事务安全。

高可用性

MongoDB的复副集(Master-Slave)配置非常简洁方便，此外，MongoDB可以快速响应的处理单节点故障，自动、安全的完成故障转移。这些特性使得MongoDB能在一个相对不稳定（如云主机）的环境中，保持高可用性。

数据量很大或者未来会变得很大

依赖数据库(MySQL)自身的特性，完成数据的扩展是较困难的事，在MySQL中，当一个单达表到5-10GB时会出现明显的性能降级，此时需要通过数据的水平和垂直拆分、库的拆分完成扩展，使用MySQL通常需要借助驱动层或代理层完成这类需求。而MongoDB内建了多种数据分片的特性，可以很好的适应大数据量的需求。

基于位置的数据查询

MongoDB支持二维空间索引，因此可以快速及精确的从指定位置获取数据。

表结构不明确，且数据在不断变大

在一些传统RDBMS中，增加一个字段会锁住整个数据库/表，或者在执行一个重负载的请求时会明显造成其它请求的性能降级。通常发生在数据表大于1G的时候（当大于1TB时更甚）。因MongoDB是文档型数据库，为非结构货的文档增加一个新字段是很快速的操作，并且不会影响到已有数据。另外一个好处当业务数据发生变化时，是将不在需要由DBA修改表结构。

没有DBA支持

如果没有专职的DBA，并且准备不使用标准的关系型思想（结构化、连接等）来处理数据，那么MongoDB将会是你的首选。MongoDB对于对像数据的存储非常方便，类可以直接序列化成JSON存储到MongoDB中。但是需要先了解一些最佳实践，避免当数据变大后，由于文档设计问题而造成的性能缺陷。

实际业务应用

BillRun – 基于MongoDB的帐单系统 (来自oc666)

BillRun是由Ofer Cohen推出开源账单系统，采用MongoDB做为数据存储。这套账单系统被以色列一家增速最快的电信运营商采用，每月处理5亿条通信记录，Ofer在Slideshare上说明了具体利到了MongoDB的哪些特性：

弱数据结构的特点，使得BillRun能很快的支持新的CDR(通讯记录)类型。这个特性使文档型数据库很适用于快速发展、业务需求不确定的系统中。

BillRun仅使用了一个Collection，已经管理了数TB的文档数据，并且没有遇到由结构变更、数据爆发式增长的带来的限制和问题。

replicaSet副本集特性使建立更多的数据中心DRP变得更轻松。

内建的Sharding分片特性避免系统在数据增长的过程中遇到性能瓶颈。

每秒钟2000条通信记录的插入，MongoDB在架构设计上很好的支持了高负载的数据写入。并且可以使用findAndModify(相对缓慢)完成基础的事务特性，并且通过应用层面的支持，实现双段式提交。

查询方式相比SQL，更加易读、易懂，开发相对轻松。

基于位置允许更好的分析用户使用情况，从而更好地制定移动电话基础设施的投入点。