背景

我們提供了容器化的mysql給業(yè)務(wù)組使用， 容器化的意義在于技術(shù)棧統(tǒng)一，提供了標(biāo)準(zhǔn)的賬號權(quán)限體系、配置文件優(yōu)化、定時備份、主從切換、指標(biāo)監(jiān)控、異常告警。

這次的問題就出在配置文件優(yōu)化上，以下是我們提供在2核4G環(huán)境下的一個模板，參考了一些mysql調(diào)優(yōu)的案例和文章

  [mysqld]
    character_set_server            = utf8mb4
    datadir                         = /var/lib/mysql
    expire_logs_days                = 1
    explicit_defaults_for_timestamp = 1
    general_log                     = ON
    general_log_file                = /var/log/mysql/general_log_file.log
    innodb_buffer_pool_size         = 2G
    innodb_flush_log_at_trx_commit  = 2
    innodb_flush_method             = O_DIRECT
    innodb_flush_neighbors          = 0
    innodb_io_capacity              = 1000
    innodb_io_capacity_max          = 2000
    innodb_large_prefix             = 1
    innodb_lock_wait_timeout        = 30
    innodb_print_all_deadlocks      = 1
    innodb_thread_concurrency       = 4
    join_buffer_size                = 20M
    log-error                       = /var/log/mysql/error.log
    log_queries_not_using_indexes   = 0
    log_slow_admin_statements       = 1
    log_slow_slave_statements       = 1
    log_timestamps                  = system
    long_query_time                 = 10
    max_connect_errors              = 10
    pid-file                        = /var/run/mysqld/mysqld.pid
    read_rnd_buffer_size            = 8388608
    slow_query_log                  = 1
    slow_query_log_file             = /var/log/mysql/slow_query_log_file.log
    socket                          = /var/run/mysqld/mysqld.sock
    sort_buffer_size                = 4194304
    tmp_table_size                  = 67108864
    wait_timeout                    = 600
    binlog-ignore-db                = mysql
    enforce-gtid-consistency        = ON
    gtid-mode                       = ON
    log-bin                         = mysql-bin
    log-slave-updates               = ON
    innodb_log_file_size            = 256M
    lower_case_table_names          = 1
    max_connections                 = 512
    server-id                       = 1

問題描述

原來的數(shù)據(jù)庫在虛擬機上運行，8核16G，需要遷移到容器上來， 在遷移測試的過程中， 突然發(fā)現(xiàn)所有的表都打不開了，執(zhí)行 show full processlist發(fā)現(xiàn)有四個視圖語句在執(zhí)行，詢問得知數(shù)倉會在這個時間抽數(shù)，并發(fā)執(zhí)行這幾個視圖語句，但是之前在虛擬機上跑是沒問題的

排查

磁盤

剛開始以為是磁盤的問題，容器化的mysql，磁盤是掛載的pv、pvc，用的ceph的這一套分布式存儲，通過ceph的性能測試和控制面板看起來是沒有任何問題的，我們每天上億的日志采集走的也是ceph，但是為了寧殺錯不放過，將容器化的mysql磁盤掛載到了虛擬機上的固態(tài)硬盤， 還是沒有解決這個問題

網(wǎng)絡(luò)

磁盤沒有問題，從網(wǎng)絡(luò)入手，視圖的查詢，走的是k8s的nodeport，懷疑可能是網(wǎng)絡(luò)傳輸過程中丟包導(dǎo)致的，于是將視圖的查詢搬到了容器內(nèi)，走容器內(nèi)部的service網(wǎng)絡(luò)，結(jié)果還是沒有解決

cpu、內(nèi)存

磁盤和網(wǎng)絡(luò)都沒有問題，懷疑可能是docker的cpu和內(nèi)存的資源加載的不及時？這是之前的配置

      resources:
        limits:
          cpu: '4'
          memory: 4000Mi
        requests:
          cpu: 100m
          memory: 1000Mi

于是將request和limit調(diào)整一致，均為4核4G，結(jié)果還是沒有解決

mysql指標(biāo)

連接數(shù)、鎖表都很正常

mysql性能分析

好吧，從mysql自身入手， 通過開啟show profiles，觀察下一個簡單的查詢語句是阻塞到哪里了，結(jié)果是sending data

當(dāng)執(zhí)行一個查詢時，MySQL會按照查詢計劃逐步執(zhí)行不同的階段。當(dāng)MySQL完成了讀取數(shù)據(jù)的階段（例如掃描表、使用索引等），并且需要將結(jié)果發(fā)送給客戶端時，就會出現(xiàn)"Sending data"階段

好吧，到這無果了，對比一下和遷移前的差距，問題可能還是在配置文件上，于是去逐個閱讀配置文件背后的原理，終于找到了一條可疑的配置

innodb_thread_concurrency=4

這個配置大概意思是限制了innodb并發(fā)的線程為4，當(dāng)有四個線程正在并發(fā)執(zhí)行查詢或者事務(wù)的時候，其他的sql想要執(zhí)行，就會放到一個等待隊列，等待前面的線程釋放資源后，才會獲得一個執(zhí)行線程去執(zhí)行，等待隊列按照FIFO的策略來執(zhí)行， 這一點和java的線程池倒是設(shè)計的一樣

之前為什么可以？

之前是使用的默認(rèn)值，默認(rèn)值為0

當(dāng)innodb_thread_concurrency參數(shù)設(shè)置為0時，表示未啟用并發(fā)度限制，InnoDB存儲引擎會根據(jù)系統(tǒng)負(fù)載和資源情況自動評估并發(fā)度。

InnoDB會根據(jù)以下幾個因素來評估并發(fā)度：

硬件資源：InnoDB會檢測系統(tǒng)的硬件資源情況，包括CPU核心數(shù)、內(nèi)存大小和磁盤I/O能力。根據(jù)可用的資源情況，它會決定是否創(chuàng)建新的執(zhí)行線程。

系統(tǒng)負(fù)載：InnoDB會監(jiān)視當(dāng)前的系統(tǒng)負(fù)載，包括并發(fā)查詢和事務(wù)數(shù)量。如果系統(tǒng)負(fù)載較低，InnoDB可能會創(chuàng)建更多的執(zhí)行線程來并發(fā)執(zhí)行查詢和事務(wù)。反之，如果系統(tǒng)負(fù)載較高，InnoDB可能會推遲或拒絕創(chuàng)建新的執(zhí)行線程，以避免資源爭用和性能下降。

動態(tài)調(diào)整：InnoDB會根據(jù)實時的負(fù)載情況和資源利用率進行動態(tài)調(diào)整。它會監(jiān)控執(zhí)行線程的活動情況，并根據(jù)需要增加或減少執(zhí)行線程的數(shù)量，以適應(yīng)系統(tǒng)負(fù)載的變化。

通過這些評估和動態(tài)調(diào)整，InnoDB能夠自動優(yōu)化并發(fā)度，以最大程度地利用系統(tǒng)資源并提高性能。然而，由于每個系統(tǒng)的配置和負(fù)載模式都不同，最佳的并發(fā)度可能會因情況而異。因此，對于特定的系統(tǒng)，可能需要根據(jù)實際情況進行調(diào)整和優(yōu)化。

為什么設(shè)置為4

對mysql了解不足， 這個值最早大家的建議都是設(shè)置為cpu的核數(shù)

應(yīng)該怎么做？

依據(jù)percona.com的文檔給出的合理值應(yīng)該是活躍用戶線程小于64的時候使用默認(rèn)值， 大于64的時候設(shè)置為128，然后根據(jù)測試情況逐步下調(diào)或上漲

到此這篇關(guān)于mysql請求阻塞的文章就介紹到這了,更多相關(guān)mysql請求阻塞內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論