增加 MySQL 8 可以處理的連接數(Debian 9 伺服器)和自動重啟事件調度程序
我有一個 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
請提供
SHOW CREATE TABLE noticia
。如果texto
是 typeTEXT
,則在 中執行的查詢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 個連接好運。