MySQL服務端配置對使用方來說是不可更改的，需要聯繫DBA進行操作。這些配置操作對我們來說是一個黑盒，但是瞭解核心配置可以幫助我們快速定位資料庫問題原因。 ...
背景
每一年都進行大促前壓測，每一次都需要再次關註到一些基礎資源的使用問題，訂單中心這邊資料庫比較多，最近頻繁報資料庫異常，所以對資料庫一些配置問題也進行了研究，本文給出一些常見的資料庫配置，說明這些配置對我們資料庫使用的影響。目前，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分鐘。
其他關鍵配置彙總
-
thread_handling
配置了服務端的線程處理模型，主要的值有no-threads、one-thread-per-connection、loaded-dynamically。其中no-threads表示同一時刻只能有一個連接被一個線程處理。one-thread-per-connection表示對於每一個連接請求都有一個線程來處理。loaded-dynamically是mysql的線程池模式，目前預設的是one-thread-per-connection，所以連接太多的話，也會導致創建的線程快速增加，消耗系統的資源。 -
slow_query_log
用來控制是否列印慢日誌，如果需要分析系統性能情況，可以打開這個開關，進行慢日誌分析。 -
profiling
是否啟用sql查詢性能分析，類似於debug日誌，線上環境需要關閉，比較耗性能，這個參數後面mysql版本會廢棄掉，現在還是可以先使用著，新的使用方式可以參考：https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html。
由於這個參數線上是關閉著，只能讓DBA臨時幫忙查詢下分析結果，平常也沒咋用，感覺還是一個不錯的工具，分析結果類似下麵截圖：
總結
mysql服務端配置太多，目前工作中主要接觸了上述這些配置，感覺還不錯的，在平常分析資料庫問題上能夠給予一定的幫助，大家也可以去多瞭解一下，更多的配置可以參考官方文檔：mysql服務端配置官網
作者：京東零售 薑昌偉
來源：京東雲開發者社區 轉載請註明來源
