背景

每一年都進行大促前壓測，每一次都需要再次關注到一些基礎資源的使用問題，訂單中心這邊資料庫比較多，最近頻繁報資料庫異常，所以對資料庫一些設定問題也進行了研究，本文給出一些常見的資料庫設定，說明這些設定對我們資料庫使用的影響。目前，MySQL伺服器端設定對使用方來說是不可更改的，需要聯絡DBA進行操作。這些設定操作對我們來說是一個黑盒，但是瞭解核心設定可以幫助我們快速定位資料庫問題原因。

問題彙總

問題一、too many connections

資料庫伺服器端設定：max_connections
這個問題我們這邊線上遇到過，對於同一個資料庫，有多個系統都連線了資料庫，導致連線資料庫的機器比較多，在資料庫qps比較大時，建立的連線數比較大，導致連線的總數超過了資料庫伺服器端連線的限制閾值，從而報了這個錯誤。

舉個栗子：如果max_connections設定為1000，我們這邊有200臺機器，每臺機器最大連線數為20，在連線比較大時，可能大致連線的總數為200 * 20 = 4000 > 1000，超過資料庫的限制。

下面讓我們在本地演示一下這種錯誤：

首先查詢當前伺服器端最大連線數：

如果這個引數太大，不好演示的話，可以通過如下引數，將這個數值改小些

下面通過使用者端嘗試連線資料庫，可以看到，直接報錯了

對於這種問題有兩種解決辦法：
第一種：聯絡DBA將max_connections設定的大一些，DBA之前反饋max_connections這個引數有自動增長的邏輯;
第二種方法：如果資料庫操作qps並不是很大，可以將每臺機器的資料庫連線最大值設定小一些，如果設定了初始化連線大小，要考慮機器數的增長，隨著機器數的增長，連線的總數肯定會遞增的。

問題二、慢紀錄檔長時間執行導致服務不可用

資料庫庫伺服器端設定：max_execution_time
之前寫了一篇文章聊了一下如何在使用者端設定引數解決慢紀錄檔長時間執行問題，這個在本地驗證是沒有問題的，但是由於我們線上環境使用的是JED，JED的架構多了中間代理層，在使用者端執行KILL QUERY CONNECTION_ID會提示失敗，導致沒法停止慢sql（這個好坑，據說JED後期會優化這個問題）。

既然目前使用者端沒法控制慢sql停止，從官網上看了一下mysql伺服器端的設定引數，發現有一個引數能夠控制伺服器端主動超時停止sql，引數變數：max_execution_time，本地環境驗證如下：

首先將sql執行超時時間設定為2s：

然後執行一個sleep函數，讓執行時間達到10s，可以看出來執行直接中斷了，因為超過了2s的最大超時時間：

問題三、伺服器端連線都斷開了，但是使用者端還用無效連線傳送請求

資料庫庫伺服器端設定：wait_timeout
之前線上用的是mysql，通過mysql驅動包直連資料庫，資料庫伺服器端預設連線空閒時間是8小時，後來響應公司號召，將傳統的mysql切到了jed（底層也是mysql）, jed由於閘道器層的存在，使用者端是通過mysql驅動包跟閘道器層進行直連，閘道器這一層資料庫空閒連線超時時間僅僅10分鐘，當時在使用者端進行空閒連線探活時間超過10分鐘，導致資料庫報錯頻繁。現在已經找不到歷史的資料庫異常紀錄檔了，本地模擬了一下，驗證如下：

先將本地空閒連線超時設定為10s

驗證原始碼如下，讓兩條sql執行時間超過10s，可以發現第二次執行sql時執行報錯了

所以，如果換了資料來源，需要確認下伺服器端的空閒連線超時時間設定，免得設定的值和使用者端檢測空閒連線健康性檢測間隔不匹配，出現意料不到的結果。

注：我們這邊使用的是DBCP資料來源連線池，設定如下：

<bean id="abstractParallelProductWriteDataSource" class="org.apache.commons.dbcp.BasicDataSource" abstract="true" destroy-method="close" init-method="createDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="username" value="${db.online.write.username}" />
        <property name="password" value="${db.online.write.password}" />
        <property name="initialSize" value="3" />
        <property name="minIdle" value="3" /><!--最小連結數 -->
        <property name="maxIdle" value="3" /><!--最大連結數 -->
        <property name="maxActive" value="8" /><!--最大活躍連結數 -->
        <property name="maxWait" value="200" />
        <property name="validationQuery" value="select 1" />
        <property name="testOnBorrow" value="false" />
        <property name="removeAbandonedTimeout" value="10" />
        <property name="removeAbandoned" value="true" />
        <!-- 池中的連線空閒10分鐘後被回收,預設值就是30分鐘 -->
        <property name="minEvictableIdleTimeMillis" value="600000" />
        <!-- 每5分鐘執行一次空閒連線回收器 -->
        <property name="timeBetweenEvictionRunsMillis" value="300000" />
        <!--指明連線是否被空閒連線回收器(如果有)進行檢驗.如果檢測失敗,則連線將被從池中去除 -->
        <property name="testWhileIdle" value="true"/>
        <!--在每次空閒連線回收器執行緒(如果有)執行時檢查的連線數量，預設值是3 -->
        <property name="numTestsPerEvictionRun" value="5"/>
    </bean>

timeBetweenEvictionRunsMillis這個引數設定的是檢測空閒連線的間隔時間，如果伺服器端空閒連線10分鐘就斷開了，這個時間需要小於10分鐘。minEvictableIdleTimeMillis這個時間是判斷當前連線已經空閒了多久了，目前設定的是10分鐘。

其他關鍵設定彙總

1. thread_handling
   設定了伺服器端的執行緒處理模型，主要的值有no-threads、one-thread-per-connection、loaded-dynamically。其中no-threads表示同一時刻只能有一個連線被一個執行緒處理。one-thread-per-connection表示對於每一個連線請求都有一個執行緒來處理。loaded-dynamically是mysql的執行緒池模式，目前預設的是one-thread-per-connection，所以連線太多的話，也會導致建立的執行緒快速增加，消耗系統的資源。

2. slow_query_log
   用來控制是否列印慢紀錄檔，如果需要分析系統效能情況，可以開啟這個開關，進行慢紀錄檔分析。

3. profiling
   是否啟用sql查詢效能分析，類似於debug紀錄檔，線上環境需要關閉，比較耗效能，這個引數後面mysql版本會廢棄掉，現在還是可以先使用著，新的使用方式可以參考：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html。

由於這個引數線上是關閉著，只能讓DBA臨時幫忙查詢下分析結果，平常也沒咋用，感覺還是一個不錯的工具，分析結果類似下面截圖：

總結

mysql伺服器端設定太多，目前工作中主要接觸了上述這些設定，感覺還不錯的，在平常分析資料庫問題上能夠給予一定的幫助，大家也可以去多瞭解一下，更多的設定可以參考官方檔案：mysql伺服器端設定官網

作者：京東零售 姜昌偉

來源：京東雲開發者社群 轉載請註明來源