بدست آوردن آمار SQL های اجرایی در Postgresql با pg_stat_statement

آیا می خواهید تمام آمار اجرای پرس و جوی SQL خود را بدانید مانند: چند بار یک پرس و جو اجرا شده است، زمان کل و متوسط برای پرس و جوها و موارد دیگر چقدر است.

PostgreSQL ماژول pg_stat_statements را ارائه می دهد که به طور خودکار انواع مختلف آمار را همه پرس و جوهای در حال اجرا ثبت می کند.

در طول فعالیت بهینه سازی عملکرد، ما همیشه مشتاق پرس و جوهای طولانی سرور پایگاه داده خود هستیم.

ما باید ماژول pg_stat_statements را پیکربندی کنیم تا بتوانیم به راحتی از آمارهای مورد نیاز برای تنظیم عملکرد استفاده کنیم.

مراحل پیکربندی و فعال کردن ماژول pg_stat_statements:

CREATE EXTENSION pg_stat_statements;

 درادامه بر روی جدول pg_stat_statements کوئری مورد نظر را اجرا می کنیم تا خروجی را مشاهده کنیم:

SELECT * FROM pg_stat_statements;

احتمالا با خطای زیر مواجه خواهید شد که راهکار انجام تنظیمات مربوطه در postgresql.conf می باشد.

ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

در فایل postgresql.conf تنظیمات را انجام می دهیم

shared_preload_libraries = ‘pg_stat_statements’

pg_stat_statements.max = 10000

pg_stat_statements.track = all

سپس جدول مورد نظر را انجام آزمایش را می سازیم و کوئری ها را اجرا می کنیم تا نتایج را در جدول pg_stat_statements مشاهدم کنیم.

CREATE TABLE tbl_ItemTransactions

 (

     TranID SERIAL

     ,TransactionDate TIMESTAMPTZ

     ,TransactionName TEXT

 );

INSERT INTO tbl_ItemTransactions

(TransactionDate, TransactionName)

SELECT x, ‘dbrnd’

FROM generate_series(‘2016-01-01 00:00:00’::timestamptz, ‘2016-12-31 23:59:59′::timestamptz,’2 seconds’::interval) a(x);

SELECT COUNT(1) FROM tbl_ItemTransactions;

SELECT *FROM tbl_ItemTransactions

WHERE TransactionDate BETWEEN ‘۲۰۱۶-۰۲-۰۸’ AND ‘۲۰۱۶-۰۴-۰۸’;

با اجرای دستور زیر مجتوی جدول را مشاهده خواهیم کرد .

SELECT * FROM pg_stat_statements;

با دستور زیر pg_stat_statements را Reset می کنیم.

SELECT * FROM pg_stat_statements_reset();

در ادامه یک نمونه کوئری کاربردی که ۱۰ دستور اجرایی طولانی مدت را نمایش می دهد ارائه می گردد.

SELECT

 pd.datname

 ,pss.query AS SQLQuery

 ,pss.rows AS TotalRowCount

 ,(pss.total_time / 1000 / 60) AS TotalMinute

 ,((pss.total_time / 1000 / 60)/calls) as TotalAverageTime

FROM pg_stat_statements AS pss

INNER JOIN pg_database AS pd

 ON pss.dbid=pd.oid

ORDER BY 1 DESC

LIMIT 10;

لینک مستند

مقالات مرتبط:

۱۰ افزونه پرکاربرد پایگاه داده PostgreSQL SYNCHRONOUS و ASYNCHRONOUS REPLICATION در پایگاه داده POSTGRESQL بررسی انواع شاخص ها (Index)  در پایگاه داده PostgreSQL بررسی view کاربردی pg_stat_activity در Postgresql