行为、审计日志 (实时索引/实时搜索) - 最佳实践

postgresql , es , 搜索引擎 , 全文检索 , 日志分析 , 倒排索引 , 优化 , 分区 , 分片 , 审计日志 , 行为日志

在很多系统中会记录用户的行为日志，行为日志包括浏览行为、社交行为、操作行为等。

典型的应用例如：数据库的sql审计、企业内部的堡垒机（行为审计）等。

行为、审计日志的量与业务量或者操作量有关，为了满足企业实时查询的需求，通常需要构建搜索引擎，比如使用es或者使用postgresql的全文检索功能来实现。

如果使用postgresql来构建，有几个优势，可以满足多个需求：

1. 明细存储的需求，除了需要建立索引的字段，明细字段也可以存储在postgresql中。

2. 索引的需求，即建立日志行为字段的全文索引。

3. 多维度索引的需求，除了日志行为字段的索引，还可以建立其他字段的索引，例如时间维度，属性维度的索引。这些索引可以组合使用，满足多个维度的搜索需求。

4. 不需要同步到搜索引擎，满足了实时搜索的需求。

磁盘，使用空间大、廉价的sata盘，使用一块ssd作为bcache写缓存。

目录规划，每块盘一个目录

略

创建12个数据库集群，对应到每一块磁盘。可以充分利用磁盘的io。

将数据库实例绑定到不同的cpu核

4个字段，分别存储pk(对应原始明细数据的pk)，时间，用户id，用户行为（tsvector字段）。

检索时可能按照时间区间，用户id，以及分词条件进行检索。

日志保留一段时间（例如1个月）后清除。

每个集群中，创建若干个分区表，例如本例使用了12个分区表。

如果条件允许，建议每个小时一个分区表，这样的话可以不建时间索引，查询时间区间的数据使用分区即可。

如果单个用户的数据量很庞大，那么建议按uid再建立哈希或list分区，这样的话，按照uid查询，不需要使用索引（可以省去在uid建立索引，甚至省去存储uid这个字段）。

行为字段，全文索引。

用户id，b-tree索引。

时间字段，brin块级索引。

时间，时序产生。

用户id，在一个范围内随机产生。

用户行为数据，长约512字符的字符串，拆分成若干个token，例如本例为40个长度不等的token。

灌入测试数据，例如每张表插入2亿，一个数据库插入24亿(约6tb)，总共插入288亿(约72tb)。

每10条一批灌入。

查询测试数据如下，数据非常随机，每条记录的content约40个元素，长度限定在512字符。

用户全文检索请求，输入4个查询条件，流式返回pk。

建议使用流式返回接口，因为结果集可能非常大。

压测

cpu基本耗尽，磁盘的写入也非常的充分

cpu大部分为user的开销，后面使用perf看一下

大部分的开销是postgres进程消耗的,建议使用以下开关重新编译一下.

<a href="https://github.com/digoal/blog/blob/master/201611/20161129_01.md">《postgresql 源码性能诊断(perf profiling)指南》</a>

换算成单机的写入，约6.5万行/s。

写入性能基本上取决于tsvector字段的元素个数，散列程度，本例每条记录约40个元素。如果元素个数下降一半，性能将提升一倍左右。

1. 全文检索索引条目

每条记录约40个元素，当插入的tps=6.5万时，构建的全文检索条目数约 260万/s。

2. uid索引条目，较小，忽略不计。

3. ts索引条目，使用brin块级索引，忽略不计。

性能影响最大，资源消耗最多的就是全文检索索引条目的构建。

举例

1. 写入tps

7万/s ，构建的全文检索条目数约 280万/s。

性能比较平稳。

7.5万/s ，构建的全文检索条目数约 300万/s。

1. 查询聚合

由于日志数据打散分布在多个集群，多个表内，建议使用plproxy进行查询的聚合。

参考

<a href="https://github.com/digoal/blog/blob/master/201110/20111025_01.md">《a smart postgresql extension plproxy 2.2 practices》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151220_04.md">《阿里云apsaradb rds for postgresql 最佳实践 - 4 水平分库 之 节点扩展》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151220_03.md">《阿里云apsaradb rds for postgresql 最佳实践 - 3 水平分库 vs 单机 性能》</a>

<a href="https://github.com/digoal/blog/blob/master/201512/20151220_02.md">《阿里云apsaradb rds for postgresql 最佳实践 - 2 教你rds pg的水平分库》</a>

2. 写入分片

写入分片，可以在业务层完成，随机打散写入。

实际应用时，可以根据需要，切分成更多的分区。

3. 主要的开销是postgres的开销，如果需要详细的分析，建议重新编译postgres

4. gin索引的优化

<a href="https://www.postgresql.org/docs/9.6/static/sql-createindex.html">https://www.postgresql.org/docs/9.6/static/sql-createindex.html</a>

gin_pending_list_limit的目的是延迟合并，因为一条记录中可能涉及较多的gin key，如果实时更新，gin索引的写入量会非常大，性能受到影响。

本例gin_pending_list_limit设置为2mb，tps比较平缓，如果设置过大，当cpu资源不足时，抖动会比较严重。

用户可以根据实际测试，设置合理的gin_pending_list_limit值。

5. 如果把postgresql完全当成索引库使用，并且允许数据丢失，那么可以使用fsync=off的开关，（检查点fsync对io的影响比较大，本例使用的是sata盘，将会导致较大的性能抖动）。

如果有ha的话，丢失的风险又会更小。（但是服务器crash后，需要重建备库，这么大的量，还是挺恐怖的。）

建议用更多的数据库实例，每个实例的大小可控（例如 &lt; 2tb），重建的时间也相对可控。

6. 为了达到更好的响应速度（rt），建议明细和索引分开存放，明细要求写入rt低，索引可以存在一定的延迟。 并且索引与明细数据的可靠性要求也不一样。