{"id":1631,"date":"2024-05-08T17:12:20","date_gmt":"2024-05-08T09:12:20","guid":{"rendered":"http:\/\/oneai.eu.org\/?p=1631"},"modified":"2024-05-08T17:12:20","modified_gmt":"2024-05-08T09:12:20","slug":"%e7%9b%91%e6%8e%a7%e6%ad%a3%e5%9c%a8%e6%89%a7%e8%a1%8c%e7%9a%84sql","status":"publish","type":"post","link":"https:\/\/oneai.eu.org\/?p=1631","title":{"rendered":"\u76d1\u63a7\u6b63\u5728\u6267\u884c\u7684SQL"},"content":{"rendered":"
postgres\u76d1\u63a7\u6b63\u5728\u6267\u884c\u7684sql\n-----------------------------\nSELECT \n    procpid, \n    start, \n    now() - start AS lap, \n    current_query \nFROM \n    (SELECT \n        backendid, \n        pg_stat_get_backend_pid(S.backendid) AS procpid, \n        pg_stat_get_backend_activity_start(S.backendid) AS start, \n       pg_stat_get_backend_activity(S.backendid) AS current_query \n    FROM \n        (SELECT pg_stat_get_backend_idset() AS backendid) AS S \n    ) AS S \nWHERE \n   current_query <> '<IDLE>' \nORDER BY \n   lap DESC;\n\n SELECT datname,pid,query_start,now() - query_start AS lap,waiting,state,query from pg_stat_activity where datname ='tscdb' and state='active' order by lap desc;\n\nprocpid\uff1a\u8fdb\u7a0bid\nstart\uff1a\u8fdb\u7a0b\u5f00\u59cb\u65f6\u95f4\nlap\uff1a\u7ecf\u8fc7\u65f6\u95f4\ncurrent_query\uff1a\u6267\u884c\u4e2d\u7684sql\n-------------------------------------------\n\u600e\u6837\u505c\u6b62\u6b63\u5728\u6267\u884c\u7684sql\nSELECT pg_cancel_backend(\u8fdb\u7a0bid);\n\u6216\u8005\u7528\u7cfb\u7edf\u51fd\u6570\nkill -9 \u8fdb\u7a0bid;\n-------------------------------------------\n\u66f4\u6539\u6570\u636e\u5e93\u7f16\u7801\uff1a\nshow client_encoding;\nset client_encoding='utf-8';\n-------------------------------------------\nselect * from (SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s) as querystring;\n\n\u67e5\u8be2\u6700\u6162\u768410\u6761SQL\uff1a\nSELECT  query, calls, total_time, (total_time\/calls) as average ,rows, \n        100.0 * shared_blks_hit \/nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent \nFROM    pg_stat_statements \nORDER   BY average DESC LIMIT 10;\n\n\u6e05\u9664\u7edf\u8ba1\u7ed3\u679c\uff1aselect pg_stat_statements_reset() ;\n-\u67e5\u770b\u5f53\u524d\u6d3b\u52a8\u7684\u5ba2\u6237\u7aef\u8fde\u63a5\u6570  \n SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();       \n\n --\u67e5\u8be2\u5ba2\u6237\u7aef\u8fde\u63a5\u7684\u60c5\u51b5  \n SELECT pid,case when waiting='f' then 'already get lock,sql executing' when waiting='t' then 'waiting get lock,sql waiting execute' end lock_satus,  \n current_timestamp - least(query_start,xact_start) AS runtime,substr(query,1,25) AS current_query  \nFROM pg_stat_activity WHERE NOT pid=pg_backend_pid() and state<>'idle' and application_name<>'pg_statsinfod' order by runtime desc  \n\n--\u67e5\u770b\u6301\u6709\u9501\u548c\u7b49\u5f85\u9501\u7684\u4e00\u4e9b\u4fe1\u606f\uff1a  \n--reltype=0\u4ee3\u8868\u5176\u4e3a\u7d22\u5f15  \nSELECT locker.pid,  \n        pc.relname,  \n        locker.mode,  \n        locker_act.application_name,  \n        least(query_start,xact_start) start_time,  \n        locker_act.state,  \n        CASE  \n    WHEN granted='f' THEN  \n    'wait_lock'  \n    WHEN granted='t' THEN  \n    'get_lock'  \n    END lock_satus,current_timestamp - least(query_start,xact_start) AS runtime,  \n    locker_act.query  \nFROM pg_locks locker,pg_stat_activity locker_act, pg_class pc  \nWHERE locker.pid=locker_act.pid  \n        AND NOT locker.pid=pg_backend_pid()  \n        AND application_name<>'pg_statsinfod'  \n        AND locker.relation = pc.oid  \n        AND pc.reltype<>0 --and pc.relname='t'  \nORDER BY  runtime desc;  \n\n-\u67e5\u8be2\u7cfb\u7edf\u4e2d\u6b63\u5728\u6267\u884c\u7684\u6216\u8005\u7b49\u5f85\u6267\u884c\u7684\u4e8b\u52a1  \n--\u6ce8\u610f\u5176\u53ea\u662f\u4ee3\u8868\u4e8b\u52a1\u4fe1\u606f\uff0c\u7cfb\u7edf\u4e2d\u4e5f\u6709\u53ef\u80fd\u5b58\u5728\u6162\u7684\u67e5\u8be2  \nselect pc.relname lock_table,pc.oid,tans.pid, CASE  \n    WHEN waiting='f' THEN  \n    'already get lock,sql executing'  \n    WHEN waiting='t' THEN  \n    'waiting get lock,sql waiting execute'  \n    END lock_satus,  \n    least(query_start,xact_start) query_start,  \n    current_timestamp - least(query_start,xact_start) AS runtime,  \n    psa.query  \nfrom pg_locks tans,pg_locks pl,pg_class pc ,pg_stat_activity psa  \nwhere tans.transactionid is NOT null and pc.oid=pl.relation and tans.pid=pl.pid  \nand tans.pid=psa.pid and pc.reltype<>0  \norder by runtime desc;  \n\n--\u67e5\u627e\u662f\u5426\u6709waiting  \nps -ef|grep postgres | grep wait \n\n\u67e5\u8be2\u8868\u4e2d\u5b58\u5728\u7684\u9501\nselect a.locktype,a.database,a.pid,a.mode,a.relation,b.relname from pg_locks a join pg_class b on a.relation = b.oid where upper(b.relname) = 'TABLE_NAME';\n\u518d\u6839\u636e\u4e0a\u9762\u67e5\u51fa\u6765\u7684pid\u53bb\u8868pg_stat_activity\u67e5\u8be2\u4e00\u4e0b\u8be5\u9501\u5bf9\u5e94\u7684SQL\u8bed\u53e5\uff1a\nselect usename,current_query ,query_start,procpid,client_addr from pg_stat_activity where procpid=17509;\n\u6740\u6389\u65b9\u6cd5\uff1a\u5728PG\u6570\u636e\u5e93mydb\u670d\u52a1\u5668\u4e2d\uff0c\u67e5\u8be2\u8fd9\u4e2a\u8fdb\u7a0bPID\u7136\u540eKill\u6389\u3002\nSELECT pg_cancel_backend(\u8fdb\u7a0bid);\nps -ef|grep 17509\n\u6216\u5728\u64cd\u4f5c\u7cfb\u7edf\u4e0b\uff1akill -9 17509<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
postgres\u76d1\u63a7\u6b63\u5728\u6267\u884c\u7684sql —————————– SELECT procpid, start, now() – start AS lap, current_query FROM (SELECT backendid, pg_stat_get_backend_pid(S.backendid) AS procpid, pg_stat_get_backend_activity_start(S.backendid) AS start, pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S WHERE current_query <> '<IDLE>' ORDER BY lap DESC; SELECT datname,pid,query_start,now() – query_start AS lap,waiting,state,query from pg_stat_activity where datname ='tscdb' and state='active' order by lap desc; procpid\uff1a\u8fdb\u7a0bid start\uff1a\u8fdb\u7a0b\u5f00\u59cb\u65f6\u95f4 lap\uff1a\u7ecf\u8fc7\u65f6\u95f4 current_query\uff1a\u6267\u884c\u4e2d\u7684sql ——————————————- \u600e\u6837\u505c\u6b62\u6b63\u5728\u6267\u884c\u7684sql SELECT pg_cancel_backend(\u8fdb\u7a0bid); \u6216\u8005\u7528\u7cfb\u7edf\u51fd\u6570 kill -9 \u8fdb\u7a0bid; ——————————————- \u66f4\u6539\u6570\u636e\u5e93\u7f16\u7801\uff1a show client_encoding; set client_encoding='utf-8'; ——————————————- select * from (SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s) as querystring; \u67e5\u8be2\u6700\u6162\u768410\u6761SQL\uff1a SELECT query, calls, total_time, (total_time\/calls) as average ,rows, 100.0 * shared_blks_hit \/nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY average DESC LIMIT 10; \u6e05\u9664\u7edf\u8ba1\u7ed3\u679c\uff1aselect pg_stat_statements_reset() ; -\u67e5\u770b\u5f53\u524d\u6d3b\u52a8\u7684\u5ba2\u6237\u7aef\u8fde\u63a5\u6570 SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid(); –\u67e5\u8be2\u5ba2\u6237\u7aef\u8fde\u63a5\u7684\u60c5\u51b5 SELECT pid,case when waiting='f' then 'already get lock,sql executing' when waiting='t' then 'waiting get lock,sql waiting execute' end lock_satus, current_timestamp – least(query_start,xact_start) AS runtime,substr(query,1,25) AS current_query FROM pg_stat_activity WHERE NOT pid=pg_backend_pid() and state<>'idle' and application_name<>'pg_statsinfod' order by runtime desc –\u67e5\u770b\u6301\u6709\u9501\u548c\u7b49\u5f85\u9501\u7684\u4e00\u4e9b\u4fe1\u606f\uff1a –reltype=0\u4ee3\u8868\u5176\u4e3a\u7d22…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","footnotes":""},"categories":[89],"tags":[],"class_list":["post-1631","post","type-post","status-publish","format-standard","hentry","category-postgres-db"],"_links":{"self":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1631"}],"version-history":[{"count":1,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1631\/revisions"}],"predecessor-version":[{"id":1632,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1631\/revisions\/1632"}],"wp:attachment":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}