sqlauditstore数据持久化工具

IT那活儿 2023-12-13

548

点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！

背景

为什么要使用sqlauditstore数据持久化工具？

gv$sql_audit数据保留存在淘汰机制，会自动将历史数据清理掉，避免该表数据过大，为了将将要淘汰的历史数据保留下来，这个时候，sqlauditstore工具发挥了很大的作用，对gv$sql_audit数据进行周期性采集并存储在本地，文件是csv格式，支持对文件进行压缩。

配置该工具的前提条件

一个工具实例以docker容器形式存在，负责采集单个OB集群的sql_audit数据。
运行时需提前准备一个单独的mysql的database，自行创建，存储必要的数据，包括一些连接集群的配置信息。
确认集群系统参数enable_sql_audit为true，且待持久化sql记录的租户的ob_enable_sql_audit变量值为“on”。
csv文件存储的本地文件系统大小要大，需要测试评估。

安装与运维

3.1 入口配置文件

每个工具实例需要引用两个入口配置文件，分别是obdw.yaml 和sqlauditstore.json。

其中前者是平台工具集成输出框架的入口配置，用户无需关心；后者包含了sqlauditstore核心的启动配置项。

3.1.1 配置文件实例

obdw.yaml

### 工具占用的端口号, 可自行指定为未被占用的端口
server_port: 8686

### basic auth认证帐密，自行指定即可
auth_user: ****
auth_password: ****

### 当前机器的物理IP
host_ip: 10.66.1.1

### 前置条件1中提到的database连接信息
data_source:
  db_user: 'root@obdw_meta#obcluster'
  db_password: '****'
  db_port: 3306
  db_host: ocp-obproxy.intra.env66.shuguang.com
  db_name: obdw

sqlauditstore.json

{
  "scanInterval": 10,
  "batchSendSize": 50000,
  "storeType": "sls",
  "logStoreTTL": 7,
  "shardCnt": 16,
  "endpoint": "data.cn-qingdao-env66-d01.sls.inter.env66.shuguang.com",
  "accessKey": "****",
  "accessSecret":"****",
  "logLevel": "info",
  "region":"cn-qingdao-env66-d01"
}

3.1.2 核心配置项说明

注：以下配置均为重启后生效。

配置项	是否必填	说明
scanInterval	是	采集间隔，单位秒。即间隔多少秒采集一次sql记录
batchSendSize	是	单次采集的sql记录条数。scanInterval和batchSendSize两个配置项需根据现场环境自行调整，可默认保持10s，50000条。调整依据见5.3
storeType	是	持久化模式，可选值 sls 或 file ，分别对应持久化到sls和持久化到csv文件
logLevel	否	不配置则默认是info，问题排查场景时可配置为debug
auditWhereCondition	否	查询v$sql_audit时的过滤条件，此配置项需填写符合mysql where子句语法规范的内容，配置后持久化的sql记录会是此条件过滤后的记录。此配置项的目的是精准控制需要持久化的sql类型或范围。
projectName	sls存储模式下必填	sls服务project名称，若需对接sddp，请填写“ali-yundun-sddp-ob”；不对接sddp的话，可以自行指定。
logStoreName	sls存储模式下必填	sls服务logstore名称，若需对接sddp，请填写“sddp_ob_log”；不对接sddp的话，可以自行指定。
logStoreTTL	sls存储模式下必填	sls服务中 logstore内数据的保存时长，建议值 7 （天）
shardCnt	sls存储模式下必填	logstore的分片数，建议值16
endpoint	sls存储模式下必填	sls服务访问域名
accessKey、accessSecret	sls存储模式下必填	sls服务访问ak，sk
region	sls存储模式，且对接sddp控制台必填	当前专有云region
auditDataScale	file存储模式下选填	可选值：1，2，3，4 ，不配置则默认值为3。值越大，持久化的字段信息越多，持久化后文件占用空间越高；4 为全量的sql_audit字段。详见5.1
storeUnit	file存储模式下选填	可选值：day 或 hour，不配置则默认值为 hour ，代表存储的文件按天分隔，还是按小时分隔
csvSeparator	file存储模式下选填	自定义csv文件的分隔符，不指定则默认为逗号。分隔符不支持长度大于1的字符串，若长度大于1 仅取第一个字符。可选值 "~"
dataCacheSize	否	用于指定缓冲数据空间大小，默认值200，限定最大值为400
fileSaveSize	否	用于指定持久化文件(压缩文件，tar.gz格式)的个数，默认值5；若压缩文件个数超过该配置，则会按照时间顺序依次清理旧的压缩文件，直至压缩文件个数不超过指定值

3.2 安装步骤

3.2.1 上传镜像文件到安装机器中，并加载镜像

####镜像压缩文件会因版本不同名称会有差别，请以实际发布的版本介质为准
docker load -i sqlauditstore.tar.gz

3.2.2 启动工具实例（容器）

注：请填写好两个入口配置文件，并在文件所在目录执行以下命令：

1）启动容器，容器名称建议sqlauditstore-{cluster_name} , cluster_name用实际的目标集群名替换，镜像id根据实际加载的镜像填写

sls 模式

docker run --network=host --name sqlauditstore-xxx -v 
${PWD}/obdw.yaml:/home/admin/obdw/conf/obdw.yaml -v 
${PWD}/sqlauditstore.json:/home/admin/obdw/plugins/sqlauditstore.json -v ${PWD}/log:/home/admin/obdw/logs -v etc/localtime:/etc/localtime:ro -d sqlauditstore:1.0.0

file 模式

docker run --network=host --name sqlauditstore-xxx -v 
${PWD}/obdw.yaml:/home/admin/obdw/conf/obdw.yaml -v 
${PWD}/sqlauditstore.json:/home/admin/obdw/plugins/sqlauditstore.json -v ${PWD}/log:/home/admin/obdw/logs -v 
${PWD}/store:/home/admin/obdw/store -v /etc/localtime:/etc/localtime:ro -d sqlauditstore:1.0.0

2）启动服务

docker exec -i sqlauditstore-xxx obdwctl start sqlauditstore -e obdw/plugins/sqlauditstore.json

3.2.3 启动成功判断

执行如下命令，命令结果出现如下running状态，则表示启动成功：

docker exec -i sqlauditstore-xxx obdwctl ls

3.2.4 录入目标集群的sys租户信息

在自行创建的database的cluster_sys表中，填入集群sys租户连接信息。

填写示例（以下信息的修改实时生效）：

id	1
cluster_name	test_obcluster (集群名中不用带 :xxx 部分)
connection_host	10.1.1.1 或 obproxy-xx.com
connection_port	2883 或3306
password	sys租户加密后的密码，加密方式见4.1.5
is_scanning	true 或者false 二选一，采集开关。可在不停止服务的情况下决定是否启用采集和持久化
tenant_ids	1001,1002,1003,1004 集群下租户id列表。此项不填写，会采集集群下所有租户（sys租户除外），建议尽量填写指定租户id列表，控制台持久化范围，减少资源占用。租户id获取方式见5.2

3.2.5 集群sys租户密码加密

1）进入容器内

docker exec -it sqlauditstore-xxx bash

2）容器内建一个空文件，并将sys租户明文密码写入进文件

echo "*****" > plain.txt

3）容器内执行下面的命令会返回一串密文

obdwctl encrypt -f plain.txt

示例：

3.3 运维

3.3.1 停止服务运行

docker exec -i sqlauditstore-xxx obdwctl stop sqlauditstore

3.3.2 修改sqlauditstore.json 后重启持久化

docker exec -i sqlauditstore-xxx obdwctl stop sqlauditstore
docker exec -i sqlauditstore-xxx obdwctl start sqlauditstore -e obdw/plugins/sqlauditstore.json

3.3.3 查看运行日志

###在启动容器命令所在目录执行
tail -f logs/sqlaudit-store.log

日志示例：

日志说明：

日志文件中，“before scan tenantMap” 代表一次采集过程前，每个租户在每个observer 上v$sql_audit中的request_id 值。“after scan tenantMap” 代表一个采集结束后，每个租户在每个observer 上v$sql_audit中的request_id 值。从两个map可以看出每次采集 v$sql_audit中每个租户的request_id 增量，这个增量正常情况会小于或等于“batchSendSize”配置的值。

3.3.4 查看持久化的sql记录

sls模式，请直接登陆SLS控制台进入对应的project 和logstore 查看具体sql日志
file模式，在启动docker容器命令的目录下挂载着一个store目录，目录下内容示例

此目录在工具正常运行一段时间后，会出现一个文件夹和多个压缩文件。文件夹和压缩文件的名称均按时间戳格式命令。若“storeUnit” 配置为“hour”，则目录名格式为：年月日小时；若若“storeUnit” 配置为“day”，则目录名格式为：年月日。工具当前的持久化数据都会写入和当前时间相对应的文件夹中，当前时间进入新的时间单元后会自动新建新的时间戳文件，相邻最近的历史文件夹会自动压缩成gz 文件（压缩比为8:1）。当前时间对应的文件夹下的文件示例:

如示例所示：此文件夹下存放了多个csv 文件，文件名称格式为：{集群名}-{租户ID}.csv 。每个csv文件里各自存放该时段集群中某个租户的sql记录。

注：store目录下的持久化文件(压缩文件 tar.gz格式)，可通过sqlauditstore.json中配置项fileSaveSize来指定保存的压缩文件个数，工具运行期间会自动按时间顺序清理旧的压缩文件，直至压缩文件个数不超过指定值。

如何发现数据漏采以及采取的措施

4.1 漏采时的现象

出现漏采时，sqlaudit-store.log中回出现以下字样的WARN日志：

tenant: 1001 sql audit on observer:10.0.0.1 scan 
exceed:50000, new requestId:280000, old requestId:200000, min requestId:260000

此条日志含义：在IP为10.0.0.1的observer节点上，以scanInterval为间隔进行采集，前一次采集完工具内部维护此租户的request_id为200000，经过这一次采集后发现request_id增长至280000，request_id增量超过了设置的batchSendSize值50000，而此时10.0.0.1节点上v$sql_audit中实时查到的最小的request_id为260000。

4.2 漏采后采取的措施

可以适当调大BatchSendSize（如从5万增至10万），或调小ScanInterval（采集间隔，但仍建议scanInterval最小要大于5），加速采集。

4.3 observer发生重启时在工具日志中的体现

observer node restart, observer: 10.0.0.1, old request id 200000, new request id 200

出现这条WARN 日志说明，request_id没有保持递增，被重置为0后再保持递增。

工具的资源占用评估

经测试，工具实例运行时对目标集群主机性能，sys租户性能，业务租户sql耗时基本无影响。

5.1 影响工具的资源占用因素

1）指定的集群tenant_ids 范围，越多资源占用越高。
2）每个租户自身压力高低，常见指标如tps，tps越高资源占用越高。
3）file模式下，auditDataScale越大，资源占用越高。
4）适当配置auditWhereCondition 过滤条件，减少不必要的sql的持久化会降低资源消耗。

5.2 测试场景和生产场景下资源分配情况

测试场景：至少4C8G

生产场景：

集群下指定采集的租户个数	cpu	memory
<= 10	8C	16G
<= 20	12C	16G
>20	16C或以上	32G

说明：上述内容为一个工具实例的资源最少占用量。资源实际占用量和现场环境每个租户承载的压力、租户个数正相关，请根据实际使用情况调整。

持久化CSV文件导入到其他数据库

file模式下持久化的csv文件可通过obloader导入到第三方db中，工具本身不包含这部分能力。一句话描述：在第三方db中建好表，安装obloader执行导入命令，将持久化的csv文件导入到表中。

6.1 建表语句

请根据工具启动时配置的 auditDataScale 值选用对应的建表语句，表名可自行指定。

###  auditDataScale=1
create table `base_audit` (
   `cluster_name` varchar(64) NOT NULL,
   `TENANT_ID` bigint(20) NOT NULL,
   `DB_NAME` varchar(128) DEFAULT NULL,
   `USER_NAME` varchar(64) DEFAULT NULL,
   `CLIENT_IP` varchar(46) DEFAULT NULL,
   `USER_CLIENT_IP` varchar(46) DEFAULT NULL,
   `QUERY_SQL` longtext DEFAULT NULL,
   `REQUEST_TIME` bigint(20) DEFAULT NULL,
   `REQUEST_ID` bigint(20) NOT NULL,
   `SVR_IP` varchar(46) NOT NULL,
   `SVR_PORT` bigint(20) NOT NULL,
    PRIMARY KEY (`SVR_IP`, `SVR_PORT`, `TENANT_ID`, `REQUEST_ID`)
) DEFAULT CHARSET = utf8mb4;


###  auditDataScale=2
create table `medium_audit` (
   `cluster_name` varchar(64) NOT NULL,
   `TENANT_ID` bigint(20) NOT NULL,
   `DB_NAME` varchar(128) DEFAULT NULL,
   `USER_NAME` varchar(64) DEFAULT NULL,
   `CLIENT_IP` varchar(46) DEFAULT NULL,
   `USER_CLIENT_IP` varchar(46) DEFAULT NULL,
   `QUERY_SQL` longtext DEFAULT NULL,
   `REQUEST_TIME` bigint(20) DEFAULT NULL,
   `REQUEST_ID` bigint(20) NOT NULL,
   `SVR_IP` varchar(46) NOT NULL,
   `SVR_PORT` bigint(20) NOT NULL,
   `ELAPSED_TIME` bigint(20) DEFAULT NULL,
   `SID` bigint(20) unsigned DEFAULT NULL,
   `SQL_ID` varchar(32) DEFAULT NULL,
   `TRACE_ID` varchar(128) DEFAULT NULL,
   `AFFECTED_ROWS` bigint(20) DEFAULT NULL,
   `RETURN_ROWS` bigint(20) DEFAULT NULL,
   `RET_CODE` bigint(20) DEFAULT NULL,
   `PLAN_TYPE` bigint(20) DEFAULT NULL,
   `PLAN_HASH` bigint(20) unsigned  DEFAULT NULL,
   `PLAN_ID` bigint(20) DEFAULT NULL,
   `IS_HIT_PLAN` tinyint(4) NOT NULL,
    PRIMARY KEY (`SVR_IP`, `SVR_PORT`, `TENANT_ID`, `REQUEST_ID`)
) DEFAULT CHARSET = utf8mb4;

###  auditDataScale=3
create table `full_audit` (
   `cluster_name` varchar(64) NOT NULL,
   `TENANT_ID` bigint(20) NOT NULL,
   `DB_NAME` varchar(128) DEFAULT NULL,
   `USER_NAME` varchar(64) DEFAULT NULL,
   `CLIENT_IP` varchar(46) DEFAULT NULL,
   `USER_CLIENT_IP` varchar(46) DEFAULT NULL,
   `QUERY_SQL` longtext DEFAULT NULL,
   `REQUEST_TIME` bigint(20) DEFAULT NULL,
   `REQUEST_ID` bigint(20) NOT NULL,
   `SVR_IP` varchar(46) NOT NULL,
   `SVR_PORT` bigint(20) NOT NULL,
   `ELAPSED_TIME` bigint(20) DEFAULT NULL,
   `SID` bigint(20) unsigned DEFAULT NULL,
   `SQL_ID` varchar(32) DEFAULT NULL,
   `TRACE_ID` varchar(128) DEFAULT NULL,
   `AFFECTED_ROWS` bigint(20) DEFAULT NULL,
   `RETURN_ROWS` bigint(20) DEFAULT NULL,
   `RET_CODE` bigint(20) DEFAULT NULL,
   `PLAN_TYPE` bigint(20) DEFAULT NULL,
   `PLAN_HASH` bigint(20) unsigned  DEFAULT NULL,
   `PLAN_ID` bigint(20) DEFAULT NULL,
   `IS_HIT_PLAN` tinyint(4) NOT NULL,
   `QUEUE_TIME` bigint(20) DEFAULT NULL,
   `EXECUTE_TIME` bigint(20) DEFAULT NULL,
   `TRANSACTION_HASH` bigint(20) unsigned DEFAULT NULL,
   `GET_PLAN_TIME` bigint(20) DEFAULT NULL,
   `NET_TIME` bigint(20) DEFAULT NULL,
   `PARTITION_CNT` bigint(20) DEFAULT NULL,
   `ROW_CACHE_HIT` bigint(20) DEFAULT NULL,
   `RETRY_CNT` bigint(20) DEFAULT NULL,
   `REQUEST_MEMORY_USED` bigint(20) DEFAULT NULL,
   `IS_BATCHED_MULTI_STMT` tinyint(4) DEFAULT NULL,
   `LOCK_FOR_READ_TIME` bigint(20) DEFAULT NULL,
   `NET_WAIT_TIME` bigint(20) DEFAULT NULL,
   `DECODE_TIME` bigint(20) DEFAULT NULL,
   `MEMSTORE_READ_ROW_COUNT` bigint(20) DEFAULT NULL,
   `SSSTORE_READ_ROW_COUNT` bigint(20) DEFAULT NULL,
   `WAIT_CLASS_ID` bigint(20) DEFAULT NULL,
   `WAIT_CLASS#` bigint(20) DEFAULT NULL,
   `WAIT_CLASS` varchar(64) DEFAULT NULL,
   `WAIT_TIME_MICRO` bigint(20) DEFAULT NULL,
   `TOTAL_WAIT_TIME_MICRO` bigint(20) DEFAULT NULL,
   PRIMARY KEY (`SVR_IP`, `SVR_PORT`, `TENANT_ID`, `REQUEST_ID`)
) DEFAULT CHARSET = utf8mb4;

###  auditDataScale=4
create table `all_sql_audit` (
   `cluster_name` varchar(64) NOT NULL,
   `TENANT_ID` bigint(20) NOT NULL,
   `DB_NAME` varchar(128) DEFAULT NULL,
   `USER_NAME` varchar(64) DEFAULT NULL,
   `CLIENT_IP` varchar(46) DEFAULT NULL,
   `USER_CLIENT_IP` varchar(46) DEFAULT NULL,
   `QUERY_SQL` longtext DEFAULT NULL,
   `REQUEST_TIME` bigint(20) DEFAULT NULL,
   `REQUEST_ID` bigint(20) NOT NULL,
   `SVR_IP` varchar(46) NOT NULL,
   `SVR_PORT` bigint(20) NOT NULL,
   `ELAPSED_TIME` bigint(20) DEFAULT NULL,
   `SID` bigint(20) unsigned DEFAULT NULL,
   `SQL_ID` varchar(32) DEFAULT NULL,
   `TRACE_ID` varchar(128) DEFAULT NULL,
   `AFFECTED_ROWS` bigint(20) DEFAULT NULL,
   `RETURN_ROWS` bigint(20) DEFAULT NULL,
   `RET_CODE` bigint(20) DEFAULT NULL,
   `PLAN_TYPE` bigint(20) DEFAULT NULL,
   `PLAN_HASH` bigint(20) unsigned  DEFAULT NULL,
   `PLAN_ID` bigint(20) DEFAULT NULL,
   `IS_HIT_PLAN` tinyint(4) NOT NULL,
   `QUEUE_TIME` bigint(20) DEFAULT NULL,
   `EXECUTE_TIME` bigint(20) DEFAULT NULL,
   `TRANSACTION_HASH` bigint(20) unsigned DEFAULT NULL,
   `GET_PLAN_TIME` bigint(20) DEFAULT NULL,
   `NET_TIME` bigint(20) DEFAULT NULL,
   `PARTITION_CNT` bigint(20) DEFAULT NULL,
   `ROW_CACHE_HIT` bigint(20) DEFAULT NULL,
   `RETRY_CNT` bigint(20) DEFAULT NULL,
   `REQUEST_MEMORY_USED` bigint(20) DEFAULT NULL,
   `IS_BATCHED_MULTI_STMT` tinyint(4) DEFAULT NULL,
   `LOCK_FOR_READ_TIME` bigint(20) DEFAULT NULL,
   `NET_WAIT_TIME` bigint(20) DEFAULT NULL,
   `DECODE_TIME` bigint(20) DEFAULT NULL,
   `MEMSTORE_READ_ROW_COUNT` bigint(20) DEFAULT NULL,
   `SSSTORE_READ_ROW_COUNT` bigint(20) DEFAULT NULL,
   `WAIT_CLASS_ID` bigint(20) DEFAULT NULL,
   `WAIT_CLASS#` bigint(20) DEFAULT NULL,
   `WAIT_CLASS` varchar(64) DEFAULT NULL,
   `WAIT_TIME_MICRO` bigint(20) DEFAULT NULL,
   `TOTAL_WAIT_TIME_MICRO` bigint(20) DEFAULT NULL,
   `SQL_EXEC_ID` bigint(20) DEFAULT NULL,
   `CLIENT_PORT` bigint(20) DEFAULT NULL,
   `EFFECTIVE_TENANT_ID` bigint(20) DEFAULT NULL,
   `USER_ID` bigint(20) DEFAULT NULL,
   `USER_GROUP` bigint(20) DEFAULT NULL,
   `DB_ID` bigint(20) unsigned  DEFAULT NULL,
   `QC_ID` bigint(20) unsigned  DEFAULT NULL,
   `DFO_ID` bigint(20) DEFAULT NULL,
   `SQC_ID` bigint(20) DEFAULT NULL,
   `WORKER_ID` bigint(20) DEFAULT NULL,
   `EVENT` varchar(64) DEFAULT NULL,
   `P1TEXT` varchar(64) DEFAULT NULL,
   `P1` bigint(20) unsigned  DEFAULT NULL,
   `P2TEXT` varchar(64) DEFAULT NULL,
   `P2` bigint(20) unsigned  DEFAULT NULL,
   `P3TEXT` varchar(64) DEFAULT NULL,
   `P3` bigint(20) unsigned  DEFAULT NULL,
   `LEVEL` bigint(20) DEFAULT NULL,
   `STATE` varchar(19) DEFAULT NULL,
   `TOTAL_WAITS` bigint(20) DEFAULT NULL,
   `RPC_COUNT` bigint(20) DEFAULT NULL,
   `IS_INNER_SQL` tinyint(4) NOT NULL,
   `IS_EXECUTOR_RPC` tinyint(4) NOT NULL,
   `APPLICATION_WAIT_TIME` bigint(20) unsigned  DEFAULT NULL,
   `CONCURRENCY_WAIT_TIME` bigint(20) unsigned  DEFAULT NULL,
   `USER_IO_WAIT_TIME` bigint(20) unsigned  DEFAULT NULL,
   `SCHEDULE_TIME` bigint(20) unsigned  DEFAULT NULL,
   `BLOOM_FILTER_CACHE_HIT` bigint(20) DEFAULT NULL,
   `BLOCK_CACHE_HIT` bigint(20) DEFAULT NULL,
   `BLOCK_INDEX_CACHE_HIT` bigint(20) DEFAULT NULL,
   `DISK_READS` bigint(20) DEFAULT NULL,
   `TABLE_SCAN` tinyint(4) NOT NULL,
   `CONSISTENCY_LEVEL` bigint(20) DEFAULT NULL,
   `EXPECTED_WORKER_COUNT` bigint(20) DEFAULT NULL,
   `USED_WORKER_COUNT` bigint(20) DEFAULT NULL,
   `SCHED_INFO` varchar(16384) DEFAULT NULL,
   `FUSE_ROW_CACHE_HIT` bigint(20) DEFAULT NULL,
   `PS_STMT_ID` bigint(20) DEFAULT NULL,
   `REQUEST_TYPE` bigint(20) DEFAULT NULL,
   `OB_TRACE_INFO` varchar(4096) DEFAULT NULL,
   `WAIT_TRX_MIGRATE_TIME` bigint(20) DEFAULT NULL,
   `PARAMS_VALUE` longtext DEFAULT NULL,
   PRIMARY KEY (`SVR_IP`, `SVR_PORT`, `TENANT_ID`, `REQUEST_ID`)
) DEFAULT CHARSET = utf8mb4;

6.2 obloader命令示例

./obloader --csv -h11.166.80.72 -P 2883 -c metacluster -t 
obdw_meta -u root -p 'xxxxx' -D test_obdw -f 
'/home/admin/yuhuai/obloader_test/csv_dir/oio_cluster-1004.csv' --table base_audit --sys-user root --sys-password 'xxxxx' --external-data --column-delimiter '"'