f 九月 2020 ~ 迪貝之家

Microsoft SQL Server Log Shipping

SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。

Oracle GolGateden

機房搬遷跨Site能即時同步資料庫利器

Nagios 資料庫維運自動化

一開始設計這架構時,就排除使用remote agent的想法因為在專業分工的組織下,要求安裝新軟體於既有系統是一件不太可行的方案,既然身為DBA就只能把資料庫instance當作是一個最大的agent 想辦法在資料庫內做到我想做的事情

This is default featured post 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured post 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

MySQL Renumbering an Existing Sequence

 

看到這個段落時
我好訝異
因為我昨天才剛看過SQL SERVER Internal的介紹
有primary key的欄位是沒辦法drop的
禁不住開啟VM來做一下測試
先建表,塞資料
# insect.sql
# This requires the multi-row INSERT syntax
DROP TABLE IF EXISTS insect;
#@ _CREATE_TABLE_FULL_
#@ _CREATE_TABLE_PART_1_
CREATE TABLE insect
(
#@ _CREATE_TABLE_PART_1_
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id),
#@ _CREATE_TABLE_PART_2_
name VARCHAR(30) NOT NULL, # type of insect
date DATE NOT NULL, # date collected
origin VARCHAR(30) NOT NULL # where collected
);
#@ _CREATE_TABLE_PART_2_
#@ _CREATE_TABLE_FULL_
#@ _INSERT_EXPLICIT_
INSERT INTO insect (id,name,date,origin) VALUES
(NULL,'housefly','2014-09-10','kitchen'),
(NULL,'millipede','2014-09-10','driveway'),
(NULL,'grasshopper','2014-09-10','front yard'),
(NULL,'stink bug','2014-09-10','front yard');
#@ _INSERT_EXPLICIT_
#@ _INSERT_IMPLICIT_
INSERT INTO insect (name,date,origin) VALUES
('cabbage butterfly','2014-09-10','garden'),
('ant','2014-09-10','back yard'),
('ant','2014-09-10','back yard'),
('termite','2014-09-10','kitchen woodwork');
#@ _INSERT_IMPLICIT_
SELECT * FROM insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | grasshopper | 2014-09-10 | front yard |
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
| 7 | ant | 2014-09-10 | back yard |
| 8 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
8 rows in set (0.01 sec)
mysql> show create table insect;
+--------+-----------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------+
| insect | CREATE TABLE `insect` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 |
+--------+----------------------------------------------+
1 row in set (0.00 sec)
清掉一筆資料造成gap
mysql> delete from insect where id = 3;
Query OK, 1 row affected (0.02 sec)
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
| 7 | ant | 2014-09-10 | back yard |
| 8 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
7 rows in set (0.00 sec)
開始測試renumber sequence
mysql> ALTER TABLE insect DROP id;
Query OK, 7 rows affected (0.04 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> show create table insect;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| insect | CREATE TABLE `insect` (
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE insect
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
-> ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | stink bug | 2014-09-10 | front yard |
| 4 | cabbage butterfly | 2014-09-10 | garden |
| 5 | ant | 2014-09-10 | back yard |
| 6 | ant | 2014-09-10 | back yard |
| 7 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
7 rows in set (0.00 sec)
mysql> show create table insect;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| insect | CREATE TABLE `insect` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------+
1 row in set (0.00 sec)
清掉一筆資料造成gap
mysql> delete from insect where id = 3;
Query OK, 1 row affected (0.01 sec)
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 4 | cabbage butterfly | 2014-09-10 | garden |
| 5 | ant | 2014-09-10 | back yard |
| 6 | ant | 2014-09-10 | back yard |
| 7 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
6 rows in set (0.00 sec)
用組合語法測試renumber sequence
mysql> ALTER TABLE insect
-> DROP id,
-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from insect;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | cabbage butterfly | 2014-09-10 | garden |
| 4 | ant | 2014-09-10 | back yard |
| 5 | ant | 2014-09-10 | back yard |
| 6 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+
6 rows in set (0.00 sec)
mysql> show create table insect;
+--------+------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------+
| insect | CREATE TABLE `insect` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`date` date NOT NULL,
`origin` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 |
+--------+-----------------------------------------------+
1 row in set (0.00 sec)
不過我想有被foreign key參照的話,應該就不能這樣做了吧~~
重點是書上還寫了這一段
我就覺得更神了
MySQL permits multiple actions to be done with ALTER TABLE (something not true for
all database systems). However, notice that this multiple-action statement is not simply
a concatenation of the two single-action ALTER TABLE statements. The difference is that
it is unnecessary to reestablish the PRIMARY KEY: MySQL doesn’t drop it unless the indexed
column is missing after all the actions specified in the ALTER TABLE statement
have been performed.
也就表示
drop 跟add兩個動作一起做的指令
是不會重建索引的
wow...........
這麼厲害.....

善用Pagbadger 報表跟開發單位溝通的一個案例

 

26號接到一封mail
說是25號晚上某個趟次的job跑的時間比平時久
...........................
只好上系統看log
手動目視我就覺得看不到有什麼異狀
然後去grep "duration:" 這個字串
靠....那來的顯著的long query
好吧~~ 就去跑了24號及25號的pgbager報表
pgbadger
看到數據後,mail給他們leader
我就問啦~~
為什麼25號的這個查詢執行次數
是24號的2倍
還好...這個leader算理性
她告知25號處理的資料是24號的2倍
我就說啦~~
這樣子的話,那25號的執行時間會比較久也是正常的囉
至少還能溝通....
我就說嘛~~
管理postgres 怎麼可能不用工具
不用工具,你怎麼跟人家溝通
這系統也是從最近離職同仁手上接來的
auto_explain及pg_stat_statements都沒預載
所以看到這個case的時候
心裡就在想....
該怎麼debug ???
Postgres的管理機制畢竟沒辦法跟Oracle比
人家一個差異報表程式一跑
我就可以看結果了
Postgres就是得人工比對
這就是opensource與商用軟體的差異

Tibero JDBC

 

剛參加了tibero 新加坡的web seminar
我是覺得沒什麼
因為他們講得我都知道
不過還是跟他們確認了一件事情
評估版是可以建置TSC
我之前測不出來
這個webnar對我有價值的在於
它秀了hibernate裏頭
有關jdbc的設定
因為我不寫java
Tibero JDBC

XACT_ ABORT 如何影響t-sql 交易

 

當我在書中看到它的描述時
我就覺得看不懂
只好去查了一下internet
照著case做一遍
哈..........
原來..我這麼不了解SQL Server呀~~
wow...還真是有點丟人現眼了
因為一直以來的認知是
大家都是承襲Oracle的transaction概念來的
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.
看了微軟的文件
我還是覺得疑惑
什麼叫做視錯誤的嚴重程度來決定
是否進行整個trasaction的rollback
恩......
好吧~~以後再觀察

挖操.....
至少以後跟AP 談程式邏輯時
我不會被吐槽
真是...
一個DBA被吐槽這個
很丟臉ㄝ
雖然它的設定預設是OFF
不過如果要查看它的值
好像不太容易ㄝ
它是session的屬性
SSMS畫面在這兒
SSMS session property
用t-sql 顯示設定可不太容易
得透過bit的運算來顯示
Internet上有高手給了一段t-sql查詢
總得改一下,不然改天因智財被告
就真的好玩了
不過我也只不過是個私人部落格的文章
是能提出什麼經濟損失來跟我求償
更何況跨國打官司
哈~~ 人家又不是白癡...
國外的律師多貴阿.......
快被法務教育影片給搞瘋了
我還是只挑幾個option來幫助我記憶就好
有興趣的,請看連結
DECLARE @session_sets INT
SELECT @session_sets = @@options
PRINT @session_sets
IF ( (32 & @session_sets) = 32 )
PRINT 'ANSI_NULLS'
IF ( (64 & @session_sets) = 64 )
PRINT 'ARITHABORT'
PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @session_sets) = 8192 )
PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @session_sets) = 16384 )
PRINT 'XACT_ABORT'
Determining SET Options for a Current Session in SQL Server

relation "xxxxxx" page 910848 is uninitialized --- fixing

 

在某個資料庫裡頭
手動vacuum某個表
常發生這個問題
2020-09-21 10:45:33 CST [6755] : [150279-1] WARNING: 01000: relation "test" page 910848 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150281-1] WARNING: 01000: relation "test" page 910849 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150283-1] WARNING: 01000: relation "test" page 910850 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150285-1] WARNING: 01000: relation "test" page 910851 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150287-1] WARNING: 01000: relation "test" page 910852 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150289-1] WARNING: 01000: relation "test" page 910853 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150291-1] WARNING: 01000: relation "test" page 910854 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150293-1] WARNING: 01000: relation "test" page 910855 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150295-1] WARNING: 01000: relation "test" page 910856 is uninitialized --- fixing
2020-09-21 10:45:33 CST [6755] : [150297-1] WARNING: 01000: relation "test" page 910857 is uninitialized --- fixing
之前一直沒時間找root cause
今天翻找了一下google
大概都是bulk insert 所造成的空間配置問題
之前一直沒時間找root cause
今天翻找了一下google
大概都是bulk insert 所造成的空間配置問題
可能的root cause有兩個
原因 1 :
These could be pages that were allocated
to put new tuples into, but the crash
happened before the inserting transaction
committed
原因 2 :
edb loader direct load 會先要求配置額外的空間
奇怪~~ 不曉得有沒有指令讓dba執行
來先把空間配出去
我管postgres這麼久了
好像還沒看到這個指令
對照了系統記錄
我原本是找看看有沒有abort
或者err字串的
結果都沒有
靠~~~
後來直接拿表格名稱去找
mmmmm................
真是edb loader所造成的
EDB*Loader
應該是我前公司的同仁
好多年前在這邊做的專案
把edb loader用java包起來執行
沒有解決之道
就是vacuum時
讓它跑完
把沒真正去要到的空間
去給要出來

MySQL sql_mode的差異

 

我的mysql 版本
為何提版本
結語會告知
我們來看預設的sql_mode參數值

sql_mode
我們來建測試表
塞資料進行測試
mysql 預設是不會讓你把error data塞進資料庫的
第一個欄位,data非數字的部分被自動忽略
第二個欄位,資料過長,自動幫你truncate
第三個欄位,時間無法成功轉換,幫你自動轉換為0000-00-00ㄝ
sql_mode有一個群組設定叫做traditional
我們來把它設上去
sql_mode=TRADITIONAL
再塞一次資料進行測試
STRICT_ALL_TABLES
其實最主要的是STRICT_ALL_TABLES
因為它會要求MySQL validate data
那Traditional與原本欲設有何差異?
我比對過囉~~
差在 ONLY_FULL_GROUP_BY
一般來說,group by的欄位一定要與select 的欄位相符
在mysql不設上這參數
select column list隨你帶ㄝ....
這在其他的資料庫裡頭是不可能發生的
我一開始為何提版本
因為書上居然說
資料檢核要在MySQL 5.7.4才開始轉為預設
wow.....
想說在104網站上頭看到mysql 的給薪超過10萬
不會就是這個原因吧~~
哈~~~~

Nagios Monitor SQL Server Space Usage

 

I received a space notification at 16:36 and ignored it due to being occupied by other task.
Nagios Notification
The next morning, the critical situation was still not resolved.
After checking the backup records in system log,
I was sure txn log backup was performed every 2 hours.
OK.....mmmmmm.....I thought I knew what happened.
In my experience, you could not find any issued session with whoisactive tool.
Lets try "dbcc opentran"
Here was the output :
dbcc opentran
See.....session id 56 was the root cause.
I had to know which login owned the session.
Therefore, check the exec DMV
dm_exec_sessions
Oh....belonged to AP
Mail to their leader for a killing session request.
After their negotiations, I got the permission.
kill session
The log space returned after NBU finishing its 10:00 backup job.
Ha....Ha.....
That's my story!!

SQL Server 交易記錄檔快爆了ㄝ

 
昨天快下班的時候
接到了Nagios 的空間警訊
看了一下內容
沒理它,想說明天再來處理
Nagios Notification
今天進資料庫查看系統記錄
每兩個小時會做一次交易記錄檔備份
空間還沒清掉??
那就是有transaction沒關掉
依照我以前的經驗
whoisactive 是看不到的~~
還好...最近我很用功
還有一招,就是dbcc opentran
哈~~~
果然..沒讓我失望
dbcc opentran
session id 56有問題
當然就去對應了session的DMV
找出相關的資訊囉~~
要告訴AP團隊
來這是你們的帳號
dm_exec_sessions
砍了之後,剛好10:00有一趟次備份
空間就放出來囉~~

Nagios 監控 AWS SQL Server RDS Slow Query 的實務案例

 

因為這個系統特殊阿
是大大大大老闆說要做的系統
所以沒辦法,只好特別關注它
這是Nagios發出執行時間超過2秒的警訊
Nagios Notification
標紅色字眼就是我在Plugin程式裡
額外帶出要進行debug的訊息
232就是session id
看到之後,當然就只好去查看whoisactive的歷史資料
整理之後,就發了這個mail













AP Leader至少做了正面回應
他說他也被反應CPU衝高
已經要求停下當下正在做的Job

Nagios 監控 AWS SQL Server RDS 的實務案例

 

我也不曉得為什麼sql server rds會自己發生副本切換
管它的..反正我就mail發出去就對了
這是mail alert
Nagios Notification
這是rds的server log 的內容
2020-09-03 00:00:40.980 spid45s This instance of SQL Server has been using a process ID of 3924 since 5/22/2020 7:39:08 AM
2020-09-03 04:04:39.060 spid40s Error: 1479, Severity: 16, State: 1.
2020-09-03 04:04:39.060 spid40s The mirroring connection to "TCP://rdspartner:1120" has timed out for database "rdsadmin"
2020-09-03 04:04:39.060 spid40s Database mirroring is inactive for database 'rdsadmin'. This is an informational message o
2020-09-03 04:04:39.110 spid33s Error: 1479, Severity: 16, State: 1.
2020-09-03 04:04:39.110 spid33s The mirroring connection to "TCP://rdspartner:1120" has timed out for database "steven" af
2020-09-03 04:04:39.110 spid33s Database mirroring is inactive for database 'steven'. This is an informational message onl
2020-09-03 04:04:39.630 spid40s Database mirroring is active with database 'rdsadmin' as the principal copy. This is an in
2020-09-03 04:04:39.820 spid63s Error: 1479, Severity: 16, State: 1.
2020-09-03 04:04:39.820 spid63s The mirroring connection to "TCP://rdspartner:1120" has timed out for database "testdb" aft
2020-09-03 04:04:39.820 spid63s Database mirroring is inactive for database 'testdb'. This is an informational message only
2020-09-03 04:04:39.930 spid33s Database mirroring is active with database 'steven' as the principal copy. This is an info
2020-09-03 04:04:51.290 spid63s Database mirroring is active with database 'testdb' as the principal copy. This is an infor