SQL Server Love Linux:管理篇

#檔案和資料夾

cp:複製

#cp -r /home/myfolder/test /home/sherryfolder/ 複製test資料夾到 sherryfolder.

#我最常使用cp進行一些config檔案備份,假如我改壞了,至少可以透過備份還原.

#cp /var/opt/mssql/mssql.conf /var/opt/mssql/mssql.conf.20190712.bak

mv:搬移/重新命名

#類似windows的剪下,如遇網路斷線,崩潰等情況,檔案將無法恢復,建議使用cp取代

#mv /var/opt/mssql/mssql.conf /var/opt/mssql/mssql.conf.bak

rm:刪除

#rm -rf myfolder 刪除myfolder資料夾和所有檔案 ,要謹慎使用, rm -rf的轟動事件,莫過於2017年GitLab發生某工程師刪除正式環境資料.

可透過備份進行恢復(勢必會遺漏一些資料)或一些工具挽救(如extundelete),要不然就花大筆錢請專業資料挽救公司進行硬碟資料救援恢復

mkdir:建立資料夾

#mkdir /var/opt/backupfiles

cat:查看小型檔案內容

#cat /opt/mssql/bin/mssql-conf

less:上下頁方式查看大型檔案內容

#空格鍵換下一頁 , End的標籤表示檔案末端 , q表示退出 ,w表示上一頁

#less /tmp/mssqllog/errorlog

more:下一頁方式查看大型檔案內容

#ps -ef | more

head:查看前10行(預設)的檔案內容

#ps -ef | head -n 20

#head -n 20 /tmp/mssqllog/errorlog

tail:查看倒數10行(預設)的檔案內容

#tail -n 20 /tmp/mssqllog/errorlog

#兩個命令一起使用,可實現查看檔案某範圍, head -n 100 /tmp/mssqllog/errorlog | tail -n 5 查看前100行中的倒數5行內容

tailf:即時輸出檔案內容

#tailf /tmp/mssqllog/errorlog

#我很常使用該命令觀察即時log

vim:編輯或建立檔案

#vim test.txt 建立一個空白test.txt檔案

#i 編輯模式 ,ESC離開編輯模式, : wq! 儲存內容並離開

#vim /etc/vim/vimrc , set bg=dark 修改背景顏色

grep:文字搜尋,支援正則表示,但無法修改

#netstat -tlunp| grep sql 搜尋 program name含有sql關鍵字

sed:文字處理,可新增和刪除,大部分使用批次修改

#sudo sed -i 's/01/rico/' test.txt  取代test.txt內中中所有01變更為rico -i:直接修改 s:取代

awk:文字處理,可新增和刪除,適合處理小型資料,大部分處理每一行的欄位資料

#awk '{print $1 "\t" $3}' test.txt 顯示test.txt內容中的第一和第三欄位資料,分隔符號空白或tab

ls -al:查看當前目錄下所有檔案

find:搜尋目錄中的檔案

#find /opt/ -name generate*.sh 搜尋opt目錄下的generate開頭和sh結尾的檔案

#網路

ifconfig:查看 NIC 資訊

#ifconfig

ip addr:查看 NIC 資訊,CentOS7用來取代ifconfig

#ip addr

route -n:查看getway資訊

#route -n

mtr:簡易網路診斷工具 (使用traceroute 和 ping)

#mtr -r 192.168.137.1

ping:檢查網路通訊

telnet:檢查某port是否開啟

#telnet localhost 50000 確認port 50000是否開啟 ,telnet需額外安裝, 離開:exit or CTRL + ] then ENTER

#系統

uptime:開機到現在的運行時間

top:查看資源使用

#load average表示當前server的負載情況, zombie:表示是否有間諜,木馬程序.

load average第一個欄位數值(過去1分鐘系統平均負載)和第二個欄位(過去5分鐘)沒太大意義,第三個欄位(過去15分鐘)最重要.

假如單核系統,load average=1.03%,表示超載3%,我個人load average只看過去15分鐘,其他兩個欄位只是短暫現象,

第三個欄位為判斷Server正常執行指標,windows我也是沿用類似該準則,準則如下

長期<0.7是好的

長期<=1也沒多大問題,但要盡快檢查系統

長期>=5就要立即查看系統問題(可先增加cpu降低系統負載應急),因為系統很容易崩潰.

P.S:如果CPU雙核,那麼load average=2才算系統達100%負載.

當AP如果執行緩慢,我會優先看top指標,尤其是CPU和Memory訊息

free -h:可用記憶體

#不用太在意mem的free欄位數值,低數值並不表示記憶體不夠用,因為Linux會最大化善用記憶體

,大部分要留意swap的used欄位數值是否太高,因為當記憶體不足時,linux會借用swap空間進行擴充,一般盡量保持0(越低越好)

df -h:硬碟空間

#tmpfs我們都可以忽略,其餘欄位都要監控,尤其是use%欄位,避免存放資料庫檔案目錄空間爆掉

ps:查看 process 資訊

#ps -ef | grep mssql 查看mssql process資訊

netstat -tlunp:查看 process和 port 資訊

#netstat -tlunp | grep 50000 查看port 50000相關process資訊

另外,可用wc來統計數量,netstat -tlnup | grep 50000 | grep -v "grep" | wc -l

useradd:新增使用者

#useradd -s /sbin/nologin kafka 新增kafka使用者,但無法透過ssh/telnet登入系統

#awk -F: '{print $1}' /etc/passwd 取得所有使用者清單

userdel:刪除使用者

#userdel -r kafka 刪除kafka使用者和相關目錄(-r)

echo:顯示文字

#echo "sql server"

chmod: 改變檔案的權限, SUID, SGID, SBIT等等的特性

#-rwxrwxrwx: 這九個權限是三個三個一組的,r:4,w:2,x:1

-rwxrwx---:owner = rwx = 4+2+1 = 7,group = rwx = 4+2+1 = 7,others= --- = 0+0+0 = 0

chmod -R 777 test.txt 將test.txt檔案所有權限都啟用

chown: 改變檔案擁有者

#chown -R kafka test.txt 將test.txt檔案擁有者改為kafka,-R表示包含所有子目錄和檔案

chgrp: 改變檔案所屬群組

#chgrp kafka test.txt

yum update:系統更新

mount: 掛載 disk

#mounk /dev/sdc /datas

umount: 卸載 disk

#umoun /datas

hostnamectl:

#hostnamectl set-hostname test.mynew-hostname.org 設定新的host name=test.mynew-hostname.org

#vim /etc/hosts 也須確認是否異動

man:顯示指令操作說

#man mount

--help:顯示指令用法

# mount --help

#找出前10大CPU最高的process

ps aux |head –l;ps aux|grep –v PID|sort –rn –k +3|head

#使用 dd 指令模擬高cpu情況

dd if=/dev/zero of=/dev/null.

note:請小心操作dd(data duplicator),dd屬於較低階的資料處理工具,

如勿使用，很容易造成硬碟資料不見..等,所以 dd 也有另一個有趣命名為 data destroyer.

#使用 dmesg 找出被OOM的process

less dmesg|grep -i kill

#從log/message找出被kill的process

less /var/log/messages | grep -i 'killed process'

#cpupower 更改 CPU 時脈機制為 performance(類似windows電源選項的高效能)

$sudo cat /usr/lib/tuned/throughput-performance/tuned.conf #查看吞吐量效能配置檔案

$sudo cpupower frequency-set --governor performance #設定frequency為高效能

#Disk 微調,使用sysctl調整後,請reboot

$sudo sysctl -w kernel.sched_min_granularity_ns=10000000

$sudo sysctl -w kernel.sched_wakeup_granularity_ns=15000000

$sudo sysctl -w vm.dirty_ratio=40

$sudo sysctl -w vm.dirty_background_ratio=10

$sudo sysctl -w vm.swappiness=10

$sudo sysctl -a | grep 'kernel.sched_min_granularity_ns \|sched_wakeup_granularity_ns \|vm.dirty_ratio \|vm.dirty_background_ratio \|vm.swappiness' #確認

#設定noatime減少不必要資訊紀錄,減輕IO負擔,針對SQL Server相關檔案

$sudo vim /etc/fstab

UUID=7d32e4a1-a088-4a58-9976-a08088cb3f45 /dblog xfs   noatime 0 0

UUID=7d39f71f-7820-4d92-a29a-d92c7704c87b /dbdata xfs   noatime 0 0

/dev/mapper/centos-root /                       xfs     noatime        0 0

#啟用Transparent Huge Pages(CentOS7 預設啟用),oracle 和 mongodb 資料庫都不建議開啟

$sudo cat /sys/kernel/mm/transparent_hugepage/enabled

$sudo echo always >> /sys/kernel/mm/transparent_hugepage/enabled

$sudo echo always >> /sys/kernel/mm/transparent_hugepage/defrag

#啟用Transparent Huge Pages後需reboot

$sudo vim /etc/default/grub  #GRUB_CMDLINE_LINUX 結尾加上 transparent_hugepage=always

$sudo grub2-mkconfig -o /boot/grub2/grub.cfg #重建 grub.cfg 檔案

#非Hyper-V虛擬化平台,請修改IO協調演算法

$sudo cat /sys/block/sdc/queue/scheduler

$sudo vim /etc/default/grub  #GRUB_CMDLINE_LINUX 結尾加上 elevator=deadline

$sudo grub2-mkconfig -o /boot/grub2/grub.cfg #重建 grub.cfg 檔案

#多NUMA系統核心設定自動 NUMA 平衡

$sudo sysctl -w kernel.numa_balancing=0

#Virtual Address Space設定

$sudo sysctl -w vm.max_map_count=262144

#取得rico使用者的指令歷史紀錄

cat rico/.bash_history|grep rm

$sudo yum install -y yum-utils device-mapper-persistent-data lvm2 #安裝lvm2

$sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo #新增docker-ce repo

$sudo yum install -y docker-ce #安裝docker-ce

$sudo usermod -aG docker $(whoami) #將現在使用者加入 docker 群組,-a會保留原來群組設定

$sudo systemctl enable docker #開啟docker

$sudo systemctl start docker  #啟動docker

$sudo docker info  #查看docker 資訊

$sudo vim /etc/docker/daemon.json  # 修改docker 主要存放路徑

{

    "data-root": "/dbdata/docker-data",

    "storage-driver": "overlay2"

}

$sudo mkdir /dbdata/docker-data  #建立資料夾存放docker image 檔案

$sudo chmod -R 777 /dbdata/docker-data #變更該資料夾權限

$sudo systemctl restart docker #重新啟動docker

$sudo docker info #確認data-root 路徑是否已經變更

$sudo yum install -y python-pip  #安裝python-pip

$sudo pip install docker-compose  #透過pip安裝docker-compose

$sudo yum upgrade python*  #更新python

$docker-compose version  #查看docker-compose 版本

$sudo tar -xvzf master.tar.gz #解壓縮檔案

$sudo rm -rf master.tar.gz #移除壓縮檔案

$cd docker-grafana-influxdb-master

$sudo docker build -t grafana_influxdb .  #建立docker image

$sudo docker run -d -p 3000:80 -p 8083:8083 -p 8084:8084 -p 8086:8086 --name MyGrafana-influxdb grafana_influxdb  #運行docker image

#grafana: admin / admin

$sudo wget https://dl.influxdata.com/telegraf/releases/telegraf-1.11.4-1.x86_64.rpm

$sudo yum localinstall -y telegraf-1.11.4-1.x86_64.rpm

$sudo systemctl enable telegraf

$sudo systemctl start telegraf

$sudo telegraf config > telegraf.conf  #/etc/telegraf/telegraf.conf

#Change in telegraf.conf

[[outputs.influxdb]]

urls = ["http://127.0.0.1:8086"]

database = "MONITORDB"

@設定並監控SQL Server

USE master;

GO

CREATE LOGIN [telegraf] WITH PASSWORD = N'p@ssWD321';

GO

GRANT VIEW SERVER STATE TO [telegraf];

GO

GRANT VIEW ANY DEFINITION TO [telegraf];

GO

namedrop = ["sqlserver*"]

[[outputs.influxdb]]

  urls = [ "http://127.0.0.1:8086" ]

  database = "MONITORSQL"

  # Only accept sqlserver data:

  namepass = ["sqlserver*"]

[[inputs.sqlserver]]

servers = [

    "Server=192.168.137.51;Port=50000;User Id=telegraf;Password=p@ssWD321;app name=telegraf;log=1;",

]

query_version = 2

exclude_query = [ 'Schedulers' , 'SqlRequests']

docker exec -it <container ID> bash

cd /src/grafana/bin

./grafana-cli plugins install grafana-piechart-panel  #install grafana-piechart-panel plugin

#需要重新啟動docker

--get database state

select dateadd(hour,-8,getdate()) as time,([name] COLLATE Latin1_General_CI_AS_KS_WS+'<br/>'+state_desc COLLATE Latin1_General_CI_AS_KS_WS ) as metric,[state] as value

from sys.databases with(nolock)

where (

(database_id>0) 

--or (database_id>=7 and database_id<=62)

)

order by [name] desc

Reference:

https://github.com/influxdata/telegraf/blob/master/docs/CONFIGURATION.md#output-configuration-examples

https://github.com/influxdata/telegraf/tree/master/plugins/inputs/sqlserver

https://grafana.com/grafana/dashboards/9386

https://grafana.com/grafana/plugins/grafana-piechart-panel

https://grafana.com/docs/features/panels/graph/

#grafana上設定slack的channel

#針對相關graph panel設定alert

exec dbo.sp_whoisactive  @get_plans=1

exec dbo.dba_BlockTracer

exec dbo.usp_who5

--@模擬blocking

--session 1

select * from Employee

where BusinessEntityID=80

--session 2

begin tran

update dbo.Employee set JobTitle=N'rico test'

where BusinessEntityID=80

rollback

cd /opt

$sudo wget

$sudo tar -xvf safe-rm-0.12.tar.gz

$sudo cd safe-rm-0.12

which rm #查看rm所在路徑

cat safe-rm #查看內容

cat INSTALL #查看內容

echo $PATH #確認環境變數

$sudo cp -a safe-rm  /usr/local/bin/rm #複製並改為rm

$sudo vim /etc/profile #修改環境變數

export PATH=/usr/local/bin:/bin:/usr/bin:$PATH

note:確認path環境變數中,safe-rm路徑必須在最前面

$sudo source /etc/profile #環境變數立即生效

$sudo vim /etc/safe-rm.conf #建立組態檔案並輸入以下保護白名單

/

/*

/boot

/boot/*

/dev

/dev/*

/etc

/etc/*

/home

/home/*

/root

/root/*

/etc/safe-rm.conf

/opt/mssql-tools/bin

/opt/mssql-tools/bin/*

/opt/mssql/bin

/opt/mssql/bin/*

/opt/mssql-tools/bin

/opt/mssql-tools/bin/*

/dbdata

/dbdata/*

/dblog

/dblog/*

進行簡單測試

note:safe-rm如想要全面防護,只能逐一新增每一路徑至safe-rm.conf

DBCC SQLPERF(LOGSPACE); 

GO

--@4種資料表類型耗用空間測試

drop table if exists dbo.Product_heap

drop table if exists dbo.Product_nonheap

drop table if exists dbo.Product_heapwithnonclusteredindex

drop table if exists dbo.Product_nonheapwithnonclusteredindex

--heap table

create table dbo.Product_heap

(

Serial int identity(1,1)

,[ProductID] int

,[Name] name

,[ProductNumber] nvarchar(100)

,[Color] nvarchar(100)

,[SafetyStockLevel] smallint

,[ReorderPoint] smallint

,[StandardCost] money

,[ListPrice] money

,[Size] nvarchar(100)

)

truncate table dbo.Product_heap

insert into dbo.Product_heap

select top 50000 [ProductID]

      ,p.[Name]

      ,[ProductNumber]

      ,[Color]

      ,[SafetyStockLevel]

      ,[ReorderPoint]

      ,[StandardCost]

      ,[ListPrice]

      ,[Size]

from [Production].[Product] p

cross join sys.all_objects

go

--non-heap table

create table dbo.Product_nonheap

(

Serial int identity(1,1)

,[ProductID] int

,[Name] name

,[ProductNumber] nvarchar(100)

,[Color] nvarchar(100)

,[SafetyStockLevel] smallint

,[ReorderPoint] smallint

,[StandardCost] money

,[ListPrice] money

,[Size] nvarchar(100)

,constraint PK_Product_nonheap primary key clustered(Serial)

)

truncate table dbo.Product_nonheap

insert into dbo.Product_nonheap

select top 50000 [ProductID]

      ,p.[Name]

      ,[ProductNumber]

      ,[Color]

      ,[SafetyStockLevel]

      ,[ReorderPoint]

      ,[StandardCost]

      ,[ListPrice]

      ,[Size]

from [Production].[Product] p

cross join sys.all_objects

go

--heap table with non-clustered index

create table dbo.Product_heapwithnonclusteredindex

(

Serial int identity(1,1)

,[ProductID] int

,[Name] name

,[ProductNumber] nvarchar(100)

,[Color] nvarchar(100)

,[SafetyStockLevel] smallint

,[ReorderPoint] smallint

,[StandardCost] money

,[ListPrice] money

,[Size] nvarchar(100)

,index IX_SafetyStockLevel_ListPrice(SafetyStockLevel,ListPrice)

)

truncate table dbo.Product_heapwithnonclusteredindex

insert into dbo.Product_heapwithnonclusteredindex

select top 50000 [ProductID]

      ,p.[Name]

      ,[ProductNumber]

      ,[Color]

      ,[SafetyStockLevel]

      ,[ReorderPoint]

      ,[StandardCost]

      ,[ListPrice]

      ,[Size]

from [Production].[Product] p

cross join sys.all_objects

go

--non-heap table with non-culstered index

create table dbo.Product_nonheapwithnonclusteredindex

(

Serial int identity(1,1)

,[ProductID] int

,[Name] name

,[ProductNumber] nvarchar(100)

,[Color] nvarchar(100)

,[SafetyStockLevel] smallint

,[ReorderPoint] smallint

,[StandardCost] money

,[ListPrice] money

,[Size] nvarchar(100)

,constraint PK_Orders_nonheapwithnonclusteredindex primary key clustered(Serial)

,index IX_SafetyStockLevel_ListPrice(SafetyStockLevel,ListPrice)

)

truncate table dbo.Product_nonheapwithnonclusteredindex

insert into dbo.Product_nonheapwithnonclusteredindex

select top 50000 [ProductID]

      ,p.[Name]

      ,[ProductNumber]

      ,[Color]

      ,[SafetyStockLevel]

      ,[ReorderPoint]

      ,[StandardCost]

      ,[ListPrice]

      ,[Size]

from [Production].[Product] p

cross join sys.all_objects

go

USE AdventureWorks2017

GO

EXEC sp_spaceused N'dbo.Product_heap';

GO

EXEC sp_spaceused N'dbo.Product_nonheap';

GO

EXEC sp_spaceused N'dbo.Product_heapwithnonclusteredindex';

GO

EXEC sp_spaceused N'dbo.Product_nonheapwithnonclusteredindex';

GO

@檢查清單

資料庫

• CPU使用率是否合理

• Memory使用量是否合理

• 連線數量是否合理

• 資料庫硬碟空間情況是否正常

• 資料庫備份是否正常

Port

• 系統提供服務的Port是否有開啟

• 外部服務存取系統的Port是否有開啟

虛擬化

• 主機(如exis)CPU資源是否使用正常

• 主機(如exis)Memory資源是否使用正常

• 主機(如exis)Storage資源是否使用正常

• 網路連接情況是否正常，掉封包情況是否嚴重

OS

• 歷史Log和tmp資料夾是否有清理

• 無用的package是否有移除

• 檢查Kernel是否有重大更新或漏洞修復

• 檢查Docker Container是否意外停止

• 檢查Log檔案中的內容，如/var/log/messages、/var/log/boot.log、/var/log/secure...等

下面清單項目是我個人實務認為必要的清單，而且合理設定警告通知可大幅降低DBA工作量。

每天

• Errorlog:檢查DB和OS上的錯誤紀錄檔案中的警告和錯誤訊息。

• HA、DR:檢查HA(如AlwaysON)和DR的錯誤紀錄檔案中的警告和錯誤訊息,同時建議設定通知。

• SQL Agent:檢查SQL Agent上的Job執行狀況。

• 備份:檢查個資料庫備份作業情況，並檢驗備份檔案有效性。

• 離峰作業排程:檢查離峰作業排程情況，如資料拋轉...等。
  

• 安全性:檢查帳號登入失敗情況，並確認非駭客攻擊。

• 長時間查詢:監控查詢執行時間太久的TSQL。

• 索引:監控索引使用情況，並適時提供Scan和Seek報表且合理清除無用索引。

• 封鎖:監控長時間封鎖問題。

• 死鎖:監控死鎖問題。

• 記憶體:監控記憶體資源是否足夠。

每周/每

• 備份:檢查過期備份檔案是否如預期清除，並透過備份檔案執行Restore Database測試。

• 更新:檢查SQL Server是否有重大更新或修正。

• 三大資源:檢查CPU、RAM和Disk三大資源，是否足夠應付未來一年甚至更長時間。

• 碎片:檢查索引碎片程度，並適時安排重新建置或組織。

• 帳號:檢查帳號安全性(密碼太簡單)，或是否有無用帳號須清除。

• 統計資料:檢查資料表有那些統計資料過時(超過3個月)，並適時安排更新作業。

• 資料封存:檢查歷史資料封存作業是否正常。

• SQL Server組態:檢查組態設定是否不正確或和其他環境不一致。

•