2022年3月8日 星期二
如何透過Merge Replication 複寫機制,同步 EC2 與 AWS RDS for SQL Server 的資料庫
2021年11月27日 星期六
SQL Server 備份效能與磁碟效能分析 - II
上一篇 (SQL Server 備份效能與磁碟效能分析 - I),我們主要介紹如何驗證 EBS Volume 的方式,本篇我們就來介紹在 SQL Server 備份時,該如何進行效能調整的部份。
進行備份前,由於我們要模擬一個比較大的資料庫,所以我找到Stack Overflow 有提供下載,所以我此次測試的資料庫約有50G的大小[1]。
在進行資料庫備份時,我們通常可以透過下列的指令進行備份。
BACKUP DATABASE [StackOverflow2013] TO DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT, NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE successfully processed 6165657 pages in 154.625 seconds (311.522 MB/sec).
首先我們先來說明一下,上述的指令主要就是將資料庫 StackOverflow2013 備份到 S:\db_backup\20210909-test3.bak 的位置,而 stats = 10 的話,就代表每完成10%就顯示出,藉以了解備份的進度,最後可以看出花了多少的時間與大約每秒的備份速度。
從前一篇的說明,我們知道整體的 Throughput 會與 Thread 和 BlockSize 有關,但從Microsoft 的文件上來看[2],SQL Server Backup 中的 BlockSize 最高只能設定至 64K,所以這就斷了一條路,我們只好從 Thread 下手,我們嘗試將原本的備份檔案,從一個檔案,改寫成多個檔案後,如下列的語法,即可將整體的 Throughput 拉上來。
BACKUP DATABASE [StackOverflow2013]
TO
DISK = N'S:\db_backup\20210909-test1.bak'
, DISK = N'S:\db_backup\20210909-test2.bak'
, DISK = N'S:\db_backup\20210909-test3.bak'
, DISK = N'S:\db_backup\20210909-test4.bak'
WITH
NAME = N'StackOverflow2013-Full Database Backup'
, INIT, STATS = 10
BACKUP DATABASE successfully processed 6165657 pages in 95.388 seconds (504.981 MB/sec).
由於 SQL Server 在備份上是採取當寫一個檔案時,會以一個 Thread 進行,所以上述的動作,我們分成四個 Thread 進行,所以你可以看出最後的 Throughput 可以達到 504MB,但其實如果我將檔案分成更多個時,是否會有更好的表現,我的答案是不一定的,因為這個會取決於你的 Data File 的分檔數量與 Core 數量等,所以這個可以測試確認後,找出一個合適的方式。
除了上述的作法,還有什麼方式可以進行調整,答案是有的,我們可以透過另外二個參數來進行,一個是 BUFFERCOUNT,而另一個就是 MAXTRANSFERSIZE,二個數值的調整會影響到記憶體的使用量,根據微軟的文件說明[2],可能會造成 OOM 的情況,所以也要請大家注意。
BACKUP DATABASE [StackOverflow2013] TO DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT, NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, BUFFERCOUNT = 7, MAXTRANSFERSIZE = 4194304, BLOCKSIZE = 65536
BACKUP DATABASE successfully processed 6165657 pages in 95.358 seconds (505.140 MB/sec).
其中 BufferCount 仍是預設值,我設定為7,關於此值的設定,會與你的備份設備裝置相關,大約也在7-10之間[3],所以我仍是以7為主,但在 MaxTransferSize 的部份,預設值為 1,048,576 (1M),所以我將此值的設定調整加大後,你也可以看到,也是有加快速度的效果。
相反的,如果將這個值設定變小時,會有什麼情況,可想而知,備份的速度將會非常的慢,如下列所示,我將 MaxTransferSize 從預設的 1M 改成 64k後,整體的備份速度就從311MB左右,跌至 63 MB。
BACKUP DATABASE [StackOverflow2013] TO DISK = N'S:\db_backup\20210909-test.bak' WITH NOFORMAT, NOINIT, NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, BUFFERCOUNT = 7, MAXTRANSFERSIZE = 65536, BLOCKSIZE = 65536
BACKUP DATABASE successfully processed 6165673 pages in 753.965 seconds (63.887 MB/sec).
另外當我們如果相要進行備份時的預設參數為何,可以透過下列的語法,即可在進行備份時,將相關的參數寫入到 Error Log 中進行確認。
DBCC TRACEON (3213, 3605, -1);
輸出結果:
Backup/Restore buffer configuration parameters
Memory limit: 12151 MB
BufferCount: 7
Sets Of Buffers: 1
MaxTransferSize: 1024 KB
Min MaxTransferSize: 64 KB
Total buffer space: 7 MB
Tabular data device count: 1
Fulltext data device count: 0
Filestream device count: 0
TXF device count: 0
Filesystem i/o alignment: 512
Media Buffer count: 7
Media Buffer size: 1024 KB
最後是否還有其他的方式,其實還是有一招的,就是透過 Windows 的軟體式陣列來達到,所以我用二顆 EBS Volume 來組成一個 Disk Array 0進行,並將測試的結果分享如下。
1. 透過 DiskSpd 進行測試。
2. 透過 SQL Server 預設值進行備份 (不調整任何的參數)。
BACKUP DATABASE [StackOverflow2013] TO DISK = N'S:\db_backup\20210909-test3.bak' WITH NOFORMAT, NOINIT, NAME = N'StackOverflow2013-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE successfully processed 6165673 pages in 99.999 seconds (481.697 MB/sec).
從上述的測試上來看,的確二顆 EBS Volume 綁成一顆後,可以看到很好的效果,而且從數字上來看也是有很大的成長,而且 Thoughtput 與 IOPS 可以成長到 8000+8000 與 500MB + 500MB 的效果,你要知道,如果你要執行 1000 MB 的 Throughput 時,至少 IOPS 要設定至 64,000 以上才可以,但這個方式就可以用較少的成本達到效果,但我強烈的不建議將 SQL Server Data File 放在此 Disk Array 上,因為一來這是一顆 Disk Array 0,再來軟體式的沒有硬體式的穩定,所以請特別注意使用的方式,千萬不要誤用,很重要!!!很重要!!!很重要!!!
上述的一系列的調整與測試的整理,希望可以提供給大家參考,如果有其他更好的方式,也歡迎提供。
參考連結:
=============
[1]. How to Download the Stack Overflow Database
https://www.brentozar.com/archive/2015/10/how-to-download-the-stack-overflow-database-via-bittorrent/
[2]. BACKUP (Transact-SQL)
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
[3]. How It Works: How does SQL Server Backup and Restore select transfer sizes
https://techcommunity.microsoft.com/t5/sql-server-support-blog/how-it-works-how-does-sql-server-backup-and-restore-select/ba-p/315454
SQL Server 備份效能與磁碟效能分析 - I
2020年2月13日 星期四
雲端上動態延展的架構下,如何不影響運作的情況下,持續的進行作業系統的更新,以AWS平台架構為例
但在雲端上,為了動態的調整所需的資源,我們可以透過 AWS Auto Scaling 的服務,自動的依據負據來動態的增加或減少伺服器的數量,但這時候問題就來了,該如何在這樣的架構下進行平日的作業系統維護與底層映像檔的更換。
在與許多人討論過,常見的方法都是將目前自行維護的映像檔再建立一台新的伺服器,然後再安裝作業系統的更新後,再作成映像檔,然後再抽換 Auto Scaling 服務中對應的映像檔。但是這樣一來通常都需要安排特定停機時間進行,而且需要花費多人來進行相關的作業。
上述的動作如果我們希望可以全部自動進行時,並儘可能的減少人工的作業,因為這種持續性的動作,人為的進行總是有疏忽的時候,讓自動的進行,才可以讓整個系統的可靠性更高。
本篇主要介紹到,希望可以透過AWS提供的眾多服務進行整合,藉以達到此需求,其實我介紹的這個方法在AWS的官網中也有介紹到,但本篇加以進行整合,然後我也特別將官網未提到的方法進行整合,並且加入排程的部份,所以我會在此文章中完整的說明並介紹給大家,也讓大家可以嘗試將這個方法導入您的環境中。
在建立前我先初步的介紹,基本上,主要是透過 SSM Automation 的服務,將指定的映像檔建立成一台新的伺服器,然後透過 SSM Run Command 進行作業系統的更新,最後再關閉伺服器,然後再進行新的映像檔的制作,最後再進行 SSM Parameter Store 參數的更新與 Auto Scaling Group 的底層映像檔的更新,基本上你可以透過下列的步驟逐一的進行即可完成初步的建置。
步驟一:建立一個Auto Scaling Group
這部份,由於後面的 AWS Lambda 程式是以 Launch Configuration 為主,所以我們也是先建立一個 Launch Configuration,後續上程式也會進行這部份的新增與抽換。

選擇預設的作業系統,由於我預期是一個全新的作業系統,所以我以 AWS 上預設的 Windows Server 2016 當成是基底進行,當然如果你有自已管理的映像檔的話,你也可以自行選擇。
建立完成範本後,再進行 Auto Scaling group 的新增,在子網路的部份,建議將目前所有的子網路區段都加入。

另外在自動成長的規則部份,我這這邊是設定為當 CPU 的使用率超過50%的時候並且超過300秒,就進行自動成長的動作。

步驟二:請先建立一個參數於 Parameter Store 中,藉以記錄目前最後更新映像檔編號 (AMI ID).
登入 Systems Manager 中心,並建立下列的參數,如下圖所示,另外名稱的部份請確認為 "latestAmi" 因為後續會使用到。
而參數中的值,請填入你目前最新維護的映像檔編號,如我的測試,我是以目前此區域中預設最新的 Windows Server 2016為此值的設定。

步驟三:建立一個 AWS Lambda執行時進行的IAM角色
登入 AWS IAM -> Roles,建立一個新的角色,並且依照下列的圖示加入相對應的權限。
AutoScalingFullAccess
AmazonSSMFullAccess
AWSLambdaExecute
此步驟中,名稱設定為:SSMAutoUpdateRoleForLambda

步驟四:建立一個 Lambda Function 藉以進行更新 Auto Scaling Group 和步驟一中建立的參數。
登入 AWS Lambda -> Create Function,如下列的圖示加入一個相對應的 Lambda Function

其中在 Execution role的部份,請選擇在步驟二中建立的 IAM 角色,建立完成後,請將下列的程式碼貼入 Function Code 的區域中存檔即可。
from __future__ import print_function
import json
import datetime
import time
import boto3
print('Loading function')
def lambda_handler(event, context):
print("Received event: " + json.dumps(event, indent=2))
# get autoscaling client
client = boto3.client('autoscaling')
# get object for the ASG we're going to update, filter by name of target ASG
response = client.describe_auto_scaling_groups(AutoScalingGroupNames=[event['targetASG']])
if not response['AutoScalingGroups']:
return 'No such ASG'
# get name of InstanceID in current ASG that we'll use to model new Launch Configuration after
sourceInstanceId = response.get('AutoScalingGroups')[0]['Instances'][0]['InstanceId']
# create LC using instance from target ASG as a template, only diff is the name of the new LC and new AMI
timeStamp = time.time()
timeStampString = datetime.datetime.fromtimestamp(timeStamp).strftime('%Y-%m-%d %H-%M-%S')
newLaunchConfigName = 'LC '+ event['newAmiID'] + ' ' + timeStampString
client.create_launch_configuration(
InstanceId = sourceInstanceId,
LaunchConfigurationName=newLaunchConfigName,
ImageId= event['newAmiID'] )
#Updates an SSM parameter
# get SSM client
client2 = boto3.client('ssm')
parameterName = 'latestAmi'
#confirm parameter exists before updating it
response2 = client2.describe_parameters(
Filters=[
{
'Key': 'Name',
'Values': [ parameterName ]
},
]
)
if not response2['Parameters']:
print('No such parameter')
return 'SSM parameter not found.'
#if parameter has a Description field, update it PLUS the Value
if 'Description' in response2['Parameters'][0]:
description = response2['Parameters'][0]['Description']
response2 = client2.put_parameter(
Name=parameterName,
Value=event['newAmiID'],
Description=description,
Type='String',
Overwrite=True
)
#otherwise just update Value
else:
response2 = client2.put_parameter(
Name=parameterName,
Value=event['newAmiID'],
Type='String',
Overwrite=True
)
# update ASG to use new LC
response = client.update_auto_scaling_group(AutoScalingGroupName = event['targetASG'],LaunchConfigurationName = newLaunchConfigName)
return 'Updated ASG `%s` with new launch configuration `%s` which includes AMI `%s`.' % (event['targetASG'], newLaunchConfigName, event['newAmiID'])
另外,你也可以在右上角的部份點選測試,確認是否執行正確,測試時,名稱就隨意輸入即可,另外在下列的格式中,請取代輸入你的參考值。
如同我的範例如下。
{
"newAmiID": "ami-02c8625672a43c5e0",
"targetASG": "SSM-ASG"
}
步驟五: 建立一個 IAM 角色設定給當執行 SSM Automation 時,所需要的權限。
登入 AWS IAM -> Roles,建立一個新的角色,並且依照下列的圖示加入相對應的權限。
AmazonSSMAutomationRole
另外再請加入一個inline policy,並允許進行AssuleRole的進行
{
"Effect": "Allow",
"Action": "sts:AssumeRole",
"Resource": "*"
}
此步驟中,名稱設定為:SSMAutomationServiceRole
步驟六: 建立 SSM Automation Document 藉以進行作業系統的更新與進行製作新的映像檔
打開 AWS Systems Manager console -> Document -> Create automation.
請將下列的程式碼貼入後即可,但其中第三行的assuleRole請指定為在步驟四中新增的帳號,而28行的部份則是指定一個IAM role設定給伺服器使用,這個角把的新增方法,可以參考下列的連結。
About Policies for a Systems Manager Instance Profile
https://docs.aws.amazon.com/systems-manager/latest/userguide/setup-instance-profile.html
description: Systems Manager Automation Demo - Patch AMI and Update ASG
schemaVersion: '0.3'
assumeRole: 'arn:aws:iam::accountid:role/SSMAutomationServiceRole'
outputs:
- createImage.ImageId
parameters:
sourceAMIid:
type: String
description: AMI to patch
targetAMIname:
type: String
default: 'patchedAMI-{{global:DATE_TIME}}'
description: Name of new AMI
targetASG:
type: String
description: Auto Scaling group to Update
mainSteps:
- name: startInstances
action: 'aws:runInstances'
timeoutSeconds: 1200
maxAttempts: 1
onFailure: Abort
inputs:
ImageId: '{{ sourceAMIid }}'
InstanceType: m3.large
MinInstanceCount: 1
MaxInstanceCount: 1
IamInstanceProfileName: AmazonSSMManagedInstanceCore
- name: installMissingWindowsUpdates
action: 'aws:runCommand'
maxAttempts: 1
onFailure: Continue
inputs:
DocumentName: AWS-InstallWindowsUpdates
InstanceIds:
- '{{ startInstances.InstanceIds }}'
Parameters:
SeverityLevels: Important
- name: stopInstance
action: 'aws:changeInstanceState'
maxAttempts: 1
onFailure: Continue
inputs:
InstanceIds:
- '{{ startInstances.InstanceIds }}'
DesiredState: stopped
- name: createImage
action: 'aws:createImage'
maxAttempts: 1
onFailure: Continue
inputs:
InstanceId: '{{ startInstances.InstanceIds }}'
ImageName: '{{ targetAMIname }}'
NoReboot: true
ImageDescription: AMI created by EC2 Automation
- name: terminateInstance
action: 'aws:changeInstanceState'
maxAttempts: 1
onFailure: Continue
inputs:
InstanceIds:
- '{{ startInstances.InstanceIds }}'
DesiredState: terminated
- name: updateASG
action: 'aws:invokeLambdaFunction'
timeoutSeconds: 1200
maxAttempts: 1
onFailure: Abort
inputs:
FunctionName: Automation-UpdateAsg
Payload: '{"targetASG":"{{targetASG}}", "newAmiID":"{{createImage.ImageId}}"}'
建立完成後,你可以透過右上角的 Execute automation進行執行,參數的部份,可以參考下列的圖示進行。

上述的動作,單次的進行後,即可完成我們想要的動作,但是當如果需要進行排程讓上述的SSM Automation Document在固定的時間進行時,我們就需要再進行下列的動作。
在排程的部份,其實大家可以找一台固定的主機進行排程進行即可,但是我希望資訊可以完整的呈現在 AWS Conole 中,所以我這邊透過 AWS Maintenance Windows進行。
步驟七:建立 Maintenance Windows 進行排程,自動進行上述的維護動作。
登入 AWS Console -> Systems Manager -> Maintenance Windows

先輸入名稱,並設定排程時間,我這邊是設定每週日的凌晨1:00進行

設定完成後,在 Tasks 的部份,請加入一個工作排程,也就是進行執行 SSM Automation 的動作,輸入的參數如下列圖示顯示。


另外關於 sourceAMIid的部份,我是透過之前建立的參數,自動的帶入,所以格式會以 ssm 開頭,這部份再請特別的注意。

由於上述的動作整合到許多 AWS 的服務,如 AWS Auto Scaling,AWS Systems Manager,AWS Lambda等功能,所以算是比較複雜的架構,但架設完成後,這樣的架構即可自動而且不斷的完成作業系統的更新並保有雲端上的彈性,所以個人非常的推薦,當然在延伸的方式上,你可以再進行更新的條件進行設定如Windows更新推出後幾天再進行安裝,或是只安裝重要更新的方式進行,這些設定都可以在 <b>SSM Systems Manager 中進行,當然如有任何問題,也歡迎提出進行討論。
參考連結:
Walkthrough: Simplify AMI Patching Using Automation, AWS Lambda, and Parameter Store
https://docs.aws.amazon.com/systems-manager/latest/userguide/automation-walk-patch-windows-ami-simplify.html