ניטור ביצועים של שאילתות 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. על הלקוח להשתכנע בצורך לקנות משהו שהוא בדרך כלל לא רוצה להבין, ולא צריך, ובכלל התקציב לשנה אושר ולא ישתנה. אז אתה צריך להקצות משאב נפרד, להגדיר אותו עבור מערכת ספציפית. הָהֵן. ראשית אתה צריך לשלם, לשלם ושוב לשלם. והלקוח קמצן. זו הנורמה של החיים האלה.

מה לעשות, צ'רנישבסקי? השאלה שלך מאוד רלוונטית. (עם)

במקרה הספציפי הזה ובמצב הנוכחי, אתה יכול לעשות קצת אחרת - בואו ניצור מערכת ניטור משלנו.

ובכן, לא מערכת, כמובן, במלוא מובן המילה, זה רועש ויומרני מדי, אבל לפחות איכשהו להקל על עצמך ולאסוף מידע נוסף כדי לפתור תקריות ביצועים. כדי לא למצוא את עצמך במצב - "לך לשם, אני לא יודע איפה, מצא את זה, אני לא יודע מה."

מהם היתרונות והחסרונות של אפשרות זו:

יתרונות:
1. זה מעניין. ובכן, לפחות מעניין יותר מהקבוע "לכווץ קובץ נתונים, לשנות מרחב טבלאות וכו'."
2. אלו כישורים חדשים והתפתחות חדשה. מה שבעתיד ייתן במוקדם או במאוחר ג'ינג'ר וסופגניות מוצדקות.
חסרונות:
1. צריך לעבוד. עובד הרבה.
2. יהיה עליך להסביר באופן קבוע את המשמעות ונקודות המבט של כל פעילות.
3. יהיה צורך להקריב משהו, כי המשאב היחיד שעומד לרשות המהנדס - הזמן - מוגבל על ידי היקום.
4. הכי גרוע והכי לא נעים - כתוצאה מכך עלול להתברר זבל כמו "לא עכבר, לא צפרדע, אלא חיה קטנה לא ידועה".

מי שלא מסתכן במשהו לא שותה שמפניה.
אז, הכיף מתחיל.

רעיון כללי - סכמטי

(האיור נלקח מהמאמר «סינתזה כאחת השיטות לשיפור ביצועי PostgreSQL")

הסבר:

• מסד הנתונים של היעד מותקן עם סיומת PostgreSQL הסטנדרטית "pg_stat_statements".
• במסד הנתונים של הניטור, אנו יוצרים קבוצה של טבלאות שירות לאחסון היסטוריית pg_stat_statements בשלב הראשוני וכדי להגדיר מדדים וניטור בעתיד
• במארח הניטור, אנו יוצרים קבוצה של סקריפטים של bash, כולל אלה ליצירת תקריות במערכת הכרטיסים.

שולחנות שירות

מלכתחילה, ERD מפושט באופן סכמטי, מה קרה בסופו של דבר:

תיאור קצר של הטבלאותנקודת סיום - מארח, נקודת חיבור למופע
מסד נתונים - אפשרויות מסד נתונים
pg_stat_history - טבלה היסטורית לאחסון תמונות מצב זמניות של תצוגת pg_stat_statements של מסד הנתונים היעד
metric_glossary - מילון מדדי ביצועים
metric_config - תצורה של מדדים בודדים
מטרי - מדד ספציפי לבקשה שנמצאת במעקב
metric_alert_history - היסטוריה של אזהרות ביצועים
log_query - טבלת שירות לאחסון רשומות מנותחות מקובץ היומן PostgreSQL שהורד מ-AWS
נקודת התחלה - פרמטרים של פרק הזמן המשמש כבסיס
מחסום - הגדרת מדדים לבדיקת מצב מסד הנתונים
checkpoint_alert_history - היסטוריית אזהרה של מדדי בדיקת מצב מסד הנתונים
pg_stat_db_queries - טבלת שירות של בקשות פעילות
יומן פעילות — טבלת שירות יומן פעילות
trap_oid - טבלת שירות תצורת מלכודת

שלב 1 - איסוף סטטיסטיקות ביצועים וקבל דוחות

טבלה משמשת לאחסון מידע סטטיסטי. pg_stat_history
pg_stat_history מבנה הטבלה

                                          עמודה "public.pg_stat_history" בטבלה | הקלד | משנה-----------------------+----------------------- --+---- -------------------------------- מזהה | מספר שלם | not null default nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | חותמת זמן ללא אזור זמן | database_id | מספר שלם | dbid | oid | userid | oid | queryid | bigint | שאילתה | טקסט | שיחות | bigint | total_time | דיוק כפול | min_time | דיוק כפול | max_time | דיוק כפול | זמן_ממוצע | דיוק כפול | stddev_time | דיוק כפול | שורות | bigint | shared_blks_hit | bigint | shared_blks_read | bigint | shared_blks_לכלוך | bigint | shared_blks_written | bigint | local_blks_hit | bigint | local_blks_read | bigint | local_blks_לכלוך | bigint | local_blks_written | bigint | temp_blks_read | bigint | temp_blks_written | bigint | blk_זמן_קריאה | דיוק כפול | blk_זמן_כתיבה | דיוק כפול | baseline_id | מספר שלם | אינדקסים: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) אילוצים של מפתח זר: "database_ID_ID_FERENID" (database_ID_ID_FERENID) ) ב-DELETE CASCADE

כפי שאתה יכול לראות, הטבלה היא רק נתוני תצוגה מצטברים pg_stat_statements במסד הנתונים היעד.

השימוש בטבלה זו הוא פשוט מאוד.

pg_stat_history ייצג את הסטטיסטיקה המצטברת של ביצוע שאילתה עבור כל שעה. בתחילת כל שעה, לאחר מילוי הטבלה, סטטיסטיקה pg_stat_statements לאפס עם pg_stat_statements_reset().
הערה: נתונים סטטיסטיים נאספים עבור בקשות באורך של יותר משנייה אחת.
אכלוס טבלת 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 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

זמן קלט/פלט

to_char(מרווח '1 millisecond' * ( 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 ms.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345| 1| .00000|00:00:06.156( 6156.352 MS.)| .16 | 8| 3644780286| 1| .00000|00:00:01.063( 1063.830 ms.)| .03

TOP10 SQL לפי זמן קלט/פלט כולל

בקשה

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 לפי זמן קלט/פלט כולל | #| queryid| שיחות| שיחות זמן קלט/פלט (ms)|db זמן קלט/פלט % +----+------+-----+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 ms.)| 31.06 ביוני | 2| 821760255| 2| .00001|00:08:27.099( 507099.036 MS.)| 30.78 | 3| 655729273| 1| .00000|00:05:02.209( 302209.137 ms.)| 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 MS.)| 2.38 | 6| 2194493487| 4| .00001|00:00:18.182( 18182.816 ms.)| 1.10 | 7| 1053044345| 1| .00000|00:00:16.611( 16611.722 ms.)| 1.01 | 8| 3644780286| 1| .00000|00:00:00.436( 436.205 MS.)| .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 לפי זמן ביצוע מקסימלי | #| תמונת מצב| snapshotID| queryid| max_time (ms) +----+-------------------+------+--------- --+---------------------------------------- | 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 על ידי קריאה/כתיבה של מאגר משותף

בקשה

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 לפי מאגר משותף קריאה/כתיבה | #| תמונת מצב| snapshotID| 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 HISTOGRAM | סה"כ שיחות : 33851920 | MIN TIME : 00:00:01.063 | זמן מקסימלי : 00:02:01.869 ------------------------------------------ ---------------------------- | משך דקות| משך מקסימום| שיחות +------------------------------------+------- ----------------------+---------- | 00:00:01.063( 1063.830 MS.) | 00:00:13.144( 13144.445 MS.) | 9 | 00:00:13.144( 13144.445 MS.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 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 תצלומים מסודרים לפי מספרי QueryPerSeconds -------------------------------------------- ------ ------------------------------------------------------------ -------------------------------------------------- | #| תמונת מצב| snapshotID| שיחות| סך dbtime| QPS | זמן קלט/פלט | זמן קלט/פלט % +-----+------------------+-----+------- ----+-----------------------------------+---------- -+-----------------------------------+----------- | 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 MS.)| .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 ms.)| 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 בשאילתות, אז נקבל את ההיסטוריה לבקשה נפרדת (על מנת לחסוך מקום, דוחות לבקשה נפרדת נשמטים).

אז, נתונים סטטיסטיים על ביצועי שאילתות זמינים ונאספים.
הושלם השלב הראשון "איסוף נתונים סטטיסטיים".

ניתן להמשיך לשלב השני – "הגדרת מדדי ביצועים".


אבל זה סיפור אחר.

להמשך ...

מקור: www.habr.com