深海游弋的鱼 – 默默的点滴

在Visual Studio Code中对Python进行单元测试(Pytest)

1.打开项目的时候，选择打开目录 "Open folder...", 这样可以在项目目录下生成 ".vscode" 目录，如下图：

继续阅读在Visual Studio Code中对Python进行单元测试(Pytest)

全功能Python测试框架：pytest

python通用测试框架大多数人用的是unittest+HTMLTestRunner，这段时间看到了pytest文档，发现这个框架和丰富的plugins很好用，所以来学习下pytest.

Java Calendar 获取上下午

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
Date date = sdf.parse(yourdate);      	
Calendar cal=Calendar.getInstance();
if(null!=date) {
	cal.setTime(date);
	if (Calendar.AM == cal.get(Calendar.AM_PM)) {
		return "上午";
	}
}

SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");

Date date = sdf.parse(yourdate);

Calendar cal=Calendar.getInstance();

if(null!=date) {

cal.setTime(date);

if (Calendar.AM == cal.get(Calendar.AM_PM)) {

return "上午";

}

参考链接

Java Calendar 获取上下午

python读取excel,获得下拉选中所有选项

利用python获取excel中所有下拉选（drop down）中的值，在百度搜了一下午，都没找到合适的方法，还是google靠谱给力，很快就找到解决办法了，分享一下，供有需要的同行参考。

继续阅读python读取excel,获得下拉选中所有选项

Python操作Excel

Python使用openpyxl-3.0.3复制Excel的一行内容并且插入到下一行，包含公式，下拉框，示例代码如下：

# coding=utf-8
# openpyxl-3.0.3
from openpyxl.reader.excel import load_workbook
from openpyxl.cell.cell import TYPE_FORMULA
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.cell_range import CellRange
import re
import copy

def insert_rows(self, row_idx, cnt, above=False, copy_style=True, copy_merged_columns=True, fill_formulae=True):
    """Inserts new (empty) rows into worksheet at specified row index.

    :param row_idx: Row index specifying where to insert new rows.
    :param cnt: Number of rows to insert.
    :param above: Set True to insert rows above specified row index.
    :param copy_style: Set True if new rows should copy style of immediately above row.
    :param fill_formulae: Set True if new rows should take on formula from immediately above row, filled with references new to rows.

    Usage:

    * insert_rows(2, 10, above=True, copy_style=False)

    """
    CELL_RE  = re.compile("(?P<col>\$?[A-Z]+)(?P<row>\$?\d+)")

    row_idx = row_idx - 1 if above else row_idx
    def replace(m):
        row = m.group('row')
        prefix = "$" if row.find("$") != -1 else ""
        row = int(row.replace("$", ""))
        row += cnt if row > row_idx else 0
        return m.group('col') + prefix + str(row)

    # First, we shift all cells down cnt rows...
    old_cells = set()
    old_fas = set()
    new_cells = dict()
    new_fas = dict()
    for c in self._cells.values():

        old_coor = c.coordinate

        # Shift all references to anything below row_idx
        if c.data_type == TYPE_FORMULA:
            c.value = CELL_RE.sub(
                replace,
                c.value
            )
            # Here, we need to properly update the formula references to reflect new row indices
            if old_coor in self.formula_attributes and 'ref' in self.formula_attributes[old_coor]:
                self.formula_attributes[old_coor]['ref'] = CELL_RE.sub(
                    replace,
                    self.formula_attributes[old_coor]['ref']
                )

        # Do the magic to set up our actual shift
        if c.row > row_idx:
            old_coor = c.coordinate 
            old_cells.add((c.row, c.column))
            c.row += cnt
            new_cells[(c.row, c.column)] = c
            if old_coor in self.formula_attributes:
                old_fas.add(old_coor)
                fa = copy.copy(self.formula_attributes[old_coor])
                new_fas[c.coordinate] = fa

    for coor in old_cells:
        del self._cells[coor]
    self._cells.update(new_cells)

    for fa in old_fas:
        del self.formula_attributes[fa]
    self.formula_attributes.update(new_fas)

    # Next, we need to shift all the Row Dimensions below our new rows down by cnt...
    # CHANGED: for row in range(len(self.row_dimensions) - 1 + cnt, row_idx + cnt, -1):
    for row in range(list(self.row_dimensions)[-1] + cnt, row_idx + cnt, -1):
        new_rd = copy.copy(self.row_dimensions[row - cnt])
        new_rd.index = row
        self.row_dimensions[row] = new_rd
        del self.row_dimensions[row - cnt]

    # Now, create our new rows, with all the pretty cells
    # CHANGED: row_idx += 1
    new_row_idx = row_idx + 1
    for row in range(new_row_idx, new_row_idx + cnt):
        # Create a Row Dimension for our new row
        new_rd = copy.copy(self.row_dimensions[row-1])
        new_rd.index = row
        self.row_dimensions[row] = new_rd

        # CHANGED: for col in range(1,self.max_column):
        for col in range(self.max_column):
            col = col + 1
            cell = self.cell(row=row, column=col)
            source = self.cell(row=row_idx, column=col)
            if copy_style:
                cell.number_format = copy.copy(source.number_format)
                cell.font = copy.copy(source.font)
                cell.alignment = copy.copy(source.alignment)
                cell.border = copy.copy(source.border)
                cell.fill = copy.copy(source.fill)
            if fill_formulae and TYPE_FORMULA == source.data_type :
                s_coor = source.coordinate
                if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]:
                    fa = copy.copy(self.formula_attributes[s_coor])
                    self.formula_attributes[cell.coordinate] = fa
                #print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
                cell.value = re.sub(
                    "(\$?[A-Z]{1,3}\$?)%d" % (row_idx),
                    lambda m: m.group(1) + str(row),
                    source.value
                ) 
                cell.data_type = TYPE_FORMULA

    # Check for Merged Cell Ranges that need to be expanded to contain new cells
    for cr in self.merged_cells.ranges:
        min_col, min_row, max_col, max_row = cr.bounds
        if min_row <= row_idx and max_row > row_idx:
            if max_row + cnt >= CellRange.max_row.max:
                cr.expand(down = CellRange.max_row.max - max_row)
            else:
                cr.expand(down = cnt)
        elif min_row > row_idx:
            if max_row + cnt >= CellRange.max_row.max:
                cr.expand(down = CellRange.max_row.max - max_row)
            else:
                cr.expand(down = cnt)
            cr.shrink(top = cnt)

    # Merge columns of the new rows in the same way row above does
    if copy_merged_columns:
        bounds = [] 
        for cr in self.merged_cells.ranges:
            if cr.max_row == cr.min_row == row_idx:
                bounds.append((cr.min_col, cr.max_col))
        for (min_col, max_col) in bounds:
            for row in range(new_row_idx, new_row_idx + cnt):
                newCellRange = get_column_letter(min_col) + str(row) + ":" + get_column_letter(max_col) + str(row)
                self.merge_cells(newCellRange)

    # update dataValidation
    validations = self.data_validations.dataValidation
    for val in validations: 
        for cr in val.cells:
            min_col, min_row, max_col, max_row = cr.bounds
            if min_row <= row_idx and max_row >= row_idx:
                if max_row + cnt >= CellRange.max_row.max:
                    cr.expand(down = CellRange.max_row.max - max_row)
                else:
                    cr.expand(down = cnt)
            elif min_row > row_idx:
                if max_row + cnt >= CellRange.max_row.max:
                    cr.expand(down = CellRange.max_row.max - max_row)
                else:
                    cr.expand(down = cnt)
                cr.shrink(top = cnt)

    # update conditional_formatting tow steps
    # first get all conditional_formatting need to update
    cond_fmts = self.conditional_formatting
    upd_cfs = []
    for cf in cond_fmts: 
        for cr in cf.cells:
            min_col, min_row, max_col, max_row = cr.bounds
            if min_row <= row_idx and max_row >= row_idx:
                upd_cfs.append(cf)
                break
            elif min_row > row_idx:
                upd_cfs.append(cf)
                break

    # second update conditional_formatting
    for cf in upd_cfs:
        rules = cond_fmts[cf]
        del cond_fmts[cf.cells]
        for cr in cf.cells:
            min_col, min_row, max_col, max_row = cr.bounds
            if min_row <= row_idx and max_row >= row_idx:
                if max_row + cnt >= CellRange.max_row.max:
                    cr.expand(down = CellRange.max_row.max - max_row)
                else:
                    cr.expand(down = cnt)
            elif min_row > row_idx:
                if max_row + cnt >= CellRange.max_row.max:
                    cr.expand(down = CellRange.max_row.max - max_row)
                else:
                    cr.expand(down = cnt)
                cr.shrink(top = cnt)
        for r in rules:
            cond_fmts[cf] = r


Worksheet.insert_rows = insert_rows

if __name__ == "__main__":
    # 注意，keep_vba=True打开的Excel文档，保存之后，MS Office 无法打开
    wb = load_workbook(filename='example.xlsx', read_only=False, keep_vba=False, data_only=False, keep_links=True)
    ws = wb.active
    ws.insert_rows(6, 4, above=True, copy_style=True)
    wb.save('new_document.xlsx')
    wb.close()

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

# coding=utf-8

# openpyxl-3.0.3

from openpyxl.reader.excel import load_workbook

from openpyxl.cell.cell import TYPE_FORMULA

from openpyxl.worksheet.worksheet import Worksheet

from openpyxl.utils import get_column_letter

from openpyxl.worksheet.cell_range import CellRange

import re

import copy

def insert_rows(self, row_idx, cnt, above=False, copy_style=True, copy_merged_columns=True, fill_formulae=True):

"""Inserts new (empty) rows into worksheet at specified row index.

:param row_idx: Row index specifying where to insert new rows.

:param cnt: Number of rows to insert.

:param above: Set True to insert rows above specified row index.

:param copy_style: Set True if new rows should copy style of immediately above row.

:param fill_formulae: Set True if new rows should take on formula from immediately above row, filled with references new to rows.

Usage:

* insert_rows(2, 10, above=True, copy_style=False)

"""

CELL_RE = re.compile("(?P<col>\$?[A-Z]+)(?P<row>\$?\d+)")

row_idx = row_idx - 1 if above else row_idx

def replace(m):

row = m.group('row')

prefix = "$" if row.find("$") != -1 else ""

row = int(row.replace("$", ""))

row += cnt if row > row_idx else 0

return m.group('col') + prefix + str(row)

# First, we shift all cells down cnt rows...

old_cells = set()

old_fas = set()

new_cells = dict()

new_fas = dict()

for c in self._cells.values():

old_coor = c.coordinate

# Shift all references to anything below row_idx

if c.data_type == TYPE_FORMULA:

c.value = CELL_RE.sub(

replace,

c.value

)

# Here, we need to properly update the formula references to reflect new row indices

if old_coor in self.formula_attributes and 'ref' in self.formula_attributes[old_coor]:

self.formula_attributes[old_coor]['ref'] = CELL_RE.sub(

replace,

self.formula_attributes[old_coor]['ref']

)

# Do the magic to set up our actual shift

if c.row > row_idx:

old_coor = c.coordinate

old_cells.add((c.row, c.column))

c.row += cnt

new_cells[(c.row, c.column)] = c

if old_coor in self.formula_attributes:

old_fas.add(old_coor)

fa = copy.copy(self.formula_attributes[old_coor])

new_fas[c.coordinate] = fa

for coor in old_cells:

del self._cells[coor]

self._cells.update(new_cells)

for fa in old_fas:

del self.formula_attributes[fa]

self.formula_attributes.update(new_fas)

# Next, we need to shift all the Row Dimensions below our new rows down by cnt...

# CHANGED: for row in range(len(self.row_dimensions) - 1 + cnt, row_idx + cnt, -1):

for row in range(list(self.row_dimensions)[-1] + cnt, row_idx + cnt, -1):

new_rd = copy.copy(self.row_dimensions[row - cnt])

new_rd.index = row

self.row_dimensions[row] = new_rd

del self.row_dimensions[row - cnt]

# Now, create our new rows, with all the pretty cells

# CHANGED: row_idx += 1

new_row_idx = row_idx + 1

for row in range(new_row_idx, new_row_idx + cnt):

# Create a Row Dimension for our new row

new_rd = copy.copy(self.row_dimensions[row-1])

new_rd.index = row

self.row_dimensions[row] = new_rd

# CHANGED: for col in range(1,self.max_column):

for col in range(self.max_column):

col = col + 1

cell = self.cell(row=row, column=col)

source = self.cell(row=row_idx, column=col)

if copy_style:

cell.number_format = copy.copy(source.number_format)

cell.font = copy.copy(source.font)

cell.alignment = copy.copy(source.alignment)

cell.border = copy.copy(source.border)

cell.fill = copy.copy(source.fill)

if fill_formulae and TYPE_FORMULA == source.data_type :

s_coor = source.coordinate

if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]:

fa = copy.copy(self.formula_attributes[s_coor])

self.formula_attributes[cell.coordinate] = fa

#print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))

cell.value = re.sub(

"(\$?[A-Z]{1,3}\$?)%d" % (row_idx),

lambda m: m.group(1) + str(row),

source.value

)

cell.data_type = TYPE_FORMULA

# Check for Merged Cell Ranges that need to be expanded to contain new cells

for cr in self.merged_cells.ranges:

min_col, min_row, max_col, max_row = cr.bounds

if min_row <= row_idx and max_row > row_idx:

if max_row + cnt >= CellRange.max_row.max:

cr.expand(down = CellRange.max_row.max - max_row)

else:

cr.expand(down = cnt)

elif min_row > row_idx:

if max_row + cnt >= CellRange.max_row.max:

cr.expand(down = CellRange.max_row.max - max_row)

else:

cr.expand(down = cnt)

cr.shrink(top = cnt)

# Merge columns of the new rows in the same way row above does

if copy_merged_columns:

bounds = []

for cr in self.merged_cells.ranges:

if cr.max_row == cr.min_row == row_idx:

bounds.append((cr.min_col, cr.max_col))

for (min_col, max_col) in bounds:

for row in range(new_row_idx, new_row_idx + cnt):

newCellRange = get_column_letter(min_col) + str(row) + ":" + get_column_letter(max_col) + str(row)

self.merge_cells(newCellRange)

# update dataValidation

validations = self.data_validations.dataValidation

for val in validations:

for cr in val.cells:

min_col, min_row, max_col, max_row = cr.bounds

if min_row <= row_idx and max_row >= row_idx:

if max_row + cnt >= CellRange.max_row.max:

cr.expand(down = CellRange.max_row.max - max_row)

else:

cr.expand(down = cnt)

elif min_row > row_idx:

if max_row + cnt >= CellRange.max_row.max:

cr.expand(down = CellRange.max_row.max - max_row)

else:

cr.expand(down = cnt)

cr.shrink(top = cnt)

# update conditional_formatting tow steps

# first get all conditional_formatting need to update

cond_fmts = self.conditional_formatting

upd_cfs = []

for cf in cond_fmts:

for cr in cf.cells:

min_col, min_row, max_col, max_row = cr.bounds

if min_row <= row_idx and max_row >= row_idx:

upd_cfs.append(cf)

break

elif min_row > row_idx:

upd_cfs.append(cf)

break

# second update conditional_formatting

for cf in upd_cfs:

rules = cond_fmts[cf]

del cond_fmts[cf.cells]

for cr in cf.cells:

min_col, min_row, max_col, max_row = cr.bounds

if min_row <= row_idx and max_row >= row_idx:

if max_row + cnt >= CellRange.max_row.max:

cr.expand(down = CellRange.max_row.max - max_row)

else:

cr.expand(down = cnt)

elif min_row > row_idx:

if max_row + cnt >= CellRange.max_row.max:

cr.expand(down = CellRange.max_row.max - max_row)

else:

cr.expand(down = cnt)

cr.shrink(top = cnt)

for r in rules:

cond_fmts[cf] = r

Worksheet.insert_rows = insert_rows

if __name__ == "__main__":

# 注意，keep_vba=True打开的Excel文档，保存之后，MS Office 无法打开

wb = load_workbook(filename='example.xlsx', read_only=False, keep_vba=False, data_only=False, keep_links=True)

ws = wb.active

ws.insert_rows(6, 4, above=True, copy_style=True)

wb.save('new_document.xlsx')

wb.close()

参考链接

Download KMSpico 11 Final Windows 10 Activator [2020]

KMSpico激活工具是激活Office 2016/2013/2010等常用的工具，一段时期之内发生被植入挖矿病毒的情况。

目前比较安全的下载地址是 Download KMSpico 11 Final Windows 10 Activator [2020]，压缩包有密码，密码 123456。

也可本站下载 kmspico

参考链接

Visual Studio Code调试Python无法Step Into导入(imported)模块的代码

Visual Studio Code调试Python无法Step Into导入(imported)外部模块的代码，导致在跟踪调用流程的时候非常不方便。

这个现象是Visual Studio Code为了防止调试的时候过多的关注不相干代码，因此默认禁止跟踪外部模块代码，只跟踪我们自己工程内部的代码。

禁止这部分功能，只需要在工程的配置文件launch.json中增加"justMyCode": false即可。

如下：

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [

        {
            "name": "Python: 当前文件",
            "type": "python",
            "request": "launch",
            "program": "${file}",
            "console": "integratedTerminal",
            "justMyCode": false
        }
    ]
}

{

// Use IntelliSense to learn about possible attributes.

// Hover to view descriptions of existing attributes.

// For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387

"version": "0.2.0",

"configurations": [

{

"name": "Python: 当前文件",

"type": "python",

"request": "launch",

"program": "${file}",

"console": "integratedTerminal",

"justMyCode": false

}

]

}

参考链接

MySQL延时复制(Delayed Replication)

延迟复制简介

即使通常MySQL复制很快，但MySQL缺省的复制存在延迟，并且用户无法缩短延迟时间。另一方面，有时却需要特意增加复制的延迟。设想这样一种场景，用户在主库上误删除了一个表，并且该操作很快被复制到从库。当用户发现这个错误时，从库早就完成了该事件重放。此时主库、从库都没有那个被误删的表了，如何恢复？如果有备份，可以幸运地从备份恢复，丢失的数据量取决于备份的新旧和从备份时间点到表被删除时间点之间该表上数据的变化量。如果没有备份呢？这种情况下，延迟复制或许可以帮上忙，作为一种恢复数据的备选方案。如果在发现问题时，从库还没有来得及重放相应的中继日志，那么就有机会在从库获得该表，继而进行恢复。这里忽略一些其它数据恢复方案，例如已经存在类似Oracle闪回技术（Flashback）在MySQL上的实现，实现方式为解析相应的二进制日志事件，生成反向的SQL语句。这些程序多为个人作品，并没有被加入MySQL发行版本中，因此在易用性、适用性、可靠性等方面还不能与原生的功能相提并论。

MySQL支持延迟复制，以便从库故意执行比主库晚至少在指定时间间隔的事务。在MySQL 8.0中，延迟复制的方法取决于两个时间戳：immediate_commit_timestamp和original_commit_timestamp。如果复制拓扑中的所有服务器都运行MySQL 8.0.1或更高版本，则使用这些时间戳测量延迟复制。如果从库未使用这些时间戳，则执行MySQL 5.7的延迟复制。

复制延迟默认为0秒。使用CHANGE MASTER TO MASTER_DELAY = N语句将延迟设置为N秒。从主库接收的事务比主库上的提交至少晚N秒才在从库上执行。每个事务发生延迟（不是以前MySQL版本中的事件），实际延迟仅强制在gtid_log_event或anonymous_gtid_log_event事件上。二进制日志中的每个GTID事务始终都以Gtid_log_event开头，匿名事务没有分配GTID，MySQL确保日志中的每个匿名事务都以Anonymous_gtid_log_event开头。对于事务中的其它事件，不会对它们施加任何等待时间，而是立即执行。注意，START SLAVE和STOP SLAVE立即生效并忽略任何延迟，RESET SLAVE将延迟重置为0。

例如，下面将实验环境中一主两从半同步复制中的一个从库设置为延迟60秒复制：

mysql> change master to master_delay = 60;
ERROR 3085 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD FOR CHANNEL '' first.
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
 
mysql> change master to master_delay = 60;
Query OK, 0 rows affected (0.01 sec)
 
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay = 60;

ERROR 3085 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD FOR CHANNEL '' first.

mysql> stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_delay = 60;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

联机设置延迟复制时，需要先停止sql_thread线程。现在主库执行一个事务，观察从库的变化：

-- 主
mysql> create table test.t3(a int);
Query OK, 0 rows affected (0.01 sec)
 
mysql> 
 
-- 从
mysql> desc test.t3;
ERROR 1146 (42S02): Table 'test.t3' doesn't exist
mysql> desc test.t3;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

-- 主

mysql> create table test.t3(a int);

Query OK, 0 rows affected (0.01 sec)

mysql>

-- 从

mysql> desc test.t3;

ERROR 1146 (42S02): Table 'test.t3' doesn't exist

mysql> desc test.t3;

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

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

| a | int(11) | YES | | NULL | |

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

1 row in set (0.00 sec)

主库上建立了一个表test.t3，DDL语句自成一个事务。60秒后，从库上才出现该表。

从库上performance_schema模式下的replication_applier_configuration.desired_delay表列显示使用master_delay选项配置的延迟，replication_applier_status.remaining_delay表列显示剩余的延迟秒数。

-- 从
mysql> select desired_delay from performance_schema.replication_applier_configuration;
+---------------+
| desired_delay |
+---------------+
|            60 |
+---------------+
1 row in set (0.00 sec)
mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)
mysql>
-- 主
mysql> drop table test.t3;
Query OK, 0 rows affected (0.02 sec)
mysql> 
-- 从
mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              54 |
+-----------------+
1 row in set (0.00 sec)
mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              23 |
+-----------------+
1 row in set (0.00 sec)
mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|              16 |
+-----------------+
1 row in set (0.00 sec)
mysql> select remaining_delay from performance_schema.replication_applier_status;
+-----------------+
| remaining_delay |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)
mysql>

-- 从

mysql> select desired_delay from performance_schema.replication_applier_configuration;

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

| desired_delay |

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

| 60 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

-- 主

mysql> drop table test.t3;

Query OK, 0 rows affected (0.02 sec)

mysql>

-- 从

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| 54 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| 23 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| 16 |

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

1 row in set (0.00 sec)

mysql> select remaining_delay from performance_schema.replication_applier_status;

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

| remaining_delay |

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

| NULL |

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

1 row in set (0.00 sec)

mysql>

延迟复制可用于多种目的：

防止用户在主库上出错。延迟复制时，可以将延迟的从库回滚到错误之前的时间。

测试滞后时系统的行为方式。例如，在应用程序中，延迟可能是由从库设备上的重负载引起的。但是，生成此负载级别可能很困难。延迟复制可以模拟滞后而无需模拟负载。它还可用于调试与从库滞后相关的条件。

检查数据库过去的快照，而不必重新加载备份。例如，通过配置延迟为一周的从库，如果需要看一下最近几天开发前的数据库样子，可以检查延迟的从库。

延迟复制的简单理解就是，客户端始终保持每条数据的同步时间不低于指定的间隔，同步是持续的。跟计划任务不同，想达到跟计划任务一样，每隔一段时间，集中执行一次是做不到的，比如一小时执行同步一次，这个是做不到的。想实现定时同步，还是写计划任务吧。

延迟复制时间戳

MySQL 8.0提供了一种新方法，用于测量复制拓扑中的延迟，或称复制滞后。该方法取决于与写入二进制日志的每个事务（不是每个事件）的GTID相关联的以下时间戳：

original_commit_timestamp：将事务写入（提交）到主库二进制日志之后的自1970年1月1日00:00:00 UTC以来的微秒数。

immediate_commit_timestamp：将事务写入（提交）到从库的二进制日志之后的自1970年1月1日00:00:00 UTC以来的微秒数。

mysqlbinlog的输出以两种格式显示这些时间戳，从epoch开始的微秒和TIMESTAMP格式，后者基于用户定义的时区以获得更好的可读性。例如：

#190516 15:12:18 server id 1125  end_log_pos 239 CRC32 0xc1ebcb7c       Anonymous_GTID  last_committed=0        sequence_number=1       rbr_only=no     original_committed_timestamp=1557990738835397   immediate_commit_timestamp=1557990738838735     transaction_length=192
# original_commit_timestamp=1557990738835397 (2019-05-16 15:12:18.835397 CST)
# immediate_commit_timestamp=1557990738838735 (2019-05-16 15:12:18.838735 CST)
/*!80001 SET @@session.original_commit_timestamp=1557990738835397*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 239

#190516 15:12:18 server id 1125 end_log_pos 239 CRC32 0xc1ebcb7c Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1557990738835397 immediate_commit_timestamp=1557990738838735 transaction_length=192

# original_commit_timestamp=1557990738835397 (2019-05-16 15:12:18.835397 CST)

# immediate_commit_timestamp=1557990738838735 (2019-05-16 15:12:18.838735 CST)

/*!80001 SET @@session.original_commit_timestamp=1557990738835397*//*!*/;

/*!80014 SET @@session.original_server_version=80016*//*!*/;

/*!80014 SET @@session.immediate_server_version=80016*//*!*/;

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 239

通常，original_commit_timestamp在应用事务的所有副本上始终相同。在主从复制中，主库二进制日志中事务的original_commit_timestamp始终与其immediate_commit_timestamp相同。在从库的中继日志中，事务的original_commit_timestamp和immediate_commit_timestamp与主库的二进制日志中的相同，而在其自己的二进制日志中，事务的immediate_commit_timestamp对应于从库提交事务的时间。

在组复制设置中，当原始主服务器是组的成员时，将在事务准备好提交时生成original_commit_timestamp。再具体说，当事务在原始主服务器上完成执行并且其写入集准备好发送给该组的所有成员以进行认证时，生成original_commit_timestamp。因此，相同的original_commit_timestamp被复制到所有服务器应用事务，并且每个服务器使用immediate_commit_timestamp在其自己的二进制日志中存储本地提交时间。

组复制中独有的视图更改事件是一种特殊情况。包含该事件的事务由每个服务器生成，但共享相同的GTID。因此，这种事务不是先在主服务器中执行，然后复制到该组其它成员，而是该组的所有成员都执行并应用相同的事务。由于没有原始主服务器，因此这些事务的original_commit_timestamp设置为零。

监控延迟复制

在MySQL 8之前的老版本中，监控复制的延迟（滞后）最常用的方法之一是依赖于show slave status输出中的seconds_behind_master字段。但是，当使用比传统主从复制更复杂的复制拓扑，例如组复制时，此度量标准不再适用。MySQL 8中添加的immediate_commit_timestamp和original_commit_timestamp可提供有关复制延迟的更精细的信息。监控支持这些时间戳的复制延迟的推荐方法是使用以下performance_schema模式中的表。

replication_connection_status：与主服务器连接的当前状态，提供有关连接线程排队到中继日志中的最后和当前事务的信息。

replication_applier_status_by_coordinator：协调器线程的当前状态，仅在使用多线程复制时显示该信息，提供有关协调器线程缓冲到工作队列的最后一个事务的信息，以及当前正在缓冲的事务。

replication_applier_status_by_worker：应用从主服务器接收事务的线程的当前状态，提供有关应用程序线程或使用多线程复制时每个工作线程应用的事务信息。

使用这些表，可以监控相应线程处理的最后一个事务以及该线程当前正在处理的事务的信息，包括：

事务的GTID。

从库中继日志中检索的事务的original_commit_timestamp和immediate_commit_timestamp。

线程开始处理事务的时间。

对于上次处理的事务，线程完成处理它的时间。
除Performance Schema表之外，show slave status的输出还有三个字段与延迟复制有关：

SQL_Delay：非负整数，表示使用CHANGE MASTER TO MASTER_DELAY = N配置的复制延迟，以秒为单位。与performance_schema.replication_applier_configuration.desired_delay值相同。

SQL_Remaining_Delay：当Slave_SQL_Running_State等待主执行事件后的MASTER_DELAY秒时，该字段包含一个整数，表示延迟剩余的秒数。在它他时候，此字段为NULL。与performance_schema.replication_applier_status.remaining_delay值相同。

Slave_SQL_Running_State：一个字符串，指示SQL线程的状态（类似于Slave_IO_State）。该值与SHOW PROCESSLIST显示的SQL线程的State值相同。
当从库的SQL线程在执行事件之前等待延迟时，SHOW PROCESSLIST将其状态值显示为：Waiting until MASTER_DELAY seconds after master executed event。

参考链接

群晖(Synology) DS718+希捷酷狼(Seagate IronWolf)12TB空闲发出持续噪声

群晖(Synology) DS718+ 使用希捷酷狼(Seagate IronWolf) 双12TB组成RAID1阵列，结果每隔几秒就会发出一阵子咳咳咳的噪声（有人说是炒豆子声音），尤其是夜深人静的时候，简直无法容忍。

网上查询了很久，发现这个问题非常普遍。

【硬盘噪音的由来】 
噪音虽然不是直接衡量硬盘性能的标准，但是经常听到一阵阵硬盘乱响毕竟不是一件让人舒心的事。纵观硬盘的发展历史，可以发现硬盘的噪音实际上和硬盘的转速是成正比的：转速每提高一个档次，噪音等级都会相应提高。 
尽管现在7200转的硬盘已经改进不少，成为了市场的主流，但噪音仍然是要高于5400转的硬盘。通常硬盘内部有两个电机，一个是驱动硬盘旋转的主轴电机，该电机过去是主要的噪音和热量源，但是现在有很多厂家开始使用液态轴承电机，它使用的是黏膜液油轴承，以油膜代替滚珠，可有效的降低因金属磨擦而产生的噪声和发热问题。 
同时液油轴承也可有效的吸收震动，使硬盘的抗震能力得到提高，由此硬盘的寿命与可靠性也可以得到提高。
但是另外还有一寻道电机，由于技术和成本上的原因，该电机还没有采用液态轴承电机，我们平时听到硬盘发出的“答，答”声，就是由它发出的。

【硬盘噪音的由来】

噪音虽然不是直接衡量硬盘性能的标准，但是经常听到一阵阵硬盘乱响毕竟不是一件让人舒心的事。纵观硬盘的发展历史，可以发现硬盘的噪音实际上和硬盘的转速是成正比的：转速每提高一个档次，噪音等级都会相应提高。

尽管现在7200转的硬盘已经改进不少，成为了市场的主流，但噪音仍然是要高于5400转的硬盘。通常硬盘内部有两个电机，一个是驱动硬盘旋转的主轴电机，该电机过去是主要的噪音和热量源，但是现在有很多厂家开始使用液态轴承电机，它使用的是黏膜液油轴承，以油膜代替滚珠，可有效的降低因金属磨擦而产生的噪声和发热问题。

同时液油轴承也可有效的吸收震动，使硬盘的抗震能力得到提高，由此硬盘的寿命与可靠性也可以得到提高。

但是另外还有一寻道电机，由于技术和成本上的原因，该电机还没有采用液态轴承电机，我们平时听到硬盘发出的“答，答”声，就是由它发出的。

如果是DSM 6.2之前的系统，存在一个磁头反复复位唤醒的BUG，导致磁盘寿命明显缩短。这个原因是由于磁头的复位次数是有上限的，超过上限就容易故障。这个问题的表现就是SMART信息中的Load_Cycle_Count(Load/Unload_Count)异常增高。噪声的一个原因也就是磁头的复位声。

通常，硬盘制造商规定的Load_Cycle_Count(Load/Unload_Count)数目上限是600,000次，要是超过300,000次就会影响到正常的读写，再多的话就差不多要报废了。

参考群晖论坛的讨论 Seagate NAS drives constant clicking noises when idle

但是我的系统是DSM 6.2.2-24922 Update 4，这个问题已经被修复了，目前观察了一下，这个数字已经不再增长了。

这个噪声其实是读写频率低的情况下，磁头默认会回到一个安全区，在这个安全区，即使由于硬盘的意外震动，磁头发生磕碰，也不会磕碰到磁盘区域，提升震动安全。但是带来的问题是磁头进入或者离开安全区的时候，会发出一阵噪声。这个声音非常吵。这也就是为什么官方网站在回应对于噪声很大的磁盘驱动器我应该怎么办？说这个问题没办法解决的原因。

上述故障的典型表现是 当磁盘负载很高的时候，噪声低很多，低负载的时候，每隔几秒就会发出非常高的噪声。

当年，迈拓的硬盘存在一个名为AMM的技术，可以通过一些指令如ATA指令或专用软件对它进行操作，使得寻道时的噪音跟空闲时差不多。但是，AMM降低了硬盘在速度变化瞬间的启动速度，在突发数据传输率上会对硬盘造成一定影响，使得硬盘的性能出现短暂下降。不过，从平均工作性能来说，由于持续传输率并不受启动速度的唯一影响，因此在整体性能上来说问题不大。迈拓的Acoustic Management(声音管理)设置工具。

可惜，希捷酷狼(Seagate IronWolf)12TB已经不支持这个功能了。

亚马逊有个回答

Short answer: No Seagate Ironwolf Pro HDD's do not support the obsolete AAM protocols. They have rotational vibration sensors and dampers which make the drive quiet.

Long answer: Automatic Acoustic Management (AAM) was a feature popularized by WD in which the spin up rate of the disks in a HDD was slowed down to reduce vibration and noise. This feature was declared obsolete in the 2010 ATA protocols because it made the drive spin up slowly and increased access times. Seagate Ironwolf Pro NAS drives are made to run 24/7/365 @ 7200 rpm. These drives spin up as quickly as any HDD that I ever tried including 15,000 rpm drives. They are not "green drives" which spin down and back up to conserve energy. The drives have rotational vibration sensors and self correct for drive eccentricities. The result is that the Ironwolf and Ironwolf Pro HDD's are very quiet. I have WD Reds, HGST's, and Toshiba drives along with Seagate Ironwolf and Ironwolf Pro 4TB, 6TB, 8TB, 10TB and 12 TB drives. The Seagate Ironwolf and Ironwolf Pro drives are the quietest drives of all the drives I own.

Short answer: No Seagate Ironwolf Pro HDD's do not support the obsolete AAM protocols. They have rotational vibration sensors and dampers which make the drive quiet.

Long answer: Automatic Acoustic Management (AAM) was a feature popularized by WD in which the spin up rate of the disks in a HDD was slowed down to reduce vibration and noise. This feature was declared obsolete in the 2010 ATA protocols because it made the drive spin up slowly and increased access times. Seagate Ironwolf Pro NAS drives are made to run 24/7/365 @ 7200 rpm. These drives spin up as quickly as any HDD that I ever tried including 15,000 rpm drives. They are not "green drives" which spin down and back up to conserve energy. The drives have rotational vibration sensors and self correct for drive eccentricities. The result is that the Ironwolf and Ironwolf Pro HDD's are very quiet. I have WD Reds, HGST's, and Toshiba drives along with Seagate Ironwolf and Ironwolf Pro 4TB, 6TB, 8TB, 10TB and 12 TB drives. The Seagate Ironwolf and Ironwolf Pro drives are the quietest drives of all the drives I own.

虽然发出评论的人否认自己是托，但是从我一张12TB的WD红盘跟Ironwolf的对比来看，WD红盘安静的根本听不到噪声，Ironwolf吵得很。

虽然不支持AMM，但是官方提供的控制工具SeaChestUtilities （本站链接 SeaChestUtilities）中的SeaChest_Configure工具（这个工具解压缩到群晖chmod +x后就可以执行）提供了一个名为 --lowCurrentSpinup 的参数，这个参数的说明如下：

Low Current SpinUP  
     
     After System Up,
     Each time they go StandBy, it stop spinning the plates, and park its heads..

     But the problem is,
     Each time you request access, they will need to start spinning the plates, and moving heads to read/write..
 
     This start process, is painful, for power Consumption, and creates more vibration( it pushs several Amps in spikes for that.. )..

     So to minimise it,
     Seagate Disks have a Feature, to start spinning in a progressive way, instead of abrupt way.

Low Current SpinUP

After System Up,

Each time they go StandBy, it stop spinning the plates, and park its heads..

But the problem is,

Each time you request access, they will need to start spinning the plates, and moving heads to read/write..

This start process, is painful, for power Consumption, and creates more vibration( it pushs several Amps in spikes for that.. )..

So to minimise it,

Seagate Disks have a Feature, to start spinning in a progressive way, instead of abrupt way.

看上去这个参数可以实现降噪功能，实际上没什么效果！执行命令如下：

$ sudo ./SeaChest_Configure_1170_11923_64 -d /dev/sg0 --lowCurrentSpinup low
==========================================================================================
 SeaChest_Configure - Seagate drive utilities - NVMe Enabled
 Copyright (c) 2014-2019 Seagate Technology LLC and/or its Affiliates, All Rights Reserved
 SeaChest_Configure Version: 1.17.0-1_19_23 X86_64
 Build Date: Jun 10 2019
 Today: Sun May 17 22:26:50 2020
==========================================================================================

/dev/sg0 - ST12000VN0008-2JH101 - ZHZ4XKKY - ATA
Set Low Current Spinup
Successfully Enabled Ultra Low Current Spinup!
A power cycle is required to complete this change.

$ sudo ./SeaChest_Configure_1170_11923_64 -d /dev/sg0 --lowCurrentSpinup low

==========================================================================================

SeaChest_Configure - Seagate drive utilities - NVMe Enabled

SeaChest_Configure Version: 1.17.0-1_19_23 X86_64

Build Date: Jun 10 2019

Today: Sun May 17 22:26:50 2020

==========================================================================================

/dev/sg0 - ST12000VN0008-2JH101 - ZHZ4XKKY - ATA

Set Low Current Spinup

Successfully Enabled Ultra Low Current Spinup!

A power cycle is required to complete this change.

low ,ultra两个参数都试过，没明显感觉到不同。

接下来，想在电源管理上看一下是不是能解决问题，比如降低转速等，结果依旧没用。老的Advanced Power Management (APM)被新的Extended Power Controls (EPC)功能取代了，具体解释如下：

There are, roughly, three general frameworks for disk drive power management.
The earlier specification is part of Advanced Power Management (APM) which
provided controls over many devices in a system, including disk drives.  APM
has since been replaced by a new framework called Extended Power Controls
(EPC).  Seagate's implementation of EPC began to phase in starting in 2010 and
is named by the marketing term "PowerChoice".  Seagate PowerChoice
technology'specifically developed for enterprise environments'offers more
energy efficiency and control over the amount of power hard drives consume.

There are, roughly, three general frameworks for disk drive power management.

The earlier specification is part of Advanced Power Management (APM) which

provided controls over many devices in a system, including disk drives. APM

has since been replaced by a new framework called Extended Power Controls

(EPC). Seagate's implementation of EPC began to phase in starting in 2010 and

is named by the marketing term "PowerChoice". Seagate PowerChoice

technology'specifically developed for enterprise environments'offers more

energy efficiency and control over the amount of power hard drives consume.

尝试用工具中的电源管理模块，结果依旧根本设置不了,如下：

$ sudo ./SeaChest_PowerControl_1100__11923_64  -d /dev/sg0 --EPCfeature disable
==========================================================================================
 SeaChest_PowerControl - Seagate drive utilities - NVMe Enabled
 Copyright (c) 2014-2019 Seagate Technology LLC and/or its Affiliates, All Rights Reserved
 SeaChest_PowerControl Version: 1.10.0-1_19_23 X86_64
 Build Date: Jun 10 2019
 Today: Sat May 16 23:05:56 2020
==========================================================================================

/dev/sg1 - ST12000VN0008-2JH101 - ZHZ54BTB - ATA
Failed to send EPC command to /dev/sg1.
EPC Feature set might not be supported.
Or EPC Feature might already be in the desired state.

$ sudo ./SeaChest_PowerControl_1100__11923_64 -d /dev/sg0 --EPCfeature disable

==========================================================================================

SeaChest_PowerControl - Seagate drive utilities - NVMe Enabled

SeaChest_PowerControl Version: 1.10.0-1_19_23 X86_64

Build Date: Jun 10 2019

Today: Sat May 16 23:05:56 2020

==========================================================================================

/dev/sg1 - ST12000VN0008-2JH101 - ZHZ54BTB - ATA

Failed to send EPC command to /dev/sg1.

EPC Feature set might not be supported.

Or EPC Feature might already be in the desired state.

工具的功能挺全的，能查到硬盘的很多信息，如下：

$ sudo ./SeaChest_Configure_1170_11923_64 -d /dev/sg1 -i
==========================================================================================
 SeaChest_Configure - Seagate drive utilities - NVMe Enabled
 Copyright (c) 2014-2019 Seagate Technology LLC and/or its Affiliates, All Rights Reserved
 SeaChest_Configure Version: 1.17.0-1_19_23 X86_64
 Build Date: Jun 10 2019
 Today: Sun May 17 22:25:00 2020
==========================================================================================

/dev/sg1 - ST12000VN0008-2JH101 - ZHZ54BTB - ATA
	Model Number: ST12000VN0008-2JH101
	Serial Number: ZHZ54BTB
	Firmware Revision: SC60
	World Wide Name: 5000C500C3DDC97B
	Drive Capacity (TB/TiB): 12.00/10.91
	Native Drive Capacity (TB/TiB): 12.00/10.91
	Temperature Data:
		Current Temperature (C): 38
		Highest Temperature (C): 48
		Lowest Temperature (C): 31
	Power On Time:  5 days 2 hours 
	Power On Hours: 122.00
	MaxLBA: 23437770751
	Native MaxLBA: 23437770751
	Logical Sector Size (B): 512
	Physical Sector Size (B): 4096
	Sector Alignment: 0
	Rotation Rate (RPM): 7200
	Form Factor: 3.5"
	Last DST information:
		Time since last DST (hours): 22.00
		DST Status/Result: 0x0
		DST Test run: 0x2
	Long Drive Self Test Time:  19 hours 14 minutes 
	Interface speed:
		Max Speed (Gb/s): 6.0
		Negotiated Speed (Gb/s): 6.0
	Annualized Workload Rate (TB/yr): 2018.24
	Total Bytes Read (TB): 15.98
	Total Bytes Written (TB): 12.13
	Encryption Support: Not Supported
	Cache Size (MiB): 256.00
	Read Look-Ahead: Enabled
	Write Cache: Enabled
	Low Current Spinup: Ultra Low Enabled
	SMART Status: Unknown or Not Supported
	ATA Security Information: Supported
	Firmware Download Support: Full, Segmented, Deferred
	Specifications Supported:
		ACS-4
		ACS-3
		ACS-2
		ATA8-ACS
		ATA/ATAPI-7
		ATA/ATAPI-6
		ATA/ATAPI-5
		SATA 3.2
		SATA 3.1
		SATA 3.0
		SATA 2.6
		SATA 2.5
		SATA II: Extensions
		SATA 1.0a
		ATA8-AST
	Features Supported:
		Sanitize
		SATA NCQ
		SATA Rebuild Assist
		SATA Software Settings Preservation [Enabled]
		SATA Device Initiated Power Management
		Power Management
		Security
		SMART [Enabled]
		48bit Address
		PUIS
		GPL
		Streaming
		SMART Self-Test
		SMART Error Logging
		Write-Read-Verify
		DSN
		AMAC
		EPC
		Sense Data Reporting
		SCT Write Same
		SCT Error Recovery Control
		SCT Feature Control
		SCT Data Tables
		Host Logging
		Set Sector Configuration
		Storage Element Depopulation
		Seagate In Drive Diagnostics (IDD)

$ sudo ./SeaChest_Configure_1170_11923_64 -d /dev/sg1 -i

==========================================================================================

SeaChest_Configure - Seagate drive utilities - NVMe Enabled

SeaChest_Configure Version: 1.17.0-1_19_23 X86_64

Build Date: Jun 10 2019

Today: Sun May 17 22:25:00 2020

==========================================================================================

/dev/sg1 - ST12000VN0008-2JH101 - ZHZ54BTB - ATA

Model Number: ST12000VN0008-2JH101

Serial Number: ZHZ54BTB

Firmware Revision: SC60

World Wide Name: 5000C500C3DDC97B

Drive Capacity (TB/TiB): 12.00/10.91

Native Drive Capacity (TB/TiB): 12.00/10.91

Temperature Data:

Current Temperature (C): 38

Highest Temperature (C): 48

Lowest Temperature (C): 31

Power On Time: 5 days 2 hours

Power On Hours: 122.00

MaxLBA: 23437770751

Native MaxLBA: 23437770751

Logical Sector Size (B): 512

Physical Sector Size (B): 4096

Sector Alignment: 0

Rotation Rate (RPM): 7200

Form Factor: 3.5"

Last DST information:

Time since last DST (hours): 22.00

DST Status/Result: 0x0

DST Test run: 0x2

Long Drive Self Test Time: 19 hours 14 minutes

Interface speed:

Max Speed (Gb/s): 6.0

Negotiated Speed (Gb/s): 6.0

Annualized Workload Rate (TB/yr): 2018.24

Total Bytes Read (TB): 15.98

Total Bytes Written (TB): 12.13

Encryption Support: Not Supported

Cache Size (MiB): 256.00

Read Look-Ahead: Enabled

Write Cache: Enabled

Low Current Spinup: Ultra Low Enabled

SMART Status: Unknown or Not Supported

ATA Security Information: Supported

Firmware Download Support: Full, Segmented, Deferred

Specifications Supported:

ACS-4

ACS-3

ACS-2

ATA8-ACS

ATA/ATAPI-7

ATA/ATAPI-6

ATA/ATAPI-5

SATA 3.2

SATA 3.1

SATA 3.0

SATA 2.6

SATA 2.5

SATA II: Extensions

SATA 1.0a

ATA8-AST

Features Supported:

Sanitize

SATA NCQ

SATA Rebuild Assist

SATA Software Settings Preservation [Enabled]

SATA Device Initiated Power Management

Power Management

Security

SMART [Enabled]

48bit Address

PUIS

GPL

Streaming

SMART Self-Test

SMART Error Logging

Write-Read-Verify

DSN

AMAC

EPC

Sense Data Reporting

SCT Write Same

SCT Error Recovery Control

SCT Feature Control

SCT Data Tables

Host Logging

Set Sector Configuration

Storage Element Depopulation

Seagate In Drive Diagnostics (IDD)

有个缩写简单记录一下

AMAC(Accessible Max Address Configuration feature set)

1	AMAC(Accessible Max Address Configuration feature set)

根据论坛上教你一招解决NAS噪音及速度问题！的经验，第1盘位是默认的系统盘，这导致硬盘不停的发出咳咳咳的寻道声。为了解决这个问题，（918+）遂决定在1、2盘位安装两块SSD，并将两块酷狼8TB转移到3、4盘位，这样一来，HDD不再是系统盘，在闲置状态下，便可启动休眠功能，大大降低噪音。

可惜718+只有两个盘位，918+的解决方法显然是行不通的。

我自己的群晖的噪声来源主要有如下几个

MariaDB 10开启了主从复制，主服务器上数据更新，会被自动同步到群晖上的MariaDB 10，导致磁盘被频繁唤醒，而磁盘寻道噪声很大！！

群晖开启了SMB服务，局域网内的机器会自动连接导致磁盘被唤醒，虽然没有写入，但是网络请求会导致磁盘唤醒，依旧出现寻道声。

系统盘安装在硬盘上，系统会定期唤醒磁盘执行读写操作。目前测试发现，关闭MariaDB 10, SMB 之后，硬盘可顺利休眠。

按理说，上面的问题，可以通过群晖的 SSD 缓存加速功能缓解，结果718+没有M.2插口，918+有两个内置的M.2插口。而外置通过eSata的接口挂载硬盘，是不能作为SSD 缓存加速的。具体信息参考 Synology’s DS718+ SSD caching is crippled/Which Synology NAS models support SSD cache?

目前能想到的办法是

继续阅读群晖(Synology) DS718+希捷酷狼(Seagate IronWolf)12TB空闲发出持续噪声

PyQt5的QWebEngineView使用模板

一.支持视频播放

关键代码

self.settings().setAttribute(QWebEngineSettings.PluginsEnabled, True)      #支持视频播放

1	self.settings().setAttribute(QWebEngineSettings.PluginsEnabled, True) #支持视频播放

二.支持页面关闭请求

关键代码

self.page().windowCloseRequested.connect(self.on_windowCloseRequested)     #页面关闭请求

1	self.page().windowCloseRequested.connect(self.on_windowCloseRequested) #页面关闭请求

三.支持页面下载请求

关键代码

self.page().profile().downloadRequested.connect(self.on_downloadRequested) #页面下载请求

1	self.page().profile().downloadRequested.connect(self.on_downloadRequested) #页面下载请求

【如下代码，完全复制，直接运行，即可使用】

import sys
import os
import datetime
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtWebEngineWidgets import QWebEngineView,QWebEngineSettings

# 调试窗口配置
# 如果不想自己创建调试窗口，可以使用Chrome连接这个地址进行调试
DEBUG_PORT = '5588'
DEBUG_URL = 'http://127.0.0.1:%s' % DEBUG_PORT
os.environ['QTWEBENGINE_REMOTE_DEBUGGING'] = DEBUG_PORT

################################################
#######创建主窗口
################################################
class MainWindow(QMainWindow):
    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.setWindowTitle('My Browser')
        #self.showMaximized()
        self.setWindowFlags(Qt.FramelessWindowHint)

        #####创建tabwidget
        self.tabWidget = QTabWidget()
        self.tabWidget.setTabShape(QTabWidget.Triangular)
        self.tabWidget.setDocumentMode(True)
        self.tabWidget.setMovable(True)
        self.tabWidget.setTabsClosable(True)
        self.tabWidget.tabCloseRequested.connect(self.close_Tab)
        self.setCentralWidget(self.tabWidget)

        ####第一个tab
        self.webview = WebEngineView(self)   #self必须要有，是将主窗口作为参数，传给浏览器
        self.webview.load(QUrl("https://www.baidu.com"))
        self.create_tab(self.webview)

        #网页调试窗口
        self.inspector = QWebEngineView()
        self.inspector.setWindowTitle('Web Inspector')
        self.inspector.load(QUrl(DEBUG_URL))
        self.webview.loadFinished.connect(self.handleHtmlLoaded)        

    # 加载完成后显示调试网页
    def handleHtmlLoaded(self, ok):
        if ok:
            self.webview.page().setDevToolsPage(self.inspector.page())
            self.inspector.show()

    #创建tab
    def create_tab(self,webview):
        self.tab = QWidget()
        self.tabWidget.addTab(self.tab, "新标签页")
        self.tabWidget.setCurrentWidget(self.tab)
        #####
        self.Layout = QHBoxLayout(self.tab)
        self.Layout.setContentsMargins(0, 0, 0, 0)
        self.Layout.addWidget(webview)

    #关闭tab
    def close_Tab(self,index):
        if self.tabWidget.count()>1:
            self.tabWidget.removeTab(index)
        else:
            self.close()   # 当只有1个tab时，关闭主窗口

################################################
#######创建浏览器
################################################
class WebEngineView(QWebEngineView):

    def __init__(self,mainwindow,parent=None):
        super(WebEngineView, self).__init__(parent)
        self.mainwindow = mainwindow
        ##############
        self.settings().setAttribute(QWebEngineSettings.PluginsEnabled, True)      #支持视频播放
        self.page().windowCloseRequested.connect(self.on_windowCloseRequested)     #页面关闭请求
        self.page().profile().downloadRequested.connect(self.on_downloadRequested) #页面下载请求

    #  支持页面关闭请求
    def on_windowCloseRequested(self):
        the_index = self.mainwindow.tabWidget.currentIndex()
        self.mainwindow.tabWidget.removeTab(the_index)


    #  支持页面下载按钮
    def on_downloadRequested(self,downloadItem):
        if  downloadItem.isFinished()==False and downloadItem.state()==0:
            ###生成文件存储地址
            the_filename = downloadItem.url().fileName()
            if len(the_filename) == 0 or "." not in the_filename:
                cur_time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
                the_filename = "下载文件" + cur_time + ".xls"
            the_sourceFile = os.path.join(os.getcwd(), the_filename)

            ###下载文件
            # downloadItem.setSavePageFormat(QWebEngineDownloadItem.CompleteHtmlSaveFormat)
            downloadItem.setPath(the_sourceFile)
            downloadItem.accept()
            downloadItem.finished.connect(self.on_downloadfinished)


    #  下载结束触发函数
    def on_downloadfinished(self):
        js_string = '''
        alert("下载成功，请到软件同目录下，查找下载文件！"); 
        '''
        self.page().runJavaScript(js_string)


    # 重写createwindow()
    def createWindow(self, QWebEnginePage_WebWindowType):
        new_webview = WebEngineView(self.mainwindow)

        self.mainwindow.create_tab(new_webview)

        return new_webview


################################################
#######程序入门
################################################
if __name__ == "__main__":
    app = QApplication(sys.argv)
    the_mainwindow = MainWindow()
    the_mainwindow.show()
    sys.exit(app.exec())

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

import sys

import os

import datetime

from PyQt5.QtWidgets import *

from PyQt5.QtCore import *

from PyQt5.QtWebEngineWidgets import QWebEngineView,QWebEngineSettings

# 调试窗口配置

# 如果不想自己创建调试窗口，可以使用Chrome连接这个地址进行调试

DEBUG_PORT = '5588'

DEBUG_URL = 'http://127.0.0.1:%s' % DEBUG_PORT

os.environ['QTWEBENGINE_REMOTE_DEBUGGING'] = DEBUG_PORT

################################################

#######创建主窗口

################################################

class MainWindow(QMainWindow):

def __init__(self, *args, **kwargs):

super().__init__(*args, **kwargs)

self.setWindowTitle('My Browser')

#self.showMaximized()

self.setWindowFlags(Qt.FramelessWindowHint)

#####创建tabwidget

self.tabWidget = QTabWidget()

self.tabWidget.setTabShape(QTabWidget.Triangular)

self.tabWidget.setDocumentMode(True)

self.tabWidget.setMovable(True)

self.tabWidget.setTabsClosable(True)

self.tabWidget.tabCloseRequested.connect(self.close_Tab)

self.setCentralWidget(self.tabWidget)

####第一个tab

self.webview = WebEngineView(self) #self必须要有，是将主窗口作为参数，传给浏览器

self.webview.load(QUrl("https://www.baidu.com"))

self.create_tab(self.webview)

#网页调试窗口

self.inspector = QWebEngineView()

self.inspector.setWindowTitle('Web Inspector')

self.inspector.load(QUrl(DEBUG_URL))

self.webview.loadFinished.connect(self.handleHtmlLoaded)

# 加载完成后显示调试网页

def handleHtmlLoaded(self, ok):

if ok:

self.webview.page().setDevToolsPage(self.inspector.page())

self.inspector.show()

#创建tab

def create_tab(self,webview):

self.tab = QWidget()

self.tabWidget.addTab(self.tab, "新标签页")

self.tabWidget.setCurrentWidget(self.tab)

#####

self.Layout = QHBoxLayout(self.tab)

self.Layout.setContentsMargins(0, 0, 0, 0)

self.Layout.addWidget(webview)

#关闭tab

def close_Tab(self,index):

if self.tabWidget.count()>1:

self.tabWidget.removeTab(index)

else:

self.close() # 当只有1个tab时，关闭主窗口

################################################

#######创建浏览器

################################################

class WebEngineView(QWebEngineView):

def __init__(self,mainwindow,parent=None):

super(WebEngineView, self).__init__(parent)

self.mainwindow = mainwindow

##############

self.settings().setAttribute(QWebEngineSettings.PluginsEnabled, True) #支持视频播放

self.page().windowCloseRequested.connect(self.on_windowCloseRequested) #页面关闭请求

self.page().profile().downloadRequested.connect(self.on_downloadRequested) #页面下载请求

# 支持页面关闭请求

def on_windowCloseRequested(self):

the_index = self.mainwindow.tabWidget.currentIndex()

self.mainwindow.tabWidget.removeTab(the_index)

# 支持页面下载按钮

def on_downloadRequested(self,downloadItem):

if downloadItem.isFinished()==False and downloadItem.state()==0:

###生成文件存储地址

the_filename = downloadItem.url().fileName()

if len(the_filename) == 0 or "." not in the_filename:

cur_time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')

the_filename = "下载文件" + cur_time + ".xls"

the_sourceFile = os.path.join(os.getcwd(), the_filename)

###下载文件

# downloadItem.setSavePageFormat(QWebEngineDownloadItem.CompleteHtmlSaveFormat)

downloadItem.setPath(the_sourceFile)

downloadItem.accept()

downloadItem.finished.connect(self.on_downloadfinished)

# 下载结束触发函数

def on_downloadfinished(self):

js_string = '''

alert("下载成功，请到软件同目录下，查找下载文件！");

'''

self.page().runJavaScript(js_string)

# 重写createwindow()

def createWindow(self, QWebEnginePage_WebWindowType):

new_webview = WebEngineView(self.mainwindow)

self.mainwindow.create_tab(new_webview)

return new_webview

################################################

#######程序入门

################################################

if __name__ == "__main__":

app = QApplication(sys.argv)

the_mainwindow = MainWindow()

the_mainwindow.show()

sys.exit(app.exec())

2020 年 5 月
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31