記一次生產資料庫"意外"重啟的經歷

来源:https://www.cnblogs.com/smallSevens/archive/2018/12/05/10067324.html
-Advertisement-
Play Games

前言 在一個陽光明媚的下午，電腦右下角傳來一片片郵件提醒，同時伴隨著微信釘釘的震動，打開一看，應用各種出錯，天兔告警，資料庫伺服器記憶體爆紅，Mysql資料庫實例掛掉了。 排查 先交代一下資料庫版本： 崩潰故障排除絕不是一項有趣的任務，特別是如果MySQL沒有報告崩潰的原因。例如，當MySQL記憶體不足 ...


前言

在一個陽光明媚的下午，電腦右下角傳來一片片郵件提醒，同時伴隨著微信釘釘的震動，打開一看，應用各種出錯，天兔告警，資料庫伺服器記憶體爆紅，Mysql資料庫實例掛掉了。

排查

先交代一下資料庫版本：

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.22-22, for Linux (x86_64) using  6.2

Connection id:          59568
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c
Protocol version:       10

崩潰故障排除絕不是一項有趣的任務，特別是如果MySQL沒有報告崩潰的原因。例如，當MySQL記憶體不足時。

資料庫郵件告警提醒發來的消息：

Type: mysql
Tags: 生產主庫
Host: 172.16.1.66:3306
Level: critical
Item: connect
Value: down
Message: mysql server down

登錄 Grafana 監控面板，資料庫連接在哪個時間段曾有幅度的增長。

順手檢查一下之前的伺服器郵件監控告警記錄，上一個時間點，記憶體占用率99%，這說明瞭資料庫連接的幅度增長，可能是壓垮伺服器的最後一根稻草。

其實導致OOM的直接原因並不複雜，就是因為伺服器記憶體不足，內核需要回收記憶體，回收記憶體就是kill掉伺服器上使用記憶體最多的程式，而MySQL服務可能就是使用記憶體最多，所以就OOM了。

Type: os
Tags: 66資料庫
Host: 172.16.1.66:
Level: critical
Item: memory
Value: 99%
Message: too more memory usage

查看系統日誌

我們帶著這個疑問來排查一下日誌：

# 查看日誌
tail -500f  /var/log/messages
# 以下是 oom-killer
Nov 27 14:55:48 itstyledb1 kernel: mysqld invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0
Nov 27 14:55:48 itstyledb1 kernel: mysqld cpuset=/ mems_allowed=0-1
Nov 27 14:55:48 itstyledb1 kernel: CPU: 2 PID: 895 Comm: mysqld Kdump: loaded Not tainted 3.10.0-862.3.2.el7.x86_64 #1
Nov 27 14:55:48 itstyledb1 kernel: Hardware name: Huawei RH1288 V3/BC11HGSC0, BIOS 3.22 05/16/2016
Nov 27 14:55:48 itstyledb1 kernel: Call Trace:

小伙伴們繼續往下看：

0 pages HighMem/MovableOnly
Nov 27 14:55:48 itstyledb1 kernel: 291281 pages reserved
Nov 27 14:55:48 itstyledb1 kernel: [ pid ]   uid  tgid total_vm      rss nr_ptes swapents oom_score_adj name
Nov 27 14:55:48 itstyledb1 kernel: [  468]     0   468    28271     4326      62       55             0 systemd-journal
Nov 27 14:55:48 itstyledb1 kernel: [  490]     0   490    11492        2      24      553         -1000 systemd-udevd
Nov 27 14:55:48 itstyledb1 kernel: [  787]     0   787    13877       18      27       96         -1000 auditd
Nov 27 14:55:48 itstyledb1 kernel: [  810]    81   810    14552       81      34       89          -900 dbus-daemon
Nov 27 14:55:48 itstyledb1 kernel: [  815]     0   815    55956        1      60      466             0 abrtd
Nov 27 14:55:48 itstyledb1 kernel: [  816]     0   816    55327        9      64      346             0 abrt-watch-log
Nov 27 14:55:48 itstyledb1 kernel: [  818]     0   818   121607      220      90      495             0 NetworkManager
Nov 27 14:55:48 itstyledb1 kernel: [  822]     0   822     5415       49      16       33             0 irqbalance
Nov 27 14:55:48 itstyledb1 kernel: [  823]   997   823   134634       97      60     1306             0 polkitd
Nov 27 14:55:48 itstyledb1 kernel: [  825]     0   825     6594       42      20       41             0 systemd-logind
Nov 27 14:55:48 itstyledb1 kernel: [  830]     0   830    31578       28      21      139             0 crond
Nov 27 14:55:48 itstyledb1 kernel: [  839]     0   839    27522        2      10       31             0 agetty
Nov 27 14:55:48 itstyledb1 kernel: [ 1142]     0  1142   143454      114      97     2672             0 tuned
Nov 27 14:55:48 itstyledb1 kernel: [ 1144]     0  1144    28203       11      59      246         -1000 sshd
Nov 27 14:55:48 itstyledb1 kernel: [ 1145]     0  1145    97438      694     103      328             0 rsyslogd
Nov 27 14:55:48 itstyledb1 kernel: [ 1369]     0  1369    22526       20      44      256             0 master
Nov 27 14:55:48 itstyledb1 kernel: [ 1371]    89  1371    22596       32      46      251             0 qmgr
Nov 27 14:55:48 itstyledb1 kernel: [ 5140]     0  5140     5102     1617      15      239             0 mysqld_exporter
Nov 27 14:55:48 itstyledb1 kernel: [ 9430]     0  9430    55966      378      62      790             0 snmpd
Nov 27 14:55:48 itstyledb1 kernel: [30320]    27 30320 22951376 13928375   43437  8163662             0 mysqld
Nov 27 14:55:48 itstyledb1 kernel: [  688]    89   688    22552      271      46        0             0 pickup
Nov 27 14:55:48 itstyledb1 kernel: Out of memory: Kill process 30320 (mysqld) score 984 or sacrifice child
Nov 27 14:55:48 itstyledb1 kernel: Killed process 30320 (mysqld) total-vm:91805504kB, anon-rss:55713500kB, file-rss:0kB, shmem-rss:0kB
Nov 27 14:56:00 itstyledb1 systemd: mysqld.service: main process exited, code=killed, status=9/KILL
Nov 27 14:56:00 itstyledb1 systemd: Unit mysqld.service entered failed state.
Nov 27 14:56:00 itstyledb1 systemd: mysqld.service failed.
Nov 27 14:56:00 itstyledb1 systemd: mysqld.service holdoff time over, scheduling restart.
Nov 27 14:56:01 itstyledb1 systemd: Starting MySQL Server...

當out of memory發生時，out_of_memory函數會選擇一個內核認為犯有分配過多記憶體 “罪行”的進程，並殺死該進程。顯然 Mysql 就是哪個“罪人”。

隨後 MySql 會自動重啟。重啟以後，記憶體是下來了，但是臨近下班的時候，差不多又又又占滿了。

[root@itstyledb1 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:          55803       54976         241          10         585         349
Swap:         32064       25036        7028

找到MySql進程，執行以下top -p pid，記憶體使用52.4g

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
935 mysql     20   0   79.7g  52.4g   7336 S   0.3 96.1 255:44.76 mysqld

計算記憶體使用

1)查看MySQL全局占用多少記憶體

SELECT (@@innodb_buffer_pool_size
+@@innodb_log_buffer_size
+@@key_buffer_size) / 1024 /1024 AS MEMORY_MB;

查詢結果為：

+----------------+
| MEMORY_MB      |
+----------------+
| 20512.00000000 |
+----------------+

2）查看performance_schema占用多少記憶體

SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, sys.format_bytes(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;

查詢結果為：

+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/performance_schema | 349.80 MiB    |
+---------------------------+---------------+

3）查看每個線程占用多少記憶體

SELECT ( ( @@read_buffer_size
+ @@read_rnd_buffer_size
+ @@sort_buffer_size
+ @@join_buffer_size
+ @@binlog_cache_size
+ @@thread_stack
+ @@max_allowed_packet
+ @@net_buffer_length )
) / (1024*1024) AS MEMORY_MB;

查詢結果為：

+-----------+
| MEMORY_MB |
+-----------+
|   87.5156 |
+-----------+

查看當前線程

show full processlist

最終結果為：

+-----------+
| MEMORY_MB |
+-----------+
| 87.5156*37|
+-----------+

4）查看 memory 存儲引擎占用多少記憶體

SELECT SUM(max_data_length)/1024/1024 AS MEMORY_MB FROM information_schema.tables WHERE ENGINE='memory';

查詢結果為：

+---------------+
| MEMORY_MB     |
+---------------+
| 3857.37713909 |
+---------------+

以上四項加起來差不多也就27975MB，差不錯28G的樣子，但是 MySql 進程顯示占用了52.4G，那麼剩下24.4G去哪了？

線程池

此線程池非彼連接池，其實兩者是有很大區別的，連接池一般在客戶端設置，而線程池是在DB伺服器上配置；另外連接池可以取到避免了連接頻繁創建和銷毀，但是無法取到控制MySQL活動線程數的目標，在高併發場景下，無法取到保護DB的作用。比較好的方式是將連接池和線程池結合起來使用。

關於線程池的一些參數：

mysql> show variables like 'thread%';
+-------------------------------+---------------------------+
| Variable_name                 | Value                     |
+-------------------------------+---------------------------+
| thread_handling               | one-thread-per-connection |
| thread_pool_high_prio_mode    | transactions              |
| thread_pool_high_prio_tickets | 4294967295                |
| thread_pool_idle_timeout      | 60                        |
| thread_pool_max_threads       | 100000                    |
| thread_pool_oversubscribe     | 3                         |
| thread_pool_size              | 12                        |
| thread_pool_stall_limit       | 500                       |
+-------------------------------+---------------------------+
thread_handling：

該參數是配置線程模型，預設情況是one-thread-per-connection，也就是不啟用線程池。將該參數設置為pool-of-threads即啟用了線程池。

thread_pool_size：

該參數是設置線程池的Group的數量，預設為系統CPU的個數，充分利用CPU資源。

thread_pool_oversubscribe：

該參數設置group中的最大線程數，每個group的最大線程數為thread_pool_oversubscribe+1，註意listener線程不包含在內。

thread_pool_high_prio_mode：

高優先順序隊列的控制參數，有三個值（transactions/statements/none），預設是transactions，三個值的含義如下：

  • transactions：對於已經啟動事務的語句放到高優先順序隊列中，不過還取決於後面的thread_pool_high_prio_tickets參數

  • statements：這個模式所有的語句都會放到高優先順序隊列中，不會使用到低優先順序隊列

  • none：這個模式不使用高優先順序隊列

thread_pool_high_prio_tickets：

該參數控制每個連接最多語序多少次被放入高優先順序隊列中，預設為4294967295，註意這個參數只有在thread_pool_high_prio_mode為transactions的時候才有效果。

thread_pool_idle_timeout：

worker線程最大空閑時間，預設為60秒，超過限制後會退出。

thread_pool_max_threads：

該參數用來限制線程池最大的線程數，超過該限制後將無法再創建更多的線程，預設為100000。

thread_pool_stall_limit：

該參數設置timer線程的檢測group是否異常的時間間隔，預設為500ms。

最終配置如下：

#thread pool
thread_handling=pool-of-threads
#Group的數量，預設為系統CPU的個數，充分利用CPU資源
thread_pool_size=24
#每個group的最大線程數為thread_pool_oversubscribe+1
thread_pool_oversubscribe=3
performance_schema=off
#extra connection，防止線程池滿的情況下無法登錄MySQL
extra_max_connections = 8
extra_port = 33333

備註：線程池在Percona,MariaDB,Oracle MySQL企業版中提供，Oracle MySQL社區版並不提供。

線程池貌似並不會直接導致記憶體不回收，網上有說同時開啟Thread pool和PS會出現記憶體泄露，但是
目前Percona server 5.7.21-20+版本已經修複了這個問題，顯然是不存在的。

慢查詢

由於是生產環境，這個問題拖得時間有點長，那麼慢查詢會不會影響記憶體使用問題呢？帶著這個問題，查看了慢查詢後臺列表，在資料庫奔潰的前一個時間段，的確有不少慢查詢語句。但是這並不能在一定程度上說明問題，由於伺服器的 MySql 服務在殺死之前，記憶體已經見底，此時連接數並不多，也就三四十來個左右，大多處於休眠狀態，並且此時已經占用了大部分的Swap空間。也就是說，在資源有限的情況下必定會出現不少慢查詢語句。

小結

其實這個"意外"一點也不意外，其實已經發生了多次了。但是還是做個小結吧，因為最終沒有確認問題出現在哪裡，所以還是發佈了吧，萬一有專業的DBA遇到類似的問題還可以小小的解惑一下。

參考

https://bugs.mysql.com/bug.php?id=91861

https://bugs.mysql.com/bug.php?id=91710

https://dev.mysql.com/doc/refman/5.7/en/memory-use.html

https://www.percona.com/blog/2018/06/28/what-to-do-when-mysql-runs-out-of-memory-troubleshooting-guide/

https://dev.mysql.com/doc/refman/5.7/en/thread-pool-tuning.html


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 電腦傳輸層埠分類
    本文最早發佈於我的51CTO博客，目前已遷移至博客園。 參考： "port的英文維基百科" ) 簡介 MAC地址定義了數據包的下一跳地址。 IP地址定義了數據包最終應該傳輸到哪台電腦上。 而埠（port）則定義了數據包中的數據應該由電腦上的哪個進程來接收。 埠是一種邏輯上的概念，用來識別一個 ...
  • 監控生產線上伺服器的docker容器及主機
    1. 部署cadvisor容器，用來收集host上的容器信息，該容器部署在需要收集容器信息的每一個主機上部署； docker run -v /:/rootfs:ro -v /var/run:/var/run:rw -v /sys:/sys:ro -v /var/lib/docker:/var/lib ...
  • nRF52832 改變ATT_MTU提高藍牙數據發送速率（nRF5_SDK_14.2.0）
    nRF52832 作為一個低功耗藍牙晶元，其數據發送發送速率一直都偏低（高就不叫低功耗了^_^），作為初學者在網上找了很多資料，終於找到通過修改ATT_MTU來提升發送速率的方法，最快能達到8.2KB/s，現在就分享出來 ...
  • 系統時間、時區、字元集
    我們是中國人，所以通常在工作中我們的系統時間、時區、字元集會做相應調整 時區: #查看當前設置時區 cat /etc/timezone #列出所有時區 timedatectl list-timezones #設置時區 timedatectl set-timezone Asia/Shanghai 字元 ...
  • Linux 查看本機串口方法
    最近在瞭解嵌入式方面的知識，就隨筆記錄一下： 查看Linux本機串口： 1、查看串口是否可用 可以對串口發送數據比如對com1口，echo /dev/ttyS02、查看串口名稱使用 ls -l /dev/ttyS* 一般情況下串口的名稱全部在dev下麵，如果你沒有外插串口卡的話預設是dev下的tty ...
  • 命令：tr
    參考資料：https://www.thegeekstuff.com/2012/12/linux tr command/ 簡介 tr命令用於轉換、刪除或者去除重覆字元。它從STDIN中讀取數據並且將其寫入SDTOUT。 因此它的用法是這樣的。從用戶鍵入的STDIN中讀取。 或者這樣的。通過輸入重定向來 ...
  • 命令：less
    與more的區別 more在man手冊中的英文原文是文件熟讀過濾器（file perusal filter），其實可以理解為一種文本查看器。 它存在一些缺點： 必須事先載入完整個文件。因此在遇到大文件的時候，需要等待。 翻閱到文件尾部的時候自動退出。 man手冊也有說明more這個命令已經是比較遠古 ...
  • MySQL基礎知識
    創建資料庫 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類