PostgreSQL сұрауларының өнімділігін бақылау. 1 бөлім – есеп беру

Инженер – латын тілінен аударғанда – шабыттанған.
Инженер кез келген нәрсені істей алады. (c) Р. Дизель.
Эпиграфтар.

Немесе дерекқор әкімшісі неліктен өзінің бағдарламалау өткенін есте сақтау керектігі туралы әңгіме.

Алғы сөз

Барлық атаулар өзгертілді. Сәйкестіктер кездейсоқ. Материал тек автордың жеке пікірі.

Кепілдіктерден бас тарту: жоспарланған мақалалар сериясында пайдаланылған кестелер мен сценарийлердің егжей-тегжейлі және нақты сипаттамасы болмайды. Материалдарды бірден «ҚОЛДАҒЫСЫНДА» пайдалануға болмайды.
Біріншіден, материалдың көп мөлшеріне байланысты,
екіншіден, нақты тапсырыс берушінің өндірістік базасымен өткірлігіне байланысты.
Сондықтан мақалаларда тек жалпылама түрде идеялар мен сипаттамалар беріледі.
Мүмкін болашақта жүйе 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. Алдыңғы нұсқадағыдай - өңдеу жоқ. Бірақ инженер үшін минус, бірінші нұсқадан айырмашылығы, мұнда үнемі ИДБ құру керек. Және бұл уақытты алады. Бұл сіздің сүйікті адамыңыздың игілігіне жұмсалуы мүмкін. Өйткені сіз өзіңізге қамқорлық жасай алмайсыз, бәрі сізге қамқорлық жасайды.

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 көрінісінің уақытша суреттерін сақтауға арналған тарихи кесте
метрикалық_глоссарий - өнімділік көрсеткіштерінің сөздігі
метрикалық_конфигурация - жеке көрсеткіштерді конфигурациялау
метрикалық - бақыланатын сұрау үшін арнайы көрсеткіш
метрикалық_ескерту_тарихы - өнімділік туралы ескертулердің тарихы
журнал_сұрау - AWS жүйесінен жүктеп алынған PostgreSQL журнал файлынан талданған жазбаларды сақтауға арналған қызмет кестесі
базалық сызба - база ретінде пайдаланылатын уақыт кезеңінің параметрлері
бақылау-өткізу пункті - деректер қорының күйін тексеру үшін метриканың конфигурациясы
checkpoint_alert_history - дерекқор күйін тексеру метрикасының ескерту тарихы
pg_stat_db_queries — белсенді сұраныстардың сервистік кестесі
әрекет_журналы — әрекеттер журналының қызмет көрсету кестесі
trap_oid - тұзақ конфигурациясының қызмет көрсету кестесі

1-кезең – өнімділік статистикасын жинау және есептерді алу

Кесте статистикалық ақпаратты сақтау үшін қолданылады. pg_stat_history
pg_stat_history кесте құрылымы

                                          "public.pg_stat_history" кестесі Баған | түрі | Модификаторлар-------------------+--------------------- --+---- --------------------------------- id | бүтін | null емес әдепкі nextval('pg_stat_history_id_seq'::regclass) snapshot_timestamp | уақыт белдеуі жоқ уақыт белгісі | database_id | бүтін | dbid | oid | пайдаланушы идентификаторы | oid | queryid | bigint | сұрау | мәтін | қоңыраулар | bigint | жалпы_уақыт | қос дәлдік | мин_уақыт | қос дәлдік | максималды_уақыт | қос дәлдік | орташа_уақыт | қос дәлдік | stddev_time | қос дәлдік | жолдар | 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_kirted | 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" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Сыртқы кілт шектеулері (Ktabaseids_REG) ӘДЕБИЕТТЕР деректер қоры (id ) КАСКАДТЫ ЖОЮ БОЙЫНША

Көріп отырғаныңыздай, кесте тек жиынтық көрініс деректері болып табылады 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 ;

ДБ уақыты

таңбаға дейін(аралық '1 миллисекунд' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

Енгізу/шығару уақыты

таңбаға дейін(аралық '1 миллисекунд' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

Жалпы_уақыт бойынша TOP10 SQL

Сұрау

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| қоңыраулар| %| шақырады жалпы_уақыт (мс) | dbtime % +----+-----------+-----------+-----------+------ -------------------+---------- | 1| 821760255| 2| .00001|00:03:23.141( 203141.681 мс.)| 5.42 | 2| 4152624390| 2| .00001|00:03:13.929( 193929.215 мс.)| 5.17 | 3| 1484454471| 4| .00001|00:02:09.129( 129129.057 мс.)| 3.44 | 4| 655729273| 1| .00000|00:02:01.869( 121869.981 мс.)| 3.25 | 5| 2460318461| 1| .00000|00:01:33.113( 93113.835 мс.)| 2.48 | 6| 2194493487| 4| .00001|00:00:17.377( 17377.868 мс.)| .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

Сұрау

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

------------------------------------------------- --------------------------------------- | ЖАЛПЫ енгізу/шығару уақыты бойынша ТОП10 SQL | #| queryid| қоңыраулар| %| шақырады Енгізу/шығару уақыты (мс)|дб енгізу/шығару уақыты % +----+-----------+-----------+------ -----+--------------------------------+----------- -- | 1| 4152624390| 2| .00001|00:08:31.616( 511616.592 мс.)| 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 мс.)| 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

------------------------------------------------- ----------------------------------- | МЕКСІМДІ ОРЫНДАУ УАҚЫТЫ БОЙЫНША ТОП10 SQL | #| сурет| snapshotID| queryid| максимал_уақыт (мс) +----+------------------+-----------+--------- --+--------------------------------------- | 1| 05.04.2019 01:03| 4169| 655729273| 00:02:01.869(121869.981 мс.) | 2| 04.04.2019 17:00| 4153| 821760255| 00:01:41.570( 101570.841 мс.) | 3| 04.04.2019 16:00| 4146| 821760255| 00:01:41.570( 101570.841 мс.) | 4| 04.04.2019 16:00| 4144| 4152624390| 00:01:36.964( 96964.607 мс.) | 5| 04.04.2019 17:00| 4151| 4152624390| 00:01:36.964( 96964.607 мс.) | 6| 05.04.2019 10:00 | 4188| 1484454471| 00:01:33.452( 93452.150 мс.) | 7| 04.04.2019 17:00| 4150| 2460318461| 00:01:33.113(93113.835 мс.) | 8| 04.04.2019 15:00| 4140| 1484454471| 00:00:11.892( 11892.302 мс.) | 9| 04.04.2019 16:00| 4145| 1484454471| 00:00:11.892( 11892.302 мс.) | 10| 04.04.2019 17:00| 4152| 1484454471| 00:00:11.892( 11892.302 мс.)

SHARED буфері бойынша 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

------------------------------------------------- ----------------------------------- | ОҚУ/ЖАЗУ БОЙЫНША ТОП10 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 ------------------------------------------------- ------------------------------------------------

Максималды орындалу уақыты бойынша сұранысты бөлу гистограммасы

сұраулар

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 ) ;

|------------------------------------------------ --------------------------------------- | GISTOGRAM MAX_TIME | БАРЛЫҚ ҚОҢЫРАУЛАР: 33851920 | MIN УАҚЫТ: 00:00:01.063 | МЕКС. УАҚЫТ: 00:02:01.869 ---------------------------------- -------- -------------------------------------- | мин. ұзақтығы| максималды ұзақтығы| қоңыраулар +---------------------------------+------------- --------------------+---------- | 00:00:01.063( 1063.830 мс.) | 00:00:13.144( 13144.445 мс.) | 9 | 00:00:13.144( 13144.445 мс.) | 00:00:25.225(25225.060 мс.) | 0 | 00:00:25.225(25225.060 мс.) | 00:00:37.305( 37305.675 мс.) | 0 | 00:00:37.305( 37305.675 мс.) | 00:00:49.386( 49386.290 мс.) | 0 | 00:00:49.386( 49386.290 мс.) | 00:01:01.466( 61466.906 мс.) | 0 | 00:01:01.466( 61466.906 мс.) | 00:01:13.547( 73547.521 мс.) | 0 | 00:01:13.547( 73547.521 мс.) | 00:01:25.628( 85628.136 мс.) | 0 | 00:01:25.628( 85628.136 мс.) | 00:01:37.708( 97708.751 мс.) | 4 | 00:01:37.708( 97708.751 мс.) | 00:01:49.789( 109789.366 мс.) | 2 | 00:01:49.789( 109789.366 мс.) | 00:02:01.869(121869.981 мс.) | 0

Секундына сұрау бойынша ТОП10 сурет

сұраулар

--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

|------------------------------------------------ --------------------------------------- | QueryPerSeconds нөмірлері бойынша реттелген ТОП10 сурет -------------------------------------- ------ --------------------------------------------- ------ ------------------------------------------- | #| сурет| snapshotID| қоңыраулар| жалпы dbtime| QPS | енгізу/шығару уақыты | Енгізу/шығару уақыты % +-----+------------------+-----------+------- ----+---------------------------------+---------- -+---------------------------------+----------- | 1| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 мс.)| 1573.396| 00: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( 767834.052 мс.)| 108.324 | 3| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 мс.)| 979.267| 00:08:41.396( 521396.555 мс.)| 84.988 | 4| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 мс.)| 785.745| 00:00:00.249( 249.865 мс.)| .134 | 5| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 мс.)| 776.979| 00:00:01.441( 1441.386 мс.)| .732 | 6| 04.04.2019 14:00 | 4137| 2397326| 00:04:43.033( 283033.854 мс.)| 665.924| 00:00:00.024( 24.505 мс.)| .009 | 7| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 мс.)| 665.116| 00:00:12.025( 12025.895 мс.)| 4.126 | 8| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 мс.)| 659.179| 00:00:00.064( 64.261 мс.)| .024 | 9| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 мс.)| 609.332| 00:05:18.847( 318847.407 мс.)| 77.507 | 10| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 мс.)| 313.004| 00:00:01.319( 1319.676 мс.)| 1.666

QueryPerSeconds және енгізу/шығару уақыты арқылы сағаттық орындалу тарихы

Сұрау

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

Нәтиже

Көріп отырғаныңыздай, қарапайым құралдардың көмегімен сіз жұмыс жүктемесі мен дерекқордың күйі туралы көптеген пайдалы ақпаратты ала аласыз.

Ескерту:Сұраулардағы сұрау идентификаторын түзетсеңіз, біз бөлек сұраудың журналын аламыз (кеңістікті сақтау үшін бөлек сұрауға арналған есептер өткізілмейді).

Осылайша, сұрау өнімділігі туралы статистикалық деректер қол жетімді және жиналады.
Бірінші кезең «статистикалық мәліметтерді жинау» аяқталды.

Екінші кезеңге өтуге болады - «өнімділік көрсеткіштерін конфигурациялау».


Бірақ бұл мүлдем өзгеше оқиға.

Жалғасы бар…

Ақпарат көзі: www.habr.com