BCC在mysqld端的应用 性能测评

阿里云教程2天前发布 体育亚特兰大
2 0 0

github blog

测试环境：

  • 操作系统：Red Hat Enterprise Linux release 8.6
  • 内核：4.18.0-372.9.1.el8.x86_64
  • BCC版本：0.26+ sourceCode安装
  • mysql版本：5.7.39

函数原型：

所要探测的函数选用了mysql-server层的命令分发处理函数bool dispatch_command(THD *thd, const COM_DATA *com_data,enum enum_server_command command)
所探测函数原型如下：

bool dispatch_command(THD *thd, const COM_DATA *com_data,
                      enum enum_server_command command)
{
  bool error= 0;
  Global_THD_manager *thd_manager= Global_THD_manager::get_instance();
  DBUG_ENTER("dispatch_command");
  DBUG_PRINT("info", ("command: %d", command));

  /* SHOW PROFILE instrumentation, begin */
#if defined(ENABLED_PROFILING)
  thd->profiling.start_new_query();
#endif

  /* DTRACE instrumentation, begin */
  MYSQL_COMMAND_START(thd->thread_id(), command,
                      (char *) thd->security_context()->priv_user().str,
                      (char *) thd->security_context()->host_or_ip().str);
  ...
    /* DTRACE instrumentation, end */
  if (MYSQL_QUERY_DONE_ENABLED() && command == COM_QUERY)
  {
    MYSQL_QUERY_DONE(thd->is_error());
  }
  if (MYSQL_COMMAND_DONE_ENABLED())
  {
    MYSQL_COMMAND_DONE(thd->is_error());
  }

  /* SHOW PROFILE instrumentation, end */
#if defined(ENABLED_PROFILING)
  thd->profiling.finish_current_query();
#endif

  DBUG_RETURN(error);
}

客户端的sql请求均经过此函数分发至下游的解析器、优化器及存储引擎，对该函数添加hook，可实现简略版的mysql审计功能。

BCC工具：

使用BCC开发一个工具对上述函数的入口及返回添加hook.(demo偷懒使用了python client…)
demo中通过探测该函数获取线程id、sql执行耗时(ns)、用户名、host、ip、sql及sql size.

注：受限于ebpf数据结构大小限制，sql可能存在截断的情况，demo中未对截断的sql进行处理.

对于参数中的数据获取，demo中用了两种方法：

  • 数据结构不复杂的可直接移植结构体定义至ebpf,可强转后直接通过变量名获取数据(mysql struct def内均为mysql自有定义)
  • 数据结构复杂的(如THD对象)，通过对象的地址加所需变量的offsets直接获取

demo.py

#!/usr/bin/python

from __future__ import print_function
from bcc import BPF
import argparse
import ctypes as ct
import sys,time
import subprocess


# define BPF program
bpf_text = """
#include <uapi/linux/ptrace.h>
/**************************mysql struct def start*****************************/
enum enum_server_command
{
    COM_SLEEP,
    COM_QUIT,
    COM_INIT_DB,
    COM_QUERY,
    COM_FIELD_LIST,
    COM_CREATE_DB,
    COM_DROP_DB,
    COM_REFRESH,
    COM_SHUTDOWN,
    COM_STATISTICS,
    COM_PROCESS_INFO,
    COM_CONNECT,
    COM_PROCESS_KILL,
    COM_DEBUG,
    COM_PING,
    COM_TIME,
    COM_DELAYED_INSERT,
    COM_CHANGE_USER,
    COM_BINLOG_DUMP,
    COM_TABLE_DUMP,
    COM_CONNECT_OUT,
    COM_REGISTER_SLAVE,
    COM_STMT_PREPARE,
    COM_STMT_EXECUTE,
    COM_STMT_SEND_LONG_DATA,
    COM_STMT_CLOSE,
    COM_STMT_RESET,
    COM_SET_OPTION,
    COM_STMT_FETCH,
    COM_DAEMON,
    COM_BINLOG_DUMP_GTID,
    COM_RESET_CONNECTION,
    /* don t forget to update const char *command_name[] in sql_parse.cc */
    /* Must be last */
    COM_END
};

typedef struct st_com_init_db_data
{
    const char *db_name;
    unsigned long length;
} COM_INIT_DB_DATA;

#define MYSQL_SHUTDOWN_KILLABLE_CONNECT    (unsigned char)(1 << 0)
#define MYSQL_SHUTDOWN_KILLABLE_TRANS      (unsigned char)(1 << 1)
#define MYSQL_SHUTDOWN_KILLABLE_LOCK_TABLE (unsigned char)(1 << 2)
#define MYSQL_SHUTDOWN_KILLABLE_UPDATE     (unsigned char)(1 << 3)

#define LOCK_MODE_MASK  0xFUL
#define LOCK_TYPE_MASK    0xF0UL

enum mysql_enum_shutdown_level {
    SHUTDOWN_DEFAULT = 0,
    SHUTDOWN_WAIT_CONNECTIONS= MYSQL_SHUTDOWN_KILLABLE_CONNECT,
    SHUTDOWN_WAIT_TRANSACTIONS= MYSQL_SHUTDOWN_KILLABLE_TRANS,
    SHUTDOWN_WAIT_UPDATES= MYSQL_SHUTDOWN_KILLABLE_UPDATE,
    SHUTDOWN_WAIT_ALL_BUFFERS= (MYSQL_SHUTDOWN_KILLABLE_UPDATE << 1),
    SHUTDOWN_WAIT_CRITICAL_BUFFERS= (MYSQL_SHUTDOWN_KILLABLE_UPDATE << 1) + 1,
    KILL_QUERY= 254,
    KILL_CONNECTION= 255
};

typedef struct st_com_refresh_data
{
    unsigned char options;
} COM_REFRESH_DATA;

typedef struct st_com_shutdown_data
{
    enum mysql_enum_shutdown_level level;
} COM_SHUTDOWN_DATA;

typedef struct st_com_kill_data
{
    unsigned long id;
} COM_KILL_DATA;

typedef struct st_com_set_option_data
{
    unsigned int opt_command;
} COM_SET_OPTION_DATA;

typedef struct st_com_stmt_execute_data
{
    unsigned long stmt_id;
    unsigned long flags;
    unsigned char *params;
    unsigned long params_length;
} COM_STMT_EXECUTE_DATA;

typedef struct st_com_stmt_fetch_data
{
    unsigned long stmt_id;
    unsigned long num_rows;
} COM_STMT_FETCH_DATA;

typedef struct st_com_stmt_send_long_data_data
{
    unsigned long stmt_id;
    unsigned int  param_number;
    unsigned char *longdata;
    unsigned long length;
} COM_STMT_SEND_LONG_DATA_DATA;

typedef struct st_com_stmt_prepare_data
{
    const char *query;
    unsigned int length;
} COM_STMT_PREPARE_DATA;

typedef struct st_stmt_close_data
{
    unsigned int stmt_id;
} COM_STMT_CLOSE_DATA;

typedef struct st_com_stmt_reset_data
{
    unsigned int stmt_id;
} COM_STMT_RESET_DATA;

typedef struct st_com_query_data
{
    const char *query;
    unsigned int length;
} COM_QUERY_DATA;

typedef struct st_com_field_list_data
{
    unsigned char   *table_name;
    unsigned int    table_name_length;
    const unsigned char *query;
    unsigned int        query_length;
} COM_FIELD_LIST_DATA;

union COM_DATA {

    COM_INIT_DB_DATA com_init_db;
    COM_REFRESH_DATA com_refresh;
    COM_SHUTDOWN_DATA com_shutdown;
    COM_KILL_DATA com_kill;
    COM_SET_OPTION_DATA com_set_option;
    COM_STMT_EXECUTE_DATA com_stmt_execute;
    COM_STMT_FETCH_DATA com_stmt_fetch;
    COM_STMT_SEND_LONG_DATA_DATA com_stmt_send_long_data;
    COM_STMT_PREPARE_DATA com_stmt_prepare;
    COM_STMT_CLOSE_DATA com_stmt_close;
    COM_STMT_RESET_DATA com_stmt_reset;
    COM_QUERY_DATA com_query;
    COM_FIELD_LIST_DATA com_field_list;
};

struct String {
    char *m_ptr;
    size_t m_length;
    void *m_charset;
    u32 m_alloced_length;
    bool m_is_alloced;
};
/**************************mysql struct def end*****************************/

typedef struct perf_event{
    u32 tid;
    u32 size;
    u64 ts;
    char user[16];
    char ip[16];
    char host[24]; 
    char sql[256];
}COM_PERF_EVENT;

BPF_HASH(tid_dispatch_map, u32, COM_PERF_EVENT);

BPF_PERF_OUTPUT(events);


static inline void*   GET_SC_CTX_PTR_FROM_THD_PTR(void* thd)                    {return *(void**)(thd + 0x1198);}
static inline void*   GET_USER_PTR_FROM_SC_CTX_PTR(void* thd)                   {return thd + 0xa0;}
static inline void*   GET_HOST_PTR_FROM_SC_CTX_PTR(void* thd)                   {return thd + 0x20;}
static inline void*   GET_IP_PTR_FROM_SC_CTX_PTR(void* thd)                     {return thd + 0x40;}

int dispatch_command_entry(struct pt_regs *ctx) {

    enum  enum_server_command command_id = PT_REGS_PARM3(ctx);
    if (command_id != COM_QUERY) return 0;

    COM_PERF_EVENT event = {};
    event.tid = bpf_get_current_pid_tgid();

    union COM_DATA *com_data = (union COM_DATA *)PT_REGS_PARM2(ctx);
    event.size = com_data->com_query.length;
    bpf_probe_read_str(&event.sql, sizeof(event.sql), com_data->com_query.query);

    void *thd = (void*) PT_REGS_PARM1(ctx);

    void *sc_ctx = GET_SC_CTX_PTR_FROM_THD_PTR(thd);
    //user
    char *user = GET_USER_PTR_FROM_SC_CTX_PTR(sc_ctx);
    bpf_probe_read_str(&event.user, sizeof(event.user), user);
    //host
    struct String *host = (struct String *)GET_HOST_PTR_FROM_SC_CTX_PTR(sc_ctx);
    bpf_probe_read_str(&event.host, sizeof(event.host), host->m_ptr);
    //ip
    struct String *ip = (struct String *)GET_IP_PTR_FROM_SC_CTX_PTR(sc_ctx);
    bpf_probe_read_str(&event.ip, sizeof(event.ip), ip->m_ptr);

    event.ts = bpf_ktime_get_ns();

    //record dispatch
    tid_dispatch_map.insert(&event.tid,&event);

    return 0;
}

int dispatch_command_return(struct pt_regs *ctx) {
    u32 thread_id = bpf_get_current_pid_tgid();
    COM_PERF_EVENT *event = tid_dispatch_map.lookup(&thread_id);
    if (!event) return 0;
    event->ts = bpf_ktime_get_ns() - event->ts;
    events.perf_submit(ctx, event, sizeof(*event));
    tid_dispatch_map.delete(&thread_id);
    return 0;
}
"""

# initialize BPF
b = BPF(text=bpf_text)
b.attach_uprobe(name="/home/mysqld",  sym="_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command",fn_name= dispatch_command_entry )
b.attach_uretprobe(name="/home/mysqld",  sym="_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command",fn_name= dispatch_command_return )
def print_event(cpu, data, size):
    event = b["events"].event(data)
    print("%s %s %s %s %s %s %s" % (event.tid, event.ts, event.user, event.host, event.ip, event.sql, event.size))
b["events"].open_perf_buffer(print_event)
print("thread_id    time(ns)    user      host      ip       sql               sql_size")
while 1:
    try:
        b.perf_buffer_poll()
    except KeyboardInterrupt:
        exit()

执行探测工具python demo.py,连接mysql执行QUERY命令后获取数据如下：

[root@localhost doc]# python mysql.py
thread_id    time(ns)    user      host      ip       sql               sql_size
14704 677838 b skip-grants use  b localhost  b 127.0.0.1  b select @@version_comment limit 1  32
14704 464399 b skip-grants use  b localhost  b 127.0.0.1  b SELECT DATABASE()  17
14704 1882739 b skip-grants use  b localhost  b 127.0.0.1  b show databases  14
14704 475563 b skip-grants use  b localhost  b 127.0.0.1  b show tables  11
14704 1083073 b skip-grants use  b localhost  b 127.0.0.1  b show tables  11
14704 1083830 b skip-grants use  b localhost  b 127.0.0.1  b select * from pi_dra_config  27
14704 31102707 b skip-grants use  b localhost  b 127.0.0.1  b update pi_dra_config set cfg_key = "test" where id = 1  54

压测

使用sysbench进行读写压测，对比开启探测工具与不开启的情况，执行压测前清除mysql缓存,执行命令reset query cache;
压测命令：sysbench --mysql-user=root --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-password=Root2017# --events=400000 /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 --threads=80 run

开启探测工具数据如下：

sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 80
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            97496
        write:                           27856
        other:                           13928
        total:                           139280
    transactions:                        6964   (672.98 per sec.)
    queries:                             139280 (13459.70 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.3452s
    total number of events:              6964

Latency (ms):
         min:                                   25.36
         avg:                                  116.06
         max:                                  588.68
         95th percentile:                      297.92
         sum:                               808226.74

Threads fairness:
    events (avg/stddev):           87.0500/3.11
    execution time (avg/stddev):   10.1028/0.12

关闭探测工具数据如下：

sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 80
Initializing random number generator from current time


Initializing worker threads...

Threads started!

SQL statistics:
    queries performed:
        read:                            102480
        write:                           29280
        other:                           14640
        total:                           146400
    transactions:                        7320   (702.81 per sec.)
    queries:                             146400 (14056.17 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.4121s
    total number of events:              7320

Latency (ms):
         min:                                   21.88
         avg:                                  111.83
         max:                                  673.60
         95th percentile:                      331.91
         sum:                               818594.04

Threads fairness:
    events (avg/stddev):           91.5000/2.96
    execution time (avg/stddev):   10.2324/0.06

平均时延对比 开启/关闭：116.06ms/111.83ms 性能下降3.7% QPS

附：在centos7.6 — 3.x的内核中测试表现一致

小结

  • 使用BCC对mysqld添加hook实现审计功能对性能影响较低
  • 众多开源项目中在内核网络栈已实现过mysql协议的捕获解析，在mysqld端添加hook实现审计，除了可绕过网络层加密，优势并不明显
  • 用户进程版本不同时，获取数据的偏移量不同，动态生成ebpf代码到编译对主机性能有抢占，利用业务外的机器作为中心，统一对二进制文件解析及偏移量获取再分发ebpf字节码或许会更好。
© 版权声明
文章版权归作者所有，未经允许请勿转载。

相关文章

暂无评论

none
暂无评论...
添加小工具
点此为“正文侧边栏”添加小工具