ã¯ããã« ããã«ã¡ã¯ãSREéšã«ãŒã決æžSREãããã¯ã®äŒè€ã§ããæ®æ®µã¯ZOZOTOWNã®ã«ãŒãæ±ºæžæ©èœã®ãªãã¬ã€ã¹ã»éçšã»ä¿å®ã«æºãã£ãŠããŸãããŸããããŒã ãè·šãã æšªææŽ»åãšããŠããŒã¿ããŒã¹ïŒä»¥äžDBïŒåšãã®éçšã»ä¿å®ã»æ§ç¯ã«é¢ãã£ãŠããŸãã ZOZOTOWNã§ã¯SQL Serverãäžå¿ãšããåçš®DBMSã皌åããŠããŸããæ¬èšäºã¯SQL Serverã®ããã©ãŒãã³ã¹ã調æ»ããäžã§é²ããå¯èŠåã«ã€ããŠã®åãçµã¿ãã玹ä»ããŸãã ã¯ããã« åŸæ¥ã®æ¹æ³ DMVéçšã®èª²é¡ Splunkã«ããããã·ã¥ããŒãå DMVã®å¯èŠåäŸ ã€ã³ã¹ããŒã«ãããŠããServerã®ãã°æ
å ±ã®éä¿¡ Datadogã®Database Monitoringã«ã€ã㊠Database Monitoringã䜿çšããŠæ¹åããäŸ CPU䜿çšçã®é«ãã¯ãšãªã®æ€åºãšæ¹å ããã©ãŒãã³ã¹ãæ¥ã«æªåããå Žåã®åå èª¿æ» ä»åŸã®å±æ æåŸã« åŸæ¥ã®æ¹æ³ 以åäžèšã®ããã¯ããã°ã§ç޹ä»ãããŠããã ããŸããããåŒç€Ÿã§ã¯ åç管çãã¥ãŒ ïŒDynamic Management View:以äžãDMVïŒãæ¡åŒµã€ãã³ãã®æ
å ±ããã®ã³ã°ããŠããŸãã techblog.zozo.com ãããã®æ
å ±ãçšããããšã§äœããã©ãã«ãèµ·ãã£ãéã«ã¯åŸè¿œãã§ããç¶æ³ãæŽããŠããŸãã äŸãã°ç¹å®ã®æé垯ã«ã¯ãšãªãæ»çããéã«ã¯æ¬¡ã®ã¯ãšãªãå®è¡ããããšã§ãæ»çããŠããã¯ãšãªã®è©³çŽ°ãªæ
å ±ã調ã¹ãããšãã§ããŸãã SELECT collect_date, count (*) AS [ã¯ãšãªã®æ»çæ°(å
šäœ)] FROM [dbo].[dm_exec_requests_dump_per_several_seconds_20230502] WHERE collect_date between @start_date and @end_date GROUP BY collect_date ORDER BY collect_date ; SELECT collect_date, wait_type, count (*) AS [ã¯ãšãªã®æ»çæ°(waitæ¯)] FROM [dbo].[dm_exec_requests_dump_per_several_seconds_20230502] WHERE collect_date between @start_date and @end_date GROUP BY collect_date, wait_type ORDER BY collect_date, wait_type ; SELECT collect_date, current_running_stmt, count (*) AS [ã¯ãšãªã®æ»çæ°(statementæ¯)] FROM [dbo].[dm_exec_requests_dump_per_several_seconds_20230502] WHERE collect_date between @start_date and @end_date GROUP BY collect_date, current_running_stmt ORDER BY collect_date, current_running_stmt ; ä»ã«ããã¹ãã¢ãããã·ãŒãžã£ïŒä»¥äžãã¹ãã¢ãïŒã®å®è¡çµ±èš sys.dm_exec_procedure_stats ãDumpããŠããŸãã ã¹ãã¢ãã«ä¿®æ£ãè¡ãªã£ãéã®ç£èŠããç¹å®ã®ã¹ãã¢ããçªç¶ããã©ãŒãã³ã¹å£åããéãªã©ã¯ãã¡ãã確èªããããšã§å
·äœçãªããã©ãŒãã³ã¹ã調ã¹ãããšãã§ããŸãã ãªãªãžãã«æ
å ±ã¯çޝç©å€ãšãªã£ãŠãããããLAG颿°ã䜿çšããŠ1ååã®æ
å ±ãšå·®åãåãããšã§1åéã®å®è¡åæ°ãå®è¡æéãåºåããŠããŸãã SELECT object_name, collect_date, execution_count AS ' å®è¡åæ°(环ç©å€) ' , -- ãªã»ãããããŠããããã£ãã·ã¥ã¢ãŠããããå¯èœæ§ãã CASE WHEN cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date) THEN CONVERT (nvarchar, execution_count - LAG(execution_count, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' å®è¡åæ°(1åéã®åèš) ' , CASE WHEN cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date) THEN CONVERT (nvarchar, total_worker_time - LAG(total_worker_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' CPUæé(1åéã®åèš) ' , CASE WHEN cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date) THEN CONVERT (nvarchar, total_elapsed_time - LAG(total_elapsed_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' å®è¡æé(1åéã®åèš) ' , CASE WHEN cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date) THEN CONVERT (nvarchar, total_logical_reads - LAG(total_logical_reads, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' è«çèªã¿èŸŒã¿é(1åéã®åèš) ' , CASE WHEN cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date) THEN CONVERT (nvarchar, total_logical_writes - LAG(total_logical_writes, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' è«çæžã蟌ã¿é(1åéã®åèš) ' FROM dbo.dm_exec_procedure_stats_dump WITH (NOLOCK) WHERE collect_date BETWEEN @start_date AND @end_date AND object_name = ' <çµã蟌ã¿ããã¹ãã¢ãã®åå> ' ORDER BY object_name, collect_date DMVéçšã®èª²é¡ ãã®ã³ã°ããDMVã¯äžèšã®ããã«ãã©ãã«ã·ã¥ãŒãã£ã³ã°æã«åœ¹ç«ãŠãããšãã§ããŸãããåšãã®ã¡ã³ããŒã«å¯Ÿå¿ããŠãããã«ããã£ãŠããŒãã«ã®é«ãã課é¡ãšããŠæããŠããŸããã ãŸããäžèšã®SQLãäœãã«ã¯DMVã«ã€ããŠçè§£ãæ·±ããå¿
èŠããããŸããSREã®å
šå¡ãDBã«ç²ŸéããŠããããã§ã¯ãªããæ®æ®µSQLãæžããªãã¡ã³ããŒãããã®ã§åŠç¿ã³ã¹ããå¿
èŠãšãªããŸãã ãŸããDBã«æ¥ç¶ããããã«ã¯ãã©ã€ããŒããªãããã¯ãŒã¯ãçµç±ããå¿
èŠããããªã©äžæéããããŸããæ¬çªç°å¢ã§çšŒåããŠããDBãµãŒããŒã«å¯ŸããŠSQLãå®è¡ããå¿
èŠãããã®ã§ãæ¥åžžçãªéçšã«ã¯åããŠããŸããã SQL Server 2016ããã¯ã¯ãšãªã®å®è¡å±¥æŽãä¿åããŠå¯èŠåã§ãã ã¯ãšãªã¹ãã¢ ã®æ©èœã远å ãããŸããããã©ãã«ã·ã¥ãŒãããäžã§æçšã§ã¯ãããŸãããäžèšãšåæ§ã®èª²é¡ã衚瀺é床ãªã©ãå«ããŠãéçšã®å©äŸ¿æ§ãé«ããã®ãšã¯èšããŸããã§ããã ãã®ããããã©ãŒãã³ã¹æªåã®åŸŽåããã£ããšããŠãåŸæã«åšããåé¡ãçºçããŠããã§ãªããšæ°ä»ãã«ããåŽé¢ããããŸããã Splunkã«ããããã·ã¥ããŒãå DMVã®èª²é¡ã解決ããããã«åãçµãã ã®ããŸãSplunkã«ããããã·ã¥ããŒãåã§ãã åŒç€Ÿã§ã¯ããŸããŸãªå Žæã§SplunkãæŽ»çšããŠããŸããéå»ã®ããã¯ããã°ã«ãSplunkã«é¢ããèšäºããããŸãã®ã§ãèå³ã®ããæ¹ã¯æ¯éã芧ãã ããã techblog.zozo.com ä»å䜿çšããã®ã¯ Splunk DB Connect ãšããã¢ããªã³ã§ããSplunk DB Connectã§ã¯ããŒã¿ããŒã¹ã®æ
å ±ãçŽæ¥ã€ã³ããŒãã§ããä»ã«ãã«ã¹ã¿ã ã¯ãšãªã宿çã«å®è¡ããŠçµæãSplunkã«éä¿¡ã§ããŸãã Splunk DB Connectèªäœã®ã€ã³ã¹ããŒã«æ¹æ³ãDBãšã®æ¥ç¶æ¹æ³ã«é¢ããŸããŠã¯ å
¬åŒããã¥ã¡ã³ã ããåç
§ãã ããã DMVã®å¯èŠåäŸ åé ã§DMVã®æŽ»çšäŸãšããŠã¹ãã¢ãã®ããã©ãŒãã³ã¹èª¿æ»ãæããŸãããããŸãã¯ãã¡ããSplunk DB Connectã§æ¯åå®è¡ããSplunkåŽãžèç©ãããããã«ããŸãããäœæããã®ãäžèšã¯ãšãªã§ãã ã¹ãã¢ãæ¯ã«ã3ååãã1ååã®éã«dumpãšããŠä¿åããã2ã€ã®ã¬ã³ãŒããååŸããå·®åãåºåããŠããŸããSQLå
ã®ã³ã¡ã³ãæã¯Splunk DB Connectã§èšå®ããéã«åäœåœ±é¿ãåºãããåé€ããŠããŸãã DECLARE @start_date DATETIME2 = dateadd(mi, -3 , GETDATE()); DECLARE @end_date DATETIME2 = dateadd(mi, -1 , GETDATE()); SELECT object_name AS ' stored_procedure ' , collect_date, exec_count_sum_1m, cpu_time_sum_1m, exec_time_sum_1m, logical_read_sum_1m, logical_write_sum_1m FROM ( SELECT object_name, collect_date, CASE WHEN (object_id = LAG(object_id, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) and (cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) THEN CONVERT (nvarchar, execution_count - LAG(execution_count, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' exec_count_sum_1m ' , CASE WHEN (object_id = LAG(object_id, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) and (cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) THEN CONVERT (nvarchar, total_worker_time - LAG(total_worker_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' cpu_time_sum_1m ' , CASE WHEN (object_id = LAG(object_id, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) and (cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) THEN CONVERT (nvarchar, total_elapsed_time - LAG(total_elapsed_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' exec_time_sum_1m ' , CASE WHEN (object_id = LAG(object_id, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) and (cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) THEN CONVERT (nvarchar, total_logical_reads - LAG(total_logical_reads, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' logical_read_sum_1m ' , CASE WHEN (object_id = LAG(object_id, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) and (cached_time = LAG(cached_time, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) THEN CONVERT (nvarchar, total_logical_writes - LAG(total_logical_writes, 1 , 0 ) OVER ( ORDER BY object_name, collect_date)) ELSE ' - ' END AS ' logical_write_sum_1m ' FROM dbo.dm_exec_procedure_stats_dump WITH (NOLOCK) WHERE collect_date BETWEEN @start_date AND @end_date AND object_name not like ' sp[_]% ' AND exists ( select * from sys.objects ob with (nolock) where ob.object_id = object_id(object_name) and is_ms_shipped = 0 ) ) AS sample WHERE NOT exec_count_sum_1m= ' - ' ORDER by collect_date, object_name äžèšã®ã¯ãšãªã§åéããæ
å ±ãæ¬¡ã®ãããªãµãŒãæã§å¯èŠåã§ããŸãã index = " heavy_forwarder_db " sourcetype= " dbconnect " host=XXX source= " XXX-procedure-stats " | timechart span=1m useother= false limit= 20 sum (exec_count_sum_1m) by stored_procedure åéã«äœ¿çšããæéãšSplunkãåä¿¡ããæéã«å·®ãããã®ã§è¡šç€ºäžå°ãã®ããã¯çºçããŠããŸããŸããã蚱容ç¯å²ãšããŠããŸãã ãŸããäžèšã®ãµãŒãæã§ã¯äžäœ20ä»¶ã®æ
å ±ã衚瀺ãããŠããŸãããå¥éããã¹ãå
¥åæ¬ãèšããŠç¹å®ã®ã¹ãã¢ãã远ããããã·ã¥ããŒããæäŸããŠããŸãã ã€ã³ã¹ããŒã«ãããŠããServerã®ãã°æ
å ±ã®éä¿¡ Splunk DB Connectãšã¯å¥ã«ããµãŒããŒã«Splunk Universal Forwarderãã€ã³ã¹ããŒã«ããããšã§ããã©ãŒãã³ã¹ã¢ãã¿ãã€ãã³ããã°ãéä¿¡ã§ããŸãã åäžããã·ã¥ããŒãå
ã§ã€ãã³ããã°ãšã¯ãšãªã®ããã©ãŒãã³ã¹æ
å ±ã衚瀺ããããšã§äž¡è
ã®çžé¢é¢ä¿ãçµã³ã€ããããšãã§ãã以äžã®åãåãã容æã«ãªããŸãã ãµãŒããŒèªäœã®åé¡ãªã®ã ãµãŒããŒäžã§åããŠããSQL Serverã®åé¡ãªã®ã SQL Serveräžã§å®è¡ãããç¹å®ã®ã¯ãšãªã®åé¡ãªã®ã Splunkã«ããããã·ã¥ããŒãåãè¡ãããšã§ãDMVãSQLã«ç²ŸéããŠããªãã¡ã³ããŒããã©ãã«ã·ã¥ãŒãã®ããã«å¿
èŠãªæ
å ±ã容æã«ååŸã§ããããã«ãªããŸããããŸããã©ã€ããŒããªãããã¯ãŒã¯ãçµç±ããŠæ¬çªç°å¢ã®DBãµãŒããŒã«SQLãå®è¡ããå¿
èŠããªããªããéçšã®å©äŸ¿æ§ãšå®å
šæ§ã®åäžãå®çŸããŸããã Datadogã®Database Monitoringã«ã€ã㊠åŒç€Ÿã§ã¯DatadogãæŽ»çšããŠããŸãã techblog.zozo.com 2022幎8æãDatadogã®Database Monitoringæ©èœãSQL Serverã«å¯ŸããŠããµããŒããããããã«ãªã£ãããããªã³ãã¬ãã¹ç°å¢ã®äž»èŠDBãžã®å°å
¥ãé²ããŸããã Datadog Agentã®ã€ã³ã¹ããŒã«æ¹æ³ã«ã€ããŠã¯å
¬åŒãµã€ãããåç
§ãã ããã docs.datadoghq.com ããŒã¿åéã¯ä»¥äžã®çç±ããKubernetesã¯ã©ã¹ã¿äžã«Datadog Agentã®podãç«ãŠãŠDBã«ã¢ã¯ã»ã¹ããæ¹æ³ãæ¡çšããŸããã å
ã
RDSã«æ¥ç¶ããŠã¡ããªã¯ã¹ãååŸããããã®é圢ãçšæããŠããããš äœãåé¡ããã£ãå Žåã«Agentã®ã¢ã³ã€ã³ã¹ããŒã«ãäžèŠã§ããããš ãªã³ãã¬ã®å Žåã¯çŽæ¥Agentãã€ã³ã¹ããŒã«ããæ¹ãOSã®ã¡ããªã¯ã¹ãªã©ååŸã§ããæ
å ±ã¯å¢ãããå¥ææ®µã§åéæžã¿ã§ããããããŸã§éèŠèŠããªãããš Database Monitoringã䜿çšããŠæ¹åããäŸ CPU䜿çšçã®é«ãã¯ãšãªã®æ€åºãšæ¹å Database Monitoringãæå¹åããããšã§äœ¿çšã§ããããã«ãªã ã¯ãšãªã¡ããªã¯ã¹ãã¥ãŒ ã§ã¯ã¯ãšãªæ¯ã®ãªãœãŒã¹äœ¿çšçãèŠãããšãã§ããŸãã æ¬¡ã®ç»åã¯WORKER TIME(CPU)é ã§äžŠã³æ¿ããç»åã§ãããç¹å®ã®ã¯ãšãªã§CPUãå€ã䜿ã£ãŠããããšãããããŸãã ã¯ãšãªã®è©³çްã確èªããçµæãæ¬¡ã®ç»åãšãªããŸãããã°ã©ãããå®è¡æéãå®å®ããŠããªãããšãããããŸãããããããã®æé垯ã§èšé²ãããŠããå®è¡èšç»ãèŠããšãããé
ãæé垯ã®ã¿ç¹å®ã®ããŒãã«ã§ã¹ãã£ã³ãçºçããŠããŸããã åé¡ã®ã¯ãšãªã®å¹³åã¬ã€ãã³ã· ããã©ãŒãã³ã¹ãæªãæã®å®è¡èšç» ããã©ãŒãã³ã¹ãè¯ãæã®å®è¡èšç» ã¹ãã£ã³ãããã©ã³ã¯æãã§ããªãããFORCESEEKãã³ãã远å ãããšããã¯ãšãªããã©ãŒãã³ã¹ãæ¹åããDBå
šäœã®CPU䜿çšçã®æ¹åã確èªã§ããŸããã Database Monitoringã®å°å
¥ã«ãããèŠéãåºãã£ãããšã§ä»ãŸã§åé¡èŠããŠããªãã£ãéšåã«å¯ŸããŠãå
åãããŠä¿®æ£ã§ããããã«ãªããŸãããæåã§å®æœããŠããããã©ãŒãã³ã¹æ
å ±ã®åéã ãã£ãã·ã¥ããã®å®è¡èšç»ã®åé ãã¯ãšãªã¡ããªã¯ã¹ãã¥ãŒããé²èЧå¯èœãªã®ã§äžèŠãšãªããäœæ¥ã®å¹çåãžãšç¹ããããŸããã ããã©ãŒãã³ã¹ãæ¥ã«æªåããå Žåã®åå èª¿æ» å®è¡èšç»ã®å€åãç¹å®ã®è² è·ãããã£ãå Žåãªã©ããã©ãŒãã³ã¹ãæ¥æ¿ã«æªåããããšã床ã
ãããŸããããã®å Žåã¯DMVãå©çšããŠæ·±æããè¡ãªã£ãŠããŸããããæåã®ã¢ã¯ã·ã§ã³ãšããŠDatabase Monitoringã確èªãããšããææ®µãåããããã«ãªããŸããã ç»åã¯ããŒããŠã§ã¢èµ·å ã®ãã©ãã«ãçºçãããšã©ãŒãå€çºããŠããŸã£ãéã®ãã®ã§ãã WriteLogã®Waitã倧éã«çºçããŠããŸã£ãŠãããäœããã®èŠå ã§ãã©ã³ã¶ã¯ã·ã§ã³æžã蟌ã¿ãåŸ
ããããŠããããšãããããŸãã ç·æ¥æã«ã¯äžå»ãæ©ãåå ç¹å®ãæ±ãããããããç°¡åã«ç¢ºèªãã§ãããŸã察å¿ã§ããã¡ã³ããŒãå¢ãããããšã¯éåžžã«å¬ãããã€ã³ãã§ãã ä»ãŸã§ã ãšåé¡ã«å¿ããŠæ§ã
ãªDMVã䜿ãåããå¿
èŠãããã察å¿ã¡ã³ããŒã«ã¯DMVã«å¯Ÿããç¥èŠãå¿
èŠã§ãããDatabase MonitoringãæŽ»çšããããšã§æ§ã
ãªè§åºŠããåæèª¿æ»ãã§ããå±äººåã®åæžã«ç¹ããããŸããã ä»åŸã®å±æ 以äžã®ããã«ãSplunkãšDatadogãçšããŠDBã®ããã©ãŒãã³ã¹ãå¯èŠåããåãçµã¿ãé²ããŸããã çŸç¶ã®äž¡è
ã®äœ¿ãåããšããŠã¯ãDatadogã«ãã£ãŠèªåã§ããã©ãŒãã³ã¹ãååŸããã«ããŒããããŠããªãç¯å²ãSplunkã®ããã·ã¥ããŒãã«ãŸãšããŠããŸãã ãã ããã®çç±ã¯æç³»åçãªåŽé¢ã匷ããäŸãã°Datadogã§ãã«ã¹ã¿ã ã¯ãšãªã䜿çšããã¡ããªã¯ã¹åã¯å®çŸå¯èœã§ããããããããDatadogåŽã«å¯ããŠããããšãå¯èœã§ãã äžæ¹ã§Splunkã¯èªç€Ÿã§ã¯DBã«é¢é£ããæ§ã
ãªãªãœãŒã¹ã®ãã°ãèç©ãããŠããŠãããããçžé¢çã«æ
å ±ãåŸããããšããã¡ãªãããååšããŸãã äž¡è
ã®ã¡ãªãããæŽ»ããã€ã€ãããã«æé©ãªDBããã©ãŒãã³ã¹ã®å¯èŠåæŠç¥ãä»åŸèããŠãããããšæããŸãã ãŸããçŸç¶äžéšã®DBã«ãã察å¿ã§ããŠããªããããä»ã®DBã«å¯ŸããŠãåæ§ã®å¯èŠåãé²ããŠããããã§ãã æåŸã« ZOZOã§ã¯ãäžç·ã«ãµãŒãã¹ãäœãäžããŠããã仲éãåéäžã§ãããèå³ã®ããæ¹ã¯ã以äžã®ãªã³ã¯ãããã²ãå¿åãã ããïŒ corp.zozo.com