1、【啟盈國際】故障報告 【啟盈國際】sqlserver連接超時問題故障報告一、故障現(xiàn)象和定位2018-11-14收到啟盈國際客戶反饋云平臺上有個應(yīng)用偶發(fā)連接sqlserver數(shù)據(jù)庫超時，導(dǎo)致應(yīng)用無法及時讀寫，如下是客戶發(fā)送的告警短信信息：初步懷疑是Ceph性能問題或應(yīng)用程序參數(shù)配置不合理導(dǎo)致，故以這兩方面原因展開排查。二、問題處理過程1. 2018-11-14 測試Ceph存儲性能以校驗云平臺存儲性能狀況，創(chuàng)建一臺4C8G+100G云硬盤的Windows 2008 R2虛擬機，安裝fio 3.1，針對云硬盤做fio性能壓力測試，測試內(nèi)容包括：4k和8k隨機讀，隨機寫，混合讀寫，1M順序讀寫等，如

2、下是測試結(jié)果：測試場景配置Win2008-VM(20181114)IOPSBW(MB/s)LAT(ms)集群基本性能測試讀4K 隨機讀1numjob + 1iodepth10234193kB/smin=414, max=13093, avg=953.021numjob + 128iodepth25.3k104MB/smin=220, max=59791,avg=2852.174K 隨機讀寫1numjob + 1iodepth 讀6152522kB/smin=329, max=92307, avg=882.271numjob + 1iodepth 寫205840kB/smin=1031, max

3、=128866, avg=2129.821numjob + 128iodepth 讀17.0k69.8MB/smin=242, max=199523, avg=3116.681numjob + 128iodepth 寫569223.3MB/smin=828, max=227708, avg=5042.448K 隨機讀1numjob + 1iodepth10828871kB/smin=370, max=14660, avg=899.971numjob + 128iodepth24.4k200MB/smin=227, max=45291, avg=2969.418K 隨機讀寫1numjob + 1

4、iodepth 讀6135022kB/smin=359, max=79511, avg=903.671numjob + 1iodepth 寫2041673kB/smin=1142, max=35088, avg=2086.611numjob + 128iodepth 讀16.7k137MB/smin=253, max=227157, avg=3208.141numjob + 128iodepth 寫558545.8MB/smin=839, max=232450, avg=4924.941M 順序讀1numjob + 1iodepth305320MB/smin=2117, max=17588,

5、avg=3252.321numjob + 16iodepth10061056MB/smin=4, max=220, avg=15.781M 順序讀寫1numjob + 1iodepth 讀554582MB/smin=3, max=199, avg=20.781numjob + 1iodepth 寫184193MB/smin=6, max=168, avg=22.821numjob + 16iodepth 讀1numjob + 16iodepth 寫寫4K 隨機寫1numjob + 1iodepth3461420kB/smin=1041, max=66287, avg=2859.351numjo

6、b + 128iodepth16.5k67.7MB/smin=842, max=180632, avg=7297.108K 隨機寫1numjob + 1iodepth4793926kB/smin=1078, max=143464, avg=2061.581numjob + 128iodepth14.6k120MB/smin=871, max=205352, avg=8421.571M 順序?qū)?numjob + 1iodepth169178MB/smin=4, max=206, avg= 5.871numjob + 16iodepth282296MB/smin=22, max=192, avg=

7、56.36通過上述fio的測試結(jié)果來看，Ceph存儲并未有明顯的性能瓶頸，存儲的iops，帶寬，延遲均表現(xiàn)正常。2. 2018-11-15，ECStack版本升級至501-1-2版本，測試Ceph存儲性能，升級后存儲在讀方面性能有提升；重新上傳鏡像并將類型設(shè)置為Windows，與客戶溝通該重新部署應(yīng)用程序，問題依舊存在；3. 2018-11-16，排查應(yīng)用程序，收集應(yīng)用程序和sqlserver數(shù)據(jù)庫日志，sqlserver數(shù)據(jù)庫連接超時時，應(yīng)用程序報如下錯誤日志信息：11/12/2018 20:27:20,Server,未知,(c) Microsoft Corporation.11/12/20

8、18 20:27:20,Server,未知,Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft CorporationEnterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)11/12/2018 20:26:13,spid7s,未知,SQL Trace was stopped due to server shutdown. Trac

9、e ID = 1. This is an informational message only; no user action is required.11/12/2018 20:26:12,spid11s,未知,The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.11/12/2018 20:26:12,spid11s,未知,Error: 1

10、7054 Severity: 16 State: 1.11/12/2018 20:26:12,spid11s,未知,Service Broker manager has shut down.11/12/2018 20:26:11,服務(wù)器,未知,SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.11/12/2018 20:19:17,spid69,未知,Autogrow of file CebAiderD

11、B in database CebAiderDB was cancelled by user or timed out after 30000 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.從日志分析可知，庫文件CebAiderDB自動增長文件時出現(xiàn)了超時導(dǎo)致連接被客戶端取消，聯(lián)系客戶獲取數(shù)據(jù)庫CebAiderDB參數(shù)設(shè)定，其內(nèi)容如下：從設(shè)定可知，CebAiderDB行數(shù)據(jù)內(nèi)容初始大小為10243MB，當(dāng)數(shù)

12、據(jù)空間寫滿之后會重新申請存儲空間，每次增量值為10G，在這個過程中應(yīng)用會向Ceph里面申請數(shù)據(jù)空間，需要較長的時間才能完成空間的申請，而應(yīng)用超過30s，應(yīng)用無法網(wǎng)數(shù)據(jù)庫中執(zhí)行sql操作，連接會自動超時退出，如下為應(yīng)用連接超時的日志：2018-11-12 20:06:00.511 下載線程4 E_CHN_KJT 獲取MQ隊列消息失敗：System.Data.SqlClient.SqlException (0 x80131904): Timeout 時間已到。在操作完成之前超時時間已過或服務(wù)器未響應(yīng)。語句已終止。 System.ComponentModel.Win32Exception (0 x8

13、0004005): 等待的操作過時。根據(jù)微軟官方建議：使用合理的小的固定增量 (64 MB-256 MB) 限制數(shù)據(jù)庫自動增長，參考： HYPERLINK /zh-cn/azure-stack/user/azure-stack-sql-server-vm-considerations /zh-cn/azure-stack/user/azure-stack-sql-server-vm-considerations，通過和客戶溝通，將數(shù)據(jù)空間設(shè)定為1024M觀察數(shù)據(jù)空間增長對應(yīng)用的影響，經(jīng)過一段時間的觀察和日志的分析，應(yīng)用沒有出現(xiàn)連接超時的報錯信息，設(shè)定內(nèi)容如下：經(jīng)過一段時間的分析和觀察，最終將自增量值設(shè)置為512M，應(yīng)用程序運行良好，穩(wěn)定，已在平臺平穩(wěn)運行3天(11.16-11.19)，未發(fā)生連接超時現(xiàn)象。三、原因分析通過日志的分析結(jié)合環(huán)境的設(shè)定，導(dǎo)致應(yīng)用連接sqlserver數(shù)據(jù)庫連接超時的原因是由于sqlserver數(shù)據(jù)庫CebAiderDB行數(shù)據(jù)增量大小設(shè)置過大導(dǎo)致，從而造成申請數(shù)據(jù)空間時需要消耗較長的時間，致使應(yīng)用程序連接超時。四、整改措施及建議整改措施： 將CebAiderDB數(shù)據(jù)庫行數(shù)據(jù)增量空間設(shè)置為1024MB并觀察應(yīng)用程序連接情況。建議措施： 1. 建