MSSQL · 应用案例 · 基于内存优化表的列存储索引分析Web Access Log

2021-11-11 14:22:12

问题引入

在日常的网站运维工作中，我们需要对网站客户端访问情况做统计、汇总、分析和报表展示，以数据来全面掌控网站运营和访问情况。当不可预知的意外情况发生时，我们可以快速发现问题以及采取相应的措施。比如：当网站受到黑客攻击时的流量陡增，又或者是网站某个资源发生意外抛异常等情况。
在提供Web服务的服务器上，比如IIS、Apache都存在访问日志记录，这篇是文章是以SQL Server 2016基于内存优化表的列存储索引来分析Apache Web Access Log为例，讲解分析网站访问情况，因此，我们需要解决以下几个问题：

Apache Web Access Log格式介绍
列存储索引表结构的设计
Apache Web Access Log导入到列存储索引表
网站访问流量统计
客户端主机访问的分布情况
客户端主机访问的资源统计
异常URI访问统计
Response Code分布情况

日志格式介绍

在设计基于内存优化表的列存储索引表结构之前，我们首先必须要对Apache Web Access Log服务器普通日志格式了解得非常清楚，以日志结构来建立我们SQL Server 2016的列存储索引表结构，在此，仅以一条日志记录格式来分析：

## 通用日志格式
LogFormat "%h %l %u %t \"%r\" %>s %b" common

## 其中一条日志举例
64.242.88.10 - - [07/Mar/2004:16:47:12 -0800] "GET /robots.txt HTTP/1.1" 200 68
......

其中：

%h：发送请求到服务器的客户端主机（IP或者是主机名），本例中的64.242.88.10；
%l：确定访问用户的标识（因为不可靠，通常不会使用，用中划线来填充），本例中的第一个中划线；
%u：由HTTP认证确定的用户名称，本例中的第二个中划线；
%t：服务器端收到客户端请求的时间点，格式为：[day/month/year:hour:minute:second zone]，本例中的[07/Mar/2004:16:47:12 -0800]；
%r：置于双引号之间的请求详细信息，包括三部分：请求方法、请求的资源和客户端协议及版本。本例中的”GET /robots.txt HTTP/1.1”；
%>s：返回的Response Code，比如本例中200表示访问成功；
%b：返回给客户端的对象大小，不包含HTTP Header，单位为byte，本例中获取了68 byte资源。

基于内存优化表的列存储索引表结构设计

基于以上对Apache Web Access Log格式的分析，我们可以建立格式对等的基于内存优化表的列存储索引表。这种类型的表数据会按列压缩存放在内存中，可以大大减少OLAP查询对IOPS的消耗，提高OLAP分析查询的性能。其表结构如下所示：

USE CCSI2016
GO
DROP TABLE IF EXISTS dbo.WebAccessLOG
GO

CREATE TABLE dbo.WebAccessLOG (
[LogId] BIGINT IDENTITY(1,1) NOT NULL,
[RemoteHost] [varchar](100) NULL,
[UserIdentity] varchar(10) NULL,
[UserName] varchar(10) NULL,
[RequestTime] varchar(50) NULL,
[Timezone] varchar(10) NULL,
[Action] varchar(10) NULL,
[URI] VARCHAR(1000) NULL,
[Version] VARCHAR(20) NULL,
[StatusCode] varchar(5) NULL,
[Size_Byte] INT NULL,
[Indate] DATETIME NOT NULL CONSTRAINT DF_Indate DEFAULT(GETDATE()),
CONSTRAINT PK_WebAccessLOG PRIMARY KEY NONCLUSTERED HASH ([LogId]) WITH (BUCKET_COUNT = 10000000)
)WITH(MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) ;
GO

ALTER TABLE dbo.WebAccessLOG
	ADD INDEX CCSI_WebAccessLOG CLUSTERED COLUMNSTORE
;
GO

在建表过程中，我们设置MEMORY_OPTIMIZED = ON，表示该表为内存优化表，此类表数据会存放在内存中；DURABILITY = SCHEMA_AND_DATA表示，我们需要持久化表结构和数据到磁盘上，以防止服务意外终止而导致的数据丢失；最后一句ALTER TABLE ADD INDEX CLUSTERED COLUMNSTORE表示为该内存优化表建立聚集列存储索引，此类型表数据会被压缩存放在内存中。

导入日志信息到列存储索引表

我们完成了基于内存优化表的列存储索引表设计以后，接下来，我们需要将Apache Web Access Log文件导入到该表中。由于Log文件不带表头，第一行就直接是数据；每行之间的信息以空格分割；行与行之间以换行分割，所以，我们可以使用BULK INSERT的方式将Log文件导入列存储索引表。方法如下：

USE CCSI2016
GO
-- Create view base on log table
DROP VIEW IF EXISTS dbo.[UV_WebAccessLOG]
GO
CREATE VIEW [dbo].[UV_WebAccessLOG]
AS
SELECT [RemoteHost]
	,[UserIdentity]
	,[UserName]
	,[RequestTime]
	,[Timezone]
	,[Action]
	,[URI]
	,[Version]
	,[StatusCode]
	,[Size_Byte]
FROM CCSI2016.dbo.WebAccessLOG WITH(NOLOCK)
GO

-- BULK INSERT Log into view
BULK INSERT dbo.[UV_WebAccessLOG]
FROM 'C:\Temp\access_log'
WITH (
 FIRSTROW = 1,
 FIELDTERMINATOR = ' ',
 ROWTERMINATOR = '\n'
)

-- Init data
;WITH DATA
AS(
	SELECT TOP (1545) LogId
	FROM CCSI2016.dbo.WebAccessLOG AS A
	ORDER BY Indate DESC
)
UPDATE TOP(1545) A
SET RequestTime = REPLACE(RequestTime, '[', '')
FROM CCSI2016.dbo.WebAccessLOG AS A
WHERE LogId IN(SELECT LogId FROM DATA)

代码解释：由于列存储索引表增加了自增列LogId和时间字段Indate，我们无法直接将数据BULK INSERT到正式表，需要建立视图dbo.[UV_WebAccessLOG]来作为中间桥梁；数据导入完毕后，由于RequestTime字段含有中括号左半部分，我们需要将中括号刷洗掉。至此，列存储索引表创建完毕，访问Log日志也已经导入，接下来就是详细的统计分析了。

网站流量统计分析

网站的流量统计是以时间为单位统计所有客户端访问网站的点击数量和以此获取到的资源总流量大小。时间单位可以小到每秒，大到每小时或者每天为单位来统计，这个统计值可以数据化网站的访问流量，随时监控网站是否有意外发生，或者是意外的突发访问，比如：被黑客攻击导致流量突然增大。在此，仅以天为时间单位，描述网站流量统计分析的方法。

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_LoadingAnalysis
GO
CREATE PROCEDURE dbo.UP_LoadingAnalysis 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	SELECT
		Day = CONVERT(CHAR(10), RequestTime, 120) 
		, minSize = CAST(MIN(Size_Byte) / 1024. AS DECIMAL(8, 2))
		, maxSize = CAST(MAX(Size_Byte) / 1024. AS DECIMAL(8, 2))
		, avgSize = CAST(AVG(Size_Byte) / 1024. AS DECIMAL(8, 2))
		, sumSize = CAST(SUM(Size_Byte) / 1024. AS DECIMAL(8, 2))
		, NoOfHits = COUNT(1)
	FROM dbo.WebAccessLOG
	GROUP BY CONVERT(CHAR(10), RequestTime, 120)
	ORDER BY 1 ASC
END
GO

单独执行该存储过程，返回的结果如下图所示：

将返回的结果，做成一个Chart图表，如下图所示：

从返回的数据结果集和做出的图表展示，我们很容易发现2004年3月8号这一天无论是点击率还是网站流量都是6天内最高的。那么，对这些流量贡献排名前十的是哪些客户端机器呢？请看下一小节。

客户端主机访问分布情况

流量统计部分只能回答“哪个时间段流量超标”的问题，如果我们需要知道流量超标时间段内，到底是哪个或者哪些客户端主机访问量过大，客户端主机访问流量分布情况如何？在此，我们以2004年3月8号为例，分析客户端主机访问分布情况，代码如下所示：

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_FrequentAccessHosts
GO
CREATE PROCEDURE dbo.UP_FrequentAccessHosts 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

	SELECT RemoteHost
		, NoOfAccess = COUNT(1)
		, Size = cast(SUM(Size_Byte)/ 1024. as decimal(8,2))
	FROM dbo.WebAccessLOG
	WHERE [RequestTime] >= '08/Mar/2004' 
		AND [RequestTime] <= '09/Mar/2004'
	GROUP BY RemoteHost
	HAVING COUNT(1) >= 10
	ORDER BY RemoteHost ASC
	
END 
GO

执行该存储过程，返回如下的结果集：

将这个返回的结果集，做成图表展示如下图所示：

从返回的结果集和图表展示，我们很容易得出，来自客户端机器64.242.88.10的点击率和访问流量远远高于其他的客户端。至此，我们已经成功的找到了访问量最大的客户端机器IP地址。我们可以针对这个客户端主机进行分析，看看是否存在黑客攻击行为，如果存在，可以考虑从网络层禁止这个IP访问网站资源。那么，客户端主机访问的是哪些网站资源呢？请继续查看下一节。

客户端主机访问的资源

根据客户端主机访问分布情况部分，我们已经找到访问量最大的某个或者某几个客户端主机，接下来我们需要回答“客户端主机访问的Web资源是哪些？经常被频繁访问的资源集中在哪些URI上？”。如果能够找出这两个问题，我们可以考量将对应的资源放到缓存设备中，以此来增加缓存的命中率，提高客户机访问网站资源的速度。方法如下：

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_FrequentAccessResouceURI
GO
CREATE PROCEDURE dbo.UP_FrequentAccessResouceURI 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	-- TOP 10 URI
	SELECT TOP 10
			URI
			, NoOfHits = COUNT(1)
			, Size = CAST(SUM(Size_Byte)/ 1024. as decimal(8,2))
	FROM dbo.WebAccessLOG
	GROUP BY URI
	ORDER BY 2 DESC
END
GO

执行该存储过程，返回如下结果集：

依据该结果集，做成图表，展示如下图所示：

从结果集和图表展示的统计结果来看，点击率最高的是获取/twiki/pub/TWiki/TWikiLogos/twikiRobot46x50.gif资源的时候，而流量最大集中在对资源/twiki/bin/view/Main/WebHome的访问上。

Response Code分布情况

在另一个方面，网站客户端主机访问成功率是衡量一个网站是否正常工作很重要的指标，我们可以统计客户端访问HTTP服务的Response Code分布情况，来获取客户端主机访问成功率，以此来判断HTTP服务工作情况是否良好。方法如下：

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_ResponseCodeAnalysis
GO
CREATE PROCEDURE dbo.UP_ResponseCodeAnalysis 
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	SELECT
		 StatusCode
		, ResponseCodeCount = COUNT(1)
	FROM dbo.WebAccessLOG
	GROUP BY StatusCode
	ORDER BY 1 ASC
END
GO

执行该存储过程，返回的结果集如下所示：

将该存储过程返回的结果集，做成图表如下所示：

从存储过程执行的结果集和展示的图表来看，资源访问成功率（返回为200的概率）仅为82.46%，换句话说，100个客户端访问中，仅有82.46个是成功访问，成功率过低，还有很大的提升空间。因此，我们需要深入调查到底是访问哪些URI导致了错误发生？请看下一小节。

报错排名前十的URI

有时候，访问我们的Web服务资源的时候，会发生很多意外情况（返回值不是200），我们需要对这些错误的发生有全面的掌控，比如：统计Web站点上发生错误次数排名前十的资源有哪些？分析出这个问题的答案以后，我们就可针对错误的资源，定向查找访问失败的原因。

USE CCSI2016
GO
DROP PROCEDURE IF EXISTS dbo.UP_FrequentExceptionURI
GO
CREATE PROCEDURE dbo.UP_FrequentExceptionURI
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER 
AS BEGIN ATOMIC 
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
	SELECT TOP 10
			URI
			, NoOfHits = COUNT(1)
			, Size = CAST(SUM(Size_Byte)/ 1024. as decimal(8,2))
	FROM dbo.WebAccessLOG
	WHERE StatusCode <> 200
	GROUP BY URI
	ORDER BY 2 DESC
END
GO

执行该存储过程，返回如下结果集：

将该结果集，做成图表，展示如下所示：

从存储过程返回的结果集和图表展示的统计结果来看，资源/twiki/pub/TWiki/TWikiLogos/twikiRobot46x50.gif点击发生的错误最多，而资源/twiki/bin/edit/Main/PostConf?topicparent=Main.PostfixCommands发生的错误流量最大。所以最终，我们找到了经常报错的一些URI资源，我们需要解决这些错误，最终达到提高客户端访问成功率的目的。

最后总结

本篇月报是SQL Server列存储索引系列月报的最后一篇，介绍SQL Server 2016基于内存优化表的列存储索引的应用案例，分析Apache Web Access Log，以此来帮助我们分析和掌控网站的运行情况。至此，我们总共分析了四篇关于SQL Server列存储技术，跨度从SQL Server 2012到SQL Server 2014，最终到SQL Server 2016。

SQL Server · 特性分析 · 2012列存储索引技术：介绍SQL Server 2012列存储索引技术。

SQL Server · 特性介绍 · 聚集列存储索引：介绍SQL Server 2014中的聚集列存储索引技术。

MSSQL · 特性分析 · 列存储技术做实时分析：介绍了SQL Server 2016列存储索引技术在实时分析场景中应用。

参考文档

Log Files：Apache Web Access Log的日志格式介绍。

Import and analyze IIS Log files using SQL Server：基于内存优化表的列存储索引表结构设计。

Apache (Unix) Log Samples：本篇文章分析的Apache Web Access Log样例数据。

码农公寓