{"id":3817,"date":"2024-04-02T01:46:15","date_gmt":"2024-04-01T17:46:15","guid":{"rendered":"http:\/\/dba.qishuo.xin\/?p=3817"},"modified":"2024-09-28T09:19:06","modified_gmt":"2024-09-28T01:19:06","slug":"mariadb%e7%a3%81%e7%9b%98io%e9%ab%98%e7%9a%84%e9%97%ae%e9%a2%98%e6%8e%92%e6%9f%a5","status":"publish","type":"post","link":"http:\/\/dba.qishuo.xin\/?p=3817","title":{"rendered":"mariadb\u78c1\u76d8io\u9ad8\u7684\u95ee\u9898\u6392\u67e5"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_76 ez-toc-wrap-right counter-hierarchy ez-toc-counter ez-toc-light-blue ez-toc-container-direction\">\n<label for=\"ez-toc-cssicon-toggle-item-6a0e3a479157c\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a0e3a479157c\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#1_%E6%95%85%E9%9A%9C%E7%8E%B0%E8%B1%A1\" >1. \u6545\u969c\u73b0\u8c61<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#11_%E7%B3%BB%E7%BB%9F%E6%97%A5%E5%BF%97_varlogmessage\" >1.1. \u7cfb\u7edf\u65e5\u5fd7 \/var\/log\/message<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#12_%E7%94%A8mpstat%E6%9F%A5%E7%9C%8Bcpu%E6%83%85%E5%86%B5\" >1.2 \u7528mpstat\u67e5\u770bcpu\u60c5\u51b5<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#13_%E4%BD%BF%E7%94%A8iotop%E6%9F%A5%E8%AF%A2%E6%B6%88%E8%80%97IO%E7%9A%84%E8%BF%9B%E7%A8%8B\" >1.3. \u4f7f\u7528iotop\u67e5\u8be2\u6d88\u8017IO\u7684\u8fdb\u7a0b<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#14_%E4%BD%BF%E7%94%A8iotop%E6%9F%A5%E8%AF%A2%E6%B6%88%E8%80%97IO%E7%9A%84%E7%BA%BF%E7%A8%8B\" >1.4. \u4f7f\u7528iotop\u67e5\u8be2\u6d88\u8017IO\u7684\u7ebf\u7a0b<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#15_%E6%9F%A5%E8%AF%A2mysql%E7%BA%BF%E7%A8%8B%E4%B8%8Eos%E7%BA%BF%E7%A8%8B%E5%AF%B9%E5%BA%94%E5%85%B3%E7%B3%BB\" >1.5. \u67e5\u8be2mysql\u7ebf\u7a0b\u4e0eos\u7ebf\u7a0b\u5bf9\u5e94\u5173\u7cfb<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#16_%E7%9B%91%E6%8E%A7mariadb%E6%85%A2%E6%97%A5%E5%BF%97\" >1.6. \u76d1\u63a7mariadb\u6162\u65e5\u5fd7<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#14_%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%90%E8%A1%8C%E7%8A%B6%E6%80%81\" >1.4. \u6570\u636e\u5e93\u8fd0\u884c\u72b6\u6001<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#2_%E9%97%AE%E9%A2%98%E5%8E%9F%E5%9B%A0\" >2. \u95ee\u9898\u539f\u56e0<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#3_%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88\" >3. \u89e3\u51b3\u65b9\u6848<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#%E9%99%84%E5%BD%95\" >\u9644\u5f55<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#%E9%99%84%E5%BD%95A_%E7%9B%B8%E5%85%B3%E8%81%94%E7%9A%84%E6%96%87%E7%AB%A0\" >\u9644\u5f55A. \u76f8\u5173\u8054\u7684\u6587\u7ae0<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"http:\/\/dba.qishuo.xin\/?p=3817\/#%E9%99%84%E5%BD%95B_%E5%8F%82%E8%80%83\" >\u9644\u5f55B. \u53c2\u8003<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<table>\n<thead>\n<tr>\n<th style=\"text-align: left;\">\u63cf\u8ff0\u9879<\/th>\n<th style=\"text-align: left;\">\u5185\u5bb9<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td style=\"text-align: left;\">\u64cd\u4f5c\u7cfb\u7edf<\/td>\n<td style=\"text-align: left;\">CentOS Linux release 7.9.2009 (Core)<\/td>\n<\/tr>\n<tr>\n<td style=\"text-align: left;\">\u6570\u636e\u5e93<\/td>\n<td style=\"text-align: left;\">10.5.8-MariaDB-log<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><span class=\"ez-toc-section\" id=\"1_%E6%95%85%E9%9A%9C%E7%8E%B0%E8%B1%A1\"><\/span>1. \u6545\u969c\u73b0\u8c61<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>\u4f7f\u7528 top \u547d\u4ee4\u67e5\u770b\u64cd\u4f5c\u7cfb\u7edf\u8fd0\u884c\u60c5\u51b5\u65f6\uff0c\u53d1\u73b0iowait\u4e00\u76f4\u5904\u4e8e 30% \u5de6\u53f3\uff0c\u5982\u4e0b\u56fe\u6240\u793a\uff1a<\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711988840137.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711988840137.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"11_%E7%B3%BB%E7%BB%9F%E6%97%A5%E5%BF%97_varlogmessage\"><\/span>1.1. \u7cfb\u7edf\u65e5\u5fd7 \/var\/log\/message<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>Apr  1 16:46:03 gxykt_mariadb1 systemd: mariadb.service: main process exited, code=killed, status=6\/ABRT\nApr  1 16:46:04 gxykt_mariadb1 systemd: Unit mariadb.service entered failed state.\nApr  1 16:46:04 gxykt_mariadb1 systemd: mariadb.service failed.\nApr  1 16:46:09 gxykt_mariadb1 systemd: mariadb.service holdoff time over, scheduling restart.\nApr  1 16:46:09 gxykt_mariadb1 systemd: Stopped MariaDB 10.5.8 database server.\nApr  1 16:46:09 gxykt_mariadb1 systemd: Starting MariaDB 10.5.8 database server...\nApr  1 16:46:10 gxykt_mariadb1 mariadbd: 2024-04-01 16:46:10 0 [Note] \/usr\/local\/mysql\/bin\/mariadbd (mysqld 10.5.8-MariaDB-log) starting as process 20232 ...\nApr  1 16:46:10 gxykt_mariadb1 mariadbd: 2024-04-01 16:46:10 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 106345)\nApr  1 16:46:32 gxykt_mariadb1 systemd: Started MariaDB 10.5.8 database server.<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711989020207.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711989020207.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"12_%E7%94%A8mpstat%E6%9F%A5%E7%9C%8Bcpu%E6%83%85%E5%86%B5\"><\/span>1.2 \u7528mpstat\u67e5\u770bcpu\u60c5\u51b5<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-shell\">]# mpstat -P ALL 1 3<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711989836970.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711989836970.png\" alt=\"file\" \/><\/a><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711989884537.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711989884537.png\" alt=\"file\" \/><\/a><\/p>\n<p><strong>\u4ece\u4e0a\u56fe\u53ef\u4ee5\u770b\u51faCPU\u90fd\u5728\u7b49\u5f85IO\u7684\u5b8c\u6210\uff01<\/strong><\/p>\n<h4><span class=\"ez-toc-section\" id=\"13_%E4%BD%BF%E7%94%A8iotop%E6%9F%A5%E8%AF%A2%E6%B6%88%E8%80%97IO%E7%9A%84%E8%BF%9B%E7%A8%8B\"><\/span>1.3. \u4f7f\u7528iotop\u67e5\u8be2\u6d88\u8017IO\u7684\u8fdb\u7a0b<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>]# iotop -oP<\/code><\/pre>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711990208631.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711990208631.png\" alt=\"file\" \/><\/a><\/p>\n<p>\u6839\u636ePID\u518d\u627e\u5230\u5bf9\u5e94\u7684\u8fdb\u7a0b\u4e3a mariadb\u3002<\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711990472679.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711990472679.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"14_%E4%BD%BF%E7%94%A8iotop%E6%9F%A5%E8%AF%A2%E6%B6%88%E8%80%97IO%E7%9A%84%E7%BA%BF%E7%A8%8B\"><\/span>1.4. \u4f7f\u7528iotop\u67e5\u8be2\u6d88\u8017IO\u7684\u7ebf\u7a0b<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code class=\"language-shell\">~]# iotop -u mysql<\/code><\/pre>\n<p><strong>\u7a0b\u5e8f\u8f93\u51fa\u5305\u542b\u4ee5\u4e0b\u5217\uff1a<\/strong><\/p>\n<ul>\n<li>TID \u2013 \u8fdb\u7a0b\u6807\u8bc6\u7b26<\/li>\n<li>PRIO \u2013 \u8fdb\u7a0b\u4f18\u5148\u7ea7<\/li>\n<li>USER \u2013 \u62e5\u6709\u8fdb\u7a0b\u7684\u7528\u6237<\/li>\n<li>DISK READ \u2013 \u78c1\u76d8\u8bfb\u53d6\u901f\u5ea6<\/li>\n<li>DISK WRITE \u2013 \u78c1\u76d8\u5199\u5165\u901f\u5ea6<\/li>\n<li>SWAPIN \u2013 \u5df2\u4f7f\u7528\u4ea4\u6362\u6587\u4ef6\u7684\u767e\u5206\u6bd4<\/li>\n<li>IO&gt; \u2013 \u6240\u6709 I\/O \u64cd\u4f5c\u7684\u767e\u5206\u6bd4<\/li>\n<li>COMMAND \u2013 \u542f\u52a8\u8fdb\u7a0b\u7684\u547d\u4ee4<\/li>\n<\/ul>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711992233025.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711992233025.png\" alt=\"file\" \/><\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"15_%E6%9F%A5%E8%AF%A2mysql%E7%BA%BF%E7%A8%8B%E4%B8%8Eos%E7%BA%BF%E7%A8%8B%E5%AF%B9%E5%BA%94%E5%85%B3%E7%B3%BB\"><\/span>1.5. \u67e5\u8be2mysql\u7ebf\u7a0b\u4e0eos\u7ebf\u7a0b\u5bf9\u5e94\u5173\u7cfb<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style='color:red'>mysql\u91cc\u5bf9\u5e94  <code>performance_schema.threads<\/code> \u67e5\u8be2mysql\u7ebf\u7a0b\u4e0eos\u7ebf\u7a0b\u5bf9\u5e94\u5173\u7cfb\u3002performance_schema\u9ed8\u8ba4\u662foff\uff0c\u6ca1\u6709\u6253\u5f00\u7684\u3002\u901a\u8fc7\u4fee\u6539\/etc\/my.cnf\u914d\u7f6e\u6587\u4ef6\uff0c\u8bbe\u7f6eperformance_schema\u4e3aon\uff0c\u6b64\u65f6performance_schema\u5e93\u5c31\u6709\u6570\u636e\u4e86\u3002<\/span><\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711992822899.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711992822899.png\" alt=\"file\" \/><\/a><\/p>\n<pre><code class=\"language-sql\">SELECT a.name,\n       a.thread_id,\n       a.thread_os_id,     -- \u64cd\u4f5c\u7cfb\u7edf\u7684\u7ebf\u7a0bid  (top -H \u5bf9\u5e94PID\uff0c       iotop -u mysql \u5bf9\u5e94TID\uff09\n       a.processlist_id,   -- mysql connection_id()\u8fd4\u56de\u7684\u503c\uff0c\u53ef\u4ee5kill query \u6216\u8005kill (connection)\u6740\u6389\u3002\n       a.type,             -- \u7ebf\u7a0b\u7c7b\u578b\uff0c\u5206\u524d\u53f0\u7ebf\u7a0b\u548c\u540e\u53f0\u7ebf\u7a0b\n       b.user,             -- \u7528\u6237\n       b.host,             -- ip\n       b.db,               -- \u64cd\u4f5c\u7684\u5e93\u540d\u79f0\n       b.command,          -- sql\u7c7b\u578b\n       b.time,             -- sql\u6267\u884c\u65f6\u95f4 \u5355\u4f4d\uff1a\u79d2\n       b.state,            -- sql\u72b6\u6001\n       b.info              -- sql\u8bed\u53e5\n  FROM performance_schema.threads a\n  LEFT JOIN information_schema.processlist b\n    ON a.processlist_id = b.id\nwhere a.type = &#039;FOREGROUND&#039;;<\/code><\/pre>\n<h4><span class=\"ez-toc-section\" id=\"16_%E7%9B%91%E6%8E%A7mariadb%E6%85%A2%E6%97%A5%E5%BF%97\"><\/span>1.6. \u76d1\u63a7mariadb\u6162\u65e5\u5fd7<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711993506302.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711993506302.png\" alt=\"file\" \/><\/a><br \/>\n\u4e3b\u8981\u662f<code>xxl_job<\/code>\u6570\u636e\u5e93\u4e2d\u7684\u8868<code>xxl_job_log<\/code>\u975e\u5e38\u591a\uff0c\u5bfc\u81f4IO\u7b49\u5f85\u4e25\u91cd\u3002<\/p>\n<p><a href=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711994030892.png\" data-fancybox=\"images\" data-fancybox=\"images\"><img decoding=\"async\" src=\"http:\/\/dba.qishuo.xin\/wp-content\/uploads\/2024\/04\/image-1711994030892.png\" alt=\"file\" \/><\/a><\/p>\n<p><code>xxl_job_log<\/code>\u670924G\u3002<\/p>\n<h4><span class=\"ez-toc-section\" id=\"14_%E6%95%B0%E6%8D%AE%E5%BA%93%E8%BF%90%E8%A1%8C%E7%8A%B6%E6%80%81\"><\/span>1.4. \u6570\u636e\u5e93\u8fd0\u884c\u72b6\u6001<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<pre><code>MariaDB [information_schema]&gt; show engine innodb status\\G\n*************************** 1. row ***************************\n  Type: InnoDB\n  Name: \nStatus: \n=====================================\n2024-04-02 00:36:57 0x7fd49ad73700 INNODB MONITOR OUTPUT\n=====================================\nPer second averages calculated from the last 42 seconds\n-----------------\nBACKGROUND THREAD\n-----------------\nsrv_master_thread loops: 22606 srv_active, 0 srv_shutdown, 654 srv_idle\nsrv_master_thread log flush and writes: 23260\n----------\nSEMAPHORES\n----------\nOS WAIT ARRAY INFO: reservation count 197836\nOS WAIT ARRAY INFO: signal count 272250\nRW-shared spins 186925, rounds 6423901, OS waits 139809\nRW-excl spins 49043, rounds 1458357, OS waits 27556\nRW-sx spins 20376, rounds 465097, OS waits 5626\nSpin rounds per wait: 34.37 RW-shared, 29.74 RW-excl, 22.83 RW-sx\n------------------------\nLATEST DETECTED DEADLOCK\n------------------------\n2024-04-01 23:09:37 0x7fd3842d9700\n*** (1) TRANSACTION:\nTRANSACTION 3830881216, ACTIVE 0 sec fetching rows\nmysql tables in use 1, locked 1\nLOCK WAIT 5 lock struct(s), heap size 1128, 881 row lock(s), undo log entries 2\nMySQL thread id 3724, OS thread handle 140551086012160, query id 27424143 172.16.1.215 dcs Updating\nDELETE FROM dcs_device_status_details WHERE (parent_device_num != 0 and parent_device_num = 1725399711149527041)\n        OR (parent_device_num = 0 and device_num = 1725399711149527041)\n*** (1) WAITING FOR THIS LOCK TO BE GRANTED:\nRECORD LOCKS space id 12084 page no 21 n bits 512 index PRIMARY of table `dcsdb`.`dcs_device_status_details` trx id 3830881216 lock_mode X waiting\nRecord lock, heap no 199 PHYSICAL RECORD: n_fields 6; compact format; info bits 0\n 0: len 8; hex 97f1d9e598e11002; asc         ;;\n 1: len 1; hex 87; asc  ;;\n 2: len 6; hex 0000e4569c2a; asc    V *;;\n 3: len 7; hex 060003000c2904; asc      ) ;;\n 4: len 8; hex 97f1d9e588211002; asc      !  ;;\n 5: SQL NULL;\n\n*** (2) TRANSACTION:\nTRANSACTION 3830881322, ACTIVE 0 sec inserting\nmysql tables in use 1, locked 1\n3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1\nMySQL thread id 3844, OS thread handle 140546432407296, query id 27424502 172.16.1.215 dcs Update\nreplace into dcs_device_status_details\n        (device_num,status,parent_device_num,content)\n        values\n            (\n            1725399712395235330,7,1725399712114216962,null\n            ),\n            (\n            1725399712395235330,6,1725399712114216962,null\n            )\n*** (2) HOLDS THE LOCK(S):\nRECORD LOCKS space id 12084 page no 21 n bits 512 index PRIMARY of table `dcsdb`.`dcs_device_status_details` trx id 3830881322 lock_mode X locks rec but not gap\nRecord lock, heap no 199 PHYSICAL RECORD: n_fields 6; compact format; info bits 0\n 0: len 8; hex 97f1d9e598e11002; asc         ;;\n 1: len 1; hex 87; asc  ;;\n 2: len 6; hex 0000e4569c2a; asc    V *;;\n 3: len 7; hex 060003000c2904; asc      ) ;;\n 4: len 8; hex 97f1d9e588211002; asc      !  ;;\n 5: SQL NULL;\n\n*** (2) WAITING FOR THIS LOCK TO BE GRANTED:\nRECORD LOCKS space id 12084 page no 21 n bits 512 index PRIMARY of table `dcsdb`.`dcs_device_status_details` trx id 3830881322 lock_mode X locks rec but not gap waiting\nRecord lock, heap no 198 PHYSICAL RECORD: n_fields 6; compact format; info bits 32\n 0: len 8; hex 97f1d9e598e11002; asc         ;;\n 1: len 1; hex 86; asc  ;;\n 2: len 6; hex 0000e4569ab3; asc    V  ;;\n 3: len 7; hex 280005166212e5; asc (   b  ;;\n 4: len 8; hex 97f1d9e588211002; asc      !  ;;\n 5: SQL NULL;\n\n*** WE ROLL BACK TRANSACTION (2)\n------------\nTRANSACTIONS\n------------\nTrx id counter 3831962857\nPurge done for trx&#039;s n:o &lt; 3831912798 undo n:o &lt; 0 state: running\nHistory list length 22299\nLIST OF TRANSACTIONS FOR EACH SESSION:\n---TRANSACTION 422027972220040, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n...\u6b64\u5904\u6709\u7701\u7565\n0 lock struct(s), heap size 1128, 0 row lock(s)\n---TRANSACTION 422027972275888, ACTIVE 10 sec fetching rows\nmysql tables in use 2, locked 0\n0 lock struct(s), heap size 1128, 0 row lock(s)\nMySQL thread id 4775, OS thread handle 140552033396480, query id 35615239 172.16.1.213 xxl_job Sending data\nSELECT\n            t.id\n        FROM\n            xxl_job_log t\n            LEFT JOIN xxl_job_registry t2 ON t.executor_address = t2.registry_value\n        WHERE\n            t.trigger_code = 200\n                AND t.handle_code = 0\n                AND t.trigger_time   &lt;=   &#039;2024-04-02 00:26:47&#039;\n                AND t2.id IS NULL\nTrx read view will not see trx with id &gt;= 3831960932, sees &lt; 3831960932\n---TRANSACTION 422027972164192, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n---TRANSACTION 422027972159896, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n---TRANSACTION 422027972391880, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n---TRANSACTION 422027972018128, ACTIVE 299 sec fetching rows\nmysql tables in use 1, locked 0\n0 lock struct(s), heap size 1128, 0 row lock(s)\nMySQL thread id 4709, OS thread handle 140547840562944, query id 35214742 172.16.1.213 xxl_job Sending data\nSELECT id FROM `xxl_job_log`\n        WHERE !(\n            (trigger_code in (0, 200) and handle_code = 0)\n            OR\n            (handle_code = 200)\n        )\n        AND `alarm_status` = 0\n        ORDER BY id ASC\n        LIMIT 1000\nTrx read view will not see trx with id &gt;= 3831912797, sees &lt; 3831912797\n---TRANSACTION 422027972189968, ACTIVE 71 sec\n0 lock struct(s), heap size 1128, 0 row lock(s)\nMySQL thread id 4691, OS thread handle 140552300525312, query id 35496080 172.16.1.213 bcpf \nTrx read view will not see trx with id &gt;= 3831948572, sees &lt; 3831948571\n---TRANSACTION 422027972473504, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n...\n---TRANSACTION 422027972546536, ACTIVE 0 sec\n0 lock struct(s), heap size 1128, 0 row lock(s)\nMySQL thread id 135, OS thread handle 140547845355264, query id 35635716 172.16.1.215 uias_user_center \n---TRANSACTION 422027971979464, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n...\uff08\u6b64\u5904\u7701\u7565\uff09\n---TRANSACTION 422027971936504, not started\n0 lock struct(s), heap size 1128, 0 row lock(s)\n--------\nFILE I\/O\n--------\nI\/O thread 0 state: (null) ((null))\n...\uff08\u6b64\u5904\u7701\u7565\uff09\nI\/O thread 33 state: (null) ((null))\nPending normal aio reads:\nPending flushes (fsync) log: 0; buffer pool: 0\n52679899 OS file reads, 6134426 OS file writes, 2647297 OS fsyncs\n2 pending reads, 0 pending writes\n1902.50 reads\/s, 16368 avg bytes\/read, 223.99 writes\/s, 98.71 fsyncs\/s\n-------------------------------------\nINSERT BUFFER AND ADAPTIVE HASH INDEX\n-------------------------------------\nIbuf: size 253, free list len 12060, seg size 12314, 28451 merges\nmerged operations:\n insert 159252, delete mark 48291, delete 41\ndiscarded operations:\n insert 0, delete mark 0, delete 0\n0.00 hash searches\/s, 30051.78 non-hash searches\/s\n---\nLOG\n---\nLog sequence number 2923499223626\nLog flushed up to   2923499223543\nPages flushed up to 2923406113816\nLast checkpoint at  2923406113816\n0 pending log flushes, 0 pending chkp writes\n2047572 log i\/o&#039;s done, 86.81 log i\/o&#039;s\/second\n----------------------\nBUFFER POOL AND MEMORY\n----------------------\nTotal large memory allocated 671088640\nDictionary memory allocated 4240320\nBuffer pool size   32260\nFree buffers       0\nDatabase pages     32260\nOld database pages 11889\nModified db pages  4137\nPercent of dirty pages(LRU &amp; free pages): 12.824\nMax dirty pages percent: 75.000\nPending reads 2\nPending writes: LRU 0, flush list 0\nPages made young 688055, not young 256506565\n12.33 youngs\/s, 12258.04 non-youngs\/s\nPages read 52527064, created 218497, written 3967535\n1902.67 reads\/s, 24.79 creates\/s, 135.43 writes\/s\nBuffer pool hit rate 988 \/ 1000, young-making rate 0 \/ 1000 not 70 \/ 1000\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s\nLRU len: 32260, unzip_LRU len: 0\nI\/O sum[94432]:cur[3585], unzip sum[0]:cur[0]\n--------------\nROW OPERATIONS\n--------------\n3 read views open inside InnoDB\nProcess ID=0, Main thread ID=0, state: sleeping\nNumber of rows inserted 1732345, updated 1010939, deleted 646156, read 18365155605\n36.57 inserts\/s, 49.62 updates\/s, 28.24 deletes\/s, 743763.60 reads\/s\nNumber of system rows inserted 0, updated 0, deleted 0, read 0\n0.00 inserts\/s, 0.00 updates\/s, 0.00 deletes\/s, 0.00 reads\/s\n----------------------------\nEND OF INNODB MONITOR OUTPUT\n============================\n1 row in set (0.001 sec)<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"2_%E9%97%AE%E9%A2%98%E5%8E%9F%E5%9B%A0\"><\/span>2. \u95ee\u9898\u539f\u56e0<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h3><span class=\"ez-toc-section\" id=\"3_%E8%A7%A3%E5%86%B3%E6%96%B9%E6%A1%88\"><\/span>3. \u89e3\u51b3\u65b9\u6848<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h3><span class=\"ez-toc-section\" id=\"%E9%99%84%E5%BD%95\"><\/span>\u9644\u5f55<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"%E9%99%84%E5%BD%95A_%E7%9B%B8%E5%85%B3%E8%81%94%E7%9A%84%E6%96%87%E7%AB%A0\"><\/span>\u9644\u5f55A. \u76f8\u5173\u8054\u7684\u6587\u7ae0<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<h4><span class=\"ez-toc-section\" id=\"%E9%99%84%E5%BD%95B_%E5%8F%82%E8%80%83\"><\/span>\u9644\u5f55B. \u53c2\u8003<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ul>\n<li>\n<p><a target=\"_blank\" href=\"https:\/\/www.cnblogs.com\/gered\/p\/16160630.html#_labelTop\" title=\"mysql\u67e5\u770b\u54ea\u4e2a\u7ebf\u7a0b\u5360\u7528\u8d44\u6e90\u9ad8 \">mysql\u67e5\u770b\u54ea\u4e2a\u7ebf\u7a0b\u5360\u7528\u8d44\u6e90\u9ad8 <\/a><\/p>\n<\/li>\n<li>\n<p><a target=\"_blank\" href=\"https:\/\/www.jianshu.com\/p\/058a08813416\" title=\"\u8bb0\u4e00\u6b21mysql\u78c1\u76d8io\u9ad8\u7684\u95ee\u9898\u6392\u67e5\">\u8bb0\u4e00\u6b21mysql\u78c1\u76d8io\u9ad8\u7684\u95ee\u9898\u6392\u67e5<\/a><\/p>\n<\/li>\n<li>\n<p><a target=\"_blank\" href=\"https:\/\/www.cnblogs.com\/xiaoxi-jinchen\/p\/17137240.html\" title=\"MySQL\u6027\u80fd\u8c03\u4f18\u5fc5\u77e5\uff1aPerformance Schema\u5f15\u64ce\u7684\u914d\u7f6e\u4e0e\u4f7f\u7528\">MySQL\u6027\u80fd\u8c03\u4f18\u5fc5\u77e5\uff1aPerformance Schema\u5f15\u64ce\u7684\u914d\u7f6e\u4e0e\u4f7f\u7528<\/a><\/p>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u63cf\u8ff0\u9879 \u5185\u5bb9 \u64cd\u4f5c\u7cfb\u7edf CentOS Linux &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[47,32],"tags":[271,48,12],"class_list":["post-3817","post","type-post","status-publish","format-standard","hentry","category-linux","category-mariadb","tag-io","tag-linux","tag-mariadb"],"_links":{"self":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/3817","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3817"}],"version-history":[{"count":18,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/3817\/revisions"}],"predecessor-version":[{"id":4044,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=\/wp\/v2\/posts\/3817\/revisions\/4044"}],"wp:attachment":[{"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3817"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3817"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dba.qishuo.xin\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3817"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}