讨论数据库减库存几种方法V1

MySQL在电子商务网站使用越来越多,做为第一梯队做MySQL的从业人员,也经常被问到MySQL对于减库存在这块有什么好的处理方法?对于这个问题,想想在课上分享及朋友中交流讲的不下百次。 所以也有了把这个总结一下的想法。
对于减库存的操作,我们需要先从一个更高的Level来看一下,先不用讨论update减1的问题。我们可以把减库存形为简单的分成以下几种:

  • 团购类减库存(库存无限,卖的越多越好) 这个可能大家见的也比较多,就不用多说,这类业务,多卖一些老板反而高兴。
  • 限时活动类(24小时活动产品)
  • 秒杀抢购类活动
  • 正常商品售卖

当然还别的形式,可能有的减订单操作是一个复合型的业务: 如 12306这样的业务,含秒杀,抢购,限时活动于一身,难度空前绝后:)

那么我们再来看一下订单系统里几个难题:
1. 超卖,听说某米是允许在商品接近预警时自动关闭交易(商品下架),并允许一定的超售现象。
2. 羊毛党下单不支付,活动结束,一个也没卖出去。 有的电商的做法是,付款后再减库存,不付款不减,可能出现的问题是,用户犹豫一下,去付款时,告诉你没货了,体验不好。
3. 高并发的抢购,打压到DB中,把DB打死的问题。比较常见的一个问题,早些年某电商还发布过把MySQL死锁去掉的分支,用于支撑抢购里业务,现在MySQL 8.0天生支持,另外也可以考虑在在业务层Nginx上控制。

那么我再回过来看一下减库存的形式,我们可以把减库存,分为两种:

  • 正常商品售卖类减库存
  • 活动促销类减库存

问题分为:是否允许超卖 ,要不要考虑羊毛党的一些形为。 处理好这些问题的情况下支撑高并发业务。另外给老板们提个醒,系统优化是一个全面工作,不只是这里有瓶颈。

我分成几种情况来讨论:

  1. 用户下单减库存 如果允许超卖的情况下, 外加一个预警商品下架(缺货)逻辑来处理,基本可以很快速的跑起来。 业务逻辑最简单适合正常商品逻辑。库存大于多少可以上架之类的一些处理(后台人工调配) 如果不允许超卖, 例如车票这类,处理的办法不在是简单的库存结构设计,可以把每个坐位做为一个商品上架构, 只是车票商品上的属性有自已是那个车,挨着谁之类的信息即可。下单后锁定30分钟… 可以目前各大电商的主流逻辑,只是不同的平台,锁时时间不一样。 防止用户下单后几天后还不支付,让商品可以回归库存,不支付订单的失效过程。 下单减库存也是大家最容易接受的一个形为,但这里把最难的问题抛给了平台,一个商品多少没支付返回库存,目前来看不同平台处理的形为不一样。 不适用于抢购,秒杀点业务。
  2. 支付减库存
    抢购,秒杀,谁先付款谁得的逻辑。 这样可以避免羊毛党下单后不支付的形为,例如,本来只有10个特价产品,羊毛党手快,下单成功,但种种原因没支付,提示商品已售完,但活动结束了,一个商品也没卖出去,老板花的活动费白花了。
  3. 高并发的抢购业务
    例如某米周二中午的抢购,10万个商品,可能是100万人在抢购,这种形为,如果都打到DB,可能是对DB的锁也是一个挑战,可能出现的形为就是不该慢的也慢了,去除检查也是一个好办法。
    现在聪明的人类想好了更好的办法,利用Nginx+LUA引入接入在接层引入摇号方法,基于Cookie在Nginx层拿到了可以购买的资格再往后面放,如果没拿到这个资格就在Nginx上看看,提示你商品已经售完就行。这样每次扩容,只用扩一下Nginx接入层就Ok了,其它不用扩了。

问题分类后,现在来看,对于减库存,如果分类处理后,你会发现,还是挺容易处理的,这里面里也不要再来找我聊用MySQL的乐观锁控制来减库存了,如果你在知数堂的课上学习过,一定会知道多点写入的乐观锁控制会造成更新丢失的现象,同样来讲,单节点的同时并发,不是串行的情况,这事没戏,我确时见过有的公司在这块用了串行事务,他们也跑的挺好,业务量不大。祝你玩的开心。也祝愿你们早日在减库存这块遇到瓶颈,让老板高兴痛苦的给你加薪。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

磁盘利用率和饱和度

导读

如何观察磁盘的IO利用率以及饱和度?

看文本文给你解药!

翻译团队:知数堂藏经阁项目 – 菜鸟盟

团队成员:菜鸟盟–hades、菜鸟盟-bruce、菜鸟盟-冰焰

译文校稿:叶师傅

原文出处:https://www.percona.com/blog/2017/08/28/looking-disk-utilization-and-saturation/

原文作者:Peter Zaitsev (Percona CEO)

在这篇文章里,会介绍磁盘利用率和饱和度相关的知识。

In this blog post, I will look at disk utilization and saturation.

在之前的博客里面,我写了一些关于CPU使用率和饱和度之间有什么实质性不同,以及CPU使用率、饱和度如何从不同维度影响响应时间(RT)的文章。
现在我们来看另一个影响数据库性能重要因素:存储子系统。在下面文章里,我会用“磁盘”代替存储子系统。

In my previous blog post, I wrote about CPU utilization and saturation, the practical difference between them and how different CPU utilization and saturation impact response times.

Now we will look at another critical component of database performance: the storage subsystem. In this post, I will refer to the storage subsystem as “disk” (as a casual catch-all).

监控IO性能最常用的工具是iostat,会显示如下的信息:

The most common tool for command line IO performance monitoring is iostat, which shows information like this:

root@ts140i:~# iostat -x nvme0n1 5
Linux 4.4.0-89-generic (ts140i)         08/05/2017      _x86_64_        (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          0.51    0.00    2.00    9.45    0.00   88.04

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 3555.57 5887.81 52804.15 87440.73    29.70     0.53    0.06    0.13    0.01   0.05  50.71

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          0.60    0.00    1.06   20.77    0.00   77.57

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 7612.80    0.00 113507.20     0.00    29.82     0.97    0.13    0.13    0.00   0.12  93.68

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          0.50    0.00    1.26    6.08    0.00   92.16

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 7653.20    0.00 113497.60     0.00    29.66     0.99    0.13    0.13    0.00   0.12  93.52

第一行(avg-cpu)显示的是自系统启动之后平均的性能。某些情况下,用当前系统的压力和平均性能作对比是很有用的。这篇文章的案例是测试环境,所以可以忽略对比这两种情况。
第二行(Device)显示的当前5秒钟的性能指标(在命令行中指定了每5秒输出一次)。

The first line shows the average performance since system start. In some cases, it is useful to compare the current load to the long term average. In this case, as it is a test system, it can be safely ignored. The next line shows the current performance metrics over five seconds intervals (as specified in the command line).

iostat命令用%util列显示利用率的信息,可以通过观察平均请求队列大小(the avgqu-sz 列)或者通过r_await和w_await列(显示平均的读和写的等待)来观察IO饱和度。如果超过正常值,设备就会过度饱和了。

The iostat command reports utilization information in the %util column, and you can look at saturation by either looking at the average request queue size (the avgqu-sz column) or looking at the r_await and w_await columns (which show the average wait for read and write operations). If it goes well above “normal” then the device is over-saturated.

和之前的文章一样,我们会执行Sysbench,然后观察iostat命令、Percona PMM的输出。

As in my previous blog post, we’ll perform some system Sysbench runs and observe how the iostat command line tool and Percona Monitoring and Management graphs behave.

我们用Sysbench测试文件IO,以便观察磁盘的变化。我创建了一个100GB的文件,因为用了DirectIO方式所以所有的请求都会直接打到磁盘。我也会用”sync”刷新模式以便更好的控制IO请求的并发度。

To focus specifically on the disk, we’re using the Sysbench fileio test. I’m using just one 100GB file, as I’m using DirectIO so all requests hit the disk directly. I’m also using “sync” request submission mode so I can get better control of request concurrency.

在这个实验中我用了一个Intel 750 NVME固态硬盘(虽然这并不重要)。

I’m using an Intel 750 NVME SSD in this test (though it does not really matter).

Sysbench FileIO 1线程

    root@ts140i:/mnt/data# sysbench  --threads=1 --time=600 --max-requests=0  fileio --file-num=1 --file-total-size=100G --file-io-mode=sync --file-extra-flags=direct --file-test-mode=rndrd run
     
    File operations:
       reads/s:                      7113.16
       writes/s:                     0.00
       fsyncs/s:                     0.00
     
    Throughput:
       read, MiB/s:                  111.14
       written, MiB/s:               0.00
     
    General statistics:
       total time:                          600.0001s
       total number of events:              4267910
     
    Latency (ms):
            min:                                  0.07
            avg:                                  0.14
            max:                                  6.18
            95th percentile:                      0.17

单线程测试结果通常可以作为基线,通常只有一个请求时的响应时间通常也最快(不过一般也不是最佳吞吐量)。

A single thread run is always great as a baseline, as with only one request in flight we should expect the best response time possible (though typically not the best throughput possible).

Iostat
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 7612.80    0.00 113507.20     0.00    29.82     0.97    0.13    0.13    0.00   0.12  93.68

磁盘读写延迟

磁盘延迟图像证实了我们从iostat命令看到的磁盘IO延迟,它指定了特定设备。我们用它作为基线和更高并发做对比。

The Disk Latency graph confirms the disk IO latency we saw in the iostat command, and it will be highly device-specific. We use it as a baseline to compare changes to with higher concurrency.

磁盘IO使用率

即便我们只发起一个IO请求(队列深度),但磁盘IO使用率已很接近100%。这个是linux磁盘使用率显示的问题,它不像CPU利用率,由于IO设备的特殊设计机制linux不能直接显示其利用率。它真正有多少可执行单元?他们是怎么被使用的?当做了raid后,每个物理磁盘都可以被视为单独的执行单元,而SSD磁盘以及云存储(比如EBS)则可能有更多执行单元。

Disk IO utilization is close to 100% even though we have just one outstanding IO request (queue depth). This is the problem with Linux disk utilization reporting: unlike CPUs, Linux does not have direct visibility on how the IO device is designed. How many “execution units” does it really have? How are they utilized? Single spinning disks can be seen as a single execution unit while RAID, SSDs and cloud storage (such as EBS) are more than one.

磁盘负载

这个图片显示磁盘负载(或者请求队列大小),它和实际压到磁盘上线程的数量是大致匹配的。

This graph shows the disk load (or request queue size), which roughly matches the number of threads that are hitting disk as hard as possible.

饱和度(IO压力)

饱和度指标图上的IO负载几乎显示了相同的数值。唯一的不同是不像磁盘IO统计信息一样,它显示了整个系统的概要。

The IO load on the Saturation Metrics graph shows pretty much the same numbers. The only difference is that unlike Disk IO statistics, it shows the summary for the whole system.

Sysbench FileIO 4线程

现在让我们把IO提高到四个并发线程,再来看看磁盘的情况:

Now let’s increase IO to four concurrent threads and see how disk responds:

root@ts140i:/mnt/data# sysbench  --threads=4 --time=600 --max-requests=0  fileio --file-num=1 --file-total-size=100G --file-io-mode=sync --file-extra-flags=direct --file-test-mode=rndrd run

    File operations:  
       reads/s:                     26248.44  
       writes/s:                    0.00  
       fsyncs/s:                    0.00  
    Throughput:  
       read, MiB/s:                  410.13  
       written, MiB/s:               0.00  
    General statistics:  
       total time:                                       600.0002s  
       total number of events:                 15749205  
    Latency (ms):  
            min:                                  0.06  
            avg:                                  0.15  
            max:                                 8.73  
            95th percentile:               0.21  

我们看到请求数量线性增加,而请求延迟变化却很小:0.14ms vs 0.15ms。这表明设备内部有足够的执行单元并行处理负载,而且不存在其他瓶颈(如连接接口)。

We can see the number of requests scales almost linearly, while request latency changes very little: 0.14ms vs. 0.15ms. This shows the device has enough execution units internally to handle the load in parallel, and there are no other bottlenecks (such as the connection interface).

Iostat  
Device:        rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await   w_await  svctm  %util  
nvme0n1       0.00    0.00  28808.60    0.00   427668.00     0.00    29.69     4.05    0.14    0.14    0.00   0.03  99.92  

磁盘读写延迟

磁盘IO使用率

磁盘负载

饱和度(IO压力)

Sysbench FileIO 16线程

root@ts140i:/mnt/data# sysbench  --threads=16 --time=600 --max-requests=0  fileio --file-num=1 --file-total-size=100G --file-io-mode=sync --file-extra-flags=direct --file-test-mode=rndrd run

    File operations:  
       reads/s:                    76845.96  
       writes/s:                     0.00  
       fsyncs/s:                    0.00  
    Throughput:  
       read, MiB/s:                  1200.72  
       written, MiB/s:                0.00  
    General statistics:
       total time:                          600.0003s  
       total number of events:              46107727  
    Latency (ms):  
            min:                                  0.07  
            avg:                                  0.21  
            max:                                  9.72  
            95th percentile:                 0.36  
            

从4个线程到16个线程,我们再次看到吞吐量有较大提升而响应时间只轻微变大。如果你仔细观察结果,你将注意到一个更有趣的事情:平均响应时间从0.15ms增加到0.21ms(增加了40%),而95%的响应时间从0.21ms增加到0.36 ms(增加了71%)。我还进行了一个单独的测试来测量99%的响应时间,发现差异甚至更大:0.26ms vs 0.48ms(增加了84%)。

Going from four to 16 threads, we again see a good throughput increase with a mild response time increase. If you look at the results closely, you will notice one more interesting thing: the average response time has increased from 0.15ms to 0.21ms (which is a 40% increase), while the 95% response time has increased from 0.21ms to 0.36ms (which is 71%). I also ran a separate test measuring 99% response time, and the difference is even larger: 0.26ms vs. 0.48ms (or 84%).

这是一个重要的观察:一旦趋于饱和,方差可能会增加,而且其中一些请求会受到不同程度的影响(不仅仅只是我们看到的平均响应时间)。

This is an important observation to make: once saturation starts to happen, the variance is likely to increase and some of the requests will be disproportionately affected (beyond what the average response time shows).

Iostat  
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util  
nvme0n1           0.00     0.00 82862.20    0.00 1230567.20     0.00    29.70    16.33    0.20    0.20    0.00   0.01 100.00  

磁盘读写延迟

磁盘IO使用率

磁盘负载

饱和度(IO压力)

上面的图表结果符合预期:磁盘负载和IO负载从基本饱和增长到约为16,磁盘IO利用率则保持在100%。

The graphs show an expected figure: the disk load and IO load from saturation are up to about 16, and utilization remains at 100%.

需要注意的是图形中的抖动有所增加。IO利用率飙到100%以上,磁盘IO负载峰值上升到18,而请求队列却并不是很大。这就需要先从这些信息如何去收集的角度去考虑问题。尝试每秒对这些数据进行采样,但是在有实际负载的系统中,这个采集进程也需要费些时间:我们本来希望每秒采集一次数据,而实际上间隔则是1.05 ~ 0.95秒。当数学应用于数据采集时,我们所看到的图表中就可能会有本不应该出现的波峰、波谷(或刺点)。所以从大的角度来看,是可以忽略这些波动的。

One thing to notice is increased jitter in the graphs. IO utilization jumps to over 100% and disk IO load spikes to 18, when there should not be as many requests in flight. This comes from how this information is gathered. An attempt is made to sample this data every second, but with the loaded system it takes time for this process to work: sometimes when we try to get the data for a one-second interval but really get data for 1.05- or 0.95-second intervals. When the math is applied to the data, it creates the spikes and dips in the graph when there should be none. You can just ignore them if you’re looking at the big picture.

Sysbench FileIO 64 Threads

最后,让我们用sysbench跑64个并发线程的情况:

Finally, let’s run sysbench with 64 concurrent threads hitting the disk:

root@ts140i:/mnt/data# sysbench  --threads=64 --time=600 --max-requests=0  fileio --file-num=1 --file-total-size=100G --file-io-mode=sync --file-extra-flags=direct --file-test-mode=rndrd run

    File operations:  
       reads/s:                      127840.59  
       writes/s:                     0.00  
       fsyncs/s:                     0.00  
    
    Throughput:  
       read, MiB/s:                  1997.51  
       written, MiB/s:               0.00  
    
    General statistics:  
       total time:                          600.0014s  
       total number of events:              76704744  
    
    Latency (ms):  
            min:                                  0.08  
            avg:                                  0.50  
            max:                                  9.34  
            95th percentile:                      1.25  

我们可以看到平均响应耗时从0.21ms上升到0.50ms(两倍多),此外95%的响应时间从0.36ms跃升到1.25ms。实际上,我们可以看到IO负载开始饱和了,以前我们看到随着CPU饱和度的增加,并发线程数越多则响应耗时也越久,但这次随着并发线程数的增加,我们并未看到响应耗时随着线性增加。猜测是因为我们测试IO设备有不错的内部IO并行能力,所以响应请求效率很高(即便并发线程从16增加到64)。

We can see the average has risen from 0.21ms to 0.50 (more than two times), and 95% almost tripped from 0.36ms to 1.25ms. From a practical standpoint, we can see some saturation starting to happen, but we’re still not seeing a linear response time increase with increasing numbers of parallel operations as we have seen with CPU saturation. I guess this points to the fact that this IO device has a lot of parallel capacity inside and can process requests more effectively (even going from 16 to 64 concurrent threads).

在这个测试中,当我们将并发性从1个增加到64个时,我们看到响应耗时从0.14ms增加到0.5ms(约三倍)。此时95%的响应耗时从0.17ms增加到1.25ms(约七倍)。实际上,从这时开始看到IO设备趋于饱和了。

Over the series of tests, as we increased concurrency from one to 64, we saw response times increase from 0.14ms to 0.5ms (or approximately three times). The 95% response time at this time grew from 0.17ms to 1.25ms (or about seven times). For practical purposes, this is where we see the IO device saturation start to show.

Iostat  
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await   w_await  svctm  %util  
nvme0n1           0.00     0.00 138090.20    0.00   2049791.20     0.00    29.69    65.99    0.48    0.48    0.00   0.01 100.24  

我们将略过相关图表,因为它们基本雷同,只是在64并发线程下有更高的延迟。

We’ll skip the rest of the graphs as they basically look the same, just with higher latency and 64 requests in flight.

Sysbench FileIO 256线程

    root@ts140i:/mnt/data# sysbench  --threads=256 --time=600 --max-requests=0  fileio --file-num=1 --file-total-size=100G --file-io-mode=sync --file-extra-flags=direct --file-test-mode=rndrd run
     
    File operations:
       reads/s:                      131558.79
       writes/s:                     0.00
       fsyncs/s:                     0.00
     
    Throughput:
       read, MiB/s:                  2055.61
       written, MiB/s:               0.00
     
    General statistics:
       total time:                          600.0026s
       total number of events:              78935828
     
    Latency (ms):
            min:                                  0.10
            avg:                                  1.95
            max:                                 17.08
            95th percentile:                      3.89            
Iostat
Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nvme0n1           0.00     0.00 142227.60    0.00 2112719.20     0.00    29.71   268.30    1.89    1.89    0.00   0.0

最后,当并发256个线程时,从平均响应耗时的线性增长结果表明了设备已经过载,且IO请求开始排队。这里没有简单的办法判断它是由于IO总线饱和(此时IO读速率为2GB/sec),还是由于设备内部处理能力所限导致的。

With 256 threads, finally we’re seeing the linear growth of the average response time that indicates overload and queueing to process requests. There is no easy way to tell if it is due to the IO bus saturation (we’re reading 2GB/sec here) or if it is the internal device processing ability.

正如我们所见,并发数从16个到64个时,响应耗时小于线性增长,当从64到256则是线性增长,我们可以看到这个设备最佳的并发是介于16个和64之间。这时的吞吐量可以达到峰值,且没有大量的请求排队。

As we’ve seen a less than linear increase in response time going from 16 to 64 connections, and a linear increase going from 64 to 256, we can see the “optimal” concurrency for this device: somewhere between 16 and 64 connections. This allows for peak throughput without a lot of queuing.

在总结之前,我想对这次特别的测试先做一个重要的说明。这是个随机读的测试,对于很多数据库来说是个重要的工作负载模式,但可能不是最主要负载场景。可能是写为主,或者顺序IO读 (则会有相应不同的表现)。对于其它的工作负载模式,我希望本文可以帮助你在分析时提供些参考、思路。

Before we get to the summary, I want to make an important note about this particular test. The test is a random reads test, which is a very important pattern for many database workloads, but it might not be the dominant load for your environment. You might be write-bound as well, or have mainly sequential IO access patterns (which could behave differently). For those other workloads, I hope this gives you some ideas on how to also analyze them.

另一种思考饱和度的方法

Another Way to Think About Saturation

当我问起他Percona的同学对本文的反馈意见时,Yves Trudeau同学提供了另外一种思考饱和度的方法:与单线程模式相比,平均响应耗时的增长百分比作为饱和度的衡量指标 。例如:

When I asked the Percona staff for feedback on this blog post by, my colleague Yves Trudeau provided another way to think about saturation: measure saturation as percent increase in the average response time compared to the single user. Like this:

Threads Avg Response Time Saturation
1 0.14
4 0.15 1.07x or 7%
16 0.21 1.5x or 50%
64 0.50 3.6x or 260%
256 1.95 13.9x or 1290%

总结

Summary

  • 我们可以看到如何理解磁盘利用率和饱和度要比CPU复杂的多;
  • We can see how understanding disk utilization and saturation is much more complicated than for the CPU:
  • 利用率指标(由iostat和PMM输出的%Util)对理解真实的IO设备利用率是没啥作用。因为它只是衡量在运行中至少有一个请求时的耗时。如果是CPU的这个指标,它将相当于运行在至少一个核上(对于高度并发系统不是很有用);
  • The Utilization metric (as reported by iostat and by PMM) is not very helpful for showing true storage utilization, as it only measures the time when there is at least one request in flight. If you had the same metric for the CPU, it would correspond to something running on at least one of the cores (not very useful for highly parallel systems).
  • 不同于CPU,Linux工具不给我们提供关于存储设备的底层结构和在不饱和的情况下还可以处理多少并行负载的信息。更甚的是,存储设备可能有不同的底层源头会导致饱和。例如,它可能是网络连接,SATA总线,甚至是老内核的内核IO栈去处理高速存储设备(译者注:跟不上时代发展啦)。
  • Unlike a CPU, Linux tools do not provide us with information about the structure of the underlying storage and how much parallel load it should be able to handle without saturation. Even more so, storage might well have different low-level resources that cause saturation. For example, it could be the network connection, SATA BUS or even the kernel IO stack for older kernels and very fast storage.
  • 根据运行中的请求数量来测量饱和有助于判断是否饱和,但是由于我们不知道设备可以高效的并发处理多少请求,只看基础指标我们不能确定设备是否过载;
  • Saturation as measured by the number of requests in flight is helpful for guessing if there might be saturation, but since we do not know how many requests the device can efficiently process concurrently, just looking the raw metric doesn’t let us determine that the device is overloaded
  • 平均响应耗时对于观察饱和度是一个很好的指标,但是和响应耗时一样,您无法判断这个设备的响应耗时是高是低。您需要在上下文中查看它并将其与基线进行比较。当你查看平均响应耗时时,确保你分别查看读响应时间和写响应时间,并且记下平均请求队列大小以确保是相同角度的比较。
  • Avg Response Time is a great metric for looking at saturation, but as with the response time you can’t say what response time is good or bad for this device. You need to look at it in context and compare it to the baseline. When you’re looking at the Avg Response Time, make sure you’re looking at read request response time vs. write request response time separately, and keep the average request size in mind to ensure we are comparing apples to apples.

MySQL命令使用技巧|新手指引

MySQL客户端读取配置文件的顺序

MySQL这个命令可以读取配置文件如下:

#mysql --help |grep my.cnf

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

按顺序加载,后面的会把前面的覆盖掉。可以识别配置文件中:
[client] [mysql] ,如下:

[client]
port            = 3306
socket          = /tmp/mysql3306.sock

[mysql]
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
no-auto-rehash

基本使用篇

更改MySQL提示符

MySQL的传统提示符是:

mysql>

这里推荐通过修改配置文件中[MySQL]的:prompt,方便了解连接到哪个MySQL哪个DB上。如下:

[mysql]
prompt="\\u@\\h [\\d]>"

更多参考可查看:man MySQL

   +-------+--------------------------------------------------------------------+
       |Option | Description                                                        |
       +-------+--------------------------------------------------------------------+
       |\C     | The current connection identifier (MySQL 5.7.6 and up)             |
       +-------+--------------------------------------------------------------------+
       |\c     | A counter that increments for each statement you issue             |
       +-------+--------------------------------------------------------------------+
       |\D     | The full current date                                              |
       +-------+--------------------------------------------------------------------+
       |\d     | The default database                                               |
       +-------+--------------------------------------------------------------------+
       |\h     | The server host                                                    |
       +-------+--------------------------------------------------------------------+
       |\l     | The current delimiter                                              |
       +-------+--------------------------------------------------------------------+
       |\m     | Minutes of the current time                                        |
       +-------+--------------------------------------------------------------------+
       |\n     | A newline character                                                |
       +-------+--------------------------------------------------------------------+
       |\O     | The current month in three-letter format (Jan, Feb, ...)           |
       +-------+--------------------------------------------------------------------+
       |\o     | The current month in numeric format                                |
       +-------+--------------------------------------------------------------------+
       |\P     | am/pm                                                              |
       +-------+--------------------------------------------------------------------+
       |\p     | The current TCP/IP port or socket file                             |
       +-------+--------------------------------------------------------------------+
       |\R     | The current time, in 24-hour military time (0–23)                  |
       +-------+--------------------------------------------------------------------+
       |\r     | The current time, standard 12-hour time (1–12)                     |
       +-------+--------------------------------------------------------------------+
       |\S     | Semicolon                                                          |
       +-------+--------------------------------------------------------------------+
       |\s     | Seconds of the current time                                        |
       +-------+--------------------------------------------------------------------+
       |\t     | A tab character                                                    |
       +-------+--------------------------------------------------------------------+
       |\U     |                                                                    |
       |       |        Your full user_name@host_name account name                  |
       +-------+--------------------------------------------------------------------+
       |\u     | Your user name                                                     |
       +-------+--------------------------------------------------------------------+
       |\v     | The server version                                                 |
       +-------+--------------------------------------------------------------------+
       |\w     | The current day of the week in three-letter format (Mon, Tue, ...) |
       +-------+--------------------------------------------------------------------+
       |\Y     | The current year, four digits                                      |
       +-------+--------------------------------------------------------------------+
       |\y     | The current year, two digits                                       |
       +-------+--------------------------------------------------------------------+
       |\_     | A space                                                            |
       +-------+--------------------------------------------------------------------+
       |\      | A space (a space follows the backslash)                            |
       +-------+--------------------------------------------------------------------+
       |\'     | Single quote                                                       |
       +-------+--------------------------------------------------------------------+
       |\"     | Double quote                                                       |
       +-------+--------------------------------------------------------------------+
       |\\     | A literal \ backslash character                                    |
       +-------+--------------------------------------------------------------------+
       |\x     |                                                                    |
       |       |        x, for any “x” not listed above                             |
       +-------+--------------------------------------------------------------------+

动态的调整测试:

mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]>
#想回到解放前原始样子,直接输入:prompt 就可以。
(user@host) [database]> prompt
Returning to default PROMPT of mysql>
mysql>

使用login-path实现无密码登录

MySQL 5.6后推出了mysql_config_editor这个命令,本地加密存储用户的密码,通过指定登录文中的某一个MySQL而不用输入密码进行登录。

使用方法: mysql_config_editor set –login-path=登录实例的名字 –host=ip –user=用户名 –password 输入密码即可。具体如下:

#mysql_config_editor set --login-path=3306 --host=127.0.0.1 --port=3306 --user=wubx --password
Enter password:


#mysql_config_editor print --all
[3306]
user = wubx
password = *****
host = 127.0.0.1
port = 3306

#mysql --login-path=3306             
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

wubx@127.0.0.1:3306 [(none)]>

更多操作查看: mysql_config_editor –help

命令行执行SQL

通过命令行执行SQL方法有多种,以下三种较为常见:

  • 通过 -e 参数指定SQL
#mysql -S /tmp/mysql3306.sock -uroot -pzhishutang.com  -e "select version()"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| version()  |
+------------+
| 5.7.19-log |
+------------+
  • 通管道来执行
#echo "select version()"|mysql -S /tmp/mysql3306.sock -uroot -pwubxwubx  zst
mysql: [Warning] Using a password on the command line interface can be insecure.
version()
5.7.19-log
  • 通过login-path进行上面的方操作
#echo "select version()"|mysql --login-path=3306
version()
5.7.19-log
#mysql --login-path=3306 -e "select version()"
+------------+
| version()  |
+------------+
| 5.7.19-log |
+------------+
 echo "select version()"|mysql --login-path=3306

高级进阶篇

Pager使用

这算是MaSQL这个命令中的一个高级功能。

  • 分屏显示
wubx@127.0.0.1:3306 [(none)]>pager more
wubx@127.0.0.1:3306 [(none)]>pager less
  • 结果用md5sum比较
wubx@127.0.0.1:3306 [(none)]>pager md5sum
PAGER set to 'md5sum'
wubx@127.0.0.1:3306 [(none)]>select * from information_schema.tables;
56696bd844f2e5885ce9278d3beca750  -
  • 结果中搜索
wubx@127.0.0.1:3306 [(none)]>pager grep  Sleep|wc -l;
wubx@127.0.0.1:3306 [(none)]>show processlist;
  • 不显示查询出来结果
wubx@127.0.0.1:3306 [(none)]>pager cat >>/dev/null
wubx@127.0.0.1:3306 [(none)]>select * from information_schema.tables;
408 rows in set (0.02 sec)
  • 恢复 pager
wubx@127.0.0.1:3306 [(none)]>pager
或是
wubx@127.0.0.1:3306 [(none)]>nopager

记录MySQL输入的命令及结果

使用tee命令或是在配置文件配置,参考:

wubx@127.0.0.1:3306 [(none)]>tee /tmp/mysql.log
wubx@127.0.0.1:3306 [(none)]>select * from information_schema.tables;

输入点操作,观察一下/tmp/mysql.log吧

另外也可以通过在配置文件中,加这个配置 [mysql] tee=/tmp/mysql.log

再次登录即可 (前提这个配置文件是可以被mysql读到的)

MySQL调用系统命令

该功能只能Linux平台支持,利用system后面跟命令调用,参考:

wubx@127.0.0.1:3306 [(none)]>system top

wubx@127.0.0.1:3306 [(none)]>system ps axu |grep mysqld
mysql     5041  0.0 21.9 1077760 223936 pts/0  Sl   14:33   0:02 /usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3306/my3306.cnf
root      5368  0.0  0.1 106148  1052 pts/0    S+   15:41   0:00 sh -c  ps axu|grep mysqld
root      5370  0.0  0.0 103360   816 pts/0    S+   15:41   0:00 grep mysqld

感受一下吧,是不是很帅,更好的等你来发现。

执行外面的SQL

利用source命令执行外面的SQL,如:

echo "select version();" >/tmp/v.sql
wubx@127.0.0.1:3306 [(none)]>source /tmp/v.sql;
+------------+
| version()  |
+------------+
| 5.7.19-log |
+------------+
1 row in set (0.00 sec)

使用binary模型批量执行

如果你在恢复数据时遇到:

mysql -u root -p -h localhost -D zhishutang < dump.sql
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'XXXX'.

请更改为,在MySQL后面添加–binary-mode,如:

mysql --binary-mode  -u root -p -h localhost -D zhishutang < dump.sql

后语

MySQL这个客端使用较多,你还有什么好玩的方法,欢迎回复,我会整理补充到这篇文章中,让更多刚接触MySQL的人员能快速地上手。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

禁止修改varchar到int|[运维规范]

在MySQL更改数据类型前一定要特别小心,分析一下是不是可行,另外在更改前,需要先进行备份,备份,备份!!!

环境描述

表结构:

CREATE TABLE `t_mobile` (
 `mobile_no` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

写入数据:

root@zhishutang:mysql3306.sock [zst]>insert into t_mobile(mobile_no) values('13501245755'),('18800108001'),('13996000001');

确认数据无误:

root@zhishutang:mysql3306.sock [zst]>select * from t_mobile;
+-------------+
| mobile_no |
+-------------+
| 13501245755 |
| 18800108001 |
| 13996000001 |
+-------------+
3 rows in set (0.00 sec)

溢出

修改数类型:

root@zhishutang:mysql3306.sock [zst]>alter table t_mobile change mobile_no mobile_no int;
Query OK, 3 rows affected, 3 warnings (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 3

查看警告:

root@zhishutang:mysql3306.sock [zst]>show warnings;
+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------+
| Warning | 1264 | Out of range value for column 'mobile_no' at row 1 |
| Warning | 1264 | Out of range value for column 'mobile_no' at row 2 |
| Warning | 1264 | Out of range value for column 'mobile_no' at row 3 |
+---------+------+----------------------------------------------------+
3 rows in set (0.00 sec)

到这里实质就可以宣布,死定了。数据已溢出。

查看数据:

root@zhishutang:mysql3306.sock [zst]>select * from t_mobile;
+------------+
| mobile_no |
+------------+
| 2147483647 |
| 2147483647 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

这里真是的男人哭吧,哭吧, … 如果是线上环境,想死的心估计大家都有了,不是简单的哭了。如果没有备份,这么重要一例数据没了,有可能意为着项目也有可能受到严重的影响。

这时也不要心存幻想在改回去就好,来看一下操作,请死心!!!

root@zhishutang:mysql3306.sock [zst]>alter table t_mobile change mobile_no mobile_no varchar(11);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@zhishutang:mysql3306.sock [zst]>select * from t_mobile;
+------------+
| mobile_no |
+------------+
| 2147483647 |
| 2147483647 |
| 2147483647 |
+------------+
3 rows in set (0.00 sec)

结论

  • 生产环境更数据类型明确提出: 不允许varchar 改成int.
  • 更改数据前一定要做好备份,无论是update,delete,或是数据类型更改。

操作Tips: 如保备份一列数据?

select pk , 修改的列 from tb where 条件 ;

用命令行记录也可以,用outfile处理也行。

恢复可以用awk反向生成update语句:
cat bak.txt |awk ‘{print “update tb set 修改列名=“ 2,”where pk=1”;”}

大概这样生成。 在执行恢复即可。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

FAQ: show slave status从那里读的数据

这个问题最近有几个学生都问到我: SHOW SLAVE STATUS; 从哪里读数据。原因是发现: show slave status输出和relay_log_info_repository定义为table两个输出不一致。

另外有的人会告诉你把relay_log_info_repository存到表里, sync_relay_log_info就无效了。来吧看手册。

sync_relay_log_info 

对于事务引擎确时走的由Innodb本身保证,也可以说在全Innodb环境中show slave status输出和relay_log_info_repository表内的的内容是一致的。
但对于非事务引擎如果sync_relay_log_info设置成1,很有可能是sync_log_info没设置成1.

结论: show slave status读的数据确时从内存中读出来的。 如果基于Innodb表,两者是一致的。如果基于非事务表,默认配置很有可能是不一致的。如果需要一致可以通过改sync_relay_log_info=1。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

发表在 FAQ

PHP大会分享《MySQL 5.7优化不求人》

2017年6月10日,第三届PHP全球开发者大会在北京亚洲大酒店举办,现场云集了近千名的PHP开发者及PHP界的大牛,这是一场关于PHP语言的技术盛宴。

这是叶老师第二次在这个大会上做分享了,去年的分享主题是《程序猿都该知道的MySQL秘籍》,广受好评,在去年的分享嘉宾打赏排行榜上名列前茅。今年叶老师给带来的主题是《MySQL 5.7优化不求人》,一如既往的干货满满。


叶老师教你如何用好MySQL 5.7,优化不求人

MySQL 5.7 新增了众多特色功能,诸如 Optimizer 增强、支持多个触发器、P_S 增强、增加sys schema、在线修改 VARCHAR 长度、多源复制、并行复制等,对于提升MySQL性能都有很大帮助。

作为非专业 DBA,如何利用这些特性帮助我们做好 SQL 优化呢?且听老叶娓娓道来!

学习要点

1、MySQL 5.7关键新特性

a) MySQL 5.7性能提升;

b) MySQL 5.7新特性,Server层优化、InnoDB层优化、Optimizer优化、EXPLAIN CONNECTION、JSON、虚拟列、新增sys schema、performance schema增强等等;

2、MySQL 5.7下的SQL优化招数

a) 如何设置SQL超时,避免个别垃圾SQL破坏生产环境;

b) 如何查看某个线程当前执行的SQL效率;

c) 如何找到性能瓶颈SQL;

d) 如何找到消耗最多内存的SQL;

e) 如何找到哪些SQL被频繁执行;

f) 如何找到慢SQL中的TOP 10;

g) 哪个文件(数据表)产生了最多的IO,读多,还是写的多;

h) 如何找出哪些利用率很低的索引;

以及其他等等。

3、关注MySQL 8.0新特性

a) 重大变化,数据字典改用InnoDB,不再使用MyISAM引擎,顺便实现了DDL的原子性;

b) 重大变化,增加不可见索引(INVISIBLE INDEX),方便删除利用率很低的索引;

c) 重大变化,增加支持倒序索引;

d) 重大变化,重构Optimizer,HINT增强等;

以及其他等等。

精彩PPT

………………..

………………..

咦,这是怎么了

老叶的精彩分享PPT已上传到百度云盘,链接

: http://pan.baidu.com/s/1jIecgo2

或者你还可以扫码直达,欢迎转存及转发。


观看直播更过瘾

错过了PHP大会很懊恼?只看PPT不过瘾?

没关系,还有机会!

DBGeeK在线直播课|《MySQL 5.7优化不求人》将于2017年6月15日上线,叶老师主讲,可以先下载PPT,对应着听,更易掌握哦!

在线分享报名入口:

http://edu.csdn.net/huiyiCourse/detail/415

报名倒计时中,也可点击“阅读原文”直接报名!

我猜你一定达不到要求的《MySQL安全策略》

导读

除了MySQL自身的账号密码安全管理,系统层面、应用层面的安全策略你注意到了吗?

数据是企业核心资产,数据对企业而言是最重要的工作之一。稍有不慎,极有可能发生数据无意泄露,甚至被黑客恶意窃取的风险。每年业界都会传出几起大事件,某知名或不知名的公司被脱裤(拖库的谐音,意思是整个数据库被黑客盗取)之类的。

从数据安全上也可以分为外网安全及内部操作安全,下面分别讨论一下。

内部操作安全策略

1. 是否回收DBA全部权限

试想,如果DBA没权限了,日常DB运维的活,以及紧急故障处理,该怎么实施呢?因此,建议在没有成熟的自动化运维平台前,不应该粗暴的回收DBA的太多权限,否则可能会导致工作效率降低的,甚至DBA有一种不被信任的负面情绪。

2. MySQL层安全策略

  • 业务帐号最多只可以通过内网远程登录,而不能通过公网远程连接。
  • 增加运维平台账号,该账号允许从专用的管理平台服务器远程连接。当然了,要对管理平台部署所在服务器做好安全措施以及必要的安全审计策略。
  • 建议启用数据库审计功能。这需要使用MySQL企业版,或者Percona/MariaDB分支版本,MySQL社区版本不支持该功能。
  • 启用 safe-update 选项,避免没有 WHERE 条件的全表数据被修改;
  • 在应用中尽量不直接DELETE删除数据,而是设置一个标志位就好了。需要真正删除时,交由DBA先备份后再物理删除,避免误操作删除全部数据。
  • 还可以采用触发器来做一些辅助功能,比如防止黑客恶意篡改数据。

3. MySQL账号权限规则

  • 业务帐号,权限最小化,坚决不允许DROP、TRUNCATE权限。
  • 业务账号默认只授予普通的DML所需权限,也就是select、update、insert、delete、execute等几个权限,其余不给。
  • MySQL初始化后,先行删除无用账号,删除匿名test数据库
mysql> delete from mysql.user where user!='root' or host!='localhost'; flush privileges;

mysql> drop database test;
  • 创建备份专用账号,只有SELECT权限,且只允许本机可登入。
  • 设置MySQL账号的密码安全策略,包括长度、复杂性。

4. 关于数据备份

记住,做好数据全量备份是系统崩溃无法修复时的最后一概救命稻草
备份数据还可以用来做数据审计或是用于数据仓库的数据源拉取之用
一般来说,备份策略是这样的:每天一次全备,并且定期对binlog做增备,或者直接利用binlog server机制将binlog传输到其他远程主机上。有了全备+binlog,就可以按需恢复到任何时间点。
特别提醒:当采用xtrabackup的流式备份时,考虑采用加密传输,避免备份数据被恶意截取。

外网安全策略

事实上,操作系统安及应用安全要比数据库自身的安全策略更重要。同理,应用程序及其所在的服务器端的系统安全也很重要,很多数据安全事件,都是通过代码漏洞入侵到应用服务器,再去探测数据库,最后成功拖库。

1. 操作系统安全建议

  • 运行MySQL的Linux必须只运行在内部网络,不允许直接对公网暴露,实在有需要从公网连接的话,再通过跳板机做端口转发,并且如上面所述,要严格限制数据库账号权限级别。
  • 系统账号都改成基于ssh key认证,不允许远程密码登入,且ssh key的算法、长度有要求以确保相对安全。这样就没有密码丢失的风险,除非个人的私钥被盗。
  • 进一步的话,甚至可以对全部服务器启用PAM认证,做到账号的统一管理,也更方便、安全。
  • 关闭不必要的系统服务,只开必须的进程,例如 mysqld、sshd、networking、crond、syslogd 等服务,其它的都关闭。
  • 禁止root账号远程登录。
  • 禁止用root账号启动mysqld等普通业务服务进程。
  • sshd服务的端口号建议修改成10000以上。
  • 在不影响性能的前提下,尽可能启用对MySQL服务端口的防火墙策略(高并发时,采用iptables可能影响性能,建议改用ip route策略)。
  • GRUB必须设置密码,物理服务器的Idrac/imm/ilo等账号默认密码也要修改。
  • 每个需要登入系统的员工,都使用每个人私有帐号,而不是使用公共账号。
  • 应该启用系统层的操作审计,记录所有ssh日志,或利bash记录相应的操作命令并发送到远程服务器,然后进行相应的安全审计,及时发现不安全操作。
  • 正确设置MySQL及其他数据库服务相关目录权限,不要全是755,一般750就够了。
  • 可以考虑部署堡垒机,所有连接远程服务器都需要先通过堡垒机,堡垒机上就可以实现所有操作记录以及审计功能了。
  • 脚本加密对安全性提升其实没太大帮助。对有经验的黑客来说,只要有系统登入权限,就可以通过提权等方式轻松获得root。

2. 应用安全建议

  • 禁用web server的autoindex配置。
  • 从制度层面,杜绝员工将代码上传到外部github上,因为很可能存在内部IP、账号密码泄露的风险,真的要上传必须先经过安全审核。
  • 尽量不要在公网上使用开源的cms、blog、论坛等系统,除非做过代码安全审计,或者事先做好安全策略。这类系统一般都是黑客重点研究对象,很容易被搞;
  • 在web server层,可以用一些安全模块,比如nginx的WAF模块;
  • 在app server层,可以做好代码安全审计、安全扫描,防止XSS攻击、CSRF攻击、SQL注入、文件上传攻击、绕过cookie检测等安全漏洞;
  • 应用程序中涉及账号密码的地方例如JDBC连接串配置,尽量把明文密码采用加密方式存储,再利用内部私有的解密工具进行反解密后再使用。或者可以让应用程序先用中间账号连接proxy层,再由proxy连接MySQL,避免应用层直连MySQL;

最后我们想说,任何高明的安全策略,都不如内部员工的安全意识来的重要。以前发生过一起案例,公司内有位员工的PC不慎中毒,结果导致内网数据被盗。

安全无小事,每个人都应铭记于心。在数据安全面前,可以适当牺牲一些便利性,当然也不能太过,否则可能得不偿失。

 

From:

云DB

MariaDB 10.2 主要新特性介绍

MariaDB 10.2是当前MariaDB主要版本(2017),支持生命周期到2022年。现在新GA版本: MairaDB 10.2.6 GA。

下面看看MariaDB 10.2版本新的重要特性:

一、官方InnoDB成为默认引擎


MariaDB 10.1及之前版本均使用Percona XtraDB做为默认引擎,而MariaDB 10.2则使用了MySQL官方的InnoDB做为默认引擎,原来使用的XtraDB引擎的也可以直接升级,不受影响。新版本放弃XtraDB引擎的原因如下:

1. XtraDB引擎在MySQL 5.1, 5.5时非常优秀,但在最近几年官方几乎把所有优秀的特性都实现了。

2. 本次在MariaDB 10.2中合并MySQL InnoDB用了差不多半年多的时间,这是一个非常复杂的工作。MairaDB觉得,此版本再去合并XtraDB对用户带来的效益不大。

3. 再看XtraDB 5.7的改动,只是优化了密集IO写入处理,可以通过适当调整

innodb_thread_concurrency等其他选项来达到相同效果,但如果把XtraDB做一个整体的代码合并,MariaDB 10.2发布还要晚半年之久。

4. 以后不是说全面放弃XtraDB,只是不把XtraDB的全部代码合并,只把其优秀的特性做为Patch合并过来。

二、语法/常规特性


1. MyRocks  做为一个Alpha引擎合并进来。 虽然是一个Alpha版本,但对于想试一下MyRocks的同学,这是一个好事,可以直接用来体验一下。[不过还是暂不推荐在生产上使用]。

2. 窗口函数(windows function)引入。

3. show create user语句引入。

4. 新的create user语句,可以引入资源限制。

5. 新的alter user语句。

6. 递归公共表表达式(Recursive Common Table Expressions)。

7. 新的with语句, With也是一个公共表式中的一个种,允许子查询。

8. 支持check constranint。

9. 支持 default with表达式。

10. BLOB & TEXT列支持默认值。

11. Virtual例,去除了很多限制。

12. decimal小数点位从原来的30增加到38。

13. 对list分区添加一个catchall特性,有点类似于Range分区中的maxvalue,对于list分区放不下了,就放到这个catchall这个分区。

14. Oracle 格式的execute immediate语句实现。

15. prepare语句可以识别更多的表达式。

16. InnoDB表支持spatial indexes。

17. ed25519 authentication plugin。

18. 更好的InnoDB crash recovery进程汇报。

19. 改进InnoDB的开启关闭实现使它更建壮。

20. 支持windows, centos, rehl下面的 AWS Key Management plugin。

 三、不兼容的更改


1. TokuDB 不在默认包里发布,如果需要使用,请下载mariadb-plugin-tokudb。对于已使用MariaDB TokuDB升级的同学会稍微麻烦些。

【作者备注:有点遗憾,MariaDB把TokuDB踢出。现在Percona下的TokuDB开发也是几个华人在做,另外国内也有一个分支版本,基于Percona的TokuDB进行优化并且在xtrabackup上实现热备,项目地址:https://github.com/XeLabs/tokudb,使用TokuDB的也可以关注一下。需要交流的,也可以加QQ群:579036588 备注 TokuDB 入群。】

2. SQL_MODE has been changed; in particular, NOT NULL fields with no default will no longer fall back to a dummy value for inserts which do not specify a value for that field。

3. Replication from legacy MySQL servers may require setting binlog_checksum to NONE。

四、Replication/Binary log


1.  支持DML对实例\库\表进行flashback操作。

2.  新的参数: read_binlog_speed_limit 用于限制从库和主库日志相差太远,需要大量的从master上获取日志造成主库的网卡,IO性能受影响( Original code from Tencent Game DBA Team, developed by chouryzhou)。

3. 支持延迟复制Delayed Replication。

4. 支持压缩的binlog Event。 (Original code from Tencent Game DBA Team, developed by vinchen.)

5. 把默认的binlog格式改成mixed [建议实际使用,还是使用row]。

6. 参数:replication_annote_row_events默认改成:on。

7. 把参数slave_net_timeout进行减小,改到:60成为默认值。

8. 把参数server-id的值由原来0改成1。

更多信息


本次发布特性比较多,这里不全部介绍了。还感兴趣的,请参考:https://mariadb.com/kb/en/mariadb/what-is-mariadb-102/

点评


1. 整体上看 MariaDB 10.2的版本和官方发布的MySQL 8.0较为匹配。MySQL 8.0的语法特性基本在MariaDB 10.2中都有看到。

2. MariaDB 10.2里也可以看出MariaDB和Percona分手了。MariaDB这次又有新欢了。

3. 从发展速度上看,官方MySQL目前还是发展势头比较利害,不过可以肯定的是MariaDB,Percona,MyRocks,AliSQL这类产品剌激了官方的发展。

总的来说,每个版本都有相对独到的地方,都是不错的分支版本。 如果对于业务比较特殊,想进行更多版本选择上的讨论,也可以联系我(QQ:82565387)或是知乎(我的ID:wubx)上邀请我一块交流。

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

MySQL binlog后面的编号最大是多大?

    在上课中讲到MySQL的binlog是mysql-bin.000001,有细心的学习提到,是不是这个达到mysql-bin.999999后数据库的binlog就要重新开始了?
对于这个问题一时间也被问住了,只是隐约记得这个可以出现大于999999,这次也来细致的研究一下,顺便Mark一下。
MySQL在启动时会扫一下binlog文件,找到sequence后最大的一个,然后产生下个文件,根据这个原理,我们可以先测试一下当有了mysql-bin.999999 后,数据库会不会产生mysql-bin.000001或是mysql-bin.1000000。

测试一:

把mysqld关掉,到日志目录:

touch mysql-bin.999999
[root@zst1 17:26:28 /data/mysql/mysql3307/logs]
#touch mysql-bin.999999

[root@zst1 17:26:37 /data/mysql/mysql3307/logs]
#ls -lh
total 12K
-rw-r----- 1 mysql mysql 923 May 22 22:16 mysql-bin.000001
-rw-r----- 1 mysql mysql 660 May 22 22:28 mysql-bin.000002
-rw-r--r-- 1 root root 0 May 24 17:26 mysql-bin.999999
-rw-r----- 1 mysql mysql 88 May 22 22:19 mysql-bin.index

[root@zst1 17:26:40 /data/mysql/mysql3307/logs]
#chown mysql:mysql *
[root@zst1 17:26:48 /data/mysql/mysql3307/logs]
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
[root@zst1 17:27:17 /data/mysql/mysql3307/logs]
#ls -lh
total 76K
-rw-r----- 1 mysql mysql 923 May 22 22:16 mysql-bin.000001
-rw-r----- 1 mysql mysql 660 May 24 17:27 mysql-bin.000002
-rw-r----- 1 mysql mysql 194 May 24 17:27 mysql-bin.1000000
-rw-r--r-- 1 mysql mysql 0 May 24 17:26 mysql-bin.999999
-rw-r----- 1 mysql mysql 133 May 24 17:27 mysql-bin.index

启动mysqld
show master status 确认:

root@zhishutang:mysql3307.sock [(none)]>show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.1000000 | 194 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

可以看到,并没有挂掉,也没重新从mysql-bin.000001开始。

对于这个问题,我们在来挖一下,具体是什么?
教学使用的版本是mysql-5.7.18下载相应的源码看看 : sql/binlog.cc 实际找到:
从这里可以看到:
zhishutang.com
if (max_found == MAX_LOG_UNIQUE_FN_EXT)
当找到的数是定义的最大数时,就退出,再找一下MAX_LOG_UNIQUE_FN_EXT定义:

#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

root@zhishutang:mysql3307.sock [(none)]>select conv('7FFFFFFF',16,10);
+------------------------+
| conv('7FFFFFFF',16,10) |
+------------------------+
| 2147483647 |
+------------------------+
1 row in set (0.00 sec)

现在看到是最大值是: pow(2,31)-1
我们来测试几个场景:

测试二: 测试当达到mysql-bin.2147483647 再进行flush logs;MySQL的反应。

touch mysql-bin.2147483640

启动后给的警告:

2017-05-24T09:57:43.081308Z 0 [Warning] Next log extension: 2147483641. Remaining log filename extensions: 6. Please consider archiving some logs.
2017-05-24T09:57:43.081436Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170524 17:57:43
2017-05-24T09:57:43.090250Z 0 [Warning] Next log extension: 2147483641. Remaining log filename extensions: 6. Please consider archiving some logs.

root@localhost:mysql3307.sock [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483647 | 194 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

进行切换超限一下试试

root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3307.sock' (111)
ERROR:
Can't connect to the server

mysqld 直接退出…

报错日志:

2017-05-24T09:59:08.207040Z 3 [Warning] Next log extension: 2147483642. Remaining log filename extensions: 5. Please consider archiving some logs.
2017-05-24T09:59:09.970075Z 3 [Warning] Next log extension: 2147483643. Remaining log filename extensions: 4. Please consider archiving some logs.
2017-05-24T09:59:10.882140Z 3 [Warning] Next log extension: 2147483644. Remaining log filename extensions: 3. Please consider archiving some logs.
2017-05-24T09:59:12.050605Z 3 [Warning] Next log extension: 2147483645. Remaining log filename extensions: 2. Please consider archiving some logs.
2017-05-24T09:59:14.090312Z 3 [Warning] Next log extension: 2147483646. Remaining log filename extensions: 1. Please consider archiving some logs.
2017-05-24T09:59:26.483618Z 3 [Warning] Next log extension: 2147483647. Remaining log filename extensions: 0. Please consider archiving some logs.
2017-05-24T09:59:34.894426Z 3 [ERROR] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
2017-05-24T09:59:34.894441Z 3 [ERROR] Can't generate a unique log-filename /data/mysql/mysql3307/logs/mysql-bin.(1-999)

2017-05-24T09:59:34.894668Z 3 [ERROR] /usr/local/mysql/bin/mysqld: Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.

看样是想产生mysql-bin.(1-999) 这样的文件。 再进行进行下面的测试。

测试三: 测试Binlog达到最大值后,能不能重新开始。

确保mysql-bin.000001不存在到999都不存在

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483646 | 154 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql3307.sock [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483647 | 154 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
root@localhost:mysql3307.sock [(none)]>show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3307.sock' (111)
ERROR:
Can't connect to the server

一样退出。并没有进行日志的轮换使用。

结论:
1. mysql binlog的最大sequence是:pow(2,31)-1 = 2147483647
2. 当binlog接近这个值是小于1000开始向error log中写入警告
3. binlog的sequence达到最大值时,不管有没有mysql-bin.000001类似这样的文件,mysqld都是退出。
4. 在mysql产生binlog时会读取当前日件文目录下的log-bin的base name获取下一个日志文件的后面的Seq。 所以日志目录下文件太多,会影响MySQL的启动及日志切换。 这里也有一个大的隐患运行中给放一个较大的日志文件,在下次日志文件切换时有可能很快就接近于最大值,造成mysqld crash退出。
5. 一定要监控error log的输出。并足够重视。

 

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

基于MySQL 5.7多源复制+Keepalived搭建高可用

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

说明

本内容来源于知数堂 公开课 : 《MySQL 5.7 高可用新玩法》–吴炳锡 相关视频推荐:https://ke.qq.com/course/172600

本些分享视频地址: http://pan.baidu.com/s/1mia6MZu

基本环境准备

使用Centos 6.X 64位系统 MySQL 使用 MySQL-5.7.17-x86_64 版本,去官方下载mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz 版本

机器名 操作系统 IP
node1 centos-6.8 192.168.11.100
node2 centos-6.8 192.168.11.101
node3 centos-6.8 192.168.11.102

对应的VIP: 192.168.11.110

特别提示: 关闭iptables chkconfig –del iptables /etc/init.d/iptables stop

关闭:selinux setenforce 0 vim /etc/sysconfig/selinux SELINUX=permissive 更改为: SELINUX=disabled

下载MySQL :

 

mkdir /data/Soft
cd /data/Soft
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz

###MySQL部署约定 二进制文件放置: /opt/mysql/ 下面对应的目录 数据文件全部放置到 /data/mysql/ 下面对应的目录 原始二进制文件下载到/data/Soft/

MySQL基本安装

以下安装步骤需要在node1, node2, node3上分别执行。

  1. mkdir /opt/mysql
  2. cd /opt/mysql
  3. tar zxvf /data/Soft/mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz
  4. ln -s /opt/mysql/mysql-5.7.17-linux-glibc2.5-x86_64 /usr/local/mysql
  5. mkdir /data/mysql/mysql3309/{data,logs,tmp} -p
  6. groupadd mysql
  7. useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -M mysql
  8. chown -R mysql:mysql /data/mysql/
  9. chown -R mysql:mysql /usr/local/mysql
  10. cd /usr/local/mysql/
  11. ./bin/mysqld –defaults-file=/data/mysql/mysql3309/my3309.cnf –initialize
  12. cat /data/mysql/mysql3309/data/error.log |grep password
  13. /usr/local/mysql/bin/mysqld –defaults-file=/data/mysql/mysql3309/my3309.cnf &
  14. echo “export PATH=$PATH:/usr/local/mysql/bin” >>/etc/profile
  15. source /etc/profile
  16. mysql -S /tmp/mysql3309.sock -p #输才查到密码进入MySQL
  17. mysql>alter user user() identified by ‘wubxwubx’
  18. mysql>grant replication slave on . to ‘repl’@’%’ identified by ‘repl4slave’;
  19. mysql>grant all privilegs on . to ‘wubx’@’%’ identified by ‘wubxwubx’ # 一会测试使用的帐号
  20. mysql>reset master

每个节点按上面进行,遇到初始化和启动故障请认真阅读/data/mysql/mysql3309/data/error.log 信息。 my3309.cnf 可以从相应的目录下载或是加入QQ群: 579036588 下载,有问题入裙讨论。

搭建主从结构

 

node1上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.101′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_101_3309′;

mysql>change master to master_host=’192.168.11.102′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_102_3309’;

mysql>start slave; mysql>show slave status\G; #确认同步OK

node2上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.100′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_100_3309′;

mysql>change master to master_host=’192.168.11.102′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_102_3309’;

mysql>start slave; mysql>show slave status\G; #确认同步OK

node3上执行:

mysql -S /tmp/mysql3309.sock -pwubxwubx

mysql>change master to master_host=’192.168.11.100′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_100_3309′;

mysql>change master to master_host=’192.168.11.101′, master_port=3309, master_user=’repl’, master_password=’repl4slave’,master_auto_position=1 for channel ‘192_168_11_101_3309’;

mysql>start slave;

mysql>show slave status\G; #确认同步OK

安装keepalived

node1, node2, node3 上分别执行: 安装keepalived

yum install keepalivled

安装python依赖模块:

yum install MySQL-python.x86_64
yum install python2-filelock.noarch

keepalived配置

配置文件放置在: /etc/keepalived/keepalived.conf 内容如下:

vrrp_script vs_mysql_82 {
    script "/etc/keepalived/checkMySQL.py -h 127.0.0.1 -P 3309"
    interval 15
}
vrrp_instance VI_82 {
    state backup
    nopreempt
    interface eth1
    virtual_router_id 82
    priority 100
    advert_int 5
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        vs_mysql_82
    }
    notify /etc/keepalived/notify.py
    virtual_ipaddress {
        192.168.11.110
    }
}

##Keepalived启动 node1, node2, node3分别执行:

/etc/init.d/keepalived start

观查每个系统上的/var/log/messages 内容输出

##测试用例 在其它机器上使用:

mysql -h 192.168.11.110 -P 3309 -uwubx -pwubxwubx -e "select @@hostname"

自已触发一下切换看看能不能完成自动化的切换。

关注知数堂公从号 第一时间参加技术交流

知数堂公众号