{"id":294603,"date":"2019-09-12T15:00:15","date_gmt":"2019-09-12T15:00:15","guid":{"rendered":"http:\/\/savepearlharbor.com\/?p=294603"},"modified":"-0001-11-30T00:00:00","modified_gmt":"-0001-11-29T21:00:00","slug":"","status":"publish","type":"post","link":"https:\/\/savepearlharbor.com\/?p=294603","title":{"rendered":"\u041f\u043e\u043f\u044b\u0442\u043a\u0430 \u0441\u043e\u0437\u0434\u0430\u0442\u044c \u0430\u043d\u0430\u043b\u043e\u0433 ASH \u0434\u043b\u044f PostgreSQL"},"content":{"rendered":"\n
\n

\u041f\u043e\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0430 \u0437\u0430\u0434\u0430\u0447\u0438<\/h1>\n

\u0414\u043b\u044f \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0438 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 PostgreSQL, \u043e\u0447\u0435\u043d\u044c \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u044c \u0430\u043d\u0430\u043b\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0438\u0441\u0442\u043e\u0440\u0438\u044e \u0430\u043a\u0442\u0438\u0432\u043d\u043e\u0441\u0442\u0438, \u0432 \u0447\u0430\u0441\u0442\u043d\u043e\u0441\u0442\u0438 \u2013 \u043e\u0436\u0438\u0434\u0430\u043d\u0438\u044f, \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043a\u0438, \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0430 \u0442\u0430\u0431\u043b\u0438\u0446. <\/p>\n

\u0418\u043c\u0435\u044e\u0449\u0438\u0435\u0441\u044f \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438<\/h1>\n

\u0418\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442 \u0430\u043d\u0430\u043b\u0438\u0437\u0430 \u0438\u0441\u0442\u043e\u0440\u0438\u0447\u0435\u0441\u043a\u043e\u0439 \u043d\u0430\u0433\u0440\u0443\u0437\u043a\u0438 \u0438\u043b\u0438 \u00abAWR \u0434\u043b\u044f Postgres\u00bb<\/a>: \u043e\u0447\u0435\u043d\u044c \u0438\u043d\u0442\u0435\u0440\u0435\u0441\u043d\u043e\u0435 \u0440\u0435\u0448\u0435\u043d\u0438\u0435, \u043d\u043e, \u043d\u0435\u0442 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 pg_stat_activity \u0438 pg_locks.<\/p>\n

\u0420\u0430\u0441\u0448\u0438\u0440\u0435\u043d\u0438\u0435 pgsentinel <\/a>:
«\u0412\u0441\u044f \u043d\u0430\u043a\u043e\u043f\u043b\u0435\u043d\u043d\u0430\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u0445\u0440\u0430\u043d\u0438\u0442\u0441\u044f \u0442\u043e\u043b\u044c\u043a\u043e \u0432 \u043e\u043f\u0435\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0439 \u043f\u0430\u043c\u044f\u0442\u0438, \u0430 \u043f\u043e\u0442\u0440\u0435\u0431\u043b\u044f\u0435\u043c\u044b\u0439 \u043e\u0431\u044a\u0451\u043c \u043f\u0430\u043c\u044f\u0442\u0438 \u0440\u0435\u0433\u0443\u043b\u0438\u0440\u0443\u0435\u0442\u0441\u044f \u043a\u043e\u043b\u0438\u0447\u0435\u0441\u0442\u0432\u043e\u043c \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0438\u0445 \u0445\u0440\u0430\u043d\u0438\u043c\u044b\u0445 \u0437\u0430\u043f\u0438\u0441\u0435\u0439.
\u0414\u043e\u0431\u0430\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u043f\u043e\u043b\u0435 queryid \u2014 \u0442\u043e\u0442 \u0441\u0430\u043c\u044b\u0439 queryid \u0438\u0437 \u0440\u0430\u0441\u0448\u0438\u0440\u0435\u043d\u0438\u044f pg_stat_statements (\u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u043f\u0440\u0435\u0434\u0432\u0430\u0440\u0438\u0442\u0435\u043b\u044c\u043d\u0430\u044f \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0430).<\/i>«
\u042d\u0442\u043e \u043a\u043e\u043d\u0435\u0447\u043d\u043e \u0441\u0438\u043b\u044c\u043d\u043e \u0431\u044b \u043f\u043e\u043c\u043e\u0433\u043b\u043e, \u043d\u043e \u0441\u0430\u043c\u0430\u044f \u043d\u0435\u043f\u0440\u0438\u044f\u0442\u043d\u043e\u0441\u0442\u044c \u0438\u043c\u0435\u043d\u043d\u043e \u043f\u0435\u0440\u0432\u044b\u0439 \u043f\u0443\u043d\u043a\u0442 \u201c\u0412\u0441\u044f \u043d\u0430\u043a\u043e\u043f\u043b\u0435\u043d\u043d\u0430\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f \u0445\u0440\u0430\u043d\u0438\u0442\u0441\u044f \u0442\u043e\u043b\u044c\u043a\u043e \u0432 \u043e\u043f\u0435\u0440\u0430\u0442\u0438\u0432\u043d\u043e\u0439 \u043f\u0430\u043c\u044f\u0442\u0438<\/b> \u201d, \u0442.\u0435. \u0438\u043c\u0435\u0435\u043c \u043c\u0435\u0441\u0442\u043e \u0438\u043c\u043f\u0430\u043a\u0442 \u043d\u0430 \u0446\u0435\u043b\u0435\u0432\u0443\u044e \u0431\u0430\u0437\u0443. \u041a \u0442\u043e\u043c\u0443, \u0436\u0435 \u043d\u0435\u0442 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a \u0438 \u0441\u0442\u0430\u0442\u0438\u0441\u0442\u0438\u043a\u0438 \u0442\u0430\u0431\u043b\u0438\u0446. \u0422.\u0435. \u0440\u0435\u0448\u0435\u043d\u0438\u0435 \u0432\u043e\u043e\u0431\u0449\u0435 \u0433\u043e\u0432\u043e\u0440\u044f \u043d\u0435\u043f\u043e\u043b\u043d\u043e\u0435: \u201c\u0413\u043e\u0442\u043e\u0432\u043e\u0433\u043e \u043f\u0430\u043a\u0435\u0442\u0430 \u0434\u043b\u044f \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u043a\u0438 \u043f\u043e\u043a\u0430 \u043d\u0435\u0442. \u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u0435\u0442\u0441\u044f \u0441\u043a\u0430\u0447\u0430\u0442\u044c \u0438\u0441\u0445\u043e\u0434\u043d\u0438\u043a\u0438 \u0438 \u0441\u043e\u0431\u0440\u0430\u0442\u044c \u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a\u0443 \u0441\u0430\u043c\u043e\u0441\u0442\u043e\u044f\u0442\u0435\u043b\u044c\u043d\u043e. \u041f\u0440\u0435\u0434\u0432\u0430\u0440\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0442\u0440\u0435\u0431\u0443\u0435\u0442\u0441\u044f \u0443\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u0442\u044c \u00abdevel\u00bb-\u043f\u0430\u043a\u0435\u0442 \u0434\u043b\u044f \u0441\u0432\u043e\u0435\u0433\u043e \u0441\u0435\u0440\u0432\u0435\u0440\u0430 \u0438 \u0432 \u043f\u0435\u0440\u0435\u043c\u0435\u043d\u043d\u0443\u044e PATH \u043f\u0440\u043e\u043f\u0438\u0441\u0430\u0442\u044c \u043f\u0443\u0442\u044c \u0434\u043e pg_config.<\/b>\u201d. <\/p>\n

\u0412 \u043e\u0431\u0449\u0435\u043c \u2013 \u0432\u043e\u0437\u043d\u0438 \u043c\u043d\u043e\u0433\u043e, \u0430 \u0432 \u0441\u043b\u0443\u0447\u0430\u0435 \u0441\u0435\u0440\u044c\u0435\u0437\u043d\u044b\u0445 \u043f\u0440\u043e\u0434\u0430\u043a\u0448\u043d \u0431\u0430\u0437, \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c, \u0438 \u043d\u0435 \u0431\u0443\u0434\u0435\u0442 \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u0447\u0442\u043e-\u0442\u043e \u0434\u0435\u043b\u0430\u0442\u044c \u0441 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u043c. \u041d\u0443\u0436\u043d\u043e \u043e\u043f\u044f\u0442\u044c, \u043f\u0440\u0438\u0434\u0443\u043c\u044b\u0432\u0430\u0442\u044c, \u0447\u0442\u043e-\u0442\u043e \u0441\u0432\u043e\u0435.<\/p>\n

\u041f\u0440\u0435\u0434\u0443\u043f\u0440\u0435\u0436\u0434\u0435\u043d\u0438\u0435.
\u0412 \u0441\u0438\u043b\u0443 \u0434\u043e\u0432\u043e\u043b\u044c\u043d\u043e \u0431\u043e\u043b\u044c\u0448\u043e\u0433\u043e \u043e\u0431\u044a\u0435\u043c\u0430 \u0438 \u0432 \u0441\u0432\u044f\u0437\u0438 \u0441 \u043d\u0435\u0437\u0430\u0432\u0435\u0440\u0448\u0435\u043d\u0438\u0435\u043c \u043f\u0435\u0440\u0438\u043e\u0434\u0430 \u0442\u0435\u0441\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f, \u0441\u0442\u0430\u0442\u044c\u044f \u043d\u043e\u0441\u0438\u0442 \u0432 \u043e\u0441\u043d\u043e\u0432\u043d\u043e\u043c \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0439 \u0445\u0430\u0440\u0430\u043a\u0442\u0435\u0440, \u0441\u043a\u043e\u0440\u0435\u0435 \u043a\u0430\u043a \u043d\u0430\u0431\u043e\u0440 \u0442\u0435\u0437\u0438\u0441\u043e\u0432 \u0438 \u0440\u0435\u0437\u0443\u043b\u044c\u0442\u0430\u0442\u043e\u0432.
\u0411\u043e\u043b\u0435\u0435 \u043f\u043e\u0434\u0440\u043e\u0431\u043d\u044b\u0439 \u043c\u0430\u0442\u0435\u0440\u0438\u0430\u043b, \u0431\u0443\u0434\u0435\u0442 \u043f\u043e\u0434\u0433\u043e\u0442\u043e\u0432\u043b\u0435\u043d \u043f\u043e\u0437\u0436\u0435, \u043f\u043e \u0447\u0430\u0441\u0442\u044f\u043c <\/p><\/blockquote>\n

<\/a> <\/p>\n

\u042d\u0441\u043a\u0438\u0437\u043d\u044b\u0435 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u044f \u043a \u0440\u0435\u0448\u0435\u043d\u0438\u044e<\/h1>\n

\u041d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0430\u0442\u044c \u0438\u043d\u0441\u0442\u0440\u0443\u043c\u0435\u043d\u0442 \u043f\u043e\u0437\u0432\u043e\u043b\u044f\u044e\u0449\u0438\u0439 \u0445\u0440\u0430\u043d\u0438\u0442\u044c:
\u0418\u0441\u0442\u043e\u0440\u0438\u044e \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f pg_stat_activity<\/b>
\u0418\u0441\u0442\u043e\u0440\u0438\u044e \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a \u0441\u0435\u0441\u0441\u0438\u0439, \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u044f \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0435 pg_locks<\/b><\/p>\n

\u0422\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u0435 \u043a \u0440\u0435\u0448\u0435\u043d\u0438\u044e<\/b>\u2013\u043c\u0438\u043d\u0438\u043c\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u0442\u044c \u0432\u043b\u0438\u044f\u043d\u0438\u0435 \u043d\u0430 \u0446\u0435\u043b\u0435\u0432\u0443\u044e \u0431\u0430\u0437\u0443 \u0434\u0430\u043d\u043d\u044b\u0445. <\/p>\n

\u041e\u0431\u0449\u0430\u044f \u0438\u0434\u0435\u044f<\/b>\u2013\u0430\u0433\u0435\u043d\u0442 \u0441\u0431\u043e\u0440\u0430 \u0434\u0430\u043d\u043d\u044b\u0445 \u0437\u0430\u043f\u0443\u0441\u043a\u0430\u0435\u0442\u0441\u044f \u043d\u0435 \u0432 \u0446\u0435\u043b\u0435\u0432\u043e\u0439 \u0431\u0430\u0437\u0435, \u0430 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 \u043a\u0430\u043a \u0441\u0435\u0440\u0432\u0438\u0441 systemd. \u0414\u0430, \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u044b \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043f\u043e\u0442\u0435\u0440\u0438 \u0434\u0430\u043d\u043d\u044b\u0445, \u043d\u043e \u044d\u0442\u043e \u043d\u0435 \u043a\u0440\u0438\u0442\u0438\u0447\u043d\u043e, \u0434\u043b\u044f \u043e\u0442\u0447\u0435\u0442\u043d\u043e\u0441\u0442\u0438, \u0437\u0430\u0442\u043e \u043d\u0435\u0442 \u0438\u043c\u043f\u0430\u043a\u0442\u0430 \u043d\u0430 \u0446\u0435\u043b\u0435\u0432\u0443\u044e \u0431\u0430\u0437\u0443 \u043f\u043e \u043f\u0430\u043c\u044f\u0442\u0438 \u0438 \u043c\u0435\u0441\u0442\u0443 \u043d\u0430 \u0434\u0438\u0441\u043a\u0435. \u0410 \u0432 \u0441\u043b\u0443\u0447\u0430\u0435 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u044f \u043f\u0443\u043b\u0430 \u0441\u043e\u0435\u0434\u0438\u043d\u0435\u043d\u0438\u0439, \u0438\u043c\u043f\u0430\u043a\u0442 \u043f\u043e \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u043c \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430\u043c \u043c\u0438\u043d\u0438\u043c\u0430\u043b\u0435\u043d.<\/p>\n

\u042d\u0442\u0430\u043f\u044b \u0440\u0435\u0430\u043b\u0438\u0437\u0430\u0446\u0438\u0438<\/h1>\n

<\/p>\n

1.\u0421\u0435\u0440\u0432\u0438\u0441\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b<\/h1>\n

\u0414\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u043e\u0442\u0434\u0435\u043b\u044c\u043d\u0430\u044f \u0441\u0445\u0435\u043c\u0430, \u0447\u0442\u043e \u0431\u044b \u043d\u0435 \u0437\u0430\u0442\u0440\u0443\u0434\u043d\u044f\u0442\u044c \u0430\u043d\u0430\u043b\u0438\u0437 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0445 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u043d\u044b\u0445 \u0442\u0430\u0431\u043b\u0438\u0446.
DROP SCHEMA IF EXISTS activity_hist;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;<\/i><\/p>\n

\u0412\u0430\u0436\u043d\u043e: \u0441\u0445\u0435\u043c\u0430 \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u043d\u0435 \u0432 \u0446\u0435\u043b\u0435\u0432\u043e\u0439 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445, \u0430 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430.<\/b>
\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f pg_stat_activity
\u0414\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u0442\u0435\u043a\u0443\u0449\u0438\u0445 \u0441\u043d\u0438\u043c\u043a\u043e\u0432 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f pg_stat_activity \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 <\/p>\n

activity_hist.history_pg_stat_activity :<\/b><\/p>\n
—ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY
DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity;
CREATE TABLE activity_hist.history_pg_stat_activity
(
timepoint timestamp without time zone,
datid oid,
datname name,
pid integer,
usesysid oid,
usename name,
application_name text,
client_addr inet,
client_hostname text,
client_port integer,
backend_start timestamp with time zone,
xact_start timestamp with time zone,
query_start timestamp with time zone,
state_change timestamp with time zone,
wait_event_type text,
wait_event text,
state text,
backend_xid xid,
backend_xmin xid,
query text,
backend_type text,
query_md5hash text,
queryid bigint
); <\/div>\n<\/div>\n

\u0414\u043b\u044f \u0443\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u044f \u0432\u0441\u0442\u0430\u0432\u043a\u0438 \u2013 \u043d\u0438\u043a\u0430\u043a\u0438\u0445 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0438 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0439.<\/p>\n

\u0414\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 <\/p>\n

activity_hist.archive_pg_stat_activity :<\/b><\/p>\n
DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity;
CREATE TABLE activity_hist.archive_pg_stat_activity
(
timepoint timestamp without time zone,
datid oid,
datname name,
pid integer,
usesysid oid,
usename name,
application_name text,
client_addr inet,
client_hostname text,
client_port integer,
backend_start timestamp with time zone,
xact_start timestamp with time zone,
query_start timestamp with time zone,
state_change timestamp with time zone,
wait_event_type text,
wait_event text,
state text,
backend_xid xid,
backend_xmin xid,
query text,
backend_type text,
query_md5hash text,
queryid bigint
)
PARTITION BY RANGE (timepoint); <\/div>\n<\/div>\n

\u041f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u0432 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u043d\u0435\u0442 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u0439 \u043f\u043e \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u0438 \u0432\u0441\u0442\u0430\u0432\u043a\u0438, \u0441\u043e\u0437\u0434\u0430\u043d\u044b \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b, \u0434\u043b\u044f \u0443\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u043e\u0442\u0447\u0435\u0442\u043e\u0432.<\/p>\n

\u0418\u0441\u0442\u043e\u0440\u0438\u044f \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a \u0441\u0435\u0441\u0441\u0438\u0439
\u0414\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u0442\u0435\u043a\u0443\u0449\u0438\u0445 \u0441\u043d\u0438\u043c\u043a\u043e\u0432 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a \u0441\u0435\u0441\u0441\u0438\u0439 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 <\/p>\n

activity_hist.history_locking :<\/b><\/p>\n
—ACTIVITY_HIST.HISTORY_LOCKING
DROP TABLE IF EXISTS activity_hist.history_locking;
CREATE TABLE activity_hist.history_locking
(
timepoint timestamp without time zone,
locktype text,
relation oid,
mode text,
tid xid,
vtid text,
pid integer,
blocking_pids integer[],
granted boolean
); <\/div>\n<\/div>\n

\u0422\u0430\u043a\u0436\u0435, \u0434\u043b\u044f \u0443\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u044f \u0432\u0441\u0442\u0430\u0432\u043a\u0438 \u2013 \u043d\u0438\u043a\u0430\u043a\u0438\u0445 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0438 \u043e\u0433\u0440\u0430\u043d\u0438\u0447\u0435\u043d\u0438\u0439.<\/p>\n

\u0414\u043b\u044f \u0445\u0440\u0430\u043d\u0435\u043d\u0438\u044f \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u0430\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u0430 <\/p>\n

activity_hist.archive_locking:<\/b><\/p>\n
DROP TABLE IF EXISTS activity_hist.archive_locking;
CREATE TABLE activity_hist.archive_locking
(
timepoint timestamp without time zone,
locktype text,
relation oid,
mode text,
tid xid,
vtid text,
pid integer,
blocking_pids integer[],
granted boolean
)
PARTITION BY RANGE (timepoint); <\/div>\n<\/div>\n

\u041f\u043e\u0441\u043a\u043e\u043b\u044c\u043a\u0443 \u0432 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435 \u043d\u0435\u0442 \u0442\u0440\u0435\u0431\u043e\u0432\u0430\u043d\u0438\u0439 \u043f\u043e \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u0438 \u0432\u0441\u0442\u0430\u0432\u043a\u0438, \u0441\u043e\u0437\u0434\u0430\u043d\u044b \u043d\u0435\u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u044b, \u0434\u043b\u044f \u0443\u0441\u043a\u043e\u0440\u0435\u043d\u0438\u044f \u0441\u043e\u0437\u0434\u0430\u043d\u0438\u044f \u043e\u0442\u0447\u0435\u0442\u043e\u0432.<\/p>\n

2.\u0417\u0430\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435 \u0442\u0435\u043a\u0443\u0449\u0435\u0439 \u0438\u0441\u0442\u043e\u0440\u0438\u0438.<\/h1>\n

\u0414\u043b\u044f \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0433\u043e \u0441\u0431\u043e\u0440\u0430 \u0441\u043d\u0438\u043c\u043a\u043e\u0432 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f bash \u0441\u043a\u0440\u0438\u043f\u0442, \u0437\u0430\u043f\u0443\u0441\u043a\u0430\u044e\u0449\u0438\u0439 plpgsql \u0444\u0443\u043d\u043a\u0446\u0438\u044e. <\/p>\n

get_current_activity.sh<\/b><\/p>\n
\n
#!\/bin\/bash ######################################################### #get_current_activity.sh  ERROR_FILE='\/home\/demon\/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S) host=$1 s_name=$2 s_pass=$3  psql  -A -t -q -v ON_ERROR_STOP=1 -c \"SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )\" >\/dev\/null 2>$ERROR_FILE  line_count=`cat $ERROR_FILE | wc -l` if [[ $line_count != '0' ]]; then     rm -f \/home\/demon\/*.err >\/dev\/null 2>\/dev\/null \tcp $ERROR_FILE $ERROR_FILE'.err' >\/dev\/null 2>\/dev\/null   fi rm $ERROR_FILE >\/dev\/null 2>\/dev\/null exit 0<\/pre>\n<\/div>\n<\/div>\n
  plpgsql<\/b> \u0444\u0443\u043d\u043a\u0446\u0438\u044f \u043f\u043e dblink \u043e\u0431\u0440\u0430\u0449\u0430\u0435\u0442\u0441\u044f \u043a \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f\u043c \u0432 \u0446\u0435\u043b\u0435\u0432\u043e\u0439 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u0432\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u0442 \u0441\u0442\u0440\u043e\u043a\u0438 \u0432 \u0441\u0435\u0440\u0432\u0438\u0441\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445 \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430.  <\/p>\n
get_current_activity.sql<\/b><\/p>\n
\n
CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$ DECLARE    database_rec record;   dblink_str text ; BEGIN     \tEXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'|| \t                                         ' user='||current_s_name||' password='||current_s_pass|| ' '')';    -------------------------------------------------------------------- --GET pg_stat_activity stats \tINSERT INTO activity_hist.history_pg_stat_activity \t( \t\tSELECT * FROM dblink('LINK1', \t\t\t'SELECT  \t\t\tnow() ,  \t\t\tdatid             ,  \t\t\tdatname           , \t\t\tpid               , \t\t\tusesysid              , \t\t\tusename              , \t\t\tapplication_name     , \t\t\tclient_addr          , \t\t\tclient_hostname      , \t\t\tclient_port       , \t\t\tbackend_start         , \t\t\txact_start            , \t\t\tquery_start           , \t\t\tstate_change          , \t\t\twait_event_type    ,                      \t\t\twait_event         ,                    \t\t\tstate              ,                   \t\t\tbackend_xid         ,                  \t\t\tbackend_xmin        ,                 \t\t\tquery              ,                \t\t\tbackend_type   \t\t\t \t\tFROM pg_stat_activity \t\t')  \t\tAS t ( \t\t    timepoint \t\t  timestamp without time zone ,\t\t\t \t\t\tdatid             oid  ,  \t\t\tdatname           name , \t\t\tpid               integer, \t\t\tusesysid          oid    , \t\t\tusename           name   , \t\t\tapplication_name  text   , \t\t\tclient_addr       inet   , \t\t\tclient_hostname   text   , \t\t\tclient_port       integer, \t\t\tbackend_start     timestamp with time zone , \t\t\txact_start        timestamp with time zone , \t\t\tquery_start       timestamp with time zone , \t\t\tstate_change      timestamp with time zone , \t\t\twait_event_type   text ,                      \t\t\twait_event        text ,                    \t\t\tstate             text ,                   \t\t\tbackend_xid       xid  ,                  \t\t\tbackend_xmin      xid  ,                 \t\t\tquery             text ,                \t\t\tbackend_type      text \t\t\t \t\t) \t);  ---------------------------------------\t --ACTIVITY_HIST.HISTORY_LOCKING\t \tINSERT INTO activity_hist.history_locking \t( \t\tSELECT * FROM dblink('LINK1', \t\t\t'SELECT  \t\t\tnow() ,  \t\t\tlock.locktype, \t\t\tlock.relation, \t\t\tlock.mode, \t\t\tlock.transactionid as tid, \t\t\tlock.virtualtransaction as vtid, \t\t\tlock.pid, \t\t\tpg_blocking_pids(lock.pid),  \t\t\tlock.granted \t\t\tFROM \tpg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database \t\t\tWHERE NOT lock.pid = pg_backend_pid()\t \t\t')  \t\tAS t ( \t\t\ttimepoint timestamp without time zone , \t\t\tlocktype text , \t\t\trelation oid ,  \t\t\tmode text , \t\t\ttid xid , \t\t\tvtid text , \t\t\tpid integer , \t\t\tblocking_pids integer[] , \t\t\tgranted boolean \t\t) \t); \tPERFORM dblink_disconnect('LINK1'); \t \tRETURN TRUE ; END $$ LANGUAGE plpgsql;<\/pre>\n<\/div>\n<\/div>\n
  \u0414\u043b\u044f \u0441\u0431\u043e\u0440\u0430 \u0441\u043d\u0438\u043c\u043a\u043e\u0432 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f systemd service, \u0438 \u0434\u0432\u0430 \u0441\u043a\u0440\u0438\u043f\u0442\u0430:  <\/p>\n
pg_current_activity.service<\/b><\/p>\n
\n
# \/etc\/systemd\/system\/pg_current_activity.service [Unit] Description=Collect history of pg_stat_activity , pg_locks  Wants=pg_current_activity.timer  [Service] Type=forking StartLimitIntervalSec=0 ExecStart=\/home\/postgres\/pgutils\/demon\/get_current_activity.sh 10.124.70.40 postgres postgres  [Install] WantedBy=multi-user.target<\/pre>\n<\/div>\n<\/div>\n
pg_current_activity.timer<\/b><\/p>\n
\n
# \/etc\/systemd\/system\/pg_current_activity.timer [Unit] Description=Run pg_current_activity.sh every 1 second Requires=pg_current_activity.service  [Timer] Unit=pg_current_activity.service OnCalendar=*:*:0\/1 AccuracySec=1  [Install] WantedBy=timers.target<\/pre>\n<\/div>\n<\/div>\n
  \u041d\u0430\u0437\u043d\u0430\u0447\u0430\u0435\u043c \u043f\u0440\u0430\u0432\u0430 \u0441\u043a\u0440\u0438\u043f\u0442\u0430\u043c: 
  # chmod 755 pg_current_activity.timer
  # chmod 755 pg_current_activity.service<\/p>\n
  \u0417\u0430\u043f\u0443\u0441\u043a\u0430\u0435\u043c \u0441\u0435\u0440\u0432\u0438\u0441: 
  # systemctl daemon-reload
  # systemctl start pg_current_activity.service<\/p>\n
  \u0422\u0430\u043a\u0438\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c, \u0438\u0441\u0442\u043e\u0440\u0438\u044f \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u0439 \u0441\u043e\u0431\u0438\u0440\u0430\u0435\u0442\u0441\u044f \u0432 \u0432\u0438\u0434\u0435 \u0435\u0436\u0435\u0441\u0435\u043a\u0443\u043d\u0434\u043d\u044b\u0445 \u0441\u043d\u0438\u043c\u043a\u043e\u0432. \u041a\u043e\u043d\u0435\u0447\u043d\u043e, \u0435\u0441\u043b\u0438 \u0432\u0441\u0435 \u043e\u0441\u0442\u0430\u0432\u0438\u0442\u044c \u043a\u0430\u043a \u0435\u0441\u0442\u044c, \u0442\u0430\u0431\u043b\u0438\u0446\u044b \u043e\u0447\u0435\u043d\u044c \u0431\u044b\u0441\u0442\u0440\u043e \u0443\u0432\u0435\u043b\u0438\u0447\u0430\u0442\u0441\u044f \u0432 \u0440\u0430\u0437\u043c\u0435\u0440\u0430\u0445 \u0438 \u0431\u043e\u043b\u0435\u0435 \u043c\u0435\u043d\u0435\u0435 \u043f\u0440\u043e\u0434\u0443\u043a\u0442\u0438\u0432\u043d\u0430\u044f \u0440\u0430\u0431\u043e\u0442\u0430 \u0441\u0442\u0430\u043d\u0435\u0442 \u043d\u0435\u0432\u043e\u0437\u043c\u043e\u0436\u043d\u0430. <\/p>\n
  \u041d\u0443\u0436\u043d\u043e \u043e\u0440\u0433\u0430\u043d\u0438\u0437\u043e\u0432\u0430\u0442\u044c \u0430\u0440\u0445\u0438\u0432\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0434\u0430\u043d\u043d\u044b\u0445.<\/p>\n
3.\u0410\u0440\u0445\u0438\u0432\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438\u0441\u0442\u043e\u0440\u0438\u0438<\/h1>\n
  \u0414\u043b\u044f \u0430\u0440\u0445\u0438\u0432\u0430\u0446\u0438\u044f \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442\u0441\u044f \u0441\u0435\u043a\u0446\u0438\u043e\u043d\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0435 \u0442\u0430\u0431\u043b\u0438\u0446\u044b archive*. 
  \u041d\u043e\u0432\u044b\u0435 \u0441\u0435\u043a\u0446\u0438\u0438 \u0441\u043e\u0437\u0434\u0430\u044e\u0442\u0441\u044f \u043a\u0430\u0436\u0434\u044b\u0439 \u0447\u0430\u0441, \u043f\u0440\u0438 \u044d\u0442\u043e\u043c \u0441\u0442\u0430\u0440\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446 history* \u0443\u0434\u0430\u043b\u044f\u044e\u0442\u0441\u044f, \u0442\u0430\u043a\u0438\u043c \u043e\u0431\u0440\u0430\u0437\u043e\u043c, \u0440\u0430\u0437\u043c\u0435\u0440 \u0442\u0430\u0431\u043b\u0438\u0446 history* \u0441\u0438\u043b\u044c\u043d\u043e \u043d\u0435 \u043c\u0435\u043d\u044f\u0435\u0442\u0441\u044f \u0438 \u0441\u043a\u043e\u0440\u043e\u0441\u0442\u044c \u0432\u0441\u0442\u0430\u0432\u043a\u0438 \u043d\u0435 \u0434\u0435\u0433\u0440\u0430\u0434\u0438\u0440\u0443\u0435\u0442\u0441\u044f \u0441\u043e \u0432\u0440\u0435\u043c\u0435\u043d\u0435\u043c.
  \u0421\u043e\u0437\u0434\u0430\u043d\u0438\u0435 \u043d\u043e\u0432\u044b\u0445 \u0441\u0435\u043a\u0446\u0438\u0439 \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442\u0441\u044f plpgsql \u0444\u0443\u043d\u043a\u0446\u0438\u0435\u0439 activity_hist.archive_current_activity. \u0410\u043b\u0433\u043e\u0440\u0438\u0442\u043c \u0440\u0430\u0431\u043e\u0442\u044b \u043e\u0447\u0435\u043d\u044c \u043f\u0440\u043e\u0441\u0442( \u043d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0435 \u0441\u0435\u043a\u0446\u0438\u0438 \u0434\u043b\u044f \u0442\u0430\u0431\u043b\u0438\u0446\u044b archive_pg_stat_activity ).<\/p>\n
\u0421\u043e\u0437\u0434\u0430\u0451\u043c \u0438 \u0437\u0430\u043f\u043e\u043b\u043d\u044f\u0435\u043c \u043d\u043e\u0432\u0443\u044e \u0441\u0435\u043a\u0446\u0438\u044e<\/b><\/p>\n
\n
EXECUTE format( 'CREATE TABLE ' || partition_name ||  ' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' ,  to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_min_range ),'MM')||'-'|| to_char(date_trunc('day', partition_min_range ),'DD')||' '|| to_char(date_trunc('hour', partition_min_range ),'HH24')||':00',  to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'|| to_char(date_trunc('month', partition_max_range ),'MM')||'-'|| to_char(date_trunc('day', partition_max_range ),'DD')||' '|| to_char(date_trunc('hour', partition_max_range ),'HH24')||':00' );  INSERT INTO activity_hist.archive_pg_stat_activity ( \tSELECT \t*  \tFROM \tactivity_hist.history_pg_stat_activity \tWHERE \ttimepoint BETWEEN partition_min_range AND partition_max_range \t\t );<\/pre>\n
  <\/div>\n<\/div>\n
  <\/p>\n
\u0421\u043e\u0437\u0434\u0430\u0435\u043c \u0438\u043d\u0434\u0435\u043a\u0441\u044b<\/b><\/p>\n
\n
EXECUTE format\t( 'CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )'  );  EXECUTE format\t('CREATE INDEX '||index_name|| ' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )'  );<\/pre>\n
  <\/div>\n<\/div>\n
  <\/p>\n
\u0423\u0434\u0430\u043b\u044f\u0435\u043c \u0441\u0442\u0430\u0440\u044b\u0435 \u0434\u0430\u043d\u043d\u044b\u0435 \u0438\u0437 \u0442\u0430\u0431\u043b\u0438\u0446\u044b history_pg_stat_activity<\/b><\/p>\n
\n
DELETE  FROM \tactivity_hist.history_pg_stat_activity WHERE \ttimepoint < partition_max_range;<\/pre>\n
  <\/div>\n<\/div>\n
  \u0420\u0430\u0437\u0443\u043c\u0435\u0435\u0442\u0441\u044f \u043f\u0435\u0440\u0438\u043e\u0434\u0438\u0447\u0435\u0441\u043a\u0438, \u0441\u0442\u0430\u0440\u044b\u0435 \u0441\u0435\u043a\u0446\u0438\u0438, \u0437\u0430 \u043d\u0435\u043d\u0430\u0434\u043e\u0431\u043d\u043e\u0441\u0442\u044c\u044e \u0443\u0434\u0430\u043b\u044f\u044e\u0442\u0441\u044f.<\/p>\n
\u0411\u0430\u0437\u043e\u0432\u044b\u0435 \u043e\u0442\u0447\u0435\u0442\u044b.<\/h1>\n
  \u0421\u043e\u0431\u0441\u0442\u0432\u0435\u043d\u043d\u043e \u0434\u043b\u044f \u0447\u0435\u0433\u043e \u0432\u0441\u0435 \u044d\u0442\u043e \u0434\u0435\u043b\u0430\u0435\u0442\u0441\u044f. \u0414\u043b\u044f \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u044f \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u043e\u0447\u0435\u043d\u044c \u043e\u0442\u0434\u0430\u043b\u0435\u043d\u043d\u043e, \u043f\u0440\u0438\u0431\u043b\u0438\u0436\u0435\u043d\u043d\u043e \u043d\u0430\u043f\u043e\u043c\u0438\u043d\u0430\u044e\u0449\u0438\u0445 AWR Oracle.  <\/p>\n
\u0412\u0430\u0436\u043d\u043e \u0434\u043e\u0431\u0430\u0432\u0438\u0442\u044c, \u0434\u043b\u044f \u043f\u043e\u043b\u0443\u0447\u0435\u043d\u0438\u044f \u043e\u0442\u0447\u0435\u0442\u043e\u0432 \u043d\u0435\u043e\u0431\u0445\u043e\u0434\u0438\u043c\u043e \u043f\u043e\u0441\u0442\u0440\u043e\u0438\u0442\u044c \u0441\u0432\u044f\u0437\u044c \u043c\u0435\u0436\u0434\u0443 \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u0438\u044f\u043c\u0438 pg_stat_activity \u0438 pg_stat_statements. \u0422\u0430\u0431\u043b\u0438\u0446\u044b \u0441\u0432\u044f\u0437\u044b\u0432\u0430\u044e\u0442\u0441\u044f \u043f\u0443\u0442\u0435\u043c \u0434\u043e\u0431\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0432 \u0442\u0430\u0431\u043b\u0438\u0446\u044b ‘history_pg_stat_activity’, ‘archive_pg_stat_activity’ \u0441\u0442\u043e\u043b\u0431\u0446\u0430 ‘queryid’. \u0421\u043f\u043e\u0441\u043e\u0431 \u0434\u043e\u0431\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u044f \u0441\u0442\u043e\u043b\u0431\u0446\u0430, \u0432\u044b\u0445\u043e\u0434\u0438\u0442 \u0437\u0430 \u0440\u0430\u043c\u043a\u0438 \u0434\u0430\u043d\u043d\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0438 \u0438 \u0431\u0443\u0434\u0435\u0442 \u043f\u043e\u0434\u0440\u043e\u0431\u043d\u043e \u043e\u043f\u0438\u0441\u0430\u043d \u0447\u0443\u0442\u044c \u043f\u043e\u0437\u0436\u0435.<\/p><\/blockquote>\n
  <\/p>\n
TOTAL CPU TIME FOR QUERIES<\/h2>\n
  <\/p>\n
\u0417\u0430\u043f\u0440\u043e\u0441 :<\/b><\/p>\n
\n
WITH hist AS ( SELECT  \taa.query ,aa.queryid ,\t\t\t \tcount(*) * interval '1 second' AS duration  FROM \tactivity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND  pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND backend_type = 'client backend' AND datname != 'postgres' AND\t( aa.wait_event_type IS NULL  ) ANDaa.state = 'active' GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid\t\t UNION  SELECT  \tha.query ,ha.queryid, \tcount(*) * interval '1 second' AS duration  FROM \tactivity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND \tbackend_type = 'client backend' AND datname != 'postgres' AND ( ha.wait_event_type IS NULL  )AND ha.state = 'active' GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid\t\t ) SELECT \tquery , queryid , SUM( duration ) as duration  FROM hist GROUP BY  query , queryid  ORDER BY 3 DESC<\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u0438\u043c\u0435\u0440:  <\/p>\n
------------------------------------------------------------------- | TOTAL CPU TIME FOR QUERIES : 07:47:36 +----+----------------------------------------+-------------------- |   #|                                 queryid|            duration +----+----------------------------------------+-------------------- |   1|                      389015618226997618|            04:28:58 |   2|                                        |            01:07:29 |   3|                     1237430309438971376|            00:59:38 |   4|                     4710212362688288619|            00:50:48 |   5|                       28942442626229688|            00:15:50 |   6|                     9150846928388977274|            00:04:46 |   7|                    -6572922443698419129|            00:00:06 |   8|                                        |            00:00:01 +----+----------------------------------------+-------------------- <\/pre>\n
TOTAL WAITINGS TIME FOR QUERIES <\/h2>\n
  <\/p>\n
\u0417\u0430\u043f\u0440\u043e\u0441 :<\/b><\/p>\n
\n
WITH hist AS ( SELECT  \taa.query ,aa.queryid ,\t\t\t \tcount(*) * interval '1 second' AS duration  FROM \tactivity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND  \tbackend_type = 'client backend' AND datname != 'postgres' AND \t( aa.wait_event_type IS NOT NULL  )  GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid\t\t UNION  SELECT  \tha.query ,ha.queryid, \tcount(*) * interval '1 second' AS duration  FROM \tactivity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour')  AND  \tbackend_type = 'client backend' AND datname != 'postgres' AND\t\t\t\t \t( ha.wait_event_type IS NOT NULL  ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid\t\t ) SELECT \tquery , queryid , SUM( duration ) as duration  FROM hist GROUP BY  query , queryid  ORDER BY 3 DESC <\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u0438\u043c\u0435\u0440 :<\/b>  <\/p>\n
------------------------------------------------------------------- | TOTAL WAITINGS TIME FOR QUERIES : 21:55:04 +----+----------------------------------------+-------------------- |   #|                                 queryid|            duration +----+----------------------------------------+-------------------- |   1|                      389015618226997618|            16:19:05 |   2|                                        |            03:47:04 |   3|                     8085340880788646241|            00:40:20 |   4|                     4710212362688288619|            00:13:35 |   5|                     9150846928388977274|            00:12:25 |   6|                       28942442626229688|            00:11:32 |   7|                     1237430309438971376|            00:09:45 |   8|                     2649515222348904837|            00:09:37 |   9|                                        |            00:03:45 |  10|                     3167065002719415275|            00:02:20 |  11|                     5731212217001535134|            00:02:13 |  12|                     8304755792398128062|            00:01:31 |  13|                     2649515222348904837|            00:00:59 |  14|                     2649515222348904837|            00:00:22 |  15|                                        |            00:00:12 |  16|                     3422818749220588372|            00:00:08 |  17|                    -5730801771815999400|            00:00:03 |  18|                    -1473395109729441239|            00:00:02 |  19|                     2404820632950544954|            00:00:02 |  20|                    -6572922443698419129|            00:00:02 |  21|                     2369289265278398647|            00:00:01 |  22|                      180077086776069052|            00:00:01 +----+----------------------------------------+-------------------- <\/pre>\n
WAITINGS FOR QUERIES<\/h2>\n
  <\/p>\n
\u0417\u0430\u043f\u0440\u043e\u0441\u044b:<\/b><\/p>\n
\n
WITH hist AS ( SELECT  \taa.wait_event_type , aa.wait_event  FROM \tactivity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND  \tbackend_type = 'client backend' AND datname != 'postgres' AND \taa.wait_event IS NOT NULL  GROUP BY aa.wait_event_type , aa.wait_event UNION  SELECT  \tha.wait_event_type , ha.wait_event  FROM \tactivity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND  \tbackend_type = 'client backend' AND datname != 'postgres' AND \tha.wait_event IS NOT NULL  GROUP BY ha.wait_event_type , ha.wait_event\t\t ) SELECT \twait_event_type , wait_event  FROM hist GROUP BY wait_event_type , wait_event ORDER BY 1 ASC,2 ASC  ----------------------------------------------------------------------  WITH hist AS ( SELECT  \taa.wait_event_type , aa.wait_event , aa.query ,aa.queryid ,\t\t\t \tcount(*) * interval '1 second' AS duration  FROM \tactivity_hist.archive_pg_stat_activity aa WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND  \tbackend_type = 'client backend' AND datname != 'postgres' AND \t( aa.wait_event_type = waitings_stat_rec.wait_event_type AND aa.wait_event = waitings_stat_rec.wait_event ) GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid\t\t UNION  SELECT  \tha.wait_event_type , ha.wait_event , ha.query ,ha.queryid, \tcount(*) * interval '1 second' AS duration  FROM \tactivity_hist.history_pg_stat_activity_for_reports ha WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND  \tbackend_type = 'client backend' AND datname != 'postgres' AND\t\t\t\t \t( ha.wait_event_type = waitings_stat_rec.wait_event_type AND ha.wait_event = waitings_stat_rec.wait_event ) GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid\t\t ) SELECT \tquery , queryid , SUM( duration ) as duration  FROM hist GROUP BY  query , queryid  ORDER BY 3 DESC<\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u0438\u043c\u0435\u0440:<\/b>  <\/p>\n
------------------------------------------------ | WAITINGS FOR QUERIES +----------------------------------------------- |                      wait_event_type = Client| |                       wait_event = ClientRead| |                        Total time  = 00:46:56| ------------------------------------------------ |    #|             queryid|            duration +-----+--------------------+-------------------- |    1| 8085340880788646241|            00:40:20 |    2|                    |            00:03:45 |    3| 5731212217001535134|            00:01:53 |    4|                    |            00:00:12 |    5| 9150846928388977274|            00:00:09 |    6| 3422818749220588372|            00:00:08 |    7| 1237430309438971376|            00:00:06 |    8|   28942442626229688|            00:00:05 |    9| 4710212362688288619|            00:00:05 |   10|-5730801771815999400|            00:00:03 |   11| 8304755792398128062|            00:00:02 |   12|-6572922443698419129|            00:00:02 |   13|-1473395109729441239|            00:00:02 |   14| 2404820632950544954|            00:00:02 |   15|  180077086776069052|            00:00:01 |   16| 2369289265278398647|            00:00:01  +----------------------------------------------- |                          wait_event_type = IO| |                      wait_event = BufFileRead| |                        Total time  = 00:00:38| ------------------------------------------------ |    #|             queryid|            duration +-----+--------------------+-------------------- |    1|   28942442626229688|            00:00:38  +----------------------------------------------- <\/pre>\n
  <\/p>\n
LOCKED PROCESSES HISTORY<\/h2>\n
  <\/p>\n
\u0417\u0430\u043f\u0440\u043e\u0441:<\/b><\/p>\n
\n
SELECT  MIN(date_trunc('second',timepoint)) AS started ,  \tcount(*) * interval '1 second' as duration , \tpid , blocking_pids , relation , mode , locktype \t  FROM  \tactivity_hist.archive_locking al  WHERE  \ttimepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND \tNOT granted AND  \tlocktype = 'relation'  GROUP BY pid , blocking_pids , relation , mode , locktype\t\t\t UNION SELECT  \tMIN(date_trunc('second',timepoint)) AS started ,  \tcount(*) * interval '1 second' as duration , \tpid , blocking_pids , relation , mode , locktype FROM  \tactivity_hist.history_locking  WHERE  \ttimepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND \tNOT granted AND  \tlocktype = 'relation'  GROUP BY pid , blocking_pids , relation , mode , locktype\t\t\t ORDER BY 1<\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u0438\u043c\u0435\u0440:<\/b>  <\/p>\n
------------------------------------------------------------------------------------------------------------------------------------- | LOCKED PROCESSES HISTORY +-----+----------+--------------------+----------+--------------------+--------------------+--------------------+-------------------- |    #|       pid|             started|  duration|       blocking_pids|            relation|                mode|            locktype +-----+----------+--------------------+----------+--------------------+--------------------+--------------------+-------------------- |    1|     26224| 2019-09-02 19:32:16|  00:01:45|             {26211}|               16541|     AccessShareLock|            relation |    2|     26390| 2019-09-02 19:34:03|  00:00:53|             {26211}|               16541|     AccessShareLock|            relation |    3|     26391| 2019-09-02 19:34:03|  00:00:53|             {26211}|               16541|     AccessShareLock|            relation |    4|     26531| 2019-09-02 19:35:27|  00:00:12|             {26211}|               16541|     AccessShareLock|            relation |    5|     27284| 2019-09-02 19:44:02|  00:00:19|             {27276}|               16541|     AccessShareLock|            relation |    6|     27283| 2019-09-02 19:44:02|  00:00:19|             {27276}|               16541|     AccessShareLock|            relation |    7|     27286| 2019-09-02 19:44:02|  00:00:19|             {27276}|               16541|     AccessShareLock|            relation |    8|     27423| 2019-09-02 19:45:24|  00:00:12|             {27394}|               16541|     AccessShareLock|            relation |    9|     27648| 2019-09-02 19:48:06|  00:00:20|             {27647}|               16541|     AccessShareLock|            relation |   10|     27650| 2019-09-02 19:48:06|  00:00:20|             {27647}|               16541|     AccessShareLock|            relation |   11|     27735| 2019-09-02 19:49:08|  00:00:06|             {27650}|               16541| AccessExclusiveLock|            relation |   12|     28380| 2019-09-02 19:56:03|  00:01:56|             {28379}|               16541|     AccessShareLock|            relation |   13|     28379| 2019-09-02 19:56:03|  00:00:01|               28377|               16541| AccessExclusiveLock|            relation |     |          |                    |          |               28376|                    |  <\/pre>\n
  <\/p>\n
BLOCKING PROCESSES HISTORY<\/h2>\n
  <\/p>\n
\u0417\u0430\u043f\u0440\u043e\u0441\u044b:<\/b><\/p>\n
\n
SELECT  blocking_pids  FROM  \tactivity_hist.archive_locking al  WHERE  \ttimepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND \tNOT granted AND  \tlocktype = 'relation'  GROUP BY blocking_pids \t\t UNION SELECT  \tblocking_pids  FROM  \tactivity_hist.history_locking  WHERE  \ttimepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND \tNOT granted AND  \tlocktype = 'relation'  GROUP BY blocking_pids \t\t ORDER BY 1  ---------------------------------------------------------------  SELECT  \tpid , usename , application_name , datname , \tMIN(date_trunc('second',timepoint)) as started ,  \tcount(*) * interval '1 second' as duration ,\t\t  \tstate ,  \tquery \t\t\t\tFROM  \tactivity_hist.archive_pg_stat_activity \t\t\t\tWHERE \tpid= current_pid AND  \t\t\t\t\t\ttimepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') \t\t\t\t\t\t  \t\t\t\tGROUP BY pid , usename , application_name ,  \t\t\t\t\t\tdatname ,  \t\t\t\t\t\tstate_change,  \t\t\t\t\t\tstate ,  \t\t\t\t\t\tquery  \t\t\t\tUNION \t\t\t\tSELECT  \t\t\t\t\tpid , usename , application_name , datname , \t\t\t\t\tMIN(date_trunc('second',timepoint)) as started ,  \t\t\t\t\tcount(*) * interval '1 second' as duration ,\t\t  \t\t\t\t\tstate ,  \t\t\t\t\tquery \t\t\t\tFROM  \tactivity_hist.history_pg_stat_activity_for_reports \t\t\t\tWHERE \tpid= current_pid AND  \t\t\t\t\t\ttimepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') \t\t\t\t\t\t  \t\t\t\tGROUP BY pid , usename , application_name ,  \t\t\t\t\t\tdatname ,  \t\t\t\t\t\tstate_change,  \t\t\t\t\t\tstate ,  \t\t\t\t\t\tquery  \t\t\t\tORDER BY 5 , 1<\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u0438\u043c\u0435\u0440:<\/b>  <\/p>\n
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- BLOCKING PROCESSES HISTORY +----+----------+----------+--------------------+----------+--------------------+--------------------+------------------------------+---------------------------------------- |   #|       pid|   usename|    application_name|   datname|             started|            duration|                         state|                                   query +----+----------+----------+--------------------+----------+--------------------+--------------------+------------------------------+---------------------------------------- |   1|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:31:54|            00:00:04|                          idle| |   2|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:31:58|            00:00:06|           idle in transaction|                                  begin; |   3|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:32:16|            00:01:45|           idle in transaction|                  lock table wafer_data; |   4|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:35:54|            00:01:23|                          idle|                                 commit; |   5|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:38:46|            00:00:02|           idle in transaction|                                  begin; |   6|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:38:54|            00:00:08|           idle in transaction|                  lock table wafer_data; |   7|     26211|     tuser|                psql|      tdb1| 2019-09-02 19:39:08|            00:42:42|                          idle|                                 commit; |   8|     26211|     tuser|                psql|      tdb1| 2019-09-03 07:12:07|            00:00:52|                        active|                     select test_del (); <\/pre>\n
\u0420\u0430\u0437\u0432\u0438\u0442\u0438\u0435.<\/h1>\n
  \u041f\u043e\u043a\u0430\u0437\u0430\u043d\u043d\u044b\u0435 \u0431\u0430\u0437\u043e\u0432\u044b\u0435 \u0437\u0430\u043f\u0440\u043e\u0441\u044b \u0438 \u043f\u043e\u043b\u0443\u0447\u0430\u0435\u043c\u044b\u0435 \u043e\u0442\u0447\u0435\u0442\u044b, \u0443\u0436\u0435 \u0441\u0438\u043b\u044c\u043d\u043e \u043e\u0431\u043b\u0435\u0433\u0447\u0430\u044e\u0442 \u0436\u0438\u0437\u043d\u044c \u043f\u0440\u0438 \u0430\u043d\u0430\u043b\u0438\u0437\u0435 \u0438\u043d\u0446\u0438\u0434\u0435\u043d\u0442\u043e\u0432 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438. 
  \u041d\u0430 \u043e\u0441\u043d\u043e\u0432\u0435 \u0431\u0430\u0437\u043e\u0432\u044b\u0445 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432, \u043c\u043e\u0436\u043d\u043e \u043f\u043e\u043b\u0443\u0447\u0438\u0442\u044c \u043e\u0442\u0447\u0435\u0442, \u043e\u0442\u0434\u0430\u043b\u0435\u043d\u043d\u043e \u043f\u0440\u0438\u0431\u043b\u0438\u0436\u0435\u043d\u043d\u043e \u043d\u0430\u043f\u043e\u043c\u0438\u043d\u0430\u044e\u0449\u0438\u0439 AWR Oracle.  <\/p>\n
\u041f\u0440\u0438\u043c\u0435\u0440 \u0441\u0432\u043e\u0434\u043d\u043e\u0433\u043e \u043e\u0442\u0447\u0435\u0442\u0430<\/b><\/p>\n
\n
+------------------------------------------------------------------------------------ | CONSOLIDATED REPORT FOR ACTIVITY AND WAITINGS . DATETIME : 03.09.2019 14:08 |------------------------------------------------------------------------------------ | HOST :10.124.70.40 | BEGIN_SNAPSHOT :02.09.2019 14:08 END_SNAPSHOT : 03.09.2019 14:00 |------------------------------------------------------------------------------------ | CURRENT DATABASES SIZE  : | DATABASE :monitor | SIZE (MB) :1370.00 |------------------------------------------------------------------------------------ | CLUSTER CPU TIME : 19:44:22 | CLUSTER WAITINGS TIME : 78:49:16 | | SQL DBTIME : 65:53:09 | SQL CPU TIME : 19:05:21 | SQL WAITINGS TIME : 21:50:46 | SQL IOTIME : 20:53:00 | SQL READ TIME : 20:52:55 | SQL WRITE TIME : 00:00:05 | | SQL CALLS : 311293 ------------------------------------------------------------- | SQL SHARED BLOCKS READS : 13351563334 | SQL SHARED BLOCKS HITS : 2775427045 | SQL SHARED BLOCKS HITS\/READS % : 20.79 | SQL SHARED BLOCKS DIRTED : 21105 | SQL SHARED BLOCKS WRITTEN : 3656 | | SQL TEMPORARY BLOCKS READS : 7464932 | SQL TEMPORARY BLOCKS WRITTEN : 10176024 ------------------------------------------------------------- | | WAITINGS STATICTICS | +------------------------------------------------------------------------------------ | TOP 10 WAITINGS BY TOTAL WAIT TIME FOR SYSTEM PROCESSES +-----+------------------------------+--------------------+-------------------- |    #|               wait_event_type|          wait_event|            duration +-----+------------------------------+--------------------+-------------------- |    1|                      Activity| LogicalLauncherMain|            11:21:01 |    2|                      Activity|    CheckpointerMain|            11:20:35 |    3|                      Activity|      AutoVacuumMain|            11:20:31 |    4|                      Activity|       WalWriterMain|            11:19:35 |    5|                      Activity|        BgWriterMain|            10:14:19 |    6|                      Activity|   BgWriterHibernate|            01:06:04 |    7|                      Activity|       WalSenderMain|            00:04:05 |    8|                        Client|         ClientWrite|            00:04:00 |    9|                            IO|        BufFileWrite|            00:02:45 |   10|                        LWLock|      buffer_mapping|            00:02:14 +-----+------------------------------+--------------------+-------------------- | TOP 10 WAITINGS BY TOTAL WAIT TIME FOR CLIENTS PROCESSES +-----+------------------------------+--------------------+--------------------+---------- |    #|               wait_event_type|          wait_event|            duration|  % dbtime +-----+------------------------------+--------------------+--------------------+---------- |    1|                          Lock|       transactionid|            11:55:37|      18.1 |    2|                            IO|        DataFileRead|            07:19:43|     11.12 |    3|                        Client|          ClientRead|            00:46:54|      1.19 |    4|                          Lock|            relation|            00:40:37|      1.03 |    5|                        LWLock|      buffer_mapping|            00:31:08|      0.79 |    6|                        LWLock|           buffer_io|            00:22:12|      0.56 |    7|                       Timeout|             PgSleep|            00:10:58|      0.28 |    8|                          Lock|               tuple|            00:01:30|      0.04 |    9|                            IO|        BufFileWrite|            00:01:16|      0.03 |   10|                            IO|         BufFileRead|            00:00:37|      0.02 +-----+------------------------------+--------------------+--------------------+---------- | WAITINGS TYPES BY TOTAL WAIT TIME, FOR SYSTEM PROCESSES +-----+------------------------------+-------------------- |    #|               wait_event_type|            duration +-----+------------------------------+-------------------- |    1|                      Activity|            56:46:10 |    2|                            IO|            00:05:13 |    3|                        Client|            00:04:00 |    4|                        LWLock|            00:03:07 +-----+------------------------------+-------------------- | WAITINGS TYPES BY TOTAL WAIT TIME, FOR CLIENTS PROCESSES +-----+------------------------------+--------------------+-------------------- |    #|               wait_event_type|            duration|            % dbtime +-----+------------------------------+--------------------+-------------------- |    1|                          Lock|            12:37:44|               19.17 |    2|                            IO|            07:21:40|               11.17 |    3|                        LWLock|            00:53:26|                1.35 |    4|                        Client|            00:46:54|                1.19 |    5|                       Timeout|            00:10:58|                0.28 |    6|                           IPC|            00:00:04|                   0 +-----+------------------------------+--------------------+-------------------- | WAITINGS FOR SYSTEM PROCESSES +-----+-----------------------------+----------+--------------------+----------------------+-------------------- |    #|                 backend_type|    dbname|     wait_event_type|            wait_event|            duration +-----+-----------------------------+----------+--------------------+----------------------+-------------------- |    1| logical replication launcher|          |            Activity|   LogicalLauncherMain|            11:21:01 |    2|                 checkpointer|          |            Activity|      CheckpointerMain|            11:20:35 |    3|          autovacuum launcher|          |            Activity|        AutoVacuumMain|            11:20:31 |    4|                    walwriter|          |            Activity|         WalWriterMain|            11:19:35 |    5|            background writer|          |            Activity|          BgWriterMain|            10:14:19 |    6|            background writer|          |            Activity|     BgWriterHibernate|            01:06:04 |    7|                    walsender|          |            Activity|         WalSenderMain|            00:04:05 |    8|                    walsender|          |              Client|           ClientWrite|            00:04:00 |    9|              parallel worker|      tdb1|                  IO|          BufFileWrite|            00:02:45 |   10|              parallel worker|      tdb1|              LWLock|        buffer_mapping|            00:02:05 |   11|              parallel worker|      tdb1|                  IO|          DataFileRead|            00:01:10 |   12|              parallel worker|      tdb1|                  IO|           BufFileRead|            00:01:05 |   13|              parallel worker|      tdb1|              LWLock|             buffer_io|            00:00:45 |   14|            autovacuum worker|      tdb1|              LWLock|        buffer_mapping|            00:00:09 |   15|                    walwriter|          |                  IO|              WALWrite|            00:00:08 |   16|                    walwriter|          |              LWLock|          WALWriteLock|            00:00:04 |   17|            background writer|          |              LWLock|          WALWriteLock|            00:00:03 |   18|            background writer|          |                  IO|              WALWrite|            00:00:02 |   19|            background writer|          |                  IO|         DataFileWrite|            00:00:02 |   20|                 checkpointer|          |                  IO| ControlFileSyncUpdate|            00:00:01 |   21|            autovacuum worker|      tdb1|              LWLock|             buffer_io|            00:00:01 +-----+-----------------------------+----------+--------------------+----------------------+-------------------- | WAITINGS FOR SQL +-----+-------------------------+----------+--------------------+--------------------+--------------------+---------- |    #|                  queryid|    dbname|     wait_event_type|          wait_event|            duration|  % dbtime +-----+-------------------------+----------+--------------------+--------------------+--------------------+---------- |    1|       389015618226997618|      tdb1|                Lock|       transactionid|            09:47:43|     14.87 |    2|       389015618226997618|      tdb1|                  IO|        DataFileRead|            05:47:07|      8.78 |    3|                         |      tdb1|                Lock|       transactionid|            02:07:54|      3.24 |    4|                         |      tdb1|                  IO|        DataFileRead|            01:30:24|      2.29 |    5|      8085340880788646241|      tdb1|              Client|          ClientRead|            00:40:20|      1.02 |    6|       389015618226997618|      tdb1|              LWLock|      buffer_mapping|            00:20:41|      0.52 |    7|       389015618226997618|      tdb1|              LWLock|           buffer_io|            00:17:30|      0.44 |    8|      2649515222348904837|      tdb1|             Timeout|             PgSleep|            00:10:58|      0.28 |    9|      4710212362688288619|      tdb1|                Lock|            relation|            00:10:44|      0.27 |   10|      9150846928388977274|      tdb1|                Lock|            relation|            00:10:24|      0.26 |   11|        28942442626229688|      tdb1|                Lock|            relation|            00:07:48|       0.2 |   12|      1237430309438971376|      tdb1|                Lock|            relation|            00:07:32|      0.19 |   13|                         |      tdb1|              LWLock|      buffer_mapping|            00:04:32|      0.11 |   14|                         |      tdb1|              LWLock|           buffer_io|            00:04:13|      0.11 |   15|                         |      tdb1|              Client|          ClientRead|            00:03:57|       0.1 |   16|      4710212362688288619|      tdb1|              LWLock|      buffer_mapping|            00:02:26|      0.06 |   17|      3167065002719415275|      tdb1|                Lock|            relation|            00:02:20|      0.06 |   18|      5731212217001535134|      tdb1|              Client|          ClientRead|            00:01:53|      0.05 |   19|      1237430309438971376|      tdb1|              LWLock|      buffer_mapping|            00:01:42|      0.04 |   20|       389015618226997618|      tdb1|                Lock|               tuple|            00:01:30|      0.04 |   21|      8304755792398128062|      tdb1|                Lock|            relation|            00:01:29|      0.04 |   22|        28942442626229688|      tdb1|                  IO|        BufFileWrite|            00:01:16|      0.03 |   23|      9150846928388977274|      tdb1|                  IO|        DataFileRead|            00:01:07|      0.03 |   24|        28942442626229688|      tdb1|              LWLock|      buffer_mapping|            00:01:03|      0.03 |   25|      9150846928388977274|      tdb1|              LWLock|      buffer_mapping|            00:00:44|      0.02 |   26|        28942442626229688|      tdb1|                  IO|         BufFileRead|            00:00:37|      0.02 |   27|        28942442626229688|      tdb1|              LWLock|           buffer_io|            00:00:25|      0.01 |   28|      1237430309438971376|      tdb1|                  IO|        DataFileRead|            00:00:24|      0.01 |   29|        28942442626229688|      tdb1|                  IO|        DataFileRead|            00:00:22|      0.01 |   30|      5731212217001535134|      tdb1|                Lock|            relation|            00:00:20|      0.01 |   31|      4710212362688288619|      tdb1|                  IO|        DataFileRead|            00:00:19|      0.01 |   32|      9150846928388977274|      tdb1|              Client|          ClientRead|            00:00:09|         0 |   33|      3422818749220588372|      tdb1|              Client|          ClientRead|            00:00:08|         0 |   34|      1237430309438971376|      tdb1|              Client|          ClientRead|            00:00:06|         0 |   35|       389015618226997618|      tdb1|              LWLock|      buffer_content|            00:00:05|         0 |   36|      4710212362688288619|      tdb1|              Client|          ClientRead|            00:00:05|         0 |   37|      4710212362688288619|      tdb1|              LWLock|           buffer_io|            00:00:04|         0 |   38|        28942442626229688|      tdb1|              Client|          ClientRead|            00:00:04|         0 |   39|        28942442626229688|      tdb1|                 IPC|      ParallelFinish|            00:00:03|         0 |   40|       389015618226997618|      tdb1|                  IO|       DataFileWrite|            00:00:02|         0 |   41|     -5730801771815999400|      tdb1|              Client|          ClientRead|            00:00:02|         0 |   42|      2404820632950544954|      tdb1|              Client|          ClientRead|            00:00:02|         0 |   43|     -6572922443698419129|      tdb1|              Client|          ClientRead|            00:00:02|         0 |   44|      8304755792398128062|      tdb1|              Client|          ClientRead|            00:00:02|         0 |   45|     -1473395109729441239|      tdb1|              Client|          ClientRead|            00:00:02|         0 |   46|                         |      tdb1|              LWLock|      buffer_content|            00:00:01|         0 |   47|       180077086776069052|      tdb1|              Client|          ClientRead|            00:00:01|         0 |   48|                         |      tdb1|                  IO|       DataFileWrite|            00:00:01|         0 |   49|        28942442626229688|      tdb1|                 IPC| MessageQueueReceive|            00:00:01|         0 |   50|      2369289265278398647|      tdb1|              Client|          ClientRead|            00:00:01|         0 |   51|      9150846928388977274|      tdb1|                  IO|       DataFileWrite|            00:00:01|         0 +-----+-------------------------+----------+--------------------+--------------------+--------------------+---------- | | CLIENT SQL STATICTICS | +------------------------------------------------------------------------------------ | CLIENT SQL ordered by Elapsed Time +--------------------+----------+----------+----------+----------+----------+-------------------- |        elapsed time|     calls|  % dbtime|     % CPU|      % IO|    dbname|             queryid +--------------------+----------+----------+----------+----------+----------+-------------------- |            06:43:19|        36|      10.2|     85.09|     17.38|      tdb1|  389015618226997618 |            02:06:53|       715|      3.21|      0.85|      0.06|      tdb1| 1237430309438971376 |            01:52:07|       720|      2.84|      1.19|      0.08|      tdb1| 4710212362688288619 |            00:39:03|       357|      0.99|      1.02|      0.33|      tdb1|   28942442626229688 |            00:22:00|         8|      0.56|      0.96|         0|      tdb1| 2649515222348904837 +--------------------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by CPU Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |            cpu time|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |            16:14:38|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618 |            00:13:38|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619 |            00:11:39|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688 |            00:10:58|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837 |            00:09:44|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by User I\/O Wait Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |        io_wait time|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |            05:47:09|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618 |            00:02:15|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688 |            00:00:24|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376 |            00:00:19|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619 |            00:00:00|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Shared Buffers Reads +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |       buffers reads|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |          2562353244|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618 |            11041689|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688 |             3303551|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376 |             3242892|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619 |                   0|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Disk Reads Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |           read time|     calls|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |            03:37:46|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618 |            00:04:07|       357|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688 |            00:00:59|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619 |            00:00:42|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376 |            00:00:00|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Executions +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |               calls|      rows|  % dbtime|total_time|     % CPU|      % IO|    dbname|             queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- |                 720|       720|      2.84|  01:52:07|      1.19|      0.08|      tdb1| 4710212362688288619 |                 715|       715|      3.21|  02:06:53|      0.85|      0.06|      tdb1| 1237430309438971376 |                 357|         0|      0.99|  00:39:03|      1.02|      0.33|      tdb1|   28942442626229688 |                  36|        36|      10.2|  06:43:19|     85.09|     17.38|      tdb1|  389015618226997618 |                   8|         8|      0.56|  00:22:00|      0.96|         0|      tdb1| 2649515222348904837 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | Complete List of SQL Text ---------------------------------------------- \u2026 <\/pre>\n<\/div>\n<\/div>\n
  \u041f\u0440\u043e\u0434\u043e\u043b\u0436\u0435\u043d\u0438\u0435 \u2013 \u0441\u043b\u0435\u0434\u0443\u0435\u0442. \u041d\u0430 \u043e\u0447\u0435\u0440\u0435\u0434\u0438 \u0444\u043e\u0440\u043c\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u0435 \u0438\u0441\u0442\u043e\u0440\u0438\u0438 \u0431\u043b\u043e\u043a\u0438\u0440\u043e\u0432\u043e\u043a (pg_stat_locks), \u0431\u043e\u043b\u0435\u0435 \u043f\u043e\u0434\u0440\u043e\u0431\u043d\u043e\u0435 \u043e\u043f\u0438\u0441\u0430\u043d\u0438\u0435 \u043f\u0440\u043e\u0446\u0435\u0441\u0441\u0430 \u0437\u0430\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u044f \u0442\u0430\u0431\u043b\u0438\u0446.<\/div>\n