Mysql

增加 MySQL 8 可以處理的連接數(Debian 9 伺服器)和自動重啟事件調度程序

  • November 11, 2019

我有一個 Debian 9 伺服器(12 個 CPU,80GB 記憶體),執行 MySQL 8.0 的 Percona 伺服器,每秒大約 1100 個客戶端,負載不是很高,大約 0.3 到 2.30

顯示引擎 innodb 狀態的最新數字:183.82 插入/秒、169.69 更新/秒、5.79 刪除/秒、2179444.29 讀取/秒

它在大多數情況下執行得非常好,但最近它有時會突然拒絕新連接(當一個非常繁忙的表被鎖定很長時間或某些查詢需要太長時間時)。

日誌顯示:

$$ ERROR $$ $$ MY-000000 $$ $$ connection_h $$錯誤日誌限制:1912“無法創建執行緒來處理新連接”錯誤被抑制

$$ ERROR $$ $$ MY-010249 $$ $$ Server $$無法創建執行緒來處理新連接(errno= 11)

$$ ERROR $$ $$ MY-010053 $$ $$ Server $$Event_scheduler::execute_top: 無法創建事件工作執行緒 (errno=11)。停止事件調度程序


所以,基本上我有兩個問題:

1.如何調整伺服器以接受更多連接(我嘗試了很多我將在下面列出的東西),確切地說它不能處理超過4821

2. 每當發生這種情況時,事件調度程序就會停止,我必須再次手動啟用它,這真的很糟糕,因為我有一些 MEMORY 表已滿,它們處理繁重的流量並每 5 秒轉儲到其他表


到目前為止,我已經嘗試在**/etc/security/limits.conf**中為 *、mysql 和 root 使用者設置軟限制和硬限制:

*         hard    nofile      400000
*         soft    nofile      400000
*         hard    nproc       400000
*         soft    nproc       400000
mysql     hard    nofile      400000
mysql     soft    nofile      400000
root      hard    nofile      400000
root      soft    nofile      400000

我還對**/etc/sysctl.conf**進行了許多調整,列出如下:

kernel.pid_max = 262144
vm.max_map_count = 262144
net.ipv4.tcp_keepalive_time = 1200
net.ipv4.ip_local_port_range = 2000 65500
net.ipv4.tcp_max_syn_backlog = 32768
fs.file-max = 450000
net.core.netdev_max_backlog = 450000
net.core.somaxconn = 32768

我還對 systemd conf 文件進行了更改:/lib/systemd/system/mysql.service - 設置:

LimitNOFILE=220000
TasksMax=32768

mysqld.conf

bind-address                    = 0.0.0.0

# GENERAL #
user                           = mysql
default-storage-engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid-file                       = /var/lib/mysql/mysql.pid

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
skip-name-resolve
sysdate-is-now                 = 1
innodb                         = FORCE

wait-timeout                   = 600

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
sync-binlog                    = 0

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
max-connections                = 20000
thread-cache-size              = 300
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 512M
innodb-flush-log-at-trx-commit = 0
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 64G
innodb-fast-shutdown           = 0
innodb-buffer-pool-dump-pct    = 75
innodb-buffer-pool-dump-at-shutdown = 1
innodb-buffer-pool-load-at-startup  = 1
innodb-io-capacity             = 400
innodb-io-capacity-max         = 2000

# LOGGING #
log-error                      = /var/log/mysql/mysql-error.log
log-queries-not-using-indexes  = 0
slow-query-log                 = 1
slow-query-log-file            = /var/log/mysql/mysql-slow.log
long-query-time                = 5

event_scheduler = 1
general_log_file               = /var/log/mysql/general.log
general_log                    = 0
local-infile                   = 1

我不知道我還能做什麼或者我還能在哪裡看,試圖搜尋問題並且找不到任何有效的東西,對於事件調度程序來說更糟糕的是,幾乎找不到任何東西(嘗試在 google MY-010053中搜尋或無法創建事件工作執行緒幾乎沒有結果)

伺服器不會無響應或變慢,如果我故意鎖定表,我會在大約 10 秒內開始收到錯誤,當我解鎖它時會立即恢復

正如威爾遜豪克所要求的:

ulimit -a

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 326193
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 400000
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 326193
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

iostat -xm 5 3

Linux 4.9.0-8-amd64 (zelda)     03/07/2019      _x86_64_        (12 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
         11.24    0.00    1.56    0.76    0.00   86.44

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.41    44.17    2.41   67.61     0.05     1.61    48.55     0.37    5.23    4.74    5.25   1.37   9.59

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          9.77    0.00    1.91    0.82    0.00   87.51

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00    52.20    0.00   81.60     0.00     1.85    46.51     0.45    5.51    0.00    5.51   1.14   9.28

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          5.83    0.00    1.41    0.77    0.00   91.99

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0.00   132.00    0.00  161.40     0.00     8.12   103.00     0.88    5.47    0.00    5.47   0.66  10.72

顯示完整的程序列表沒有什麼特別的:

4087783 event_scheduler localhost       Daemon  1   Waiting for next activation     0   0
275050068   xgh 179.191.66.174:51143    xgh Sleep   3           0   0
275050130   xgh 179.191.66.174:51144    xgh Query   0   starting    show full processlist   0   0
300324788   xgh 179.191.66.174:40708    xgh Sleep   12595           5   61
304505269   xgh 179.191.66.174:51680    xgh Sleep   72          0   0
304505986   xgh 179.191.66.174:51706    xgh Sleep   72          0   0
305818676   xgh 172.30.5.2:57288    xgh Query   0   Sending data    SELECT *
FROM (`noticias`.`noticia`)
WHERE `texto` =  'Carlos Ghosn deixa prisão após mais de 100 dias detido em Tóquio'    0   0
305818680   xgh 172.30.5.2:57296    xgh Sleep   57          0   0
305818682   xgh 172.30.5.2:57302    xgh Sleep   57          0   0
305818689   xgh 172.30.5.2:57316    xgh Sleep   57          0   0
305818692   xgh 172.30.5.2:57324    xgh Sleep   57          0   0
305842475   xgh 172.30.5.2:49326    xgh Sleep   1           94  550698
305842479   xgh 172.30.5.2:49334    xgh Sleep   1           0   0
305842481   xgh 172.30.5.2:49340    xgh Sleep   1           0   0
305842486   xgh 172.30.5.2:49350    xgh Sleep   1           0   0
305842489   xgh 172.30.5.2:49358    xgh Sleep   1           0   0
305842492   xgh 172.30.5.2:49364    xgh Sleep   1           0   0
305842496   xgh 172.30.5.2:49372    xgh Sleep   1           0   0
305842498   xgh 172.30.5.2:49376    xgh Sleep   1           0   0
305842501   xgh 172.30.5.2:49382    xgh Sleep   1           0   0
305842505   xgh 172.30.5.2:49392    xgh Sleep   1           0   0
305842508   xgh 172.30.5.2:49398    xgh Sleep   1           0   0

顯示全球狀態:https ://pastebin.com/cg1v5bHD

顯示全域變數:https ://pastebin.com/8mkTzpr0

請提供SHOW CREATE TABLE noticia。如果texto是 type TEXT,則在 中執行的查詢PROCESSLIST需要表掃描。如果是VARCHAR,那麼索引可能會有所幫助。

你聲稱有很多聯繫,但我看到只有一個在做任何事情。所以SHOW FULL PROCESSLIST再次,希望得到幾個查詢。

每秒速率=RPS - 為您的 my.cnf 考慮的建議

$$ mysqld $$部分

innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
innodb_flushing_avg_loops=5  # from 30 to reduce delay and reduce innodb_buffer_pool_pages_dirty of 90,000 +
innodb_io_capacity=1900  # from 400 to allow higher IOPS to data devices
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of ~ 600,000 RPS

使用 SHOW GLOBAL STATUS LIKE ‘%dirty%’ 監控 innodb_buffer_pool_pages_dirty;

需要審查的時間消費者,A) com_rollback_to_savepoint AVG 1 每 62 秒,B) handler_rollback AVG 1 每 52 秒,C) handler_savepoint_rollback AVG 1 每 31 秒。

免責聲明:我是 mysqlservertuning.com 的作者,如我的個人資料“網路個人資料”中所列。祝您的實例平均每秒 367 個連接好運。

引用自:https://serverfault.com/questions/955726