精华内容
下载资源
问答
  • 审计日志实现

    2021-06-08 06:11:21
    审计日志实现 目标 记录用户行为: 用户A 在xx时间 做了什么 用户B 在xx时间 改变了什么 针对以上场景,需要记录以下一些接口信息: 时间 ip 用户 入参 响应 改变数据内容描述 标签-区分领域 效果 将此类信息...

    审计日志实现

    目标

    记录用户行为:

    1. 用户A 在xx时间 做了什么
    2. 用户B 在xx时间 改变了什么

    针对以上场景,需要记录以下一些接口信息:

    1. 时间
    2. ip
    3. 用户
    4. 入参
    5. 响应
    6. 改变数据内容描述
    7. 标签-区分领域

    效果

    1. 将此类信息单独输出log(可不选)
    2. 持久化储存,便于查询追踪

    设计

    1. 提供两个信息记录入口:注解和api调用
    2. 信息通过log记录,输出到log和mq
    3. 消费mq数据,解析到ES做持久化
    4. 查询:根据时间,操作名称,标签进行检索

    示意图

    image

    实现

    属性封装

    LcpAuditLog:数据实体

    @Builder
    @Data
    public class LcpAuditLog implements Serializable {
        private static final long serialVersionUID = -6309732882044872298L;
    
        /**
         * 操作人
         */
        private String operator;
        /**
         * 操作(可指定,默认方法全路径)
         */
        private String operation;
        /**
         * 操作时间
         */
        private Date operateTime;
        /**
         * 参数(可选)
         */
        private String params;
        /**
         * ip(可选)
         */
        private String ip;
    
        /**
         * 返回(可选)
         */
        private String response;
    
        /**
         * 标签
         */
        private String tag;
    
        /**
         * 影响数据
         */
        private String influenceData;
    
    }
    定义注解AuditLog

    AuditLog注解,用于标记哪些方法需要做审计日志,与业务解耦。仅记录基本信息:时间,用户,操作,入参,响应。

    @Target({ElementType.PARAMETER, ElementType.METHOD})
    @Retention(RetentionPolicy.RUNTIME)
    @Documented
    public @interface AuditLog {
        /**
         * 操作标识
         */
        @AliasFor(value = "value")
        String operation() default "";
    
        @AliasFor(value = "operation")
        String value() default "";
    
        /**
         * 标签
         * @return
         */
        String tag() default "";
    }
    注解实现类

    常规做法,借助aop实现。

    @Slf4j
    @Aspect
    @Order(2)
    @Configuration
    public class AuditLogAspect {
    
        /**
         * 单个参数最大长度
         */
        private static final int PARAM_MAX_LENGTH = 5000;
        private static final int RESULT_MAX_LENGTH = 20000;
    
        @Resource(name = "logService")
        private LogService logService;
    
        public AuditLogAspect() {
            log.info("AuditLogAspect is init");
        }
    
        /**
         * 后置通知,当方法正常运行后触发
         *
         * @param joinPoint
         * @param auditLog  审计日志
         * @param result
         */
        @AfterReturning(pointcut = "@annotation(auditLog)", returning = "result")
        public void doAfterReturning(JoinPoint joinPoint, AuditLog auditLog, Object result) {
            doPrintLog(joinPoint, auditLog, result);
        }
    
        /**
         * 方法抛出异常后通知
         *
         * @param joinPoint
         * @param auditLog
         * @param throwable
         */
        @AfterThrowing(value = "@annotation(auditLog)", throwing = "throwable")
        public void AfterThrowing(JoinPoint joinPoint, AuditLog auditLog, Throwable throwable) {
            doPrintLog(joinPoint, auditLog, throwable.getMessage());
        }
    
        /**
         * 打印安全日志
         *
         * @param joinPoint
         * @param auditLog
         * @param result
         */
        private void doPrintLog(JoinPoint joinPoint, AuditLog auditLog, Object result) {
            try {
                String approveUser = getUser();
                String ip = getHttpIp();
                Object[] args = joinPoint.getArgs();
                String methodName = joinPoint.getTarget().getClass().getName()
                        + "."
                        + joinPoint.getSignature().getName();
    
                String tag = auditLog.tag() == null ? "" : auditLog.tag();
                String operation = auditLog.operation();
                operation = StringUtils.isEmpty(operation) ? auditLog.value() : operation;
                operation = StringUtils.isEmpty(operation) ? methodName : operation;
                String resultString = JsonUtils.toJSONString(result);
                if (resultString != null && resultString.length() > RESULT_MAX_LENGTH) {
                    resultString = resultString.substring(0, RESULT_MAX_LENGTH);
                }
                logService.writeAuditLog(LcpAuditLog
                        .builder()
                        .ip(ip)
                        .operateTime(new Date())
                        .operator(approveUser)
                        .operation(operation)
                        .params(generateParamDigest(args))
                        .response(resultString)
                        .tag(tag)
                        .build());
            } catch (Throwable t) {
                log.error("AuditLogAspect 打印审计日志失败,失败原因:", t);
            }
        }
    
        private String getHttpIp() {
            try {
                HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
                return request.getRemoteAddr();
            } catch (Exception e) {
                //jsf调用时没有http上游ip,记录jsf ip 没有意义
                return "";
            }
        }
    
    
        /**
         * 获取用户PIN
         *
         * @return
         */
        private String getUser() {
            // ...
        }
    
        /**
         * 生成参数摘要字符串
         *
         * @since 1.1.12
         */
        private String generateParamDigest(Object[] args) {
            StringBuffer argSb = new StringBuffer();
            for (Object arg : args) {
                if (!(arg instanceof HttpServletRequest)) {
                    if (argSb.length() > 0) {
                        argSb.append(",");
                    }
                    String argString = JsonUtils.toJSONString(arg);
                    //避免超大参数
                    if (argString != null && argString.length() > PARAM_MAX_LENGTH) {
                        argString = argString.substring(0, PARAM_MAX_LENGTH);
                    }
                    argSb.append(argString);
                }
            }
            return argSb.toString();
        }
    
    }
    定义操作API

    有了注解还需要API?

    1. 注解可以解决大部分情况,但是个别场景需要定制化记录
    2. 注解的解析结果也需要业务实现,代码层面业务解耦

    service

    public interface LogService {
        /**
         * 输出审计日志
         *
         * <pre>
         *     ex:
         *     writeAuditLog(LcpAuditLog
         *                 .builder()
         *                 .operation(operation)
         *                 .operator(operator)
         *                 .operateTime(new Date())
         *                 .ip(getLocalHost())
         *                 .influenceData(influenceData)
         *                 .build());
         * </pre>
         *
         * @param log
         */
        void writeAuditLog(LcpAuditLog log);
    
        /**
         * 记录操作日志
         * <pre>
         *     ex1:recordOperationLog("刁德三","删除用户","{userId:12,userName:lao sh an}");
         *     ex2:recordOperationLog("di da","deleteUser","{userId:12,userName:lao sh an}");
         * </pre>
         *
         * @param operator      操作人
         * @param operation     动作
         * @param influenceData 影响数据
         */
        void recordOperationLog(String operator, String operation, String influenceData);
    
        /**
         * 记录操作日志
         * <pre>
         *     ex:recordOperationLog("刁德三","删除用户","{userId:12,userName:lao sh an}","运维操作");
         * </pre>
         *
         * @param operator      操作人
         * @param operation     动作
         * @param influenceData 影响数据
         * @param tag           标签
         */
        void recordOperationLog(String operator, String operation, String influenceData, String tag);
    }
    业务实现ServiceImpl
    @Slf4j
    @Service("logService")
    public class LogServiceImpl implements LogService {
        @Override
        public void writeAuditLog(LcpAuditLog lcpAuditLog) {
            try {
                if (log.isInfoEnabled()) {
                    log.info(JsonUtils.toJSONString(lcpAuditLog));
                }
            } catch (Throwable e) {
                //借助外部输出异常log,因为当前类的log被特殊 监控!!
                PrintLogUtil.printErrorLog("LcpLogServiceImpl 打印审计日志失败,e=", e);
            }
        }
    
        @Override
        public void recordOperationLog(String operator, String operation, String influenceData) {
            this.writeAuditLog(LcpAuditLog
                    .builder()
                    .operation(operation)
                    .operator(operator)
                    .operateTime(new Date())
                    .ip(getLocalHost())
                    .influenceData(influenceData)
                    .build());
        }
    
        @Override
        public void recordOperationLog(String operator, String operation, String influenceData, String tag) {
            this.writeAuditLog(LcpAuditLog
                    .builder()
                    .operation(operation)
                    .operator(operator)
                    .operateTime(new Date())
                    .ip(getLocalHost())
                    .influenceData(influenceData)
                    .tag(tag)
                    .build());
        }
    
        private static String getLocalHost() {
            try {
                return InetAddress.getLocalHost().getHostAddress();
            } catch (Exception e) {
                PrintLogUtil.printErrorLog("LcpLogServiceImpl 打印审计日志失败,e=", e);
                return "";
            }
        }
    }

    业务代码只是一句log.info()???
    kafka呢?

    sl4j配置及kafka写入

    sl4f2.0有封装对kafka的写入能力,具体实现:

    引入必要的pom

    <dependency>
                <groupId>org.apache.kafka</groupId>
                <artifactId>kafka-clients</artifactId>
                <version>1.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-log4j2</artifactId>
            </dependency>

    log配置

    sl4j2.xml

    <?xml version="1.0" encoding="UTF-8"?>
    <Configuration status="WARN">
        <properties>
            <property name="LOG_HOME">/data/Logs/common</property>
            <property name="FILE_NAME">audit</property>
        </properties>
    
        <Appenders>
            <RollingFile name="asyncRollingFile" fileName="${LOG_HOME}/${FILE_NAME}.log"
                         filePattern="${LOG_HOME}/$${date:yyyy-MM}/${FILE_NAME}-%d{yyyy-MM-dd}-%i.log.gz">
                <PatternLayout pattern="%-d{yyyy-MM-dd HH:mm:ss}[ %t:%r ] [%X{traceId}] - [%-5p] %c-%M:%L - %m%n%throwable{full}"/>
                <Policies>
                    <TimeBasedTriggeringPolicy/>
                    <SizeBasedTriggeringPolicy size="100 MB"/>
                </Policies>
                <DefaultRolloverStrategy max="20"/>
            </RollingFile>
            <Kafka name="auditLog" topic="log_jmq" syncSend="false">
                <PatternLayout pattern="%m%n"/>
                <Property name="client.id">client.id</Property>
                <Property name="retries">3</Property>
                <Property name="linger.ms">1000</Property>
                <Property name="bootstrap.servers">nameserver:port</Property>
                <Property name="compression.type">gzip</Property>
            </Kafka>
            <Console name="Console" target="SYSTEM_OUT">
                <PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss} [%t] [%X{requestId}] %-5level %l - %msg%n"/>
            </Console>
        </Appenders>
    
        <Loggers>
            <Logger name="org.apache.kafka" level="info" />
            <!--操作日志-->
            <AsyncLogger name="com.service.impl.LogServiceImpl" level="INFO" additivity="false">
                <AppenderRef ref="auditLog"/>
                <AppenderRef ref="asyncRollingFile"/>
                <AppenderRef ref="Console"/>
            </AsyncLogger>
        </Loggers>
    </Configuration>

    至此,可以完成对审计日志的log输出和mq写入,后续的mq消费,写入es就省掉了(因为是封装好的功能模块)

    展开全文
  • 发送审计日志3.1 安装部署ELK3.2 定义es索引模板3.3 Logstash配置3.4 Filebeat配置4. 查看数据 1. 前言 在上一篇博客《微服务轮子项目(14) - 慢查询SQL》,主要讲解了慢查询在MySQL以及在Logstash和Filebeat的...

    1. 前言

    在上一篇博客《微服务轮子项目(14) - 慢查询SQL》,主要讲解了慢查询在MySQL以及在Logstash和Filebeat的操作。

    接下来,本文要讲解的是审计日志。

    2. 审计日志集成

    将来要集成审计日志到我们的项目中,其中审计信息默认输出到logger文件中:logs/audit/audit.log
    在这里插入图片描述

    集成的步骤如下:

    step1: 配置信息,该功能默认关闭,添加以下配置到需要记录审计日志的服务上即可开启

    ylw:
      audit-log:
        enabled: true
    

    step2:添加依赖

    <dependency>
        <groupId>wheel-commons</groupId>
        <artifactId>wheel-log-spring-boot-starter</artifactId>
    </dependency>
    

    step3: 添加注解,在需要审计的方法上添加 @AuditLog注解,如果在类上添加则该类的所有方法都生效,支持 spEL 表达式
    在这里插入图片描述

    如果配置成功,开启后,会并生成出审计日志,内容如下:
    在这里插入图片描述

    3. 发送审计日志

    3.1 安装部署ELK

    3.2 定义es索引模板

    执行下面的ssh脚本创建 【审计日志】 的索引模板,ip改为自己的服务器地址:

    curl -XPUT http://192.168.28.130:9200/_template/template_audit_log -H 'Content-Type: application/json' -d '
    {
        "index_patterns" : ["audit-log-*"],
        "order" : 0,
        "settings" : {
            "number_of_replicas" : 0
        },
        "mappings": {
            "doc": {
                "properties": {
                    "operation": {
                        "type": "text",
                        "fields": {
                            "keyword": {
                                "type": "keyword",
                                "ignore_above": 256
                            }
                        },
                        "analyzer": "ik_max_word"
                    }
                }
            }  
        } 
    }'
    

    3.3 Logstash配置

    step1:修改10-syslog.conf配置,在filter里增加以下内容:

    if [fields][docType] == "audit-log" {
        grok {
          patterns_dir => ["/opt/logstash/patterns"]
          match => {
            "message" => "%{TIMESTAMP_ISO8601:logTime}\|%{MYAPPNAME:appName}\|%{MYTHREADNAME:className}\|%{WORD:methodName}\|%{MYAPPNAME:userId}\|%{MYAPPNAME:userName}\|%{MYAPPNAME:clientId}\|%{GREEDYDATA:operation}"
          }
        }
        date {
          match => ["logTime","yyyy-MM-dd HH:mm:ss.SSS Z"]
        }
        date {
          match => ["logTime","yyyy-MM-dd HH:mm:ss.SSS"]
          target => "timestamp"
          locale => "en"
          timezone => "+08:00"
        }
        mutate {
          remove_field => "message"
          remove_field => "logTime"
          remove_field => "@version"
          remove_field => "host"
          remove_field => "offset"
        }
    }
    

    step2: 修改30-output.conf配置,增加以下内容

    if [fields][docType] == "audit-log" {
        elasticsearch {
          hosts => ["localhost"]
          manage_template => false
          index => "audit-log-%{+YYYY.MM.dd}"
          document_type => "%{[@metadata][type]}"
        }
    }
    

    3.4 Filebeat配置

    修改配置文件 filebeat.yml,在 filebeat.inputs 里添加以下内容,抓取日志内容:

    - type: log
      enabled: true
      paths:
        - D:\workspaces\projects\logs\audit\*.log
      fields:
        docType: audit-log
        project: microservices-platform
    

    4. 查看数据

    配置好elk之后日志数据就能同步到es了并自动创建索引:
    在这里插入图片描述
    通过页面展示数据:
    在这里插入图片描述

    展开全文
  • PostgreSQL , ES , 搜索引擎 , 全文检索 , 日志分析 , 倒排索引 , 优化 , 分区 , 分片 , 审计日志 , 行为日志 背景 在很多系统中会记录用户的行为日志,行为日志包括浏览行为、社交行为、操作行为等。 典型的应用...

    标签

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


    背景

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

    典型的应用例如:数据库的SQL审计、企业内部的堡垒机(行为审计)等。

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

    如果使用PostgreSQL来构建,有几个优势,可以满足多个需求:

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

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

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

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

    例子

    测试机

    磁盘,使用空间大、廉价的SATA盘,使用一块SSD作为BCACHE写缓存。

    12 * N TB          
    

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

    /disk[1-12]          
    

    操作系统配置

    编译PostgreSQL

    wget https://ftp.postgresql.org/pub/snapshot/dev/postgresql-snapshot.tar.bz2          
              
    tar -jxvf postgresql-snapshot.tar.bz2          
    cd postgresql-10beta1          
              
    export USE_NAMED_POSIX_SEMAPHORES=1          
    LIBS=-lpthread CFLAGS="-O3" ./configure --prefix=/home/digoal/pgsql10_8k --with-blocksize=8 --with-wal-segsize=1024          
    LIBS=-lpthread CFLAGS="-O3" make world -j 128           
    LIBS=-lpthread CFLAGS="-O3" make install-world           
    

    环境变量

    vi env_pg10.sh           
              
    export PS1="$USER@`/bin/hostname -s`-> "          
    export PGPORT=$(($1+1920))          
    export PGDATA=/disk$1/digoal/pgdata/pg_root$(($1+1920))          
    export LANG=en_US.utf8          
    export PGHOME=/home/digoal/pgsql10_8k          
    export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH          
    export LD_RUN_PATH=$LD_LIBRARY_PATH          
    export DATE=`date +"%Y%m%d%H%M"`          
    export PATH=$PGHOME/bin:$PATH:.          
    export MANPATH=$PGHOME/share/man:$MANPATH          
    export PGHOST=127.0.0.1          
    export PGUSER=postgres          
    export PGDATABASE=postgres          
    alias rm='rm -i'          
    alias ll='ls -lh'          
    unalias vi          
    

    初始化集群

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

    vi init.sh          
              
    for ((i=1;i<13;i++))          
    do          
      . ~/env_pg10.sh $i          
      initdb -D $PGDATA -E SQL_ASCII --locale=C -U postgres          
      echo "local   all             all                                     trust" > $PGDATA/pg_hba.conf          
      echo "host    all             all             127.0.0.1/32            trust" >> $PGDATA/pg_hba.conf          
      echo "host    all             all             ::1/128                 trust" >> $PGDATA/pg_hba.conf          
      echo "host    all             all             0.0.0.0/0               trust" >> $PGDATA/pg_hba.conf          
    done          
    
    . ./init.sh        
    

    配置参数

    postgresql.auto.conf          
              
    # Do not edit this file manually!          
    # It will be overwritten by the ALTER SYSTEM command.          
    listen_addresses = '0.0.0.0'          
    max_connections = 400          
    unix_socket_directories = '.'          
    shared_buffers = 32GB          
    maintenance_work_mem = 2GB          
    dynamic_shared_memory_type = posix          
    vacuum_cost_delay = 0          
    bgwriter_delay = 10ms          
    bgwriter_lru_maxpages = 1000          
    bgwriter_lru_multiplier = 10.0          
    bgwriter_flush_after = 0          
    max_parallel_workers_per_gather = 0          
    synchronous_commit = off          
    full_page_writes = off          
    wal_buffers = 2047MB          
    wal_writer_delay = 10ms          
    wal_writer_flush_after = 0          
    checkpoint_timeout = 45min          
    max_wal_size = 96GB          
    min_wal_size = 32GB          
    checkpoint_completion_target = 0.9          
    checkpoint_flush_after = 2MB          
    max_wal_senders = 0          
    random_page_cost = 1.3          
    effective_cache_size = 32GB          
    log_destination = 'csvlog'          
    logging_collector = on          
    log_truncate_on_rotation = on          
    log_checkpoints = on          
    log_connections = on          
    log_disconnections = on          
    log_error_verbosity = verbose          
    log_timezone = 'PRC'          
    autovacuum = on          
    log_autovacuum_min_duration = 0          
    autovacuum_freeze_max_age = 1500000000          
    autovacuum_multixact_freeze_max_age = 1600000000          
    vacuum_freeze_table_age = 1400000000          
    vacuum_multixact_freeze_table_age = 1400000000          
    datestyle = 'iso, mdy'          
    timezone = 'PRC'          
    lc_messages = 'C'          
    lc_monetary = 'C'          
    lc_numeric = 'C'          
    lc_time = 'C'          
    default_text_search_config = 'pg_catalog.english'          
    autovacuum_work_mem = 2GB          
    autovacuum_max_workers = 12          
    autovacuum_naptime = 30s          
    autovacuum_vacuum_scale_factor = 0.2          
    autovacuum_analyze_scale_factor = 0.2          
    autovacuum_vacuum_cost_delay = 0                  
    autovacuum_vacuum_cost_limit = 0         
    zero_damaged_pages = on        
    
    cp postgresql.auto.conf /disk1/digoal/pgdata/pg_root1921/          
    ...          
    ...          
    cp postgresql.auto.conf /disk12/digoal/pgdata/pg_root1932/          
    

    启动集群

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

    vi start.sh          
              
    for ((i=1;i<13;i++))          
    do          
      . /home/digoal/env_pg10.sh $i          
              
      if [ $i -eq 12 ]; then          
      numactl --physcpubind=56-63 pg_ctl start -o "-c port=$PGPORT"          
      else          
      numactl --physcpubind="$((($i-1)*(5)+(1)))"-"$(($i*5))" pg_ctl start -o "-c port=$PGPORT"          
      fi          
              
    done          
    
    . ./start.sh        
    

    停止集群

    vi stop.sh           
              
    for ((i=1;i<13;i++))          
    do          
      . /home/digoal/env_pg10.sh $i          
              
      pg_ctl stop -m fast          
              
    done          
    
    . ./stop.sh        
    

    建模

    表结构

    4个字段,分别存储PK(对应原始明细数据的PK),时间,用户ID,用户行为(tsvector字段)。

    检索需求

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

    保留时长

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

    分区

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

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

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

    索引

    行为字段,全文索引。

    用户ID,B-TREE索引。

    时间字段,brin块级索引。

    模拟数据

    时间,时序产生。

    用户ID,在一个范围内随机产生。

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

    初始化数据表

    vi init.sql          
              
    drop table bptest cascade;          
    do language plpgsql $$          
    declare          
    begin          
      for i in 1..12 loop          
        execute 'drop table bptest'||i||' cascade';          
      end loop;          
    end;          
    $$;          
              
    create table bptest(pk serial8, uid int, ts int8, content tsvector);          
    create index idx_bptest_content on bptest using gin (content) with (fastupdate=on,gin_pending_list_limit=2048);          
    create index idx_bptest_uid on bptest(uid);          
    create index idx_bptest_ts on bptest using brin(ts);          
              
    do language plpgsql $$          
    declare          
    begin          
      for i in 1..12 loop          
        execute 'create table bptest'||i||'(like bptest including all) inherits(bptest)';          
      end loop;          
    end;          
    $$;          
              
    -- 产生若干字符长度的随机字符串          
    CREATE OR REPLACE FUNCTION public.gen_rand_str(integer)          
     RETURNS text          
     LANGUAGE sql          
     STRICT          
    AS $function$          
      select string_agg(a[random()*6+1],'') from generate_series(1,$1), (select array['a','b','c','d','e','f',' ']) t(a);          
    $function$;          
    
    psql -f ./init.sql -p 1921          
    ...          
    ...          
    psql -f ./init.sql -p 1932          
    

    测试

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

    每10条一批灌入。

    vi test.sh          
              
    for ((i=1;i<13;i++))          
    do          
      echo "insert into bptest${i} (uid,ts,content) select random()*100000 , extract(epoch from now()), to_tsvector('english',gen_rand_str(512)) from generate_series(1,10);" > ~/test${i}.sql          
    done          
              
    for ((i=1;i<13;i++))          
    do          
      . /home/digoal/env_pg10.sh ${i}          
              
      for ((x=1;x<13;x++))          
      do          
        nohup pgbench -M prepared -n -r -P 3 -f ./test${x}.sql -c 1 -j 1 -t 200000000 >/tmp/bptest_${i}_${x}.log 2>&1 &          
      done          
    done          
              
              
    chmod 500 test.sh          
    

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

    select * from bptest1 limit 1;          
              
    pk      | 1        
    uid     | 849185          
    ts      | 1494928859          
    content | 'aaaefba':14 'acddcfd':39 'acdeeaadbffdbbecceb':50 'aceeedfbaefbdfcbd':59 'adbbeddbecfdcffaeedcedaeeddaeaaeebfbdcdcecfbbebfcebabceffbfdbfbfa':60           
    'adcdf':61 'aead':47 'afddf':70 'ba':8 'bae':37 'bbaacffbcafeffafefdf':38 'bbe':55 'bbecfdf':32 'bcbfd':27 'bdce':45 'bdeccbcdeaabefbeeebcdbfddd':19           
    'bed':17 'beedeadccbbbecbfcbf':44 'bfccaeddaddbc':2 'cafdfcf':5 'cbcacefaff':3 'cbcfc':52 'cbfef':63 'ccdcbedb':33 'ccdcd':20 'cd':6 'cfecfeeccabf':42           
    'cffb':15 'dabdfddeeabfdcefb':16 'dacdeecfbcefebfabeedfabbaccec':57 'daee':1 'daffcdffadddbaffd':68 'dbcddacefcd':9           
    'dbdbcbfadfffbdddaaabdcbcecdbecbbdecffbfcfecbbfebfebcadefecfceadaeffd':11 'dcdf':23 'dd':53 'ddec':31 'debdcdebfffebdbfdeefffbcfbccbececdbeaffffedfbefdcccbbccadedecfbeccccbbb':48           
    'deefaeeaabdbbdafcfcbeecc':71 'df':26 'dfcbbcd':46 'e':7,51,56 'eafddcaac':43 'ecbaffa':21 'ecdeeceddbdcbfcabdc':10 'ecedcec':41 'ed':66           
    'edcbaecfcdfbcbcdedeebdbfceeeececfac':35 'eeca':25 'eeebafeacfebfdbdbddaacabebabbfbfdefeddefccfbeaefdbf':29 'eefdbfcadebcbbfffaefcaecafbddbdbfcf':13           
    'ef':58 'efbdc':67 'efccdddaebfbdaffcdfcbfdcbdeb':54 'efccebdddededdeda':64 'effcbfdfdeebfbbcfaabfd':12 'f':24,28,65 'fbbfccfcbcba':30 'fc':4           
    'fcbbdbbaefcefefdf':34 'fd':18 'fdffcbe':69 'fea':62 'feeabdcd':36 'feeadcedecedebaedccffbfddadcfececbefddcbeaedbebfadefedcbd':22 'feffceceaeec':49 'ffaffde':40          
    

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

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

    select pk from bptest1 where uid=$1 and ts between $2 and $3 and content @@ to_tsquery('english', $4);        
    

    压测

    ./test.sh          
    

    资源使用

    dstat

    CPU基本耗尽,磁盘的写入也非常的充分

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

    dstat          
              
    ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--          
    usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw           
     92   7   1   0   0   1|1075M 2435M|4048B 2297B|   0     0 | 142k  167k          
     92   7   0   0   0   1|1137M 2075M|2391B 1945B|   0     0 | 135k  161k          
     91   8   0   0   0   0|1182M 2125M|3483B 2845B|   0     0 | 140k  166k          
     91   8   0   0   0   1|1193M 1971M|3788B 1633B|   0     0 | 135k  159k          
     91   8   0   0   0   0|1089M 2305M|2232B 1478B|   0     0 | 139k  159k          
     92   7   0   0   0   1| 986M 2795M|2176B 1568B|   0     0 | 127k  142k          
     92   7   1   0   0   0| 760M 2864M|6028B 1408B|   0     0 | 116k  118k          
     90   8   0   0   0   0|1029M 3057M|1565B 2116B|   0     0 | 132k  150k          
     90   9   1   0   0   1|1000M 3237M|2336B 4850B|   0     0 | 133k  154k          
     90   8   1   1   0   1| 659M 4399M|2872B 7992B|   0     0 | 115k  119k          
     91   7   0   0   0   1| 925M 2996M|1293B 1059B|   0     0 | 122k  127k          
     90   8   1   1   0   1| 996M 3350M| 664B  574B|   0     0 | 133k  148k          
     91   7   1   0   0   1| 948M 2927M|3525B 2500B|   0     0 | 132k  146k          
     90   8   0   1   0   0|1114M 2869M|1751B 2645B|   0     0 | 132k  150k          
     90   8   0   1   0   1|1267M 2408M|3003B 2244B|   0     0 | 137k  167k          
     91   8   0   1   0   1|1086M 2539M| 900B  347B|   0     0 | 133k  154k          
     91   8   0   0   0   1| 998M 2614M|1975B 1757B|   0     0 | 130k  151k          
     91   8   0   0   0   0|1120M 2150M|1466B 4911B|   0     0 | 130k  154k          
     92   7   0   0   0   0|1163M 2387M|1356B  498B|   0     0 | 136k  163k          
     90   8   1   1   0   1| 864M 2656M|2601B 3373B|   0     0 | 130k  143k          
     91   8   0   0   0   1| 987M 2651M|2052B  898B|   0     0 | 135k  154k          
     91   8   0   0   0   0|1073M 2205M|2479B 2319B|   0     0 | 130k  144k          
     90   8   1   1   0   1| 951M 2941M|1390B 1001B|   0     0 | 130k  148k          
    

    磁盘使用率

    iostat -x           
              
    avg-cpu:  %user   %nice %system %iowait  %steal   %idle          
              85.29    0.49    9.89    1.90    0.00    2.43          
              
    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util          
    sdb               0.00    92.40   99.40  389.00  2822.40 136435.20   285.13     2.25    4.60   0.65  31.64          
    sdc               0.00    33.60  154.20  211.60  4838.40 85700.80   247.51     1.14    3.10   0.63  22.96          
    sdd               0.00    63.00  232.40  238.40  7316.80 109648.00   248.44     2.17    4.61   0.99  46.54          
    sde               0.00    78.80  269.60  340.80  7980.80 102419.20   180.87     2.53    4.14   0.94  57.62          
    sdf               0.00    58.40  283.00  234.20  8204.80 99129.60   207.53     2.30    4.45   0.93  48.20          
    sdg               0.00    50.80  207.60  236.60  6652.80 94337.60   227.35     1.42    3.19   0.68  30.34          
    sdh               0.00   102.20  109.40  475.20  3489.60 131211.20   230.42     2.60    4.45   0.52  30.40          
    sdi               0.00    70.20  107.00  337.00  3228.80 79603.20   186.56     1.35    3.04   0.53  23.38          
    sdj               0.00    31.00   70.60  158.80  2534.40 85124.80   382.12     0.82    3.59   0.86  19.72          
    sdk               0.20    58.40  190.60  295.80  5587.20 123539.20   265.47     1.74    3.57   0.68  33.28          
    sdl               0.00    91.00  162.80  396.40  4441.60 119507.20   221.65     1.98    3.54   0.59  33.26          
    sdm               0.00   274.80  103.20  359.20  2296.00 158908.80   348.63     3.81    8.23   1.08  50.06          
    

    perf

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

    《PostgreSQL 源码性能诊断(perf profiling)指南》

    top -ag          
              
       PerfTop:    9171 irqs/sec  kernel:63.7%  exact:  0.0% [1000Hz cycles],  (all, 64 CPUs)        
    ----------------------------------------------------------------------------------------------                           
            
     samples  pcnt function                    DSO        
     _______ _____ ___________________________ _______________________________________        
            
    23044.00  4.5% tsCompareString             /home/digoal/pgsql10_8k/bin/postgres         
    19821.00  3.9% ExecInterpExpr              /home/digoal/pgsql10_8k/bin/postgres         
    12258.00  2.4% gintuple_get_key            /home/digoal/pgsql10_8k/bin/postgres         
    12208.00  2.4% pg_detoast_datum_packed     /home/digoal/pgsql10_8k/bin/postgres         
    11111.00  2.2% hash_search_with_hash_value /home/digoal/pgsql10_8k/bin/postgres         
    10318.00  2.0% memcpy                      /lib64/libc-2.12.so                            
     9078.00  1.8% AllocSetAlloc               /home/digoal/pgsql10_8k/bin/postgres         
     8944.00  1.7% advance_aggregates          /home/digoal/pgsql10_8k/bin/postgres         
     8547.00  1.7% cmpEntryAccumulator         /home/digoal/pgsql10_8k/bin/postgres         
     7311.00  1.4% array_seek                  /home/digoal/pgsql10_8k/bin/postgres         
     6744.00  1.3% gin_cmp_tslexeme            /home/digoal/pgsql10_8k/bin/postgres         
     6650.00  1.3% __closure_wake_up           [bcache]                                     
     6550.00  1.3% appendBinaryStringInfo      /home/digoal/pgsql10_8k/bin/postgres         
     6475.00  1.3% TParserGet                  /home/digoal/pgsql10_8k/bin/postgres         
     5578.00  1.1% ginFindLeafPage             /home/digoal/pgsql10_8k/bin/postgres         
     5543.00  1.1% PyParser_AddToken           /lib64/libpython2.7.so.1.0                  
     5412.00  1.1% array_get_element           /home/digoal/pgsql10_8k/bin/postgres         
     5355.00  1.0% heap_fill_tuple             /home/digoal/pgsql10_8k/bin/postgres         
     4936.00  1.0% entryLocateLeafEntry        /home/digoal/pgsql10_8k/bin/postgres         
     4732.00  0.9% heap_form_minimal_tuple     /home/digoal/pgsql10_8k/bin/postgres         
     4512.00  0.9% rb_insert                   /home/digoal/pgsql10_8k/bin/postgres        
    

    top

    top -c -u digoal          
              
    top - 19:20:47 up 179 days,  5:38,  8 users,  load average: 183.79, 189.01, 166.41          
    Tasks: 2939 total, 159 running, 2780 sleeping,   0 stopped,   0 zombie          
    Cpu(s): 87.7%us,  8.9%sy,  0.9%ni,  0.9%id,  1.2%wa,  0.0%hi,  0.5%si,  0.0%st          
    Mem:  529321828k total, 512395020k used, 16926808k free,   299780k buffers          
    Swap:        0k total,        0k used,        0k free, 482162560k cached          
              
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                                                                                                                                                   
    11258 digoal  20   0 20.7g 2.9g 2.9g R 78.7  0.6   5:11.98 postgres: postgres postgres 127.0.0.1(52848) INSERT                                                            
    11253 digoal  20   0 20.7g 3.0g 2.9g R 70.7  0.6   5:14.48 postgres: postgres postgres 127.0.0.1(52843) INSERT                                                            
    11264 digoal  20   0 20.7g 3.0g 2.9g R 68.1  0.6   5:14.63 postgres: postgres postgres 127.0.0.1(52854) INSERT                                                            
    11263 digoal  20   0 20.7g 3.0g 2.9g R 65.2  0.6   5:14.89 postgres: postgres postgres 127.0.0.1(52853) INSERT                                                            
    11250 digoal  20   0 20.7g 3.0g 3.0g R 59.4  0.6   5:16.66 postgres: postgres postgres 127.0.0.1(52840) idle                                                              
    11268 digoal  20   0 20.7g 2.9g 2.9g R 53.3  0.6   5:11.36 postgres: postgres postgres 127.0.0.1(52858) INSERT                                                            
    11266 digoal  20   0 20.7g 3.0g 3.0g R 52.3  0.6   5:09.00 postgres: postgres postgres 127.0.0.1(52856) INSERT                                                            
    11068 digoal  20   0 20.7g 2.6g 2.6g R 51.4  0.5   4:44.47 postgres: postgres postgres 127.0.0.1(45823) INSERT                                                            
    11097 digoal  20   0 20.7g 2.6g 2.5g R 49.5  0.5   4:47.85 postgres: postgres postgres 127.0.0.1(45839) INSERT                                                            
    11161 digoal  20   0 20.7g 2.6g 2.6g R 49.5  0.5   4:47.87 postgres: postgres postgres 127.0.0.1(44881) INSERT                                                            
    11256 digoal  20   0 20.7g 3.0g 3.0g R 49.5  0.6   5:14.69 postgres: postgres postgres 127.0.0.1(52846) INSERT                                                            
    10819 digoal  20   0 20.7g 2.7g 2.7g R 48.8  0.5   4:58.17 postgres: postgres postgres 127.0.0.1(47342) INSERT                                                            
    11107 digoal  20   0 20.7g 2.7g 2.7g R 48.8  0.5   5:02.00 postgres: postgres postgres 127.0.0.1(59612) INSERT                                                            
    11255 digoal  20   0 20.7g 3.0g 3.0g R 48.2  0.6   5:15.68 postgres: postgres postgres 127.0.0.1(52845) INSERT                                                            
    11267 digoal  20   0 20.7g 3.0g 3.0g R 47.9  0.6   5:18.82 postgres: postgres postgres 127.0.0.1(52857) INSERT                                                            
    11066 digoal  20   0 20.7g 2.6g 2.6g R 46.9  0.5   4:44.97 postgres: postgres postgres 127.0.0.1(45821) INSERT                                                            
    11222 digoal  20   0 20.7g 2.6g 2.6g R 45.9  0.5   5:00.43 postgres: postgres postgres 127.0.0.1(40379) idle                                                              
    11207 digoal  20   0 20.7g 2.6g 2.6g R 45.6  0.5   5:04.59 postgres: postgres postgres 127.0.0.1(46160) INSERT                                                            
    11224 digoal  20   0 20.7g 2.6g 2.6g R 45.3  0.5   5:02.60 postgres: postgres postgres 127.0.0.1(40381) INSERT                                                            
    11249 digoal  20   0 20.7g 2.6g 2.6g R 45.3  0.5   4:59.58 postgres: postgres postgres 127.0.0.1(46187) INSERT                                                            
    11045 digoal  20   0 20.7g 2.6g 2.6g R 44.6  0.5   4:39.75 postgres: postgres postgres 127.0.0.1(64424) idle                                                              
    11064 digoal  20   0 20.7g 2.6g 2.6g R 44.6  0.5   4:44.69 postgres: postgres postgres 127.0.0.1(45819) INSERT                                                            
    11145 digoal  20   0 20.7g 2.6g 2.6g S 44.3  0.5   4:46.18 postgres: postgres postgres 127.0.0.1(44876) INSERT                                                            
    10865 digoal  20   0 20.7g 2.6g 2.6g R 44.0  0.5   4:59.89 postgres: postgres postgres 127.0.0.1(49769) INSERT                                                            
    11080 digoal  20   0 20.7g 2.6g 2.5g R 44.0  0.5   4:43.70 postgres: postgres postgres 127.0.0.1(45825) INSERT                                                            
    11247 digoal  20   0 20.7g 2.6g 2.6g R 43.4  0.5   5:01.91 postgres: postgres postgres 127.0.0.1(40391) idle                                                              
    11163 digoal  20   0 20.7g 2.6g 2.6g R 42.7  0.5   4:48.34 postgres: postgres postgres 127.0.0.1(44882) idle                                                              
    11164 digoal  20   0 20.7g 2.6g 2.6g R 42.4  0.5   4:53.21 postgres: postgres postgres 127.0.0.1(44883) INSERT                                                            
    10882 digoal  20   0 20.7g 2.6g 2.6g R 41.8  0.5   5:04.78 postgres: postgres postgres 127.0.0.1(49772) INSERT                                                            
    10868 digoal  20   0 20.7g 2.6g 2.6g R 41.4  0.5   5:00.30 postgres: postgres postgres 127.0.0.1(49770) INSERT          
    

    写入tps

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

    progress: 729.0 s, 55.0 tps, lat 11.610 ms stddev 4.836          
    progress: 732.0 s, 59.7 tps, lat 20.071 ms stddev 107.984          
    progress: 735.0 s, 57.0 tps, lat 20.492 ms stddev 125.445          
    progress: 738.0 s, 38.7 tps, lat 25.891 ms stddev 154.607          
    progress: 741.0 s, 41.0 tps, lat 24.405 ms stddev 140.247          
    progress: 744.0 s, 43.0 tps, lat 13.550 ms stddev 10.448          
    progress: 747.0 s, 60.0 tps, lat 20.691 ms stddev 131.640          
    progress: 750.0 s, 60.0 tps, lat 17.394 ms stddev 83.385          
    progress: 753.0 s, 44.3 tps, lat 25.510 ms stddev 146.719          
    progress: 756.0 s, 25.0 tps, lat 39.819 ms stddev 213.642          
    progress: 759.0 s, 50.0 tps, lat 11.439 ms stddev 5.319          
    progress: 762.0 s, 60.0 tps, lat 20.979 ms stddev 106.782          
    progress: 765.0 s, 60.0 tps, lat 18.778 ms stddev 167.714          
    progress: 768.0 s, 58.0 tps, lat 18.017 ms stddev 99.949          
    progress: 771.0 s, 51.0 tps, lat 19.636 ms stddev 124.429          
    

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

    postgres=# select array_length(tsvector_to_array(content),1) from bptest1 limit 10;        
     array_length         
    --------------        
               40        
               37        
               40        
               45        
               35        
               42        
               38        
               46        
               30        
               40        
    (10 rows)        
    

    评估每秒构建了多少个索引条目

    1. 全文检索索引条目

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

    65000*40 = 2600000        
    

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

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

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

    查询性能

    举例

    postgres=# \dt+ bptest1      
                          List of relations      
     Schema |  Name   | Type  |  Owner   |  Size   | Description       
    --------+---------+-------+----------+---------+-------------      
     public | bptest1 | table | postgres | 1689 MB |       
    (1 row)      
          
    postgres=# explain (analyze,verbose,timing,costs,buffers) select * from bptest1 where ts between 1494999617 and 1495999617 and content @@ to_tsquery ('english','abc');      
                                                                  QUERY PLAN                                                                    
    --------------------------------------------------------------------------------------------------------------------------------------      
     Bitmap Heap Scan on public.bptest1  (cost=175.95..23691.41 rows=20015 width=811) (actual time=7.017..23.376 rows=19755 loops=1)      
       Output: uid, ts, content      
       Recheck Cond: (bptest1.content @@ '''abc'''::tsquery)      
       Filter: ((bptest1.ts >= 1494999617) AND (bptest1.ts <= 1495999617))      
       Heap Blocks: exact=18933      
       Buffers: shared hit=18948      
       ->  Bitmap Index Scan on bptest1_content_idx  (cost=0.00..170.94 rows=20019 width=0) (actual time=3.811..3.811 rows=19755 loops=1)      
             Index Cond: (bptest1.content @@ '''abc'''::tsquery)      
             Buffers: shared hit=15      
     Planning time: 0.097 ms      
     Execution time: 24.517 ms      
    (11 rows)       
          
    postgres=# explain (analyze,verbose,timing,costs,buffers) select * from bptest1 where ts between 1494999617 and 1495999617 and content @@ to_tsquery ('english','abc & bc');      
                                                                QUERY PLAN                                                                   
    -----------------------------------------------------------------------------------------------------------------------------------      
     Bitmap Heap Scan on public.bptest1  (cost=36.27..2598.42 rows=1996 width=811) (actual time=4.577..6.711 rows=2125 loops=1)      
       Output: uid, ts, content      
       Recheck Cond: (bptest1.content @@ '''abc'' & ''bc'''::tsquery)      
       Filter: ((bptest1.ts >= 1494999617) AND (bptest1.ts <= 1495999617))      
       Heap Blocks: exact=2116      
       Buffers: shared hit=2239      
       ->  Bitmap Index Scan on bptest1_content_idx  (cost=0.00..35.77 rows=1997 width=0) (actual time=4.291..4.291 rows=2125 loops=1)      
             Index Cond: (bptest1.content @@ '''abc'' & ''bc'''::tsquery)      
             Buffers: shared hit=123      
     Planning time: 0.125 ms      
     Execution time: 6.849 ms      
    (11 rows)      
    

    纯SSD fsync=on 写入性能

    1. 写入TPS

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

    性能比较平稳。

    纯SATA+SSD bcache fsync=off 写入性能

    1. 写入TPS

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

    性能比较平稳。

    小结

    1. 查询聚合

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

    参考

    《A Smart PostgreSQL extension plproxy 2.2 practices》

    《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 4 水平分库 之 节点扩展》

    《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 3 水平分库 vs 单机 性能》

    《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 2 教你RDS PG的水平分库》

    2. 写入分片

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

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

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

    4. gin索引的优化

    https://www.postgresql.org/docs/9.6/static/sql-createindex.html

    GIN indexes accept different parameters:          
              
    1. fastupdate          
              
    This setting controls usage of the fast update technique described in Section 63.4.1.           
    It is a Boolean parameter: ON enables fast update, OFF disables it.           
    (Alternative spellings of ON and OFF are allowed as described in Section 19.1.) The default is ON.          
              
    Note: Turning fastupdate off via ALTER INDEX prevents future insertions from going into the list of pending index entries,           
    but does not in itself flush previous entries.           
              
    You might want to VACUUM the table or call gin_clean_pending_list function afterward to ensure the pending list is emptied.          
              
    2. gin_pending_list_limit          
              
    Custom gin_pending_list_limit parameter.           
    This value is specified in kilobytes.          
    

    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盘,将会导致较大的性能抖动)。

    postgresql.auto.conf      
          
    fsync = off      
    zero_damaged_pages = on        
    

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

    建议用更多的数据库实例,每个实例的大小可控(例如 < 2TB),重建的时间也相对可控。

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

    展开全文
  • PostgreSQL , ES , 搜索引擎 , 全文检索 , 日志分析 , 倒排索引 , 优化 , 分区 , 分片 , 审计日志 , 行为日志 , schemaless 背景 在很多系统中会记录用户的行为日志,行为日志包括浏览行为、社交行为、操作行为等...

    标签

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


    背景

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

    典型的应用例如:数据库的SQL审计、企业内部的堡垒机(行为审计)等。

    前面写了一篇最佳实践,通过PostgreSQL来存储审计日志,同时对审计日志需要检索的字段建立全文索引。

    SSD机器可以达到7万/s的写入(换算成全文索引条目,约280万/s的条目建立速度)。达到这个性能指标时,CPU,磁盘IO全部吃满。

    全文如下:

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

    除了使用全文索引,还有其他方法呢?

    本文将采用另一个角度来解决审计日志的检索和高速写入问题。

    审计日志带三个维度的查询条件,一个是UID,一个是时间范围,最后是词条匹配。

    1. UID表示客户ID,用来区分不同用户产生的行为数据。

    2. TS字段,表示日志是什么时间点产生的。

    3. 行为数据字段,表示用户的行为。

    优化思路:

    1. 将UID作为表名的一部分,每个UID一张表。

    (好处:省一个字段,节约空间。同时在数据组织时不会混淆不同用户的数据,查询时消除了IO放大的问题,提升了查询效率。)

    (缺点:每个UID一张表,表可能很多,元数据会变大。变更结构时,可能需要涉及较多表。)

    2. TS字段,采用BRIN块级索引,因为每个用户产生的行为数据,都是时间顺序的,所以堆存储与值顺序有非常强的线性相关性。

    3. 将数据打散存放,使用元数据记录UID对应的DB list,随机写入对应的DBs,查询时按TS范围查询,查询所有的DBs汇聚(应用层负责merge sort)后返回(行为字段不使用索引)。

    pic

    PostgreSQL 10内置了merge sort的功能,所以你如果需要一个中间层来实现merge sort的话,PG也是个不错的选择。

    只需要将所有的数据源配置为fdw子表即可。

    例如

    postgres=# explain select * from bptest where ts between now()-interval '20 min' and now()-interval '10 min' order by ts;  
                                                         QUERY PLAN                                                        
    ---------------------------------------------------------------------------------------------------------------------  
     Merge Append  (cost=34.00..634325.20 rows=4808182 width=524)  
       Sort Key: bptest.ts  
       ->  Index Scan using idx_bptest_ts on bptest  (cost=0.14..3.15 rows=1 width=40)  
             Index Cond: ((ts >= (now() - '00:20:00'::interval)) AND (ts <= (now() - '00:10:00'::interval)))  
       ->  Index Scan using bptest_32_20170522_ts_idx on bptest_32_20170522  (cost=0.30..4802.19 rows=49918 width=524)  
             Index Cond: ((ts >= (now() - '00:20:00'::interval)) AND (ts <= (now() - '00:10:00'::interval)))  
       ->  Index Scan using bptest_64_20170522_ts_idx on bptest_64_20170522  (cost=0.30..4114.22 rows=42820 width=524)  
             Index Cond: ((ts >= (now() - '00:20:00'::interval)) AND (ts <= (now() - '00:10:00'::interval)))  
       ->  Index Scan using bptest_34_20170522_ts_idx on bptest_34_20170522  (cost=0.30..3984.00 rows=41459 width=524)  
             Index Cond: ((ts >= (now() - '00:20:00'::interval)) AND (ts <= (now() - '00:10:00'::interval)))  
       ->  Index Scan using bptest_37_20170522_ts_idx on bptest_37_20170522  (cost=0.30..4898.77 rows=50972 width=524)  
             Index Cond: ((ts >= (now() - '00:20:00'::interval)) AND (ts <= (now() - '00:10:00'::interval)))  
       ->  Index Scan using bptest_31_20170522_ts_idx on bptest_31_20170522  (cost=0.30..4682.19 rows=48668 width=524)  
       .....................  
    

    设计

    方案1:

    GIN索引 BUILD全文索引的方式,6万tps时,基本榨干了CPU和IO资源。BCACHE GC或轻微的IO抖动,会导致比较严重的性能变化。

    方案2:

    通过uid+ts_prefix分区,确保一个用户的数据在一份堆存储中,减少检索时的IO开销。

    ts字段具备时序属性,通过brin块级索引降低索引大小。

    当数据量达到一定程度时,自动触发PG10并行查询特性,提升查询性能。

    由于UID数据已经分片,查询时会输入TS和文本匹配两个变量,数据分配到每个节点已经不多,使用模糊查询代替全文检索,加上PG10的多核并行,完全可以满足查询响应时延需求。

    元数据表结构

    create table db_meta
    (
    dbid int primary key, -- 每个数据库节点一条记录,表示一个数据库分片
    groupid int, -- 每个分片属于一个分组
    conn_info text -- 连接信息(URL)
    );

    create table uid_mapping
    (
    uid int primary key, -- 客户唯一标示
    dbgroupid int -- 数据库分组,表示这个用户的数据随机写入这个分组的所有分片中。
    );

    行为数据保留一段时间后清除。

    如果用户觉得这样设计比较麻烦,可以将所有的数据库作为一个大池,所有用户都随机写入这个大池。

    这种设计就好像greenplum和hawq的设计理念。greenplum是大池思想,hawq是分而治之思想。

    行为数据表结构

    主表结构:

    create table bptest  
    (  
      ts timestamptz,   -- 行为发生时间  
      content text    -- 行为内容  
    );        
    

    每个用户的表名为bptest_$uid_$yyyymmdd

    结构和索引与主表保持一致。

    行为数据索引

    ts字段的存储顺序与值的顺序有非常强的线性相关性,采用块级索引。

    BRIN索引相比btree索引节省几百倍空间,同时提升写入性能。

    create index idx_bptest_ts on bptest using brin(ts);  -- TS字段块级索引  
    

    数据分片设计

    每个分片属于一个组,每个UID的数据随机的写入一个指定组的所有分片。

    如果用户觉得这样设计比较麻烦,可以将所有的数据库作为一个大池,所有用户都随机写入这个大池。

    就好像greenplum和hawq的设计理念。greenplum是大池思想,hawq是分而治之思想。

    数据汇聚设计

    当需要查询某个UID的行为数据时,并行查询所有分片的数据,按ts字段merge sort并返回。

    merge sort可以放在数据库中实现,也可以在应用层实现。

    数据merge sort设计(建议业务层实现,以提升性能)

    如果merge sort放在数据库层实现,可以使用PostgreSQL 10的postgres_fdw,每个UID的每个分片对应一张FDW TABLE,挂在UID对应的父表中。

    当查询父表时,按TS排序,会使用merge sort。

    merge sort功能详见:

    《PostgreSQL 10.0 preview 性能增强 - mergesort(Gather merge)》

    pic

    排序下推功能详见:

    《PostgreSQL 10.0 preview sharding增强 - pushdown 增强》

    pic

    如果在应用层实现,方法与之类似,并行的查询UID对应的所有分片,每个分片都是有order by返回,在应用层使用merge sort的方法返回给客户端。

    数据写入schema less设计(建议业务层实现,以提高性能)

    由于每个UID对应若干张表bptest_$uid_$yyyymmdd,我们可以在数据库端设计类似mongo的schemaless写入风格:

    有表时则插入,没有表时则创建后再插入。

    实现方法详见

    《PostgreSQL schemaless 的实现(类mongodb collection)》

    创建一个自动建表的函数,用于自动创建目标表。

    create or replace function create_schemaless(target name) returns void as $$    
    declare    
    begin    
      execute format('create table if not exists %I (like bptest including all)', target);    
    exception when others then    
      return;    
    end;    
    $$ language plpgsql strict;    
    

    创建一个插入数据的函数,使用动态SQL,如果遇到表不存在的错误,则调用建表函数进行建表。

    create or replace function ins_schemaless(uid int, ts timestamptz, content text) returns void as $$    
    declare   
      target name := 'bptest_'||uid||'_'||to_char(ts,'yyyymmdd');  
    begin    
      execute format('insert into %I values (%L, %L)', target, ts, content);    
      exception     
        WHEN SQLSTATE '42P01' THEN     
        perform create_schemaless(target);    
        execute format('insert into %I values (%L, %L)', target, ts, content);     
    end;    
    $$ language plpgsql strict;    
    

    数据库端的schemaless会牺牲一部分性能,因为无法使用绑定变量。

    建议业务层实现schemaless(自动拼接表名,自动建表),以提高性能。

    历史数据处理设计

    历史数据,可以清除,直接DROP分表即可(bptest_$uid_$yyyymmdd)。

    如果有保留数据的需求,可以通过阿里云RDS PostgreSQL的OSS_FDW接口将数据写入OSS对象存储永久保存,要读取时,通过FDW读取。

    云端存储与计算分离用法:

    《RDS PostgreSQL : 使用 oss_fdw 读写OSS对象存储》

    《HybridDB PostgreSQL : 使用 oss_fdw 读写OSS对象存储》

    分析需求

    如果有审计日志的分析需求,可以将RDS PostgreSQL数据写入OSS,通过HybridDB for PostgreSQL进行分析。

    性能测试

    环境准备

    与上文 《行为、审计日志 (实时索引/实时搜索)建模 - 最佳实践》 一样,使用同样的服务器,依旧是12个实例。

    1. 环境变量配置

    vi env_pg10.sh   
    export PS1="$USER@`/bin/hostname -s`-> "  
    export PGPORT=$(($1+1920))  
    export PGDATA=/disk$1/digoal/pgdata/pg_root$(($1+1920))  
    export LANG=en_US.utf8  
    export PGHOME=/home/digoal/pgsql10_8k  
    export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH  
    export LD_RUN_PATH=$LD_LIBRARY_PATH  
    export DATE=`date +"%Y%m%d%H%M"`  
    export PATH=$PGHOME/bin:$PATH:.  
    export MANPATH=$PGHOME/share/man:$MANPATH  
    export PGHOST=127.0.0.1  
    export PGUSER=postgres  
    export PGDATABASE=postgres  
    alias rm='rm -i'  
    alias ll='ls -lh'  
    unalias vi  
    

    2. 初始化SQL

    vi init.sql  
      
    -- 清除环境  
    drop table bptest cascade;  
      
    -- 初始父表  
    create table bptest  
    (  
      ts timestamptz,   -- 行为发生时间  
      content text    -- 行为内容  
    );   
      
    -- 建立索引  
    -- create index idx_bptest_ts on bptest using brin(ts) with(pages_per_range=1);  -- TS字段块级索引,如果不需要按ts排序输出结果,建议使用brin索引  
    create index idx_bptest_ts on bptest using btree(ts) ;  -- 如果需要按ts sort输出结果,建议使用btree  
      
    -- schemaless相关,自动建表函数  
    create or replace function create_schemaless(target name) returns void as $$    
    declare    
    begin    
      execute format('create table if not exists %I (like bptest including all) inherits(bptest)', target);    
      -- 这里可以设置列的statistic  
    exception when others then    
      return;    
    end;    
    $$ language plpgsql strict;    
      
    -- 数据写入函数  
    create or replace function ins_schemaless(uid int, ts timestamptz, content text) returns void as $$    
    declare   
      target name := 'bptest_'||uid||'_'||to_char(ts,'yyyymmdd');  
    begin    
      execute format('insert into %I values (%L, %L)', target, ts, content);    
      exception     
        WHEN SQLSTATE '42P01' THEN     
        perform create_schemaless(target);    
        execute format('insert into %I values (%L, %L)', target, ts, content);     
    end;    
    $$ language plpgsql strict;    
      
    -- 产生随机字符串的函数, 本例的CPU消耗大量来自这个函数  
    CREATE OR REPLACE FUNCTION public.gen_rand_str(integer)  
     RETURNS text  
     LANGUAGE sql  
     STRICT  
    AS $function$  
      select string_agg(a[(random()*6)::int+1],'') from generate_series(1,$1), (select array['a','b','c','d','e','f',' ']) t(a);  
    $function$;  
      
    -- 分页评估函数, 用于评估返回多少条记录,尽量避免使用count(*)估算精确值  
    CREATE FUNCTION count_estimate(query text) RETURNS INTEGER AS    
    $func$    
    DECLARE    
        rec   record;    
        ROWS  INTEGER;    
    BEGIN    
        FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP    
            ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');    
            EXIT WHEN ROWS IS NOT NULL;    
        END LOOP;    
        RETURN ROWS;    
    END    
    $func$ LANGUAGE plpgsql;    
    

    初始化每个数据库实例

    psql -f ./init.sql -p 1921  
    ...  
    psql -f ./init.sql -p 1932  
    

    写入压测

    12个库,100个UID。

    每个UID每个库写入1000万记录,每个UID总共写入1.2亿,所有UID总共写入120亿记录。

    使用gen_rand_str生成指定长度的随机字符串。

    vi test.sql  
      
      
    \set uid random(1,100)  
    select ins_schemaless(:uid, now(), gen_rand_str(512));  
    

    测试脚本

    vi test.sh  
      
      
    for ((i=1;i<13;i++))  
    do  
      . /home/digoal/env_pg10.sh ${i}  
      nohup pgbench -M prepared -n -r -P 3 -f ./test.sql -c 10 -j 10 -t 100000000 >/tmp/bptest_${i}.log 2>&1 &  
    done  
    

    测试结果

    写入性能:

    1. 使用brin索引时 9.47万/s

    2. 使用btree索引时 7.9万/s

    3. 服务器资源开销:

    CPU:  
      
    Cpu(s): 90.7%us,  7.1%sy,  0.1%ni,  0.4%id,  0.1%wa,  0.0%hi,  1.6%si,  0.0%st  
      
    IOUITL:  
      
    SSD: 50 %    
    

    瓶颈分析

    1. 大部分CPU开销在产生随机串的函数中,所以实际场景,CPU的消耗会小很多。

    如下

    perf top -ag  
      
                60953.00  6.0% ExecInterpExpr                          /home/digoal/pgsql10_8k/bin/postgres  
                33647.00  3.3% AllocSetAlloc                           /home/digoal/pgsql10_8k/bin/postgres  
                27560.00  2.7% advance_aggregates                      /home/digoal/pgsql10_8k/bin/postgres  
                22894.00  2.3% base_yyparse                            /home/digoal/pgsql10_8k/bin/postgres  
                21976.00  2.2% SearchCatCache                          /home/digoal/pgsql10_8k/bin/postgres  
                21768.00  2.2% array_seek                              /home/digoal/pgsql10_8k/bin/postgres  
                20957.00  2.1% appendBinaryStringInfo                  /home/digoal/pgsql10_8k/bin/postgres  
                19912.00  2.0% memcpy                                  /lib64/libc-2.12.so                     
                17058.00  1.7% array_get_element                       /home/digoal/pgsql10_8k/bin/postgres  
                14916.00  1.5% heap_form_minimal_tuple                 /home/digoal/pgsql10_8k/bin/postgres  
                13617.00  1.3% heap_fill_tuple                         /home/digoal/pgsql10_8k/bin/postgres  
                12201.00  1.2% __rint                                  /lib64/libm-2.12.so                     
                10938.00  1.1% palloc0                                 /home/digoal/pgsql10_8k/bin/postgres  
                10683.00  1.1% MemoryContextAllocZeroAligned           /home/digoal/pgsql10_8k/bin/postgres  
                10591.00  1.0% ExecStoreMinimalTuple                   /home/digoal/pgsql10_8k/bin/postgres  
                10292.00  1.0% string_agg_transfn                      /home/digoal/pgsql10_8k/bin/postgres  
                10202.00  1.0% advance_transition_function             /home/digoal/pgsql10_8k/bin/postgres  
                 9973.00  1.0% core_yylex                              /home/digoal/pgsql10_8k/bin/postgres  
    

    2. bcache问题

    bcache垃圾回收时,对IO的影响非常严重。

      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND    
    40207 root      20   0     0    0    0 R 100.0  0.0  34:47.86 [bch_gc-0b34a79a]  
    

    await已经到秒级

    Device:         rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s avgrq-sz avgqu-sz   await  svctm  %util  
    bcache11          0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00  
    bcache10          0.00  6054.00    1.00  105.00     8.00 48640.00   458.94    17.19  157.73   9.43 100.00  
    bcache9           0.00  2431.00    0.00  100.00     0.00 29408.00   294.08     4.22  102.87   9.86  98.60  
    bcache8           0.00  4013.00    0.00   85.00     0.00 37352.00   439.44    10.04  174.19  11.76 100.00  
    bcache7           0.00  1661.00    0.00   58.00     0.00 12952.00   223.31     2.34   53.47  17.02  98.70  
    bcache6           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00  
    bcache5           0.00    48.00    0.00   71.00     0.00 44896.00   632.34   183.24 2124.06  14.08 100.00  
    bcache4           0.00  6506.00    0.00  211.00     0.00 56312.00   266.88    74.89  488.20   4.74 100.00  
    bcache3           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00  
    bcache2           0.00  5502.00    0.00  103.00     0.00 44168.00   428.82   144.85  709.10   9.71 100.00  
    bcache1           0.00 12302.00    0.00   34.00     0.00 13464.00   396.00    84.11  877.03  29.41 100.00  
    bcache0           0.00 15148.00    0.00  260.00     0.00 42504.00   163.48    47.39  155.56   3.85 100.00  
    

    3. 配置了smooth checkpoint后,checkpoint已经没有问题, sync时间非常短暂。

    2017-05-23 10:49:55.749 CST,,,25095,,59239d76.6207,9,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8369",""  
    2017-05-23 10:50:25.434 CST,,,25095,,59239d76.6207,10,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint complete: wrote 233036 buffers (5.6%); 0 WAL file(s) added, 0 removed, 131 recycled; write=29.250 s, sync=0.018 s, total=29.685 s; sync files=300, longest=0.004 s, average=0.000 s; distance=2145650 kB, estimate=2145650 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8451",""  
    2017-05-23 10:54:55.529 CST,,,25095,,59239d76.6207,11,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8369",""  
    2017-05-23 10:57:59.222 CST,,,25095,,59239d76.6207,12,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint complete: wrote 191434 buffers (4.6%); 0 WAL file(s) added, 0 removed, 131 recycled; write=118.012 s, sync=59.816 s, total=183.693 s; sync files=300, longest=16.126 s, average=0.199 s; distance=1752021 kB, estimate=2106288 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8451",""  
    2017-05-23 10:59:55.328 CST,,,25095,,59239d76.6207,13,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8369",""  
    2017-05-23 11:00:25.350 CST,,,25095,,59239d76.6207,14,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint complete: wrote 176278 buffers (4.2%); 0 WAL file(s) added, 0 removed, 107 recycled; write=29.688 s, sync=0.009 s, total=30.021 s; sync files=300, longest=0.003 s, average=0.000 s; distance=1630757 kB, estimate=2058734 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8451",""  
    2017-05-23 11:04:55.467 CST,,,25095,,59239d76.6207,15,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8369",""  
    2017-05-23 11:05:25.381 CST,,,25095,,59239d76.6207,16,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint complete: wrote 232981 buffers (5.6%); 0 WAL file(s) added, 0 removed, 99 recycled; write=29.555 s, sync=0.013 s, total=29.914 s; sync files=300, longest=0.006 s, average=0.000 s; distance=2142180 kB, estimate=2142180 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8451",""  
    2017-05-23 11:06:44.503 CST,,,36368,"127.0.0.1:44645",5923a744.8e10,1,"",2017-05-23 11:06:44 CST,,0,LOG,00000,"connection received: host=127.0.0.1 port=44645",,,,,,,,"BackendInitialize, postmaster.c:4178",""  
    2017-05-23 11:06:44.504 CST,"postgres","postgres",36368,"127.0.0.1:44645",5923a744.8e10,2,"authentication",2017-05-23 11:06:44 CST,13/223,0,LOG,00000,"connection authorized: user=postgres database=postgres",,,,,,,,"PerformAuthentication, postinit.c:272",""  
    2017-05-23 11:09:55.512 CST,,,25095,,59239d76.6207,17,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint starting: time",,,,,,,,"LogCheckpointStart, xlog.c:8369",""  
    2017-05-23 11:09:56.221 CST,"postgres","postgres",36368,"127.0.0.1:44645",5923a744.8e10,3,"idle",2017-05-23 11:06:44 CST,,0,LOG,00000,"disconnection: session time: 0:03:11.717 user=postgres database=postgres host=127.0.0.1 port=44645",,,,,,,,"log_disconnections, postgres.c:4500","psql"  
    2017-05-23 11:10:25.512 CST,,,25095,,59239d76.6207,18,,2017-05-23 10:24:54 CST,,0,LOG,00000,"checkpoint complete: wrote 232090 buffers (5.5%); 0 WAL file(s) added, 0 removed, 131 recycled; write=29.489 s, sync=0.141 s, total=29.999 s; sync files=300, longest=0.033 s, average=0.000 s; distance=2136269 kB, estimate=2141589 kB",,,,,,,,"LogCheckpointEnd, xlog.c:8451",""  
    

    查询压测

    单节点2100万记录。

    postgres=# select count(*) from bptest_1_20170522;  
      count     
    ----------  
     21106948  
    (1 row)  
    Time: 1776.889 ms (00:01.777)  
    

    查询需求:

    1. 范围查询,排序输出

    返回462万记录,2.5秒。

    postgres=# explain (analyze,verbose,timing,costs,buffers) select * from bptest_1_20170522 where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08' order by ts;  
                                                                                            QUERY PLAN                                                                                           
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
     Index Scan using bptest_1_20170522_ts_idx on public.bptest_1_20170522  (cost=0.44..2373942.95 rows=4631011 width=524) (actual time=0.015..2326.653 rows=4622534 loops=1)  
       Output: ts, content  
       Index Cond: ((bptest_1_20170522.ts >= '2017-05-22 19:04:19.05347+08'::timestamp with time zone) AND (bptest_1_20170522.ts <= '2017-05-22 20:04:19.05347+08'::timestamp with time zone))  
       Buffers: shared hit=4639344  
     Planning time: 0.207 ms  
     Execution time: 2578.147 ms  
    (6 rows)  
    Time: 2578.789 ms (00:02.579)  
    

    2. 范围+全文检索查询,排序输出

    返回2941196万记录,8.5秒。

    postgres=# explain (analyze,verbose,timing,costs,buffers) select * from bptest_1_20170522    
    where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08'   
    and content ~ 'abc' and content ~ 'bcd'   
    order by ts;  
                                                         QUERY PLAN                                                                                                    
    --------------------------------------------------------------------------------------------------------------------------  
     Gather Merge  (cost=1890834.11..1933136.32 rows=2870936 width=524) (actual time=6601.842..8136.187 rows=2941196 loops=1)  
       Output: ts, content  
       Workers Planned: 4  
       Workers Launched: 4  
       Buffers: shared hit=84046, temp read=102440 written=102588  
       ->  Sort  (cost=1890834.06..1892628.39 rows=717734 width=524) (actual time=6584.684..6804.063 rows=588239 loops=5)  
             Output: ts, content  
             Sort Key: bptest_1_20170522.ts  
             Sort Method: external merge  Disk: 313080kB  
             Buffers: shared hit=347169, temp read=501609 written=502338  
             Worker 0: actual time=6582.649..6803.139 rows=588224 loops=1  
               Buffers: shared hit=66037, temp read=100242 written=100388  
             Worker 1: actual time=6590.768..6813.019 rows=587934 loops=1  
               Buffers: shared hit=66168, temp read=100191 written=100337  
             Worker 2: actual time=6579.297..6799.509 rows=587915 loops=1  
               Buffers: shared hit=66014, temp read=100172 written=100318  
             Worker 3: actual time=6569.191..6785.155 rows=578030 loops=1  
               Buffers: shared hit=64904, temp read=98564 written=98707  
             ->  Parallel Bitmap Heap Scan on public.bptest_1_20170522  (cost=72481.78..1603389.84 rows=717734 width=524) (actual time=338.604..5182.340 rows=588239 loops=5)  
                   Output: ts, content  
                   Recheck Cond: ((bptest_1_20170522.ts >= '2017-05-22 19:04:19.05347+08'::timestamp with time zone) AND (bptest_1_20170522.ts <= '2017-05-22 20:04:19.05347+08'::timestamp with time zone))  
                   Rows Removed by Index Recheck: 19  
                   Filter: ((bptest_1_20170522.content ~ 'abc'::text) AND (bptest_1_20170522.content ~ 'bcd'::text))  
                   Rows Removed by Filter: 336268  
                   Heap Blocks: exact=7063 lossy=60173  
                   Buffers: shared hit=347141  
                   Worker 0: actual time=336.885..5215.415 rows=588224 loops=1  
                     Buffers: shared hit=66030  
                   Worker 1: actual time=337.105..5239.414 rows=587934 loops=1  
                     Buffers: shared hit=66161  
                   Worker 2: actual time=337.128..5213.433 rows=587915 loops=1  
                     Buffers: shared hit=66007  
                   Worker 3: actual time=337.078..5129.862 rows=578030 loops=1  
                     Buffers: shared hit=64897  
                   ->  Bitmap Index Scan on bptest_1_20170522_ts_idx  (cost=0.00..71764.05 rows=4631011 width=0) (actual time=336.215..336.215 rows=4622534 loops=1)  
                         Index Cond: ((bptest_1_20170522.ts >= '2017-05-22 19:04:19.05347+08'::timestamp with time zone) AND (bptest_1_20170522.ts <= '2017-05-22 20:04:19.05347+08'::timestamp with time zone))  
                         Buffers: shared hit=16810  
     Planning time: 1.996 ms  
     Execution time: 8560.577 ms  
    (39 rows)  
    Time: 8563.154 ms (00:08.563)  
    

    3. 分页数评估

    如果业务允许,建议使用评估值,评估值的准确性取决于统计信息的准确性,使用alter table 表名 alter column 列名 SET STATISTICS 1000可以调整列的统计精准度,默认为100。

    《论count与offset使用不当的罪名 和 分页的优化》

    评估记录数与实际记录数对比如下,足够精确:

    postgres=# \timing  
    Timing is on.  
      
    -- 评估记录数  
    postgres=# select count_estimate($$select * from bptest_1_20170522 where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08'$$);  
     count_estimate   
    ----------------  
            4631011  
    (1 row)  
    Time: 0.733 ms  
      
    -- 实际记录数  
    postgres=# select count(*) from bptest_1_20170522 where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08';  
      count    
    ---------  
     4622534  
    (1 row)  
    Time: 1389.424 ms (00:01.389)  
      
    -- 评估记录数  
    postgres=# select count_estimate($$select * from bptest_1_20170522    
    where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08'   
    and content ~ 'abc' and content ~ 'bcd' and content ~ 'cdef'$$);  
     count_estimate   
    ----------------  
             914755  
    (1 row)  
    Time: 3.713 ms  
      
    -- 实际记录数  
    postgres=# select count(*) from bptest_1_20170522    
    where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08'   
    and content ~ 'abc' and content ~ 'bcd' and content ~ 'cdef';  
     count    
    --------  
     962780  
    (1 row)  
    Time: 7756.863 ms (00:07.757)  
    

    4. 分页查询返回

    流式返回,返回10行仅需0.562毫秒。

    postgres=# begin;  
    BEGIN  
    Time: 0.170 ms  
    postgres=# declare cur cursor for select * from bptest_1_20170522    
    postgres-# where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08'   
    postgres-# and content ~ 'abc' and content ~ 'bcd'   
    postgres-# order by ts;  
    DECLARE CURSOR  
      
    postgres=# fetch 10 from cur;  
     .........................  
    (10 rows)  
    Time: 0.562 ms  
      
    下一页,fetch继续获取  
    

    如果要回翻,使用SCROLL游标

    postgres=# begin;  
    BEGIN  
    Time: 0.114 ms  
    postgres=# declare cur SCROLL cursor for select * from bptest_1_20170522    
    where ts between '2017-05-22 19:04:19.05347+08' and '2017-05-22 20:04:19.05347+08'   
    and content ~ 'abc' and content ~ 'bcd'   
    order by ts;  
    DECLARE CURSOR  
    Time: 2.362 ms  
      
    回翻  
    postgres=# fetch BACKWARD 10 from cur;  
    前翻  
    postgres=# fetch FORWARD 10 from cur;  
    

    小结

    性能指标

    1. 数据量:

    单个UID,单节点,一天2100万记录(12GB, 索引600MB)。(100个节点/分片,单个用户一天约21亿数据量)

    2. 写入性能

    2.1. 使用brin索引时 9.47万/s

    2.2. 使用btree索引时 7.9万/s

    3. 范围查询,排序输出

    返回462万记录,2.5秒。

    4. 范围+全文检索查询,排序输出

    返回294万记录,8.5秒。

    5. 分页数评估

    精确度:+- 5% 左右

    响应速度:1毫秒左右。

    6. 精确分页数

    与实际数据量、条件有关。1秒以上

    7. 分页查询

    范围+全文检索查询,排序输出: 每获取1000条记录约11毫秒。

    (与命中率有关),极端情况为处理所有记录,只有最后一条记录满足条件。

    随机分布与可用性、数据倾斜问题

    使用jdbc或libpq时,一个连接可以设置多个实例,将从先到后,自动选择一个可读写的实例。(相当于客户端自动failover)。

    配置示例,假设有4个数据库实例,可以配置4个数据源如下:

    db1: host1:port1,host2:port2,host3:port3,host4:port4  
      
    db2: host2,port2,host3:port3,host4:port4,host1:port1  
      
    db3: host3:port3,host4:port4,host1:port1,host2,port2  
      
    db4: host4:port4,host1:port1,host2:port2,host3:port3  
    

    当任意一个实例出现问题时,每个数据源还是能获取到下一个可用的连接,不会堵塞写入。

    当实例修复后,依旧使用首选实例。

    使用这种方法,可以最大化的提高可用性,无需备库。

    另外异常的实例活了之后,就会继续被首选,无需担心倾斜问题,因为不保留历史。时间会抹平倾斜问题。

    参考

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

    《PostgreSQL schemaless 的实现(类mongodb collection)》

    《PostgreSQL 最佳实践 - 水平分库(基于plproxy)》

    《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 2 教你RDS PG的水平分库》

    《PostgreSQL 10.0 preview 性能增强 - mergesort(Gather merge)》

    《PostgreSQL 10.0 preview sharding增强 - 支持Append节点并行》

    《PostgreSQL 10.0 preview sharding增强 - pushdown 增强》

    《PostgreSQL 10.0 preview sharding增强 - postgres_fdw 多节点异步并行执行》

    《PostgreSQL 9.6 sharding based on FDW & pg_pathman》

    《PostgreSQL 9.6 单元化,sharding (based on postgres_fdw) - 内核层支持前传》

    《PostgreSQL 9.6 sharding + 单元化 (based on postgres_fdw) 最佳实践 - 通用水平分库场景设计与实践》

    《PostgreSQL 物联网黑科技 - 瘦身几百倍的索引(BRIN index)》

    《论count与offset使用不当的罪名 和 分页的优化》

    《RDS PostgreSQL : 使用 oss_fdw 读写OSS对象存储》

    《HybridDB PostgreSQL : 使用 oss_fdw 读写OSS对象存储》

    《PostgreSQL 10.0 preview 功能增强 - libpq支持多主机连接(failover,LB)让数据库HA和应用配合更紧密》

    展开全文
  • 摘要: 标签 PostgreSQL , ES , 搜索引擎 , 全文检索 , 日志分析 , 倒排索引 , 优化 , 分区 , 分片 , 审计日志 , 行为日志 , schemaless 背景 在很多系统中会记录用户的行为日志,行为日志包括浏览行为、社交行为、...
  • MySQL 数据库的日志审计

    千次阅读 2019-02-27 15:17:35
    简单实现 MySQL 数据库的日志审计 数据分析与开发 2018-12-28 (给数据分析与开发加星标,提升数据技能) 来源:freebuf - si1ence www.freebuf.com/articles/es/192062.html 0x0 背景 由于MySQL社区版没有自带的...
  • 时间2018-12-23 08:01:11FreeBuf ...由于MySQL社区版没有自带的审计功能或插件,对于等级保护当中对数据库管理的要求的就存在一定的不满足情况的,抛开条条框框不说数据库的日志是值得研究的,通过收集数...
  • graylog 是一个开源的专业的日志聚合、分析、审计、展示、预警的工具,跟 ELK 很相似,但是更简单,下面说一说 graylog 如何部署,使用,以及对 graylog 的工作流程做一个简单的梳理 本文篇幅比较长,一共使用了...
  • 文章目录xpack简介实验步骤ES集群的设置Kibana端...4)审计 实验步骤 实验目的: 为es集群开启 xpack安全认证,并在logstash和Kibana开启相应的用户认证。 实验背景: 172.25.2.7 ser7 es端 172.25.2.8 ser8 es端 172.25
  • 日志是系统的重要组成部分,用于记载系统的执行记录、审计、排查问题、数据采集等。日志需要持久化,通常日志仅仅需要持久化到磁盘,或者存储到ES,有些场景也需要将日志存储到MySQL中,例如重要的请求日志、用户...
  • ELK日志平台

    2021-04-10 17:58:33
    主要用户日志的收集,审计,过滤等。 ElasticSearch 是基于JAVA语言开发的。可以实现对数据内容进行校验。检索日志内容。可以创建索引。分布式。等等。 Logstash 是客户端工具,是基于JAVA语言编写的。主要是用于...
  • Elasticsearch安全管理插件通过用户认证、用户授权、加密通信、审计日志、IP过滤等多种手段来保证集群的安全。 领域(realms)和 领域链(realms chain) 领域(realms) x-pack的安全插件可以通过用户名密码、...
  • Search Guard是一个开源的安全插件,用于Elasticsearch和整个搜索提供加密、认证、授权和审计日志的ELK堆栈多租户和遵从性特性。官网地址为:https://search-guard.com/ 二、所需安装包下载 search-guard-2-2.4....
  • 审计。 部署过程 实验目的:为es集群开启 xpack安全认证,并在logstash和Kibana开启相应的用户认证。 实验环境: 主机 角色 server2 es的master 节点 server9 es主机 server10 es主机 server3 ...
  • 以文档较为详细的描述了elasticsearch体系所有工具,包括java开发的完整示例代码和过程,包括以下内容: 1.es常用命令 2.es配置详解 3.ik分词器安装 4.基于x-pack的权限模块安装配置 ... -5)审计日志开启
  • Elastic: 分析oracle数据

    2019-07-11 20:06:31
    本文主要讲解配置,默认环境已安装docker ,本示例分析的是oracle的审计日志 安装 ELK 套件 1.先修改宿主机的配置文件 切换到root用户修改配置sysctl.conf vi /etc/sysctl.conf 在尾行添加以下内容 vm.max_map_count...
  • Elasticsearch之数据安全

    2020-11-20 15:15:23
    ES安全问题分析 ES在默认安装后不提供任何形式的安全防护 ...日志审计 免费方案 设置Nginx反向代理 安装免费的Security插件 Search Guard - https://search-guard.com ReadOnly REST - https://github.com/s
  • 微服务架构方案 使用到的相关技术如下: 基础框架:springBoot+dubbo...日志中心:plumelog+es APM监控:pinpoint 某云服务(安全相关产品):web防火墙+态势感知+堡垒机+主机安全+数据库审计 总体架构图如下: ...
  • 37304 审计页面,处理一下换行和字段显示不完整问题 37378 地盘日志中查看所有日志时,二级导航无选中态 37379 地盘日志中查看所有日志时,日期控件icon不支持点击 37380 内禅地盘项目列表,区块点击刷新后列表字段...
  • 37304 审计页面,处理一下换行和字段显示不完整问题 37378 地盘日志中查看所有日志时,二级导航无选中态 37379 地盘日志中查看所有日志时,日期控件icon不支持点击 37380 内禅地盘项目列表,区块点击刷新后列表字段...
  • Elasticsearch安全认证

    千次阅读 2020-05-09 10:46:05
    6.8之前免费版本并不包含安全认证功能 ...日志审计 IP过滤 LDAP、PKI和活动目录身份验证 单点登录身份验证(SAML、Kerberos) 基于属性的权限控制 字段和文档级别安全性 静态数据加密支持 需要同时在ES
  • ES + Kibana、Zipkin ✅ 监控告警 Grafana ✅ 更新日志 2019-10-18: 1.使用nacos替代eureka为服务的注册中心 2.使用nacos替代apollo为服务的配置中心 3.引入使用sentinel替换掉hystrix,引入sentinel-...

空空如也

空空如也

1 2
收藏数 23
精华内容 9
热门标签
关键字:

es审计日志