ProHoster > وبلاگ > اداره > نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش

نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش

مهندس - ترجمه از لاتین - الهام گرفته شده است.
یک مهندس می تواند هر کاری انجام دهد. (ج) R. دیزل.
کتیبه ها.
نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش
یا داستانی در مورد اینکه چرا یک مدیر پایگاه داده باید گذشته برنامه نویسی خود را به خاطر بسپارد.

پیش گفتار

همه اسامی تغییر کرده اند. مسابقات تصادفی هستند. مطالب صرفاً نظر شخصی نویسنده است.

سلب مسئولیت از گارانتی: در سری مقالات برنامه ریزی شده، هیچ توضیح دقیق و دقیقی از جداول و اسکریپت های استفاده شده وجود نخواهد داشت. مواد را نمی توان بلافاصله "همانطور که هست" استفاده کرد.
اول، به دلیل حجم زیاد مواد،
ثانیا، به دلیل تیز بودن با پایه تولید یک مشتری واقعی.
بنابراین، تنها ایده ها و توضیحات به کلی ترین شکل در مقالات ارائه خواهد شد.
شاید در آینده سیستم به سطح ارسال در GitHub رشد کند یا شاید نه. زمان نشان خواهد داد.

آغاز داستان -یادت میاد چطوری شروع شد'.
آنچه در نتیجه اتفاق افتاد، به کلی ترین اصطلاح - "سنتز به عنوان یکی از روش های بهبود عملکرد PostgreSQL»

چرا به این همه نیاز دارم؟

خوب، اولا، برای اینکه خود را فراموش نکنید، به یاد روزهای باشکوه دوران بازنشستگی باشید.
ثانیاً آنچه نوشته شده را نظام مند کنیم. برای خودم، گاهی اوقات شروع به گیج شدن می کنم و قسمت های جداگانه را فراموش می کنم.

خوب، و مهمتر از همه - ناگهان می تواند برای کسی مفید باشد و به عدم اختراع مجدد چرخ و جمع آوری چنگک کمک کند. به عبارت دیگر، کارمای خود را بهبود بخشید (نه خابروفسکی). زیرا با ارزش ترین چیز در این دنیا ایده هاست. نکته اصلی این است که یک ایده پیدا کنید. و تبدیل این ایده به واقعیت یک مسئله کاملاً فنی است.

پس آرام آرام شروع کنیم...

فرمول بندی مسئله.

در دسترس:

PostgreSQL (10.5)، بار مختلط (OLTP+DSS)، بار متوسط ​​تا سبک، میزبانی شده در ابر AWS.
هیچ نظارت بر پایگاه داده وجود ندارد، نظارت زیرساخت به عنوان ابزار استاندارد AWS در یک پیکربندی حداقل ارائه شده است.

مورد نیاز:

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

معرفی یا تحلیل مختصر راه حل ها

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

گزینه 1 - "کار بر اساس تقاضا"

ما همه چیز را همانطور که هست رها می کنیم. اگر مشتری از چیزی در سلامت، عملکرد پایگاه داده یا برنامه راضی نباشد، مهندسان DBA را از طریق ایمیل یا با ایجاد یک حادثه در جعبه بلیط مطلع می کند.
یک مهندس پس از دریافت اعلان، مشکل را درک می کند، راه حلی ارائه می دهد یا مشکل را در قفسه می گذارد، به این امید که همه چیز خود به خود حل شود، و به هر حال، همه چیز به زودی فراموش می شود.
نان زنجبیلی و دونات، کبودی و برآمدگینان زنجبیلی و دونات:
1. هیچ چیز اضافی برای انجام دادن
2. همیشه فرصت بیرون آمدن و کثیف شدن وجود دارد.
3. زمان زیادی را که می توانید به تنهایی صرف کنید.
کبودی و برآمدگی:
1. دیر یا زود، مشتری به ماهیت هستی و عدالت جهانی در این دنیا فکر می کند و یک بار دیگر این سوال را از خود می پرسد که چرا پولم را به آنها می پردازم؟ نتیجه همیشه یکسان است - تنها سؤال این است که مشتری حوصله اش سر می رود و برای خداحافظی دست تکان می دهد. و فیدر خالی است. آن غم انگیز است.
2. پیشرفت یک مهندس صفر است.
3. مشکلات در برنامه ریزی کار و بارگیری

گزینه 2 - "با تنبور برقص، کفش بپوش و بپوش"

بند 1-چرا به سیستم نظارتی نیاز داریم، همه درخواست ها را دریافت خواهیم کرد. ما مجموعه‌ای از انواع پرسش‌ها را به فرهنگ لغت داده و نماهای پویا راه‌اندازی می‌کنیم، انواع شمارنده‌ها را روشن می‌کنیم، همه چیز را در جداول می‌آوریم، فهرست‌ها و جداول را به صورت دوره‌ای تجزیه و تحلیل می‌کنیم. در نتیجه، نمودارها، جداول، گزارش های زیبا یا نه چندان زیبا داریم. نکته اصلی - که بیشتر، بیشتر خواهد بود.
بند 2-تولید فعالیت - تجزیه و تحلیل همه اینها را اجرا کنید.
بند 3-ما در حال تهیه یک سند خاص هستیم، به این سند می گوییم، به سادگی - "چگونه پایگاه داده را تجهیز کنیم."
بند 4- مشتری، با دیدن این همه شکوه نمودارها و ارقام، در یک اعتماد به نفس ساده و ساده کودکانه است - اکنون همه چیز به زودی برای ما کار خواهد کرد. و به راحتی و بدون دردسر از منابع مالی خود جدا شوید. مدیریت نیز مطمئن است که مهندسان ما سخت کار می کنند. حداکثر بارگذاری
بند 5- مرحله 1 را به طور منظم تکرار کنید.
نان زنجبیلی و دونات، کبودی و برآمدگینان زنجبیلی و دونات:
1. زندگی مدیران و مهندسان ساده، قابل پیش بینی و پر از فعالیت است. همه چیز شلوغ است، همه مشغول هستند.
2. زندگی مشتری نیز بد نیست - او همیشه مطمئن است که شما باید کمی صبور باشید و همه چیز درست می شود. بهتر نشدن، خوب، خوب - این دنیا ناعادلانه است، در زندگی بعدی - خوش شانس.
کبودی و برآمدگی:
1. دیر یا زود، یک ارائه دهنده باهوش تر از خدمات مشابه وجود خواهد داشت که همان کار را انجام می دهد، اما کمی ارزان تر. و اگر نتیجه یکسان است، چرا بیشتر پرداخت کنید. که دوباره منجر به ناپدید شدن فیدر می شود.
2. خسته کننده است. هر فعالیت کوچک معنی دار چقدر خسته کننده است.
3. همانطور که در نسخه قبلی - بدون توسعه. اما برای یک مهندس، منهای این است که برخلاف گزینه اول، در اینجا باید دائما یک IDB تولید کنید. و این زمان می برد. که می تواند به نفع عزیزتان خرج شود. چون شما نمی توانید از خودتان مراقبت کنید، همه به شما اهمیت می دهند.

گزینه 3- نیازی به اختراع دوچرخه نیست، باید آن را بخرید و سوار آن شوید.

مهندسان شرکت های دیگر آگاهانه پیتزا را با آبجو می خورند (اوه، دوران باشکوه سن پترزبورگ در دهه 90). بیایید از سیستم های نظارتی استفاده کنیم که ساخته شده، اشکال زدایی شده و کار می کنند، و به طور کلی، مزایایی را به همراه دارند (خوب، حداقل برای سازندگانشان).
نان زنجبیلی و دونات، کبودی و برآمدگینان زنجبیلی و دونات:
1. نیازی به تلف کردن زمان برای اختراع آنچه قبلاً اختراع شده است نیست. بگیرید و استفاده کنید.
2. سیستم های نظارتی را احمق ها نمی نویسند و البته مفید هستند.
3. سیستم های نظارت کار معمولاً اطلاعات فیلتر شده مفیدی را ارائه می دهند.
کبودی و برآمدگی:
1. مهندس در این مورد یک مهندس نیست، بلکه فقط کاربر محصول شخص دیگری است یا یک کاربر.
2. مشتری باید به نیاز به خرید چیزی که عموماً نمی خواهد بفهمد و نباید آن را درک کند متقاعد شود و به طور کلی بودجه سال تصویب شده و تغییر نخواهد کرد. سپس باید یک منبع جداگانه اختصاص دهید، آن را برای یک سیستم خاص پیکربندی کنید. آن ها ابتدا باید پرداخت کنید، پرداخت کنید و دوباره پرداخت کنید. و مشتری خسیس است. این هنجار این زندگی است.

چرنیشفسکی چه باید کرد؟ سوال شما خیلی به جاست (با)

در این مورد خاص و وضعیت فعلی، می توانید کمی متفاوت عمل کنید - بیایید سیستم نظارتی خودمان را بسازیم.
نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش
خب، البته نه یک سیستم، به معنای کامل کلمه، این خیلی بلند و متکبرانه است، اما حداقل به نحوی کار را برای خود راحت کنید و اطلاعات بیشتری برای حل حوادث عملکرد جمع آوری کنید. برای اینکه در موقعیتی قرار نگیرید - "برو آنجا، نمی دانم کجا، آن را پیدا کن، نمی دانم چیست."

مزایا و معایب این گزینه چیست:

مزایا:
1. جالب است. خوب، حداقل جالب تر از ثابت "کوچک کردن فایل داده، تغییر جدول و غیره" است.
2. اینها مهارت های جدید و پیشرفت های جدید هستند. که در آینده دیر یا زود شیرینی زنجفیلی و دونات را می دهد.
منفی:
1. باید کار کرد. زیاد کار کن
2. شما باید به طور منظم معنی و دیدگاه همه فعالیت ها را توضیح دهید.
3. چیزی باید قربانی شود، زیرا تنها منبع در دسترس مهندس - زمان - توسط جهان محدود شده است.
4. بدترین و ناخوشایندترین - در نتیجه، زباله هایی مانند "نه یک موش، نه یک قورباغه، بلکه یک حیوان کوچک ناشناخته" ممکن است ظاهر شود.

کسی که چیزی را به خطر نمی اندازد شامپاین نمی نوشد.
بنابراین، سرگرمی شروع می شود.

ایده کلی - شماتیک

نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش
(تصویر برگرفته از مقاله «سنتز به عنوان یکی از روش های بهبود عملکرد PostgreSQL")

توضیح:

  • پایگاه داده هدف با پسوند استاندارد PostgreSQL "pg_stat_statements" نصب شده است.
  • در پایگاه داده مانیتورینگ، ما مجموعه ای از جداول سرویس را ایجاد می کنیم تا تاریخچه pg_stat_statements را در مرحله اولیه ذخیره کرده و معیارها و نظارت را در آینده پیکربندی کنیم.
  • در میزبان مانیتورینگ، مجموعه‌ای از اسکریپت‌های bash را ایجاد می‌کنیم، از جمله مواردی برای ایجاد حوادث در سیستم بلیط.

میزهای خدماتی

برای شروع، یک ERD ساده شده به صورت شماتیک، در پایان چه اتفاقی افتاد:
نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش
شرح مختصری از جداولنقطه پایانی - میزبان، نقطه اتصال به نمونه
پایگاه داده - گزینه های پایگاه داده
pg_stat_history - جدول تاریخی برای ذخیره عکس های موقت از نمای pg_stat_statements پایگاه داده هدف
متریک_واژه نامه - فرهنگ لغت معیارهای عملکرد
metric_config - پیکربندی معیارهای فردی
متری - یک معیار خاص برای درخواستی که در حال نظارت است
سابقه_هشدار متریک - سابقه هشدارهای عملکرد
log_query - جدول سرویس برای ذخیره رکوردهای تجزیه شده از فایل گزارش PostgreSQL دانلود شده از AWS
پایه - پارامترهای دوره زمانی مورد استفاده به عنوان پایه
ایست بازرسی - پیکربندی معیارها برای بررسی وضعیت پایگاه داده
checkpoint_alert_history - سابقه هشدار از معیارهای بررسی وضعیت پایگاه داده
pg_stat_db_queries - جدول خدمات درخواست های فعال
گزارش فعالیت - جدول خدمات گزارش فعالیت
trap_oid - جدول سرویس پیکربندی تله

مرحله 1 - جمع آوری آمار عملکرد و دریافت گزارش

برای ذخیره اطلاعات آماری از جدول استفاده می شود. pg_stat_history
ساختار جدول pg_stat_history

                                          جدول "public.pg_stat_history" ستون | نوع | اصلاح کننده ها ------------------------------------------------ -------------------------------- شناسه | عدد صحیح | non null پیش فرض nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | مهر زمانی بدون منطقه زمانی | پایگاه_id | عدد صحیح | dbid | oid | userid | oid | queryid | bigint | پرس و جو | متن | تماس | bigint | total_time | دقت دوگانه | min_time | دقت دوگانه | حداکثر_زمان | دقت دوگانه | میانگین_زمان | دقت دوگانه | stddev_time | دقت دوگانه | ردیف | bigint | shared_blks_hit | bigint | shared_blks_read | bigint | به اشتراک گذاشته شده_بلکس_کثیف | bigint | اشتراک گذاری_بلکس_نوشته | bigint | local_blks_hit | bigint | local_blks_read | bigint | local_blks_dirtied | bigint | local_blks_written | bigint | temp_blks_read | bigint | temp_blks_written | bigint | blk_read_time | دقت دوگانه | blk_write_time | دقت دوگانه | baseline_id | عدد صحیح | شاخص‌ها: "pg_stat_history_pkey" کلید اولیه، btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) "Foreign-dase" پایگاه داده مراجع (id ) ON DELETE CASCADE

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

استفاده از این میز بسیار ساده است.

pg_stat_history آمار انباشته اجرای پرس و جو را برای هر ساعت نشان خواهد داد. در ابتدای هر ساعت پس از پرکردن جدول، آمار pg_stat_statements تنظیم مجدد با pg_stat_statements_reset().
توجه: آمار برای درخواست هایی با مدت زمان بیش از 1 ثانیه جمع آوری می شود.
پر کردن جدول pg_stat_history

--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
  endpoint_rec record ;
  database_rec record ;
  pg_stat_snapshot record ;
  current_snapshot_timestamp timestamp without time zone;
BEGIN
  current_snapshot_timestamp = date_trunc('minute',now());  
  
  FOR endpoint_rec IN SELECT * FROM endpoint 
  LOOP
    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
	  LOOP
	    
		RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
		
		--Connect to the target DB	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_time double precision , 
	         rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
             local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
             temp_blks_read	 bigint ,temp_blks_written bigint ,
             blk_read_time double precision , blk_write_time double precision	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
	
        FOR pg_stat_snapshot IN
          --All queries with max_time greater than 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

	                  )
	        AS t
	         ( dbid oid , userid oid , queryid bigint ,query text , calls bigint , 
  	           total_time double precision ,min_time double precision	 ,max_time double precision	 , mean_time double precision	 ,  stddev_time double precision	 , 
	           rows bigint , shared_blks_hit bigint , shared_blks_read bigint ,shared_blks_dirtied bigint ,shared_blks_written	 bigint ,
               local_blks_hit	 bigint ,local_blks_read bigint , local_blks_dirtied bigint ,local_blks_written bigint ,
               temp_blks_read	 bigint ,temp_blks_written bigint ,
               blk_read_time double precision , blk_write_time double precision	  
	         )
	    LOOP
		  INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	      VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );
		  
        END LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
    
  END LOOP;

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

در نتیجه پس از مدتی مشخص در جدول pg_stat_history مجموعه ای از عکس های فوری از محتویات جدول خواهیم داشت pg_stat_statements پایگاه داده هدف

در واقع گزارش دادن

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

داده های جمع آوری شده برای یک دوره زمانی معین

درخواست

SELECT 
  database_id , 
  SUM(calls) AS calls ,SUM(total_time)  AS total_time ,
  SUM(rows) AS rows , SUM(shared_blks_hit)  AS shared_blks_hit,
  SUM(shared_blks_read) AS shared_blks_read ,
  SUM(shared_blks_dirtied) AS shared_blks_dirtied,
  SUM(shared_blks_written) AS shared_blks_written , 
  SUM(local_blks_hit) AS local_blks_hit , 
  SUM(local_blks_read) AS local_blks_read , 
  SUM(local_blks_dirtied) AS local_blks_dirtied , 
  SUM(local_blks_written)  AS local_blks_written,
  SUM(temp_blks_read) AS temp_blks_read, 
  SUM(temp_blks_written) temp_blks_written , 
  SUM(blk_read_time) AS blk_read_time , 
  SUM(blk_write_time) AS blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

زمان D.B

to_char(فاصله "1 میلی ثانیه" * pg_total_stat_history_rec.total_time، "HH24:MI:SS.MS")

زمان I/O

to_char(فاصله '1 میلی ثانیه' * (pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time)، 'HH24:MI:SS.MS')

TOP10 SQL توسط total_time

درخواست

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(total_time)  AS total_time  	
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT 
GROUP BY queryid 
ORDER BY 3 DESC 
LIMIT 10
------------------------------------------------ ------------------------------------ | TOP10 SQL در مجموع زمان اجرا | #| queryid| تماس می گیرد| %| total_time (ms) | dbtime % +----+------------------------------------------ ------------------------------- | 1| 821760255| 2| 00001|00:03:23.141( 203141.681 ms.)| 5.42 | 2| 4152624390| 2| 00001|00:03:13.929( 193929.215 ms.)| 5.17 | 3| 1484454471| 4| 00001|00:02:09.129( 129129.057 ms.)| 3.44 | 4| 655729273| 1| 00000|00:02:01.869( 121869.981 ms.)| 3.25 | 5| 2460318461| 1| 00000|00:01:33.113( 93113.835 میلی‌ثانیه)| 2.48 | 6| 2194493487| 4| 00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| 00000|00:00:06.156( 6156.352 میلی ثانیه)| .16 | 8| 3644780286| 1| 00000|00:00:01.063( 1063.830 میلی‌ثانیه)| 03

TOP10 SQL با کل زمان I/O

درخواست

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10
------------------------------------------------ -------------------------------------- | TOP10 SQL BY TOTAL I/O TIME | #| queryid| تماس می گیرد| %| زمان ورود/خروج (میلی‌ثانیه)|دسی‌بل زمان ورودی/خروجی % +----+------------------------------- ------------------------------------------------- -- | 1| 4152624390| 2| 00001|00:08:31.616( 511616.592 ms.)| 31.06 ژوئن | 2| 821760255| 2| 00001|00:08:27.099( 507099.036 میلی‌ثانیه)| 30.78 | 3| 655729273| 1| 00000|00:05:02.209( 302209.137 میلی ثانیه)| 18.35 | 4| 2460318461| 1| 00000|00:04:05.981( 245981.117 ms.)| 14.93 | 5| 1484454471| 4| 00001|00:00:39.144( 39144.221 میلی‌ثانیه)| 2.38 | 6| 2194493487| 4| 00001|00:00:18.182( 18182.816 میلی‌ثانیه)| 1.10 | 7| 1053044345| 1| 00000|00:00:16.611( 16611.722 میلی ثانیه)| 1.01 | 8| 3644780286| 1| 00000|00:00:00.436( 436.205 میلی ثانیه)| 03

TOP10 SQL با حداکثر زمان اجرا

درخواست

SELECT 
  id AS snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC 
LIMIT 10

------------------------------------------------ ------------------------------------ | TOP10 SQL با حداکثر زمان اجرا | #| عکس فوری| شناسه عکس لحظه ای| queryid| حداکثر_زمان (میلی‌ثانیه) +----+----------------------------------------- --+---------------------------------------- | 1| 05.04.2019/01/03 4169:655729273| 00| 02| 01.869:121869.981:2( 04.04.2019 ms.) | 17| 00/4153/821760255 00:01| 41.570| 101570.841| 3:04.04.2019:16( 00 ms.) | 4146| 821760255/00/01 41.570:101570.841| 4| 04.04.2019| 16:00:4144( 4152624390 ms.) | 00| 01/36.964/96964.607 5:04.04.2019| 17| 00| 4151:4152624390:00( 01 ms.) | 36.964| 96964.607/6/05.04.2019 10:00| 4188| 1484454471| 00:01:33.452( 93452.150 ms.) | 7| 04.04.2019/17/00 4150:2460318461 | 00| 01| 33.113:93113.835:8( 04.04.2019 ms.) | 15| 00/4140/1484454471 00:00| 11.892| 11892.302| 9:04.04.2019:16( 00 ms.) | 4145| 1484454471/00/00 11.892:11892.302| 10| 04.04.2019| 17:00:4152( 1484454471 ms.) | 00| 00/11.892/11892.302 XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.) | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX ( XNUMX ms.)

TOP10 SQL توسط SHARED بافر خواندن/نوشتن

درخواست

SELECT 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC  , 5 DESC 
LIMIT 10
------------------------------------------------ ------------------------------------ | TOP10 SQL BY BUFFER SHARED READ/WRITE | #| عکس فوری| شناسه عکس لحظه ای| queryid| بلوک های مشترک خوانده شده| نوشتن بلوک های مشترک +----+---------------------------------------- -+--------------------+------------------------------------------------------------------------------------ 1| 04.04.2019/17/00 4153:821760255| 797308| 0| 2| 04.04.2019 | 16| 00/4146/821760255 797308:0| 3| 05.04.2019| 01| 03 | 4169| 655729273/797158/0 4:04.04.2019| 16| 00| 4144| 4152624390 | 756514| 0/5/04.04.2019 17:00| 4151| 4152624390| 756514| 0 | 6| 04.04.2019/17/00 4150:2460318461| 734117| 0| 7| 04.04.2019 | 17| 00/4155/3644780286 52973:0| 8| 05.04.2019| 01| 03 | 4168| 1053044345/52818/0 9:04.04.2019| 15| 00| 4141| 2194493487 | 52813| 0/10/04.04.2019 16:00| 4147| 2194493487| 52813| 0 | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX| XNUMX ------------------------------------------------ ------------------------------------------------

هیستوگرام توزیع پرس و جو بر اساس حداکثر زمان اجرا

درخواست ها

SELECT  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;

SELECT 
  SUM(calls) AS calls
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id =DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND 
  ( max_time >= hist_current_min AND  max_time < hist_current_max ) ;

|----------------------------------------------- ---------------------------------------- | MAX_TIME هیستوگرام | مجموع تماس ها : 33851920 | MIN TIME : 00:00:01.063 | حداکثر زمان: 00:02:01.869 ----------------------------------------- ---------------------------- | حداقل مدت| حداکثر مدت زمان| تماس +---------------------------------+------------- ---------------------+---------- | 00:00:01.063( 1063.830 میلی ثانیه) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 میلی ثانیه) | 0 | 00:00:25.225( 25225.060 میلی ثانیه) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0

TOP10 عکس فوری بر اساس پرس و جو در ثانیه

درخواست ها

--pg_qps.sql
--Calculate Query Per Second 
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
 pg_stat_history_rec record ;
 prev_pg_stat_history_id integer ;
 prev_pg_stat_history_rec record;
 total_seconds double precision ;
 result double precision;
BEGIN 
  result = 0 ;
  
  SELECT *
  INTO pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = pg_stat_history_id ;

  IF pg_stat_history_rec.snapshot_timestamp IS NULL 
  THEN
    RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id < pg_stat_history_rec.id ;

  IF prev_pg_stat_history_id IS NULL 
  THEN
    RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
	RETURN NULL ;
  END IF;
  
  SELECT *
  INTO prev_pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = prev_pg_stat_history_id ;
  
  --RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;    

  total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
  
  --RAISE NOTICE 'total_seconds = % ', total_seconds ;    
  
  --RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;      
  
  IF total_seconds > 0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

|----------------------------------------------- ---------------------------------------- | TOP10 Snapshots مرتب شده بر اساس اعداد QueryPerSeconds ------------------------------------- ------ ------------------------------------------------- ------------------------------------------- | #| عکس فوری| شناسه عکس لحظه ای| تماس می گیرد| کل dbtime| QPS | زمان I/O | زمان ورودی/خروجی % +-----+--------------------------------------- ------------------------------------------------- -+----------------------------------+-------------------------------------------------------------------------------------------- 1| 04.04.2019/20/04 4161:5758631| 00| 06| 30.513:390513.926:1573.396( 00 ms.)| 00| 01.470:1470.110:376( 2 میلی ثانیه)| .04.04.2019 | 17| 00/4149/3529197 00:11| 48.830| 708830.618| 980.332:00:12( 47.834 ms.)| 767834.052| 108.324:3:04.04.2019( 16 ms.)| 00 | 4143| 3525360/00/10 13.492:613492.351| 979.267| 00| 08:41.396:521396.555( 84.988 ms.)| 4| 04.04.2019:21:03( 4163 ms.)| 2781536 | 00| 03/06.470/186470.979 785.745:00| 00| 00.249| 249.865:134:5( 04.04.2019 ms.)| 19| 03:4159:2890362( 00 ms.)| .03 | 16.784| 196784.755/776.979/00 00:01.441| 1441.386| 732| 6:04.04.2019:14( 00 ms.)| 4137| 2397326:00:04( 43.033 ms.)| .283033.854 | 665.924| 00/00/00.024 24.505:009 | 7| 04.04.2019| 15:00:4139( 2394416 ms.)| 00| 04:51.435:291435.010( 665.116 ms.)| .00 | 00| 12.025/12025.895/4.126 8:04.04.2019| 13| 00| 4135:2373043:00( 04 ms.)| 26.791| 266791.988:659.179:00( 00 ms.)| 00.064 | 64.261| 024/9/05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| .77.507 | 10| 04.04.2019/18/01 4157:1145596| 00| 01| 19.217:79217.372:313.004( 00 ms.)| 00| 01.319:1319.676:1.666( XNUMX ms.)| XNUMX | XNUMX| XNUMX/XNUMX/XNUMX XNUMX:XNUMX| XNUMX| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX میلی ثانیه)| XNUMX| XNUMX:XNUMX:XNUMX( XNUMX ms.)| XNUMX

تاریخچه اجرای ساعتی با QueryPerSeconds و زمان I/O

درخواست

SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2

|-----------------------------------------------------------------------------------------------
| HOURLY EXECUTION HISTORY  WITH QueryPerSeconds and I/O Time
-----------------------------------------------------------------------------------------------------------------------------------------------
| QUERY PER SECOND HISTORY
|    #|          snapshot| snapshotID|      calls|                      total dbtime|        QPS|                          I/O time| I/O time %
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 11:00|       4131|       3747|  00:00:00.835(       835.374 ms.)|      1.041|  00:00:00.000(          .000 ms.)|       .000
|    2|  04.04.2019 12:00|       4133|    1002722|  00:01:52.419(    112419.376 ms.)|    278.534|  00:00:00.149(       149.105 ms.)|       .133
|    3|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    4|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    5|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    6|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    7|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    8|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666
|    9|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|   10|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|   11|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|   12|  04.04.2019 23:03|       4165|    1443155|  00:01:34.467(     94467.539 ms.)|    200.438|  00:00:00.015(        15.287 ms.)|       .016
|   13|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   14|  05.04.2019 02:03|       4171|     189852|  00:00:10.989(     10989.899 ms.)|     52.737|  00:00:00.539(       539.110 ms.)|      4.906
|   15|  05.04.2019 03:01|       4173|       3627|  00:00:00.103(       103.000 ms.)|      1.042|  00:00:00.004(         4.131 ms.)|      4.010
|   16|  05.04.2019 04:00|       4175|       3627|  00:00:00.085(        85.235 ms.)|      1.025|  00:00:00.003(         3.811 ms.)|      4.471
|   17|  05.04.2019 05:00|       4177|       3747|  00:00:00.849(       849.454 ms.)|      1.041|  00:00:00.006(         6.124 ms.)|       .721
|   18|  05.04.2019 06:00|       4179|       3747|  00:00:00.849(       849.561 ms.)|      1.041|  00:00:00.000(          .051 ms.)|       .006
|   19|  05.04.2019 07:00|       4181|       3747|  00:00:00.839(       839.416 ms.)|      1.041|  00:00:00.000(          .062 ms.)|       .007
|   20|  05.04.2019 08:00|       4183|       3747|  00:00:00.846(       846.382 ms.)|      1.041|  00:00:00.000(          .007 ms.)|       .001
|   21|  05.04.2019 09:00|       4185|       3747|  00:00:00.855(       855.426 ms.)|      1.041|  00:00:00.000(          .065 ms.)|       .008
|   22|  05.04.2019 10:00|       4187|       3797|  00:01:40.150(    100150.165 ms.)|      1.055|  00:00:21.845(     21845.217 ms.)|     21.812

متن تمام SQL انتخاب شده است

درخواست

SELECT 
  queryid , 
  query 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query

مجموع

همانطور که می بینید، با روش های نسبتاً ساده، می توانید اطلاعات مفید زیادی در مورد حجم کار و وضعیت پایگاه داده به دست آورید.

توجه داشته باشید:اگر queryid را در کوئری ها اصلاح کنید، تاریخچه یک درخواست جداگانه را دریافت می کنیم (به منظور صرفه جویی در فضا، گزارش های درخواست جداگانه حذف می شوند).

بنابراین، داده های آماری در مورد عملکرد پرس و جو در دسترس و جمع آوری شده است.
مرحله اول «جمع آوری داده های آماری» به پایان می رسد.

می توانید به مرحله دوم بروید - "پیکربندی معیارهای عملکرد".
نظارت بر عملکرد پرس و جوهای PostgreSQL. بخش 1 - گزارش

اما این یک داستان دیگر است.

ادامه ...

منبع: www.habr.com

اضافه کردن نظر