ELB7层监控中心

前提条件

• 已采集ELB日志，详情请参见ELB接入。
• 日志配置结构化，详情请参见结构化配置。

背景信息

弹性负载均衡（Elastic Load Balance，简称ELB）是将访问流量根据分配策略分发到后端多台服务器的流量分发控制服务。弹性负载均衡可以通过流量分发扩展应用系统对外的服务能力，同时通过消除单点故障提升应用系统的可用性，支持查看和分析对七层负载均衡HTTP和HTTPS进行请求的详细访问日志记录，包括请求时间、客户端IP地址、请求路径和服务器响应等。

分析网站访问情况

1. 登录云日志服务控制台，在左侧导航栏中选择“日志管理”。
2. 在“日志应用”模块中，单击“ELB日志中心”，选择“进入仪表盘”。
3. 在仪表盘模板下方，选择“ELB仪表盘模板>ELB7层监控中心”仪表盘，查看图表详情。

ELB7层监控中心仪表盘中的过滤器说明如下所示：

• 获取所有负载均衡器，所关联的查询分析语句如下所示：

  select distinct(lb_name)

• 获取所有客户端IP，所关联的查询分析语句如下所示：

  select distinct(remote_addr)

• 获取所有后端服务器IP，所关联的查询分析语句如下所示：

  select distinct(upstream_addr)

• 获取所有弹性IP地址，所关联的查询分析语句如下所示：

  select distinct(eip_address) 

ELB7层监控中心仪表盘中的重要图表说明如下所示：

在搜索框上方选择时间范围，建议选择相对时间30分钟以上的查询时间（即日志上报时间）。由于日志的业务时间（字段名称为time_iso8601）和日志上报时间的差异，在查询时间范围限制下根据业务时间排序的折线图两端的数据不具有参考性。

• 相对时间：表示查询距离当前时间1分钟、5分钟、15分钟等时间区间的日志数据。例如当前时间为19:20:31，设置相对时间1小时，表示查询18:20:31~19:20:31的日志数据。
• 整点时间：表示查询最近整点1分钟、15分钟等时间区间的日志数据。例如当前时间为19:20:31，设置整点时间1小时，表示查询18:00:00~19:00:00的日志数据。
• 自定义：表示查询指定时间范围的日志数据。

• 访问量PV图展示访问量PV的变化情况，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,PV FROM (select  TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') AS _time_ , count(1) as PV from log group by _time_ order by _time_ )

• 请求成功率图展示请求成功率的变化情况，所关联的查询分析语句如下所示：

  select ROUND(sum(case when status < 400 then 1 else 0 end) * 100.0 / count(1),2) as cnt

• 4XX请求数图展示4XX请求数的变化情况，所关联的查询分析语句如下所示：

  SELECT COUNT(1) as cnt WHERE "status" >= 400 and "status" < 500

• 499请求数图展示499请求数的变化情况，所关联的查询分析语句如下所示：

  SELECT COUNT(1) as cnt WHERE "status" = 499

• 平均延迟图展示平均延迟的变化情况，所关联的查询分析语句如下所示：

  select round(avg(request_time) * 1000, 3) as cnt

• 404请求数图展示404请求数的变化情况，所关联的查询分析语句如下所示：

  SELECT COUNT(1) as cnt WHERE "status" = 404

• 504请求数图展示504请求数的变化情况，所关联的查询分析语句如下所示：

  SELECT COUNT(1) as cnt WHERE "status" = 504

• 5XX请求数图展示5XX请求数的变化情况，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,cnt FROM ( SELECT TIME_CEIL ( TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'), 'PT300S' ) AS _time_, count( 1 ) AS cnt FROM log where "status" >= 500 GROUP BY _time_ )

• 状态码分布图展示状态码分布的变化情况，所关联的查询分析语句如下所示：

  SELECT status, COUNT(1) AS rm GROUP BY status

• 访问量UV图展示访问量UV的变化情况，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,UV FROM (select TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') AS _time_ , APPROX_COUNT_DISTINCT(remote_addr) as UV  from log group by _time_)

• 流量图展示流量的变化情况，所关联的查询分析语句如下所示：

  select TIME_FORMAT(_time_,'yyyy-MM-dd HH:mm:ss','+08:00') AS _time_,round( CASE WHEN "入流量" > 0 THEN "入流量" ELSE 0 END, 2 ) AS "入流量",round( CASE WHEN "出流量" > 0 THEN "出流量" ELSE 0 END, 2 ) AS "出流量" FROM (SELECT TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') AS _time_,sum(request_length) / 1024.0 AS "入流量",sum(bytes_sent) / 1024.0 AS "出流量" group by  _time_)

• 访问失败率图展示访问失败率的变化情况，所关联的查询分析语句如下所示：

  SELECT TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,round( CASE WHEN "失败率" > 0 THEN "失败率" ELSE 0 END, 2 ) AS "失败率",round( CASE WHEN "5XX比例" > 0 THEN "5XX比例" ELSE 0 END, 2 ) AS "5XX比例" from (select TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') AS _time_,sum(case when status >= 400 then 1 else 0 end) * 100.0 / count(1) as '失败率' , sum(case when status >=500 THEN 1 ELSE 0 END)*100.0/COUNT(1) as '5XX比例' group by  _time_)

• 延迟图展示延迟的变化情况，所关联的查询分析语句如下所示：

  select TIME_FORMAT( _time_, 'yyyy-MM-dd HH:mm:ss', '+08:00' ) as _time_,round( CASE WHEN "平均" > 0 THEN "平均" ELSE 0 END, 2 ) AS "平均",round( CASE WHEN "P50" > 0 THEN "P50" ELSE 0 END, 2 ) AS "P50",round( CASE WHEN "P90" > 0 THEN "P90" ELSE 0 END, 2 ) AS "P90",round( CASE WHEN "P99" > 0 THEN "P99" ELSE 0 END, 2 ) AS "P99",round( CASE WHEN "P9999" > 0 THEN "P9999" ELSE 0 END, 2 ) AS "P9999" from (select TIME_CEIL(TIME_PARSE(SUBSTRING(time_iso8601, 2, 25) ,'yyyy-MM-dd''T''HH:mm:ssZZ'),'PT600S') as _time_,avg(request_time) * 1000 as "平均", APPROX_QUANTILE_DS("request_time", 0.50)*1000 as "P50", APPROX_QUANTILE_DS("request_time", 0.90)*1000 as "P90" ,APPROX_QUANTILE_DS("request_time", 0.99)*1000 as 'P99',APPROX_QUANTILE_DS("request_time", 0.9999)*1000 as 'P9999' group by  _time_)

• Host请求TOP图展示Host请求TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT host, pv, uv, round( CASE WHEN "访问成功率(%)" > 0 THEN "访问成功率(%)" ELSE 0 END, 2 ) AS "访问成功率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "入流量(KB)" > 0 THEN "入流量(KB)" ELSE 0 END, 3 ) AS "入流量(KB)", round( CASE WHEN "出流量(KB)" > 0 THEN "出流量(KB)" ELSE 0 END, 3 ) AS "出流量(KB)"  FROM ( SELECT "host", count( 1 ) AS pv, APPROX_COUNT_DISTINCT ( http_x_forwarded_for ) AS uv, sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问成功率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", sum( request_length ) / 1024.0 AS "入流量(KB)", sum( body_bytes_sent ) / 1024.0 AS "出流量(KB)"  WHERE "host" != ''  GROUP BY "host" ) ORDER BY pv DESC

• Host延迟TOP图展示Host延迟TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT "host", pv, round( CASE WHEN "访问成功率(%)" > 0 THEN "访问成功率(%)" ELSE 0 END, 2 ) AS "访问成功率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "P90延迟(ms)" > 0 THEN "P90延迟(ms)" ELSE 0 END, 3 ) AS "P90延迟(ms)", round( CASE WHEN "P99延迟(ms)" > 0 THEN "P99延迟(ms)" ELSE 0 END, 3 ) AS "P99延迟(ms)" FROM ( SELECT "host", count( 1 ) AS pv, sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问成功率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)",APPROX_QUANTILE_DS(request_time, 0.9) * 1000 AS "P90延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.99) * 1000 AS "P99延迟(ms)" WHERE "host" != ''  GROUP BY "host" ) ORDER BY "平均延迟(ms)" desc

• Host失败率TOP图展示Host失败率TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT "host", pv,round( CASE WHEN "访问失败率(%)" > 0 THEN "访问失败率(%)" ELSE 0 END, 2 ) AS "访问失败率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "P90延迟(ms)" > 0 THEN "P90延迟(ms)" ELSE 0 END, 3 ) AS "P90延迟(ms)", round( CASE WHEN "P99延迟(ms)" > 0 THEN "P99延迟(ms)" ELSE 0 END, 3 ) AS "P99延迟(ms)"  FROM ( SELECT "host", count( 1 ) AS pv, sum( CASE WHEN "status" >= 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问失败率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.9) * 1000 AS "P90延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.99) * 1000 AS "P99延迟(ms)" WHERE "host" != ''  GROUP BY "host"  ) ORDER BY "访问失败率(%)" desc

• URL请求TOP图展示URL请求TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT router_request_uri, pv,uv, round( CASE WHEN "访问成功率(%)" > 0 THEN "访问成功率(%)" ELSE 0 END, 2 ) AS "访问成功率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "入流量(KB)" > 0 THEN "入流量(KB)" ELSE 0 END, 3 ) AS "入流量(KB)", round( CASE WHEN "出流量(KB)" > 0 THEN "出流量(KB)" ELSE 0 END, 3 ) AS "出流量(KB)"  FROM ( SELECT router_request_uri, count( 1 ) AS pv, APPROX_COUNT_DISTINCT ( http_x_forwarded_for ) AS uv, sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问成功率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", sum( request_length ) / 1024.0 AS "入流量(KB)", sum( body_bytes_sent ) / 1024.0 AS "出流量(KB)"  WHERE "host" != ''  GROUP BY router_request_uri  ) ORDER BY pv desc

• URL延迟TOP图展示URL延迟TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT router_request_uri, pv,round( CASE WHEN "访问成功率(%)" > 0 THEN "访问成功率(%)" ELSE 0 END, 2 ) AS "访问成功率(%)",round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)",round( CASE WHEN "P90延迟(ms)" > 0 THEN "P90延迟(ms)" ELSE 0 END, 3 ) AS "P90延迟(ms)",round( CASE WHEN "P99延迟(ms)" > 0 THEN "P99延迟(ms)" ELSE 0 END, 3 ) AS "P99延迟(ms)" FROM (SELECT router_request_uri, count( 1 ) AS pv, sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问成功率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.9) * 1000 AS "P90延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.99) * 1000 AS "P99延迟(ms)" WHERE "host" != ''  GROUP BY router_request_uri  ) ORDER BY "平均延迟(ms)" desc

• URL失败率TOP图展示URL失败率TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT router_request_uri, pv, round( CASE WHEN "访问失败率(%)" > 0 THEN "访问失败率(%)" ELSE 0 END, 2 ) AS "访问失败率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "P90延迟(ms)" > 0 THEN "P90延迟(ms)" ELSE 0 END, 3 ) AS "P90延迟(ms)", round( CASE WHEN "P99延迟(ms)" > 0 THEN "P99延迟(ms)" ELSE 0 END, 3 ) AS "P99延迟(ms)" FROM( SELECT router_request_uri, count( 1 ) AS pv, sum( CASE WHEN "status" >= 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问失败率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.9) * 1000 AS "P90延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.99) * 1000 AS "P99延迟(ms)" WHERE "host" != '' GROUP BY router_request_uri) ORDER BY "访问失败率(%)" desc

• 后端请求TOP图展示后端请求TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT addr, pv, uv, round( CASE WHEN "访问成功率(%)" > 0 THEN "访问成功率(%)" ELSE 0 END, 2 ) AS "访问成功率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "入流量(KB)" > 0 THEN "入流量(KB)" ELSE 0 END, 3 ) AS "入流量(KB)", round( CASE WHEN "出流量(KB)" > 0 THEN "出流量(KB)" ELSE 0 END, 3 ) AS "出流量(KB)"  FROM ( SELECT upstream_addr_priv as addr, count( 1 ) AS pv, APPROX_COUNT_DISTINCT ( http_x_forwarded_for ) AS uv, sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问成功率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", sum( request_length ) / 1024.0 AS "入流量(KB)", sum( body_bytes_sent ) / 1024.0 AS "出流量(KB)"  WHERE "host" != ''  GROUP BY addr  having length(upstream_addr_priv) > 2) ORDER BY "pv" desc

• 后端延迟TOP图展示后端延迟TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT addr,pv,round( CASE WHEN "访问成功率(%)" > 0 THEN "访问成功率(%)" ELSE 0 END, 2 ) AS "访问成功率(%)",round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)",round( CASE WHEN "P90延迟(ms)" > 0 THEN "P90延迟(ms)" ELSE 0 END, 3 ) AS "P90延迟(ms)",round( CASE WHEN "P99延迟(ms)" > 0 THEN "P99延迟(ms)" ELSE 0 END, 3 ) AS "P99延迟(ms)" FROM (SELECT upstream_addr_priv as addr,count( 1 ) AS pv,sum( CASE WHEN "status" < 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问成功率(%)",avg( request_time ) * 1000 AS "平均延迟(ms)",APPROX_QUANTILE_DS(request_time, 0.9) * 1000 AS "P90延迟(ms)",APPROX_QUANTILE_DS(request_time, 0.99) * 1000 AS "P99延迟(ms)" WHERE "host" != '' GROUP BY addr having length(upstream_addr_priv) > 2) ORDER BY "平均延迟(ms)" desc

• 后端失败率TOP图展示后端失败率TOP的变化情况，所关联的查询分析语句如下所示：

  SELECT addr, pv, round( CASE WHEN "访问失败率(%)" > 0 THEN "访问失败率(%)" ELSE 0 END, 2 ) AS "访问失败率(%)", round( CASE WHEN "平均延迟(ms)" > 0 THEN "平均延迟(ms)" ELSE 0 END, 3 ) AS "平均延迟(ms)", round( CASE WHEN "P90延迟(ms)" > 0 THEN "P90延迟(ms)" ELSE 0 END, 3 ) AS "P90延迟(ms)", round( CASE WHEN "P99延迟(ms)" > 0 THEN "P99延迟(ms)" ELSE 0 END, 3 ) AS "P99延迟(ms)"  FROM ( SELECT upstream_addr_priv as addr, count( 1 ) AS pv, sum( CASE WHEN "status" >= 400 THEN 1 ELSE 0 END ) * 100.0 / count( 1 ) AS "访问失败率(%)", avg( request_time ) * 1000 AS "平均延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.9) * 1000 AS "P90延迟(ms)", APPROX_QUANTILE_DS(request_time, 0.99) * 1000 AS "P99延迟(ms)" WHERE "host" != ''  GROUP BY addr having length(upstream_addr_priv) > 2) ORDER BY "访问失败率(%)" desc