HoneyHoney: 2015

Query plan如何自动被清掉

Posted on Tuesday, December 29, 2015 by 醉·醉·鱼 and labeled under sql

引用: https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx
https://www.youtube.com/watch?list=PLoGAcXKPcRvanix7u9eqg_qt1tp849rX3&v=p-6ii2NiUI0

MSDN上面有这么一段关于plan cache的文章。大致就是说

当没有cached query plan 的时候，执行query就会创建一个query plan并被cache起来。
下面这些操作可以导致cached plan被清掉

SERVER RESTART/DATABASE LEVEL CHANGES/CONFIGURATION CHANGES
DBCC FREEPROCCACHE/DBCC FREESYSTEMCACHE/SP_RECOMPILE
DROP & RECREATE SP
Object changes，包括view，table，statistics
长时间没用

MSDN提到，SQL SERVER采用了基于COST的机制去清理query plan。如果当前没有内存压力，SQL SERVER 不会去清query plan。如果有内存压力，SQL SERVER回去验证每个query plan，并且降低他们的currect cost，直至0。一旦当它变成0并且内存压力来的时候，每次验证的同时就会清掉这个plan。如果在这之前这个query plan被用掉，那current cost又会被reset成最原来的cost，即不会被清掉。

Cron不能够正常启动

Posted on Monday, October 12, 2015 by 醉·醉·鱼 and labeled under cron

用ruby写了一个定期发邮件的脚本，然后又写了个sh去调这个脚本，在cron里面添加一个job定期调用这个sh文件，然后他就能够正常工作了。但是最近突然发现有时候他居然不能够发邮件了。我以为又是gmail被墙了，但是我上次已经改过host了，应该不会有问题了。而且还加了30分钟内retry的机制。

64.233.161.108 smtp.gmail.com

百思不得其解。在遇到N次这事之后，我就去看log，看邮件，看自己job的日志。突然发现一件事，不知道什么时候，cron的PID 会变成一个很小的number，然后一路往上升，直到下次又变成一个很小的number。一旦PID变成很小的number，第一个被启动的job就不会正常工作。

一般来说，cron的job被启动，会在cron.log里面有记录，在系统邮件(/var/mail)里面也有记录。如下图，在10月7日早上10点，有一个脚本被启动了，但是在系统邮件里面却没法找到对应的记录。在这个job之前和之后的job在系统邮件和cron.log里面都有记录相对应，唯独这条没有。同时，也可以看到，大概4个小时前，PID number由64348降成352。

我又找了几个例子，都能够对应我上面的结论：“一旦PID变成很小的number，第一个被启动的job就不会正常工作。”

现在我只能够在我需要跑的job前一分钟再添加一个job A，啥事都不做，只是单独占个坑，这样就算倒霉也就是这个job A没法正常启动。

这都是啥奇葩的事情。

利率调整对等额本息的影响

Posted on Sunday, July 19, 2015 by 醉·醉·鱼 and labeled under ruby , 贷款

其实，网上已经有很多计算器了，比如新浪的计算器就算还能够过去，只是有两点

计算不够精确。其实这个不能够怪新浪的计算器。实际上每个银行在计算每年1月份利率调整的方法各不一样。比如这篇博客里面解释了工商银行的计算方式，而我按照他的计算方式去算结果总是会有一些偏差。
局限性。他只能够计算到3个利率变化的情况，一般5年以上的贷款利率变化很有可能超过3个。

关于等额本息以及相关计算，请转百科和这篇博客

我按照我自己的案例，写了以下脚本，去看农行是如何计算1月份的本息和的。结果发现，农行在计算一月份利息的时候是按照通用分段计息的方法，但是本金却是继续延续上一轮利率计算的本金。比如，12月份的剩余本金是10000块，那么1月份的本金是按照上一年的利率算，利息又是分段计算，然后加起来算出本息和。此后2月份就正常了。

反正每年1月份都是算的31天的利率，基本上1月份都要比平时还得多。

require 'date'

# 贷款总额
total_loan_money = 50 * 10000
# 贷款年限
total_loan_month = 20 * 12
# 初始利率
initial_interest_rate = 4.7
# 第一次还贷时间
initial_pay_dt = '2011-05-08'
# 利率调整时间表
interest_rate_changes = [] 
interest_rate_changes << [initial_pay_dt, initial_interest_rate]
interest_rate_changes << ['2012-01-01', 4.9]
interest_rate_changes << ['2013-01-01', 4.5]
interest_rate_changes << ['2015-01-01', 4.25]
interest_rate_changes << ['2016-01-01', 3.5]

p interest_rate_changes

def averageInterest(n, money, rate, ini_pay_dt)
 list = []
 money_to_pay_per_month = (money * rate * (1 + rate) ** n /((1 + rate) ** n - 1)).round(2)

 n.times do |i|
  remaining_load_money = (i==0) ? money : list[i-1][4]
  interest_to_pay = (remaining_load_money * rate).round(2)
  load_money_to_pay = (money_to_pay_per_month - interest_to_pay).round(2)
  # [date, 本期利息，本期本金，本期本息，剩余本金]
  list << [ini_pay_dt.strftime('%Y-%m-%d'), interest_to_pay, load_money_to_pay,

money_to_pay_per_month, (remaining_load_money - load_money_to_pay).round(2)]

  ini_pay_dt = ini_pay_dt.next_month
 end
 list
end

def averageInterestAdjust(list, date, total_loan_month, rate1, rate2)
 tmp_list = []
 last_list = list[-2]
 money = last_list[4]
 rate1_per_day = rate1 / 30
 rate2_per_day = rate2 / 30
 ini_pay_dt = Date.parse(last_list[0])

 rate1_days = date - ini_pay_dt
 rate2_days = ini_pay_dt.next_month - date

 # 本月利息
 interest_to_pay = (money * (rate1_per_day * rate1_days +

rate2_per_day * rate2_days)).round(2)

 # 本月应还金额
 # 农业银行特色 应还本金还是按照上一年的利率计算的，但是利息是分段计算的
 money_to_pay = (interest_to_pay + list[-1][2]).round(2)

 tmp_list = [ini_pay_dt.strftime('%Y-%m-%d'), interest_to_pay,

(money_to_pay - interest_to_pay), money_to_pay,

money-(money_to_pay - interest_to_pay)]

 list[-1][1] = tmp_list[1].round(2)
 list[-1][2] = tmp_list[2].round(2)
 list[-1][3] = tmp_list[3].round(2)
 list[-1][4] = tmp_list[4].round(2)

 list
end

list = []
i = 0
ini_pay_dt = Date.parse(initial_pay_dt)

interest_rate_changes.each do |t|

 date = Date.parse(t[0])

 if ini_pay_dt == date
  i += 1
  next
 else
  diff = (date.year - ini_pay_dt.year) * 12 + (date.month - ini_pay_dt.month)
 end

 list += averageInterest(total_loan_month, total_loan_money,

    interest_rate_changes[i-1][1]/100.0/12.0, ini_pay_dt).slice(0, diff+1)

 total_loan_month -= (diff) 

 # 每年1月1日利率调整，按日计息
 list = averageInterestAdjust(list, date, total_loan_month,

    interest_rate_changes[i-1][1]/100.0/12.0, interest_rate_changes[i][1]/100.0/12.0)

 total_loan_month -= 1
 ini_pay_dt = Date.parse(list[-1][0]).next_month
 total_loan_money = list[-1][4]
 i += 1
end

list += averageInterest(total_loan_month, total_loan_money,

    interest_rate_changes[-1][1]/100.0/12.0, ini_pay_dt)

total_interest = 0

list.each do |t|
 p t
 total_interest += t[1]
end

puts total_interest

SQL SERVER NOT EXISTS in ActiveRecord

Posted on Wednesday, July 08, 2015 by 醉·醉·鱼 and labeled under activerecord , ruby , sql

最近在玩ActiveRecord和Sql server，发现ActiveRecord居然支持preparedstatement了。但有些功能还是不能支持，比如NOT EXISTS和CROSS APPLY。

比如，下面这段SQL就没有办法转换成为ActiveRecord

只是有些接近的方法

生成的SQL实际上是

用到了LEFT JOIN，而不是EXISTS。不过，还好是prepared statement。

或者直接用SQL

实际上面生成的SQL就不是prepared过的了。这样就不好了，就不好的利用cache plan了。比起LEFT JOIN 引起的side effect，我更加倾向用LEFT JOIN方式，至少能够cache plan，不用每次compile。

通过extended events来观察死锁

Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under deadlock , extended events , lock , sql

继续前一篇http://zhongxiao37.blogspot.com/2015/04/observing-locks-by-using-extended-events.html。如果我想要观察deadlock呢？

在SSMS > management > extented events > session 下面添加一个deadlock的event session，然后直接watch live data，这个时候就可以看到deadlock的信息和示意图了。

reader - writer deadlock

这个一个比较常见的读写死锁情况。第一个session在第一个表上拥有X锁，需要第二个表的S锁；第二个session在第二个表上有X锁，需要第一个表上的S锁。这样，这两个锁互相block，互相等待，直到SQL SERVER 选择一个session为victim并kill掉。

-- Session 1
BEGIN TRANSACTION

UPDATE children
SET name = 2
WHERE id = 10

-- Session 2
BEGIN TRANSACTION

UPDATE parents
SET name = 2
WHERE id = 10

-- Session 1
SELECT * FROM parents WHERE id = 10

-- Session 2
SELECT * FROM children WHERE id = 10

writer - writer deadlock

和前者比较相像只是S锁换成X锁而已。

-- Session 1
BEGIN TRANSACTION

UPDATE children
SET name = 2
WHERE id = 10

-- Session 2
BEGIN TRANSACTION

UPDATE parents
SET name = 2
WHERE id = 10

-- Session 1
UPDATE parents
SET name = 2
WHERE id = 10


-- Session 2
UPDATE children
SET name = 2
WHERE id = 10

key-lookup deadlock

这个死锁就比较特殊了。一个session是拥有X锁也要访问X锁，而另一个session是拥有S锁需要访问S锁。一般来说，S锁释放得都比较快，但是在并发的情况下就很容易发生了。下面的代码都会一直跑下去，但是跑不了多久死锁就会发生了。Session 1里面，UPDATE的column是value，刚好被non-clustered index给included掉，所以，在clustered index和non-clustered index上都会有X锁。而对于session 2来说，SELECT的WHERE clause是parent_id，则会先在non-clustered index上面拥有S锁，再继续在clustered index上面request S锁。在某一时刻，两者刚好各自拥有一个锁，request另一个锁，这时死锁就发生了。

-- Session 1
DECLARE @i int = 1

while 1 = 1
BEGIN
        UPDATE children
        SET value = @i
        WHERE id = 10

        SET @i = @i + 1
END

-- Session 2
DECLARE @i int

while 1 = 1
BEGIN

        SELECT @i = name
        FROM children
        WHERE parent_id = 10

END

引用
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/

采花

Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under photo

又一年春天，买了一盆玫瑰，想着栽在家里，不知道它什么时候会死去，只是希望它能够活得更久些。闲着没事在家，洒上点水，举起相机。在捏下快门的一瞬间，这个小家伙竟然跑进了我的镜头。

原图地址

通过extended events来观察锁

Posted on Thursday, April 30, 2015 by 醉·醉·鱼 and labeled under lock , sql

SQL SERVER 2008增加了这么一个新功能，叫EXTENDED EVENTS。非常非常地牛逼。我就用它来看看增删查改里面lock的behavior吧！

首先，创建测试表

USE [event_service]
GO

CREATE TABLE [dbo].[parents](
        [id] [bigint] NOT NULL,
        [name] [varchar](30) NULL,
        [value] [int] NULL,
 CONSTRAINT [pk_parents] PRIMARY KEY CLUSTERED
(
        [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

USE [event_service]
GO

CREATE TABLE [dbo].[children](
        [id] [bigint] NOT NULL,
        [name] [varchar](30) NULL,
        [parent_id] [bigint] NULL,
        [value] [int] NULL,
 CONSTRAINT [pk_children] PRIMARY KEY CLUSTERED
(
        [id] ASC
)

GO

ALTER TABLE [dbo].[children]  WITH CHECK ADD  CONSTRAINT [fk_children_parent_id] FOREIGN KEY([parent_id])
REFERENCES [dbo].[parents] ([id])
GO

ALTER TABLE [dbo].[children] CHECK CONSTRAINT [fk_children_parent_id]
GO

CREATE NONCLUSTERED INDEX [ix_children_parent_id] ON [dbo].[children]
(
        [parent_id] ASC
)
INCLUDE ([value])

INSERT INTO parents
SELECT n, n, n
FROM [dbo].[GetNums](100000)

INSERT INTO children
SELECT n, n, n, n
FROM [dbo].[GetNums](100000)

然后创建 event session。对于SQL SERVER 2012来说，可以通过UI界面来创建，这里为了方便直接通过TSQL来创建了。

CREATE EVENT SESSION [sql_locks_observer] ON SERVER
ADD EVENT sqlserver.lock_acquired(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(package0.event_sequence,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([database_id]=(5) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.lock_released(SET collect_database_name=(1),collect_resource_description=(1)
    ACTION(package0.event_sequence,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.request_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id)
    WHERE ([package0].[equal_uint64]([database_id],(5)) AND [sqlserver].[is_system]=(0)))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY=4096 KB)
GO

验证lock的query

; WITH locks AS (
SELECT
objlocks.value('@name', 'varchar(50)') operation_type,
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
        AS session_id,
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
        AS database_id,
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
        AS resource_0,
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
        AS resource_1,
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
        AS resource_2,
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
        AS plan_handle,
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
        objlocks.value('@timestamp', 'datetime')
                AS dt
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xest.target_name = 'ring_buffer' AND xes.name = 'sql_locks_observer'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_acquired"]') AS T(objlocks)

UNION ALL

SELECT
objlocks.value('@name', 'varchar(50)') operation_type,
objlocks.value('(action[@name="session_id"]/value)[1]', 'int')
        AS session_id,
    objlocks.value('(data[@name="database_id"]/value)[1]', 'int')
        AS database_id,
    objlocks.value('(data[@name="resource_type"]/text)[1]', 'nvarchar(50)' )
        AS resource_type,
    objlocks.value('(data[@name="resource_0"]/value)[1]', 'bigint')
        AS resource_0,
    objlocks.value('(data[@name="resource_1"]/value)[1]', 'bigint')
        AS resource_1,
    objlocks.value('(data[@name="resource_2"]/value)[1]', 'bigint')
        AS resource_2,
    objlocks.value('(data[@name="mode"]/text)[1]', 'nvarchar(50)')
        AS mode,
    objlocks.value('(action[@name="sql_text"]/value)[1]', 'varchar(MAX)')
        AS sql_text,
    CAST(objlocks.value('(action[@name="plan_handle"]/value)[1]', 'varchar(MAX)') AS xml)
        AS plan_handle,
    CAST(objlocks.value('(action[@name="tsql_stack"]/value)[1]', 'varchar(MAX)') AS xml)
        AS tsql_stack,
        objlocks.value('@timestamp', 'datetime')
                AS dt
FROM (
    SELECT CAST(xest.target_data as xml)
        lockinfo
    FROM sys.dm_xe_session_targets xest
    JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
    WHERE xes.name = 'sql_locks_observer' AND xest.target_name = 'ring_buffer'
) heldlocks
CROSS APPLY lockinfo.nodes('//event[@name="lock_released"]') AS T(objlocks)
)
SELECT *
FROM locks
--ORDER BY dt

好了，准备工作完毕，可以开始了。

SELECT中的lock

select name
from children
WHERE parent_id = 10

运行那段验证lock的query，可以看到

根据http://zhongxiao37.blogspot.com/2015/04/john-huangs-function-for-converting.html 里面提到的function，我们可以查到在non-clustered index上面有一个S lock，但是clustered index上面并没有Slock。按道理来说，在这两个index上面都应该有一个S lock。http://mashijie.blogspot.com/2012/11/observing-locking-behaviour-with.html 解释到，如果如果该page上面的数据距离上次访问以后并没有改动的话，就不需要加S lock，已减少额外的lock开销。

SELECT dbo.[ConvertedLockResource]('KEY',137,3591962880,1194138335)

SELECT * FROM event_service.sys.partitions
WHERE object_id = OBJECT_ID(N'event_service.dbo.children')

DBCC IND ('event_service', 'dbo.children', 2)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,590080,3)
DBCC TRACEOFF(3604)

UPDATE

用同样的方式，我们可以看UPDATE中的lock。在index和clustered index（其实就是数据页）上都有一个X lock。

UPDATE children
SET value = 13
WHERE id = 10

SELECT dbo.[ConvertedLockResource]('KEY',134,3976528128,2637997192)

DBCC IND ('event_service', 'dbo.children', 1)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,581150,3)
DBCC TRACEOFF(3604)

INSERT

这个时候会在children表的两个index上面有个X lock，同时在parent表上面有个S lock，即需要看parent表中是否有id为1000001的记录。验证一下，的确能够看到S lock是在第1000001条记录上。

INSERT children VALUES
(1000001, 1000001, 1000001, 1000001)

SELECT dbo.[ConvertedLockResource]('KEY',133,2650734848,1539397256)

SELECT * FROM event_service.sys.partitions
WHERE object_id = OBJECT_ID(N'event_service.dbo.parents')

DBCC IND ('event_service', 'dbo.parents', 1)

DBCC TRACEON(3604)
DBCC PAGE('event_service',1,585103,3)
DBCC TRACEOFF(3604)

DELETE

在clustered index和non-clustered index上面拥有X lock。

DELETE FROM children
where parent_id = 10

引用:
https://sqlscope.wordpress.com/2012/09/16/are-key-and-row-level-locks-always-acquired/
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
http://aboutsqlserver.com/2013/06/11/locking-in-microsoft-sql-server-part-18-key-lookup-deadlock/
http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588

John Huang's function for converting lock resources - ConvertedLockResource

Posted on Monday, April 20, 2015 by 醉·醉·鱼 and labeled under extended events , lock , sql

出处： http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/#more-588

在用extended events观察SQL SERVER lock行为的时候，resource_0, resource_1, resource_2 比较让人困惑，究竟是什么意思。John Huang的博客写了一个函数，方便我们去理解具体在那个object上面lock的行为。

USE [master]
GO

create function [dbo].[ConvertedLockResource](@ResourceType sysname, @res0 bigint, @res1 bigint, @res2 bigint)
returns varchar(60)
as
begin
        if @ResourceType = 'OBJECT'
                return cast(@res0 as varchar(20));
        else if @ResourceType in ('PAGE', 'EXTENT')
        begin
                return cast(@res1 as varchar(10)) + ':' + cast(@res0 as varchar(20))
        end
        else if @ResourceType = 'RID'
        begin
                return  cast(cast(cast(right(cast(@res1 as binary(8)),2) as binary(2)) as smallint) as varchar(10))+ ':'
                                + cast(@res0 as varchar(20))+':'
                                + cast(cast(cast(left(right(cast(@res1 as binary(8)),4), 2) as binary(2)) as smallint) as varchar(10))
        end
        else if @ResourceType = 'HOBT'
        begin
                return cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        else if @ResourceType = 'KEY'
        begin
                return  '('
                                + lower(convert( varchar(20),
                                                        cast(substring(cast(@res1 as binary(8)), 6, 1) as binary(1))
                                                        + cast(substring(cast(@res1 as binary(8)), 5, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),8, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),7, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),6, 1) as binary(1))
                                                        + cast(substring(cast(@res2 as binary(8)),5, 1) as binary(1))
                                                ,2))
                                +')/'
                                + cast(cast(
                                                        cast(right(cast(right(cast(@res1 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        +cast(0x0000 as binary(2))
                                                        + cast(right(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                        + cast(left(cast(right(cast(@res0 as binary(8)),4) as binary(4)), 2) as binary(2))
                                                as bigint)
                                        as varchar(20))
        end
        return null
end

GO

HoneyHoney

醉醉鱼·涂鸦志