{"id":326,"date":"2025-11-28T16:17:08","date_gmt":"2025-11-28T08:17:08","guid":{"rendered":"https:\/\/yuzikimikage.top\/?p=326"},"modified":"2025-12-23T14:23:24","modified_gmt":"2025-12-23T06:23:24","slug":"common_scripts","status":"publish","type":"post","link":"https:\/\/yuzikimikage.top\/index.php\/2025\/11\/28\/common_scripts\/","title":{"rendered":"\u5e38\u7528\u811a\u672c\u548c\u67e5\u8be2\u8bed\u53e5"},"content":{"rendered":"\n

centos7\u914d\u7f6eyum\u6e90<\/h2>\n\n\n\n
sudo cp \/etc\/yum.repos.d\/CentOS-Base.repo \/etc\/yum.repos.d\/CentOS-Base.repo.backup\nsudo vim \/etc\/yum.repos.d\/CentOS-Base.repo\n\n[base]\nname=CentOS-$releasever - Base - mirrors.aliyun.com\nbaseurl=http:\/\/mirrors.aliyun.com\/centos\/$releasever\/os\/$basearch\/\ngpgcheck=1\ngpgkey=http:\/\/mirrors.aliyun.com\/centos\/RPM-GPG-KEY-CentOS-7\n\n[updates]\nname=CentOS-$releasever - Updates - mirrors.aliyun.com\nbaseurl=http:\/\/mirrors.aliyun.com\/centos\/$releasever\/updates\/$basearch\/\ngpgcheck=1\ngpgkey=http:\/\/mirrors.aliyun.com\/centos\/RPM-GPG-KEY-CentOS-7\n\n[extras]\nname=CentOS-$releasever - Extras - mirrors.aliyun.com\nbaseurl=http:\/\/mirrors.aliyun.com\/centos\/$releasever\/extras\/$basearch\/\ngpgcheck=1\ngpgkey=http:\/\/mirrors.aliyun.com\/centos\/RPM-GPG-KEY-CentOS-7\n\n[centosplus]\nname=CentOS-$releasever - CentOSPlus - mirrors.aliyun.com\nbaseurl=http:\/\/mirrors.aliyun.com\/centos\/$releasever\/centosplus\/$basearch\/\ngpgcheck=1\nenabled=0\ngpgkey=http:\/\/mirrors.aliyun.com\/centos\/RPM-GPG-KEY-CentOS-7\n\nsudo yum clean all \nsudo yum makecache\n<\/code><\/pre>\n\n\n\n
\u5728\u7ebf\u6269\u5bb9\u6839\u76ee\u5f55<\/h2>\n\n\n\n
sudo yum -y install cloud-utils-growpart\nsudo growpart \/dev\/sda 3\nsudo xfs_growfs \/dev\/sda3<\/code><\/pre>\n\n\n\n
PG\u5206\u6790\u4f1a\u8bdd\u7684CPU\u5360\u7528<\/h2>\n\n\n\n
pids=$(top -b -n 1 | awk 'NR>7 {print $1}' | head -n 10 | tr '\\n' ',' | sed 's\/,$\/\/')\npsql -d postgres -c \"SELECT * FROM pg_stat_activity WHERE pid IN ($pids)\"\n\n--\u5206\u6790\u6bcf\u4e2apostgres\u5b50\u8fdb\u7a0b\u7684\u8d44\u6e90\u5360\u7528\n#!\/bin\/bash\n\n# \u67e5\u8be2\u5f53\u524d\u6d3b\u52a8\u8fde\u63a5\u5e76\u63d0\u53d6 PID\npids=$(psql -d postgres -p 6543 -c \"SELECT pid FROM pg_stat_activity where state = 'active'\" -t)\n\n# \u904d\u5386\u6240\u6709 PID \u5e76\u83b7\u53d6\u5bf9\u5e94\u7684 CPU\u548c\u5185\u5b58 \u4f7f\u7528\u60c5\u51b5\nfor pid in $pids\ndo\n    ps -o pid,%cpu,vsz,rss,comm -p $pid\ndone<\/code><\/pre>\n\n\n\n
\u6bcf\u65e5\u4ea7\u751f\u7684wal\u6587\u4ef6\u6570<\/h2>\n\n\n\n
# \/XXOO\/\u66ff\u6362\u4e3a\u5b9e\u9645wal\u6587\u4ef6\u76ee\u5f55\nls -lh --time-style=\"+%Y%m%d\" \/XXOO\/ | grep '^-' | awk '{x[$6]++;} END{for(i in x) print(i \"|\" x[i])}'<\/code><\/pre>\n\n\n\n
\u68c0\u67e5\u7cfb\u7edf\u8d44\u6e90<\/h2>\n\n\n\n
top     ##cpu\u548c\u5185\u5b58\niostat -ctx 2 30  ##\u78c1\u76d8IO<\/code><\/pre>\n\n\n\n
\u6570\u636e\u5e93\u8fde\u63a5<\/h2>\n\n\n\n
select state,count(*) from pg_stat_activity group by state order by 2;\n\nwhile true;do psql -c 'select state,count(*) from pg_stat_activity group by state order by 2;'; date;sleep 5; done\n\n\u6570\u636e\u5e93\u5185\u5b58\u5360\u7528\nselect * from pg_total_memory_detail;\n\n\u67e5\u770b\u8fde\u63a5\u7684\u5185\u5b58\u5360\u7528\nselect a.client_addr,a.usename,a.state,a.query_start,a.pid,left(a.query,50),sum(d.totalsize\/1000\/1000)||'MB' from dbe_perf.session_memory_detail d,pg_stat_activity a where a.pid=right(d.sessid,14)  group by 1,2,3,4,5,6 order by 7 desc;\n<\/code><\/pre>\n\n\n\n
\u957f\u4e8b\u52a1<\/h2>\n\n\n\n
select\n    datname,pid,usename,client_addr,application_name,client_hostname,\n    xact_start, now() - xact_start xact_duration,\n    query_start, now() - query_start query_duration,\n    STATE,query\nfrom pg_stat_activity\nwhere STATE <> 'idle'\n    and now()-xact_start > interval '2 minutes'\norder by xact_start;<\/code><\/pre>\n\n\n\n
\u963b\u585e<\/h2>\n\n\n\n
WITH t_wait AS \n(SELECT\n\ta.mode,a.locktype, a.database,a.relation,\n\ta.page,a.classid, a.objid,a.objsubid,\n\ta.pid,a.virtualtransaction, a.virtualxid,a.transactionid,\n\tb.query,b.xact_start, b.query_start,b.usename,b.datname\nFROM pg_locks a,pg_stat_activity b\nWHERE a.pid = b.pid AND NOT a.granted),\nt_run AS\n(SELECT a.mode,a.locktype,\n\ta.database,a.relation, a.page,a.tuple,\n\ta.classid,a.objid, a.objsubid,a.pid,\n\ta.virtualtransaction,a.virtualxid, a.transactionid,b.query,\n\tb.xact_start,b.query_start, b.usename,b.datname\nFROM pg_locks a,pg_stat_activity b\nWHERE a.pid = b.pid AND a.granted)\nSELECT\n\tr.locktype,r.mode r_mode,\n\tr.usename r_user,r.datname r_db,\n\tr.relation::regclass::regclass,r.pid r_pid,\n\tr.xact_start r_xact_start,r.query_start r_query_start,\n\tnow()-r.query_start r_locktime,r.query r_query,\n\tw.mode w_mode,w.pid w_pid,\n\tw.xact_start w_xact_start,w.query_start w_query_start,\n\tnow()-w.query_start w_locktime,w.query w_query\nFROM t_wait w, t_run r\nWHERE r.locktype IS NOT DISTINCT FROM w.locktype\n\tAND r.database IS NOT DISTINCT FROM w.database\n\tAND r.relation IS NOT DISTINCT FROM w.relation\n\tAND r.page     IS NOT DISTINCT FROM w.page\n\tAND r.tuple    IS NOT DISTINCT FROM w.classid\n\tAND r.classid  IS NOT DISTINCT FROM w.classid\n\tAND r.objid    IS NOT DISTINCT FROM w.objid\n\tAND r.objsubid IS NOT DISTINCT FROM w.objsubid\n\tAND r.transactionid IS NOT DISTINCT FROM w.transactionid\n\tAND r.pid <> w.pid; \n\n--\u963b\u585e\u4fe1\u606f,\u53ea\u6709dml\nselect\n\twaiting1.pid as waiting_pid,\n\twaiting2.usename as waiting_user,\n\twaiting2.query as waiting_statement,\n\tblocking1.pid as blocking_pid,\n\tblocking2.usename as blocking_user,\n\tblocking2.query as blocking_statement\nfrom\n\tpg_locks waiting1\njoin pg_Stat_activity waiting2 on\n\twaiting1.pid = waiting2.pid\njoin pg_locks blocking1 on\n\twaiting1.transactionid = blocking1.transactionid\n\tand waiting1.pid != blocking1.pid\njoin pg_stat_activity blocking2 on\n\tblocking1.pid = blocking2.pid\nwhere\n\tnot waiting1.granted;  \n<\/code><\/pre>\n\n\n\n
\u67e5\u770b\u65e5\u5fd7<\/h2>\n\n\n\n
grep -E 'FATAL|ERROR' postgresql.log<\/code><\/pre>\n\n\n\n
\u7f13\u51b2\u533a\u547d\u4e2d\u7387<\/h2>\n\n\n\n
select \n  round(sum(blks_hit)*100\/sum(blks_hit+blks_read),2)::numeric \nfrom pg_stat_database where datname = current_database(); <\/code><\/pre>\n\n\n\n
\u91cd\u590d\u7d22\u5f15<\/h2>\n\n\n\n
SELECT   indrelid::regclass table_name,\n         att.attname column_name,\n         amname index_method\nFROM     pg_index i,\n         pg_class c,\n         pg_opclass o,\n         pg_am a,\n         pg_attribute att\nWHERE    o.oid = ALL (indclass) \nAND      att.attnum = ANY(i.indkey)\nAND      a.oid = o.opcmethod\nAND      att.attrelid = c.oid\nAND      c.oid = i.indrelid\nGROUP BY table_name, \n         att.attname,\n         indclass,\n         amname, indkey\nHAVING count(*) > 1;\n\n# \u8be6\u7ec6\u7248\n\nSELECT indrelid::regclass::text AS table\n     , idx_columns\n     , indexrelid::regclass::text AS index\nFROM   pg_index i\n     , LATERAL (\n   SELECT string_agg(attname, ', ') AS idx_columns\n   FROM   pg_attribute\n   WHERE  attrelid = i.indrelid\n   AND    attnum  = ANY(i.indkey)  -- 0 excluded by: indexprs IS NULL\n   ) a\nWHERE  EXISTS (\n   SELECT FROM pg_index\n   WHERE  indrelid = i.indrelid\n   AND    indkey = i.indkey\n   AND    indexrelid <> i.indexrelid  -- exclude self\n   )\nAND    indexprs IS NULL -- exclude expression indexes\nORDER  BY 1, 2, 3;\n<\/code><\/pre>\n\n\n\n
vacuum\u65f6\u95f4\u4e0e\u6b7b\u5143\u7ec4\u5360\u6bd4<\/h2>\n\n\n\n
select schemaname,relname,n_live_tup,n_dead_tup,n_dead_tup::float\/(n_dead_tup::float+n_live_tup::float+100)*100 as percent_of_dead_tuples,last_autovacuum from pg_stat_user_tables;<\/code><\/pre>\n\n\n\n
\u8868\u81a8\u80c0<\/h2>\n\n\n\n
--\u67e5\u770b\u81a8\u80c0\u8868\n--otta \u8bc4\u4f30\u51fa\u7684\u8868\u5b9e\u9645\u9700\u8981\u9875\u6570, \n--iotta \u8bc4\u4f30\u51fa\u7684\u7d22\u5f15\u5b9e\u9645\u9700\u8981\u9875\u6570\n--bs \u6570\u636e\u5e93\u7684\u5757\u5927\u5c0f\n--tbloat\u8868\u81a8\u80c0\u500d\u6570\n--ibloat\u7d22\u5f15\u81a8\u80c0\u500d\u6570\n--wastedpages\u8868\u6d6a\u8d39\u4e86\u591a\u5c11\u4e2a\u6570\u636e\u5757\n--wastedipages\u7d22\u5f15\u6d6a\u8d39\u4e86\u591a\u5c11\u4e2a\u6570\u636e\u5757\n--wastedbytes\u8868\u6d6a\u8d39\u4e86\u591a\u5c11\u5b57\u8282\n--wastedibytes\u7d22\u5f15\u6d6a\u8d39\u4e86\u591a\u5c11\u5b57\u8282\nSELECT    \n  current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,    \n  ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages\/otta::numeric END,1) AS tbloat,    \n  CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,    \n  CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,    \n  CASE WHEN relpages < otta THEN $$0 bytes$$::text ELSE (bs*(relpages-otta))::text || $$ bytes$$ END AS wastedsize,    \n  iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,    \n  ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages\/iotta::numeric END,1) AS ibloat,    \n  CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,    \n  CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,    \n  CASE WHEN ipages < iotta THEN $$0 bytes$$ ELSE (bs*(ipages-iotta))::text || $$ bytes$$ END AS wastedisize,    \n  CASE WHEN relpages < otta THEN    \n    CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END    \n    ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)    \n      ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END    \n  END AS totalwastedbytes    \nFROM (    \n  SELECT    \n    nn.nspname AS schemaname,    \n    cc.relname AS tablename,    \n    COALESCE(cc.reltuples,0) AS reltuples,    \n    COALESCE(cc.relpages,0) AS relpages,    \n    COALESCE(bs,0) AS bs,    \n    COALESCE(CEIL((cc.reltuples*((datahdr+ma-    \n      (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))\/(bs-20::float)),0) AS otta,    \n    COALESCE(c2.relname,$$?$$) AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,    \n    COALESCE(CEIL((c2.reltuples*(datahdr-12))\/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols    \n  FROM    \n     pg_class cc    \n  JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> $$information_schema$$    \n  LEFT JOIN    \n  (    \n    SELECT    \n      ma,bs,foo.nspname,foo.relname,    \n      (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,    \n      (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2    \n    FROM (    \n      SELECT    \n        ns.nspname, tbl.relname, hdr, ma, bs,    \n        SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,    \n        MAX(coalesce(null_frac,0)) AS maxfracsum,    \n        hdr+(    \n          SELECT 1+count(*)\/8    \n          FROM pg_stats s2    \n          WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname    \n        ) AS nullhdr    \n      FROM pg_attribute att     \n      JOIN pg_class tbl ON att.attrelid = tbl.oid    \n      JOIN pg_namespace ns ON ns.oid = tbl.relnamespace     \n      LEFT JOIN pg_stats s ON s.schemaname=ns.nspname    \n      AND s.tablename = tbl.relname    \n      AND s.inherited=false    \n      AND s.attname=att.attname,    \n      (    \n        SELECT    \n          (SELECT current_setting($$block_size$$)::numeric) AS bs,    \n            CASE WHEN SUBSTRING(SPLIT_PART(v, $$ $$, 2) FROM $$#\"[0-9]+.[0-9]+#\"%$$ for $$#$$)    \n              IN ($$8.0$$,$$8.1$$,$$8.2$$) THEN 27 ELSE 23 END AS hdr,    \n          CASE WHEN v ~ $$mingw32$$ OR v ~ $$64-bit$$ THEN 8 ELSE 4 END AS ma    \n        FROM (SELECT version() AS v) AS foo    \n      ) AS constants    \n      WHERE att.attnum > 0 AND tbl.relkind=$$r$$    \n      GROUP BY 1,2,3,4,5    \n    ) AS foo    \n  ) AS rs    \n  ON cc.relname = rs.relname AND nn.nspname = rs.nspname    \n  LEFT JOIN pg_index i ON indrelid = cc.oid    \n  LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid    \n) AS sml order by wastedbytes desc limit 5;\n<\/code><\/pre>\n\n\n\n
\u7d22\u5f15\u81a8\u80c0<\/h2>\n\n\n\n
--\u67e5\u770b\u81a8\u80c0\u7d22\u5f15\n--otta \u8bc4\u4f30\u51fa\u7684\u8868\u5b9e\u9645\u9700\u8981\u9875\u6570, \n--iotta \u8bc4\u4f30\u51fa\u7684\u7d22\u5f15\u5b9e\u9645\u9700\u8981\u9875\u6570\n--bs \u6570\u636e\u5e93\u7684\u5757\u5927\u5c0f\n--tbloat\u8868\u81a8\u80c0\u500d\u6570\n--ibloat\u7d22\u5f15\u81a8\u80c0\u500d\u6570\n--wastedpages\u8868\u6d6a\u8d39\u4e86\u591a\u5c11\u4e2a\u6570\u636e\u5757\n--wastedipages\u7d22\u5f15\u6d6a\u8d39\u4e86\u591a\u5c11\u4e2a\u6570\u636e\u5757\n--wastedbytes\u8868\u6d6a\u8d39\u4e86\u591a\u5c11\u5b57\u8282\n--wastedibytes\u7d22\u5f15\u6d6a\u8d39\u4e86\u591a\u5c11\u5b57\u8282\nSELECT\n        schemaname, tablename, reltuples::bigint, relpages::bigint, otta,\n        ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages\/otta::numeric END,1) AS tbloat,\n        relpages::bigint - otta AS wastedpages,\n        bs*(sml.relpages-otta)::bigint AS wastedbytes,\n        pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,\n        iname, ituples::bigint, ipages::bigint, iotta,\n        ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE ipages\/iotta::numeric END,1) AS ibloat,\n        CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,\n        CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,\n        CASE WHEN ipages < iotta THEN pg_size_pretty(0::bigint) ELSE pg_size_pretty((bs*(ipages-iotta))::bigint) \n                  END AS wastedisize\n      FROM (\n        SELECT\n          schemaname, tablename, cc.reltuples, cc.relpages, bs,\n          CEIL((cc.reltuples*((datahdr+ma-\n            (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))\/(bs-20::float)) AS otta,\n                  COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, \n                  COALESCE(c2.relpages,0) AS ipages, \n                  COALESCE(CEIL((c2.reltuples*(datahdr-12))\/(bs-20::float)),0) AS iotta \n                            -- very rough approximation, assumes all cols\n        FROM (\n          SELECT\n            ma,bs,schemaname,tablename,\n            (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,\n            (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2\n          FROM (\n            SELECT\n              schemaname, tablename, hdr, ma, bs,\n              SUM((1-null_frac)*avg_width) AS datawidth,\n              MAX(null_frac) AS maxfracsum,\n              hdr+(\n                SELECT 1+count(*)\/8\n                FROM pg_stats s2\n                WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename\n              ) AS nullhdr\n            FROM pg_stats s, (\n              SELECT\n                (SELECT current_setting('block_size')::numeric) AS bs,\n                CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,\n                CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma\n              FROM (SELECT version() AS v) AS foo\n            ) AS constants\n            GROUP BY 1,2,3,4,5\n          ) AS foo\n        ) AS rs\n        JOIN pg_class cc ON cc.relname = rs.tablename\n        JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname\n        LEFT JOIN pg_index i ON indrelid = cc.oid\n        LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid\n      ) AS sml\n      WHERE sml.relpages - otta > 0 OR ipages - iotta > 10\n      ORDER BY wastedbytes DESC, wastedibytes DESC limit 10;\n<\/code><\/pre>\n\n\n\n
\u5783\u573e\u6570\u636etop5<\/h2>\n\n\n\n
select current_database(),schemaname,relname,n_dead_tup \n from pg_stat_all_tables\nwhere n_live_tup>0 \n and n_dead_tup\/n_live_tup>0.2 \n and schemaname not in ($$pg_toast$$,$$pg_catalog$$) \norder by n_dead_tup desc limit 5;<\/code><\/pre>\n\n\n\n
\u5e74\u9f84<\/h2>\n\n\n\n
--\u67e5\u770b\u6570\u636e\u5e93\u5269\u4f59\u5e74\u9f84\nselect datname,age(datfrozenxid),2^31-age(datfrozenxid) age_remain from pg_database order by age(datfrozenxid);\n\n--\u67e5\u770b\u8868\u5e74\u9f84\nSELECT\ncurrent_database(),\nrolname,\nnspname\nrelkind,\nrelname,\nage(relfrozenxid),\n2^31-age(relfrozenxid) age_remain\nfrom pg_authid tl\nJOIN pg_class t2 ON tl.oid = t2.relowner\nJOIN pg_namespace t3 ON t2.relnamespace = t3.oid\nWHERE t2.relkind IN ('t','r')\nORDER BY age(relfrozenxid) DESC\nlimit 5;<\/code><\/pre>\n\n\n\n
\u78c1\u76d8\u8bfb\u5199<\/h2>\n\n\n\n
\u6df7\u5408\u968f\u673a\u8bfb\u5199\uff087\/3\uff09\uff0c8k\uff0c100\u5e76\u53d1\u3001\u538b\u529b100%\uff08\u6a21\u62df\u8d34\u8fd1\u6570\u636e\u5e93\u8bfb\u5199\u538b\u529b\uff09\nfio --direct=1 --iodepth=10 --bs=8k --size=1G --numjobs=100 --runtime=300 --group_reporting --rw=randrw --rwmixread=70 --name=test --filename=\/vbback\/randrw.file \n\n--dd \u6d4b\u8bd5\u78c1\u76d8\u968f\u673a\u751f\u6210\u6570\u636e\u5185\u5bb9  \u987a\u5e8f\ndd if=\/dev\/urandom of=\/dev\/sdc bs=5G count=1 oflag=dsync\n\n--dd \u6d4b\u8bd5\u78c1\u76d8\u987a\u5e8f\u751f\u6210\u6570\u636e\u5185\u5bb9  \u987a\u5e8f\ndd if=\/dev\/zero of=\/dev\/zero bs=5G count=1 oflag=dsync<\/code><\/pre>\n\n\n\n
\u9501\u7b49\u5f85<\/h2>\n\n\n\n
with t_wait as      \n(      \n  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,     \n  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,      \n  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name     \n    from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted     \n),     \nt_run as     \n(     \n  select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.granted,     \n  a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a.transactionid,a.fastpath,     \n  b.state,b.query,b.xact_start,b.query_start,b.usename,b.datname,b.client_addr,b.client_port,b.application_name     \n    from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted     \n),     \nt_overlap as     \n(     \n  select r.* from t_wait w join t_run r on     \n  (     \n    r.locktype is not distinct from w.locktype and     \n    r.database is not distinct from w.database and     \n    r.relation is not distinct from w.relation and     \n    r.page is not distinct from w.page and     \n    r.tuple is not distinct from w.tuple and     \n    r.virtualxid is not distinct from w.virtualxid and     \n    r.transactionid is not distinct from w.transactionid and     \n    r.classid is not distinct from w.classid and     \n    r.objid is not distinct from w.objid and     \n    r.objsubid is not distinct from w.objsubid and     \n    r.pid <> w.pid     \n  )      \n),      \nt_unionall as      \n(      \n  select r.* from t_overlap r      \n  union all      \n  select w.* from t_wait w      \n)      \nselect locktype,datname,relation::regclass,page,tuple,virtualxid,transactionid::text,classid::regclass,objid,objsubid,     \nstring_agg(     \n'Pid: '||case when pid is null then 'NULL' else pid::text end||chr(10)||     \n'Lock_Granted: '||case when granted is null then 'NULL' else granted::text end||' , Mode: '||case when mode is null then 'NULL' else mode::text end||' , FastPath: '||case when fastpath is null then 'NULL' else fastpath::text end||' , VirtualTransaction: '||case when virtualtransaction is null then 'NULL' else virtualtransaction::text end||' , Session_State: '||case when state is null then 'NULL' else state::text end||chr(10)||     \n'Username: '||case when usename is null then 'NULL' else usename::text end||' , Database: '||case when datname is null then 'NULL' else datname::text end||' , Client_Addr: '||case when client_addr is null then 'NULL' else client_addr::text end||' , Client_Port: '||case when client_port is null then 'NULL' else client_port::text end||' , Application_Name: '||case when application_name is null then 'NULL' else application_name::text end||chr(10)||      \n'Xact_Start: '||case when xact_start is null then 'NULL' else xact_start::text end||' , Query_Start: '||case when query_start is null then 'NULL' else query_start::text end||' , Xact_Elapse: '||case when (now()-xact_start) is null then 'NULL' else (now()-xact_start)::text end||' , Query_Elapse: '||case when (now()-query_start) is null then 'NULL' else (now()-query_start)::text end||chr(10)||      \n'SQL (Current SQL in Transaction): '||chr(10)||    \ncase when query is null then 'NULL' else query::text end,      \nchr(10)||'--------'||chr(10)      \norder by      \n  (  case mode      \n    when 'INVALID' then 0     \n    when 'AccessShareLock' then 1     \n    when 'RowShareLock' then 2     \n    when 'RowExclusiveLock' then 3     \n    when 'ShareUpdateExclusiveLock' then 4     \n    when 'ShareLock' then 5     \n    when 'ShareRowExclusiveLock' then 6     \n    when 'ExclusiveLock' then 7     \n    when 'AccessExclusiveLock' then 8     \n    else 0     \n  end  ) desc,     \n  (case when granted then 0 else 1 end)    \n) as lock_conflict    \nfrom t_unionall     \ngroup by     \nlocktype,datname,relation,page,tuple,virtualxid,transactionid::text,classid,objid,objsubid ;\n<\/code><\/pre>\n\n\n\n
\u6162SQL<\/h2>\n\n\n\n
# \u5e73\u5747\u6267\u884c\u8017\u65f6\u6700\u957f\u7684SQL\n\n# for pg10\/11\/12\nselect  replace(query,chr(10), ' ') as 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# for pg 13+\nselect  replace(query,chr(10), ' ') as query, calls, total_exec_time, (total_exec_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\n\n# \u6d88\u8017CPU\u6700\u957f\u7684SQL\n\n# for pg 10\/11\/12\n--\u6d88\u8017cpu\u65f6\u95f4\u8d85\u8fc7\u6240\u6709SQL\u8bed\u53e5\u6d88\u8017\u7684\u603bCPU\u65f6\u95f4\u76845%\u7684SQL\u8bed\u53e5\n\n--\u8bbe\u7f6e\u663e\u793asql\u957f\u5ea6,\u91cd\u542f\u751f\u6548\nalter system set track_activity_query_size=4096\uff1b\n--\u5148\u6e05\u9664\u7d2f\u8ba1\u7684\u7edf\u8ba1\u4fe1\u606f\nselect pg_stat_statements_reset();\n--\u7136\u540e\u7b49\u4e00\u6bb5\u65f6\u95f4\u540e\u518d\u67e5\u8be2\u5982\u4e0b\u7684\u8bed\u53e5\nwith total as\n (select sum(total_time) as total_exec_time,\n         sum(blk_read_time + blk_write_time) as io_time,\n         sum(total_time - blk_read_time - blk_write_time) as cpu_time,\n         sum(calls) as ncalls,\n         sum(rows) as total_rows\n    from pg_stat_statements\n   where dbid in\n         (select oid from pg_database where datname = current_database()))\nselect userid::regrole,dbid,calls,pss.total_time,min_time,max_time,rows,shared_blks_hit,shared_blks_read,\n       shared_blks_dirtied,shared_blks_written,local_blks_hit,local_blks_read,local_blks_dirtied,local_blks_written,\n       temp_blks_read,temp_blks_written,blk_read_time,blk_write_time,io_time,cpu_time,total_rows,\n       (pss.total_time - pss.blk_read_time - pss.blk_write_time) \/ total.cpu_time * 100 as cpu_pct,\n        replace(query,chr(10), ' ') as query\n  from pg_stat_statements pss, total\n where (pss.total_time - pss.blk_read_time - pss.blk_write_time) \/ total.cpu_time >= 0.1\n order by pss.total_time - pss.blk_read_time - pss.blk_write_time desc;\n\n# for pg13+\n--\u6d88\u8017cpu\u65f6\u95f4\u8d85\u8fc7\u6240\u6709SQL\u8bed\u53e5\u6d88\u8017\u7684\u603bCPU\u65f6\u95f4\u76845%\u7684SQL\u8bed\u53e5\n\n--\u8bbe\u7f6e\u663e\u793asql\u957f\u5ea6,\u91cd\u542f\u751f\u6548\nalter system set track_activity_query_size=4096\uff1b\n--\u5148\u6e05\u9664\u7d2f\u8ba1\u7684\u7edf\u8ba1\u4fe1\u606f\nselect pg_stat_statements_reset();\n--\u7136\u540e\u7b49\u4e00\u6bb5\u65f6\u95f4\u540e\u518d\u67e5\u8be2\u5982\u4e0b\u7684\u8bed\u53e5\nwith total as\n (select sum(total_exec_time) as total_exec_time,\n         sum(blk_read_time + blk_write_time) as io_time,\n         sum(total_exec_time - blk_read_time - blk_write_time) as cpu_time,\n         sum(calls) as ncalls,\n         sum(rows) as total_rows\n    from pg_stat_statements\n   where dbid in\n         (select oid from pg_database where datname = current_database()))\nselect userid::regrole,dbid,calls,pss.total_exec_time,min_exec_time,max_exec_time,rows,shared_blks_hit,shared_blks_read,\n       shared_blks_dirtied,shared_blks_written,local_blks_hit,local_blks_read,local_blks_dirtied,local_blks_written,\n       temp_blks_read,temp_blks_written,blk_read_time,blk_write_time,io_time,cpu_time,total_rows,\n       (pss.total_exec_time - pss.blk_read_time - pss.blk_write_time) \/ total.cpu_time * 100 as cpu_pct,\n        replace(query,chr(10), ' ') as query\n  from pg_stat_statements pss, total\n where (pss.total_exec_time - pss.blk_read_time - pss.blk_write_time) \/ total.cpu_time >= 0.1\n order by pss.total_exec_time - pss.blk_read_time - pss.blk_write_time desc;\n\n\n# \u6d88\u8017IO\u65f6\u95f4\u8d85\u8fc7\u6240\u6709SQL\u8bed\u53e5\u6d88\u8017\u7684\u603bIO\u65f6\u95f4\u76845%\u7684SQL\u8bed\u53e5--for pg10+\nwith total as\n (select sum(total_time) as total_time,\n         sum(blk_read_time + blk_write_time) as io_time,\n         sum(total_time - blk_read_time - blk_write_time) as cpu_time,\n         sum(calls) as ncalls,\n         sum(rows) as total_rows\n    from pg_stat_statements\n   where dbid in\n         (select oid from pg_database where datname = current_database()))\nselect *,\n       (pss.blk_read_time + pss.blk_write_time) \/ total.io_time * 100 as io_pct\n  from pg_stat_statements pss, total\n where (pss.blk_read_time + pss.blk_write_time) \/ total.io_time >= 0.05\n   and total.io_time > 0\n order by pss.blk_read_time + pss.blk_write_time desc;\n<\/code><\/pre>\n\n\n\n
\u8ba1\u7b97\u5185\u6838\u53c2\u6570<\/h2>\n\n\n\n
echo \"kernel.sem= `cat \/proc\/sys\/kernel\/sem`\" && cat \/proc\/meminfo|grep MemTotal|awk {'print $2'*1024*0.8\/$(getconf PAGE_SIZE)}|awk '{printf \"kernel.shmall=%d\\n\",$1}' && cat \/proc\/meminfo|grep MemTotal| awk '{printf \"kernel.shmmax=%d\\n\",$2*1024*0.8}' && echo \"kernel.shmmni =`cat \/proc\/sys\/kernel\/shmmni`\"<\/code><\/pre>\n\n\n\n
\u590d\u5236\u69fd<\/h2>\n\n\n\n
-- 1. \u67e5\u770b\u6240\u6709\u903b\u8f91\u590d\u5236\u69fd\uff08slot_type = 'logical'\uff09\nselect * from pg_replication_slots where slot_type = 'logical';\n\n-- 2. \u5224\u65ad\u590d\u5236\u69fd\u662f\u5426\u5b58\u5728\uff08\u66ff\u6362 'your_slot_name'\uff09\nSELECT EXISTS (\n    SELECT 1\n    FROM pg_replication_slots\n    WHERE slot_type = 'logical' AND slot_name = 'your_slot_name'\n) AS slot_exists;\n\n-- 3. \u67e5\u770b\u903b\u8f91\u590d\u5236\u69fd\u662f\u5426\u6d3b\u8dc3\uff08active \u8868\u793a\u662f\u5426\u88ab\u8ba2\u9605\u7aef\u5360\u7528\uff09\nSELECT slot_name, active\nFROM pg_replication_slots\nWHERE slot_type = 'logical';\n\n-- 4. \u68c0\u67e5\u903b\u8f91\u69fd\u5ef6\u8fdf\uff08WAL \u5806\u79ef\u5b57\u8282\u6570\uff09\uff0c\u5224\u65ad\u662f\u5426\u53ef\u80fd\u5931\u6548\u6216\u5361\u4f4f\nSELECT\n    slot_name,\n    active,\n    pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)) AS wal_delay,\n    pg_current_wal_lsn() AS current_lsn,\n    confirmed_flush_lsn\nFROM pg_replication_slots\nWHERE slot_type = 'logical';\n\n-- 5. \u67e5\u770b\u903b\u8f91\u8ba2\u9605\u5ef6\u8fdf\uff08\u8ba2\u9605\u7aef\u4f7f\u7528\uff0c\u67e5\u8be2 pg_stat_subscription\uff09\nSELECT\n    subname,\n    received_lsn,\n    latest_end_lsn,\n    latest_end_time,\n    now() AS now,\n    EXTRACT(EPOCH FROM now() - latest_end_time)::int AS delay_seconds\nFROM pg_stat_subscription;<\/code><\/pre>\n\n\n\n
\u6570\u636e\u5bfc\u51fa<\/h2>\n\n\n\n
######\u5c06\u6570\u636e\u5bfc\u51fa\u5230txt\u6587\u4ef6*************************************************\nsu - vastbase\nvsql -r -d dbname -U username -W password -c \"\\copy (select * from t_t1) to '\/tmp\/t_t1_v1.txt' with  delimiter ','\"<\/code><\/pre>\n\n\n\n
pg_freespacemap<\/h2>\n\n\n\n
# \u8ba1\u7b97\u8868\u7684\u5e73\u5747\u7a7a\u95f4\u7a7a\u95f2\u7387\ncreate extension pg_freespacemap;\n\nselect count(*) as \"number of pages\",\npg_size_pretty(cast(avg(avail) as bigint)) as \"Av. freespace size\",\nround(100 * avg(avail)\/8192 ,2) as \"Av. freespace ratio\"\nfrom pg_freespace('test');\n\n# \u67e5\u8be2\u68c0\u67e5\u7279\u5b9a\u8868\u4e2d\u6bcf\u4e2a\u9875\u9762\u7684\u81ea\u7531\u7a7a\u95f4\u5360\u6bd4\nselect *,round(100 * avail\/8192 ,2) as \"freespace ratio\" from pg_freespace('test');<\/code><\/pre>\n\n\n\n
pageinspect<\/h2>\n\n\n\n
# \u67e5\u770b\u8868\u5728\u9875\u9762\u7684\u5b9e\u9645\u5b58\u50a8\ncreate extension pageinspect;\n\nselect * from heap_page_items(get_raw_page('test',0));\n\n# \u67e5\u770b\u9875\u5934\nSELECT * from page_header(get_raw_page('test', 0));\n\n# \u4e00\u4e2a\u9875\u9762\u6709\u591a\u5c11\u884c\u6570\u636e\nselect count(*) from heap_page_items(get_raw_page('test',0));\n# \u4e00\u4e2a\u8868\u6709\u591a\u5c11\u884c\u6570\u636e\nselect count(*) from test;\n# \u4e24\u8005\u76f8\u9664\uff0c\u53ef\u5927\u81f4\u4f30\u7b97\u51fa\u4e00\u4e2a\u8868\u6709\u591a\u5c11\u9875\n<\/code><\/pre>\n\n\n\n
\u8ba1\u7b97\u67d0\u4e2aschema\u4e0b\u6709\u591a\u5c11\u5f20\u8868<\/h2>\n\n\n\n
select count(*) from information_schema.tables where table_schema='sjzx';<\/code><\/pre>\n\n\n\n
VB\u67e5\u6570\u636e\u5e93\u5e74\u9f84<\/h2>\n\n\n\n
select datname,age(datfrozenxid64),2^31-age(datfrozenxid64) age_remain from pg_database order by age(datfrozenxid64);\n\nSELECT age(pgo.ctime) FROM pg_object pgo,pg_class pgc WHERE pgc.oid = pgo.object_oid  AND pgc.relname = 'table_name';<\/code><\/pre>\n\n\n\n
VB\u67e5\u963b\u585e<\/h2>\n\n\n\n
WITH t_wait AS \n(SELECT\n\ta.mode,a.locktype, a.database,a.relation,\n\ta.page,a.classid, a.objid,a.objsubid,\n\ta.pid,a.virtualtransaction, a.virtualxid,a.transactionid,\n\tb.query,b.xact_start, b.query_start,b.usename,b.datname\nFROM pg_locks a,pg_stat_activity b\nWHERE a.pid = b.pid AND NOT a.granted),\nt_run AS\n(SELECT a.mode,a.locktype,\n\ta.database,a.relation, a.page,a.tuple,\n\ta.classid,a.objid, a.objsubid,a.pid,\n\ta.virtualtransaction,a.virtualxid, a.transactionid,b.query,\n\tb.xact_start,b.query_start, b.usename,b.datname\nFROM pg_locks a,pg_stat_activity b\nWHERE a.pid = b.pid AND a.granted)\nSELECT\n\tr.locktype,r.mode r_mode,\n\tr.usename r_user,r.datname r_db,\n\tr.relation::regclass::regclass,r.pid r_pid,\n\tr.xact_start r_xact_start,r.query_start r_query_start,\n\tnow()-r.query_start r_locktime,r.query r_query,\n\tw.mode w_mode,w.pid w_pid,\n\tw.xact_start w_xact_start,w.query_start w_query_start,\n\tnow()-w.query_start w_locktime,w.query w_query\nFROM t_wait w, t_run r\nWHERE r.locktype IS NOT DISTINCT FROM w.locktype\n\tAND r.database IS NOT DISTINCT FROM w.database\n\tAND r.relation IS NOT DISTINCT FROM w.relation\n\tAND r.page     IS NOT DISTINCT FROM w.page\n\tAND r.tuple    IS NOT DISTINCT FROM w.classid\n\tAND r.classid  IS NOT DISTINCT FROM w.classid\n\tAND r.objid    IS NOT DISTINCT FROM w.objid\n\tAND r.objsubid IS NOT DISTINCT FROM w.objsubid\n\tAND r.transactionid IS NOT DISTINCT FROM w.transactionid\n\tAND r.pid <> w.pid; \n<\/code><\/pre>\n\n\n\n
\u67e5\u770b\u8868\u7ed3\u6784<\/h2>\n\n\n\n
select pg_get_tabledef('\u8868\u540d');<\/code><\/pre>\n\n\n\n
\u5207\u6362vastbase\u7528\u6237\u65f6\u81ea\u52a8\u52a0\u8f7d\u6570\u636e\u5e93\u73af\u5883\u53d8\u91cf<\/h2>\n\n\n\n
echo -e \"\\nsource \/home\/vastbase\/.Vastbase\" >> \/home\/vastbase\/.bashrc<\/code><\/pre>\n\n\n\n
VB\u67e5\u6570\u636e\u5e93\u5185\u5b58<\/h2>\n\n\n\n
select case when \nmemorytype='max_process_memory'      then 'VastbaseG100\u5b9e\u4f8b\u5141\u8bb8\u5360\u7528\u7684\u6700\u5927\u5185\u5b58'\n     when memorytype='process_used_memory'     then '\u6570\u636e\u5e93\u8fdb\u7a0b\u5360\u7528\u7684\u5185\u5b58'\n     when memorytype='max_dynamic_memory'      then '\u6700\u5927\u52a8\u6001\u5185\u5b58'\n     when memorytype='dynamic_used_memory'     then '\u5df2\u4f7f\u7528\u7684\u52a8\u6001\u5185\u5b58'\n     when memorytype='dynamic_peak_memory'     then '\u5185\u5b58\u7684\u52a8\u6001\u5cf0\u503c'\n     when memorytype='dynamic_used_shrctx'     then '\u6700\u5927\u52a8\u6001\u5171\u4eab\u5185\u5b58\u4e0a\u4e0b\u6587'\n     when memorytype='dynamic_peak_shrctx'     then '\u5171\u4eab\u5185\u5b58\u4e0a\u4e0b\u6587\u7684\u52a8\u6001\u5cf0\u503c'\n     when memorytype='max_shared_memory'       then '\u6700\u5927\u5171\u4eab\u5185\u5b58'\n     when memorytype='shared_used_memory'      then '\u5df2\u4f7f\u7528\u7684\u5171\u4eab\u5185\u5b58'\n     when memorytype='max_cstore_memory'       then '\u5217\u5b58\u6240\u5141\u8bb8\u4f7f\u7528\u7684\u6700\u5927\u5185\u5b58'\n     when memorytype='cstore_used_memory'      then '\u5217\u5b58\u5df2\u4f7f\u7528\u7684\u5185\u5b58\u5927\u5c0f'\n     when memorytype='max_sctpcomm_memory'  then 'sctp\u901a\u4fe1\u6240\u5141\u8bb8\u4f7f\u7528\u7684\u6700\u5927\u5185\u5b58'\n     when memorytype='sctpcomm_used_memory'    then 'sctp\u901a\u4fe1\u5df2\u4f7f\u7528\u7684\u5185\u5b58\u5927\u5c0f'\n     when memorytype='sctpcomm_peak_memory'    then 'sctp\u901a\u4fe1\u7684\u5185\u5b58\u5cf0\u503c'\n     when memorytype='other_used_memory'       then '\u5176\u4ed6\u5df2\u4f7f\u7528\u7684\u5185\u5b58\u5927\u5c0f'\n     when memorytype='gpu_max_dynamic_memory'  then 'GPU\u6700\u5927\u52a8\u6001\u5185\u5b58'\n     when memorytype='gpu_dynamic_used_memory' then 'GPU\u5df2\u4f7f\u7528\u7684\u52a8\u6001\u5185\u5b58'\n     when memorytype='gpu_dynamic_peak_memory' then 'GPU\u5185\u5b58\u7684\u52a8\u6001\u5cf0\u503c'\n     when memorytype='pooler_conn_memory'      then '\u94fe\u63a5\u6c60\u7533\u8bf7\u5185\u5b58\u8ba1\u6570'\n     when memorytype='pooler_freeconn_memory'  then '\u94fe\u63a5\u6c60\u7a7a\u95f2\u8fde\u63a5\u7684\u5185\u5b58\u8ba1\u6570'\n    when memorytype='storage_compress_memory' then '\u5b58\u50a8\u6a21\u5757\u538b\u7f29\u4f7f\u7528\u7684\u5185\u5b58\u5927\u5c0f'\n     when memorytype='udf_reserved_memory'     then 'UDF\u9884\u7559\u7684\u5185\u5b58\u5927\u5c0f'\n\t else memorytype end as memorytype\n\t ,memorymbytes from pg_total_memory_detail;\n\n--\u67e5\u770b\u5185\u5b58\u8017\u5728\u54ea\u91cc\nselect sessid,contextname,totalsize\/1024\/1024 as totalsize,freesize\/1024\/1024 freesize,usedsize\/1024\/1024 as usedsize from dbe_perf.session_memory_detail group by sessid,contextname order by 3 desc limit 5;\n\nselect sessid,contextname,sum(totalsize)\/1024\/1024 sum,sum(freesize)\/1024\/1024 freesize,count(*) count from gs_session_memory_detail group by sessid,contextname order by sum desc limit 10;\n\nwith t1 as (select sessid, sum(totalsize) totalsize, sum(usedsize) usedsize from gs_session_memory_detail group by 1) select a.sessionid, a.datname, a.usename, a.application_name, a.client_addr, pg_size_pretty(totalsize) total_size, pg_size_pretty(usedsize) used_size, a.query  from pg_stat_activity a, t1  where a.sessionid = split_part(sessid,'.',2) order by usedsize desc; <\/code><\/pre>\n\n\n\n
\u67e5\u8be2\u963b\u585e<\/h2>\n\n\n\n
-- \u67e5\u8be2\u963b\u585e\u4f1a\u8bdd\u7684SQL\n--\u7b80\u5316\u7248\nselect ws.sessionid, --\u88ab\u963b\u585e\u4f1a\u8bddID\nws.wait_status, -- \u7b49\u5f85\u72b6\u6001\nws.wait_event, -- \u7b49\u5f85\u4e8b\u4ef6\nws.lockmode, -- \u9501\u6a21\u5f0f\nsubstr(at.query, 0, 50) as query, -- \u88ab\u963b\u585e\u8bed\u53e5\nws.block_sessionid -- \u9020\u6210\u963b\u585e\u7684\u4f1a\u8bddID\nfrom pg_thread_wait_status as ws, pg_stat_activity as at \nwhere ws.sessionid = at.pid\nand ws.wait_status = 'acquire lock'; \n\n--\u8be6\u60c5\u7248\nwith t_lock as (select psa.pid as ssid,psa.*,pl.* from pg_stat_activity psa,pg_locks pl where psa.pid=pl.pid and psa.pid<>pg_backend_pid())\nselect \nl.relation::regclass, -- \u88ab\u9501\u5b9a\u7684\u8868\u540d\nl.locktype as l_locktype, -- \u9501\u7c7b\u578b\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.mode as l_mode, -- \u9501\u6a21\u5f0f\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.usename as l_user, -- \u7528\u6237\u540d\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.datname as l_db, -- \u6570\u636e\u5e93\u540d\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.ssid as l_pid, -- \u4f1a\u8bddID\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.state as l_state, -- \u4f1a\u8bdd\u72b6\u6001\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.xact_start as l_xact_start, -- \u4e8b\u52a1\u5f00\u59cb\u65f6\u95f4\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.query_start as l_query_start, -- \u67e5\u8be2\u5f00\u59cb\u65f6\u95f4\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nnow() - l.query_start as l_locktime, -- \u9501\u6301\u6709\u65f6\u95f4\uff08\u6301\u6709\u9501\u7684\u4f1a\u8bdd\uff09\nl.query as l_query, -- \u6301\u6709\u9501\u7684\u67e5\u8be2\u8bed\u53e5\nb.locktype as b_locktype, -- \u9501\u7c7b\u578b\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nb.mode as b_mode, -- \u9501\u6a21\u5f0f\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nb.ssid as b_pid, -- \u4f1a\u8bddID\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nb.state as b_state, -- \u4f1a\u8bdd\u72b6\u6001\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nb.xact_start as b_xact_start, -- \u4e8b\u52a1\u5f00\u59cb\u65f6\u95f4\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nb.query_start as b_query_start, -- \u67e5\u8be2\u5f00\u59cb\u65f6\u95f4\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nnow() - b.query_start as b_locktime, -- \u9501\u7b49\u5f85\u65f6\u95f4\uff08\u88ab\u963b\u585e\u7684\u4f1a\u8bdd\uff09\nb.query as b_query -- \u88ab\u963b\u585e\u7684\u67e5\u8be2\u8bed\u53e5\nfrom t_lock l, t_lock b \nwhere l.locktype is not distinct from b.locktype \nand l.database is not distinct from b.database\nand l.relation is not distinct from b.relation \nand l.page is not distinct from b.page\nand l.tuple is not distinct from b.classid\nand l.classid is not distinct from b.classid \nand l.objid is not distinct from b.objid \nand l.objsubid is not distinct from b.objsubid \nand l.transactionid is not distinct from b.transactionid \nand l.granted = true \nand b.granted = false\nand l.ssid <> b.ssid; \n<\/code><\/pre>\n\n\n\n
postgres\u67e5\u8be2\u6240\u6709\u7528\u6237\u53ca\u5176\u72b6\u6001<\/h2>\n\n\n\n
SELECT rolname       AS \u7528\u6237\u540d,\n       CASE WHEN rolcanlogin THEN '\u5141\u8bb8\u767b\u5f55' ELSE '\u7981\u6b62\u767b\u5f55' END AS \u767b\u5f55\u6743\u9650,\n       CASE WHEN rolsuper     THEN '\u8d85\u7ea7\u7528\u6237' ELSE '\u666e\u901a\u7528\u6237' END AS \u7528\u6237\u7c7b\u578b,\n       CASE WHEN rolvaliduntil IS NULL THEN '\u6c38\u4e45\u6709\u6548'\n            WHEN rolvaliduntil > now() THEN '\u751f\u6548\u4e2d '\n            ELSE '\u5df2\u8fc7\u671f' END AS \u8d26\u53f7\u72b6\u6001,\n       rolconnlimit  AS \u6700\u5927\u8fde\u63a5\u6570,\n       pg_user.usename IS NOT NULL AS \u5f53\u524d\u5df2\u8fde\u63a5\nFROM pg_roles\nLEFT JOIN pg_user ON pg_user.usename = pg_roles.rolname\nWHERE rolcanlogin          -- \u53ea\u770b\u80fd\u767b\u5f55\u7684\u8d26\u53f7\nORDER BY rolname;<\/code><\/pre>\n\n\n\n
\u67e5\u8be2\u4e00\u6bb5\u65f6\u95f4\u5f00\u59cb\u7684\u6162sql<\/h2>\n\n\n\n
select unique_sql_id as sqlid,user_name as \"\u7528\u6237\u540d\u79f0\",query as \"SQL\u6587\u672c\",n_calls as \u6267\u884c\u6b21\u6570, last_updated as \"\u6700\u8fd1\u4e00\u6b21\u6267\u884c\u65f6\u95f4\",min_elapse_time\/1000 as \"\u6700\u5feb\u6267\u884c\u8017\u65f6ms\",max_elapse_time\/1000 as \"\u6700\u6162\u6267\u884c\u8017\u65f6ms\",n_returned_rows as \"\u7ed3\u679c\u96c6\u884c\u6570\",n_tuples_fetched as \"\u8bfb\u53d6\u884c\u6570\",n_tuples_inserted as \"\u5199\u5165\u884c\u6570\",n_tuples_updated as \"\u66f4\u65b0\u884c\u6570\",n_tuples_deleted as \"\u5220\u9664\u884c\u6570\",n_blocks_fetched as \"\u8bfb\u53d6\u5757\u6570\",n_blocks_hit as \"\u7f13\u5b58\u547d\u4e2d\u5757\u6570\",data_io_time as \"\u786c\u76d8IO\u65f6\u95f4\",sort_time as \"\u6392\u5e8f\u8017\u65f6\"\nfrom dbe_perf.statement \nwhere last_updated >= '2025-10-30 00:00:00'  \nand  user_name <> 'vastbase'\nand \"\u6700\u6162\u6267\u884c\u8017\u65f6ms\" > 500\norder by  min_elapse_time desc;<\/code><\/pre>\n\n\n\n
\u67e5\u8be2\u7b49\u5f85\u4e8b\u4ef6<\/h2>\n\n\n\n
select wait_status,wait_event ,count(1) from pg_thread_wait_status group by 1,2 order by 3 desc;<\/code><\/pre>\n\n\n\n
\u67e5\u8be2\u6162sql<\/h2>\n\n\n\n
select start_time,round(db_time\/1000,0) as db_time,\nround(cpu_time\/1000,0) as db_time,\nround(execution_time\/1000,0) as exec_time,\nround(parse_time\/1000,0) as parse_time,\nround(plan_time\/1000,0) as plan_time,\nquery,query_plan\nfrom statement_history\nwhere \nstart_time>='2025-10-30 07:15:18'\nand start_time<='2025-10-30 18:00:00'\nand user_name='u8c'\nand application_name='Vastbase JDBC Driver'\norder by start_time desc;<\/code><\/pre>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
centos7\u914d\u7f6eyum\u6e90 \u5728\u7ebf\u6269\u5bb9\u6839\u76ee\u5f55 PG\u5206\u6790\u4f1a\u8bdd\u7684CPU\u5360\u7528 \u6bcf\u65e5\u4ea7\u751f\u7684wal\u6587\u4ef6\u6570 \u68c0\u67e5\u7cfb\u7edf\u8d44\u6e90 \u6570\u636e\u5e93\u8fde\u63a5 \u957f\u4e8b\u52a1 \u963b …<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"emotion":"","emotion_color":"","title_style":"","license":"","footnotes":""},"categories":[18,22],"tags":[],"class_list":["post-326","post","type-post","status-publish","format-standard","hentry","category-18","category-22"],"_links":{"self":[{"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/posts\/326","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/comments?post=326"}],"version-history":[{"count":22,"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/posts\/326\/revisions"}],"predecessor-version":[{"id":356,"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/posts\/326\/revisions\/356"}],"wp:attachment":[{"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/media?parent=326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/categories?post=326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yuzikimikage.top\/index.php\/wp-json\/wp\/v2\/tags?post=326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}