虚谷数据库日志文件COMMAND.LOG记录接收到的SQL语句解读(以TPCC为例)

m0_38018848

已于 2023-09-20 16:35:19 修改

阅读量91

点赞数

文章标签：数据库 sql

于 2023-09-20 16:33:15 首次发布

本文链接：https://blog.csdn.net/m0_38018848/article/details/133086202

版权

1. 前言介绍

设置虚谷数据库的 reg_command = true，又 tpcc 涉及到增删改查合计 31 种类型的语句

DELETE FROM bmsql_new_order     WHERE no_w_id = ? AND no_d_id = ? AND no_o_id = ?
INSERT INTO bmsql_history (    h_c_id, h_c_d_id, h_c_w_id, h_d_id, h_w_id,     h_date, h_amount, h_data) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
INSERT INTO bmsql_new_order (    no_o_id, no_d_id, no_w_id) VALUES (?, ?, ?)
INSERT INTO bmsql_oorder (    o_id, o_d_id, o_w_id, o_c_id, o_entry_d,     o_ol_cnt, o_all_local) VALUES (?, ?, ?, ?, ?, ?, ?)
INSERT INTO bmsql_order_line (    ol_o_id, ol_d_id, ol_w_id, ol_number,     ol_i_id, ol_supply_w_id, ol_quantity,     ol_amount, ol_dist_info) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
SELECT c_data     FROM bmsql_customer     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
SELECT c_discount, c_last, c_credit, w_tax     FROM bmsql_customer     JOIN bmsql_warehouse ON (w_id = c_w_id)     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
SELECT cfg_value FROM bmsql_config  WHERE cfg_name = ?
SELECT c_first, c_middle, c_last, c_balance     FROM bmsql_customer     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
SELECT c_first, c_middle, c_last, c_street_1, c_street_2,        c_city, c_state, c_zip, c_phone, c_since, c_credit,        c_credit_lim, c_discount, c_balance     FROM bmsql_customer     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?     FOR UPDATE
SELECT c_id     FROM bmsql_customer     WHERE c_w_id = ? AND c_d_id = ? AND c_last = ?     ORDER BY c_first
SELECT d_name, d_street_1, d_street_2, d_city,        d_state, d_zip     FROM bmsql_district     WHERE d_w_id = ? AND d_id = ?
SELECT d_tax, d_next_o_id     FROM bmsql_district     WHERE d_w_id = ? AND d_id = ?     FOR UPDATE
SELECT i_price, i_name, i_data     FROM bmsql_item     WHERE i_id = ?
SELECT no_o_id     FROM bmsql_new_order     WHERE no_w_id = ? AND no_d_id = ?     ORDER BY no_o_id ASC
SELECT o_c_id     FROM bmsql_oorder     WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?
SELECT o_id, o_entry_d, o_carrier_id     FROM bmsql_oorder     WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?       AND o_id = (          SELECT max(o_id)               FROM bmsql_oorder               WHERE o_w_id = ? AND o_d_id = ? AND o_c_id = ?          )
SELECT ol_i_id, ol_supply_w_id, ol_quantity,        ol_amount, ol_delivery_d     FROM bmsql_order_line     WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?     ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number
select pp(?,?,?,?)
SELECT s_quantity, s_data,        s_dist_01, s_dist_02, s_dist_03, s_dist_04,        s_dist_05, s_dist_06, s_dist_07, s_dist_08,        s_dist_09, s_dist_10     FROM bmsql_stock     WHERE s_w_id = ? AND s_i_id = ?     FOR UPDATE
SELECT sum(ol_amount) AS sum_ol_amount     FROM bmsql_order_line     WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?
SELECT w_name, w_street_1, w_street_2, w_city,        w_state, w_zip     FROM bmsql_warehouse     WHERE w_id = ?
UPDATE bmsql_customer     SET c_balance = c_balance + ?,         c_delivery_cnt = c_delivery_cnt + 1     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
UPDATE bmsql_customer     SET c_balance = c_balance - ?,         c_ytd_payment = c_ytd_payment + ?,         c_payment_cnt = c_payment_cnt + 1,         c_data = ?     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
UPDATE bmsql_customer     SET c_balance = c_balance - ?,         c_ytd_payment = c_ytd_payment + ?,         c_payment_cnt = c_payment_cnt + 1     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
UPDATE bmsql_district     SET d_next_o_id = d_next_o_id + 1     WHERE d_w_id = ? AND d_id = ?
UPDATE bmsql_district     SET d_ytd = d_ytd + ?     WHERE d_w_id = ? AND d_id = ?
UPDATE bmsql_oorder     SET o_carrier_id = ?     WHERE o_w_id = ? AND o_d_id = ? AND o_id = ?
UPDATE bmsql_order_line     SET ol_delivery_d = ?     WHERE ol_w_id = ? AND ol_d_id = ? AND ol_o_id = ?
UPDATE bmsql_stock     SET s_quantity = ?, s_ytd = s_ytd + ?,         s_order_cnt = s_order_cnt + 1,         s_remote_cnt = s_remote_cnt + ?     WHERE s_w_id = ? AND s_i_id = ?
UPDATE bmsql_warehouse     SET w_ytd = w_ytd + ?     WHERE w_id = ?

统计，以五分钟运行时常 TPCC为例。
//The following five values must add up to 100
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
统计如下：
在这里插入图片描述

2.日志解读

[root@xugu210 XGLOG]# grep 'st12f40c2513' COMMAND* | more 
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:57     Prepare st12f40c2513 as SELECT c_data     FROM bmsql_customer     WHERE c_w_id = ? AND c_d_id = ? AND c_id = ?
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:57     ? st12f40c2513 as cursor cur12f40c25236 return 15        {PN=3,GN=1 <1,I4,41><2,I4,6><3,I4,912>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:57     ? st12f40c2513 as cursor cur12f40c25351 return 15        {PN=3,GN=1 <1,I4,70><2,I4,1><3,I4,1569>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:58     ? st12f40c2513 as cursor cur12f40c251129 return 15       {PN=3,GN=1 <1,I4,41><2,I4,4><3,I4,1961>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:58     ? st12f40c2513 as cursor cur12f40c251257 return 15       {PN=3,GN=1 <1,I4,41><2,I4,3><3,I4,840>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:58     ? st12f40c2513 as cursor cur12f40c251290 return 15       {PN=3,GN=1 <1,I4,41><2,I4,7><3,I4,1630>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:12:59     ? st12f40c2513 as cursor cur12f40c251381 return 15       {PN=3,GN=1 <1,I4,41><2,I4,5><3,I4,19>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:00     ? st12f40c2513 as cursor cur12f40c252608 return 15       {PN=3,GN=1 <1,I4,41><2,I4,10><3,I4,2935>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:00     ? st12f40c2513 as cursor cur12f40c252813 return 15       {PN=3,GN=1 <1,I4,41><2,I4,9><3,I4,603>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:01     ? st12f40c2513 as cursor cur12f40c253882 return 15       {PN=3,GN=1 <1,I4,41><2,I4,5><3,I4,796>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:01     ? st12f40c2513 as cursor cur12f40c254218 return 15       {PN=3,GN=1 <1,I4,41><2,I4,5><3,I4,23>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:02     ? st12f40c2513 as cursor cur12f40c254294 return 15       {PN=3,GN=1 <1,I4,41><2,I4,5><3,I4,2591>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:02     ? st12f40c2513 as cursor cur12f40c254460 return 15       {PN=3,GN=1 <1,I4,41><2,I4,1><3,I4,757>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:02     ? st12f40c2513 as cursor cur12f40c254769 return 15       {PN=3,GN=1 <1,I4,41><2,I4,3><3,I4,865>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:02     ? st12f40c2513 as cursor cur12f40c255015 return 15       {PN=3,GN=1 <1,I4,41><2,I4,4><3,I4,92>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:03     ? st12f40c2513 as cursor cur12f40c255294 return 15       {PN=3,GN=1 <1,I4,486><2,I4,4><3,I4,87>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:03     ? st12f40c2513 as cursor cur12f40c255303 return 15       {PN=3,GN=1 <1,I4,41><2,I4,5><3,I4,739>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:03     ? st12f40c2513 as cursor cur12f40c256263 return 15       {PN=3,GN=1 <1,I4,41><2,I4,2><3,I4,580>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:05     ? st12f40c2513 as cursor cur12f40c257740 return 15       {PN=3,GN=1 <1,I4,41><2,I4,3><3,I4,1934>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:05     ? st12f40c2513 as cursor cur12f40c257768 return 15       {PN=3,GN=1 <1,I4,41><2,I4,6><3,I4,2945>}
COMMAND_23_08_07_10_13_54.LOG:1 2       SYSDBA  6       127.0.0.1       2023-08-07 10:13:05     ? st12f40c2513 as cursor cur12f40c258313 return 15       {PN=3,GN=1 <1,I4,41><2,I4,8><3,I4,727>}

类型条数百分比
DELETE 83930 2.3603%
INSERT 359126 10.0994%
SELECT 2137761 60.1186%
UPDATE 975087 27.4216%
表维度统计

bmsql_config            DELETE          0               INSERT          0               SELECT          3               UPDATE          0
bmsql_customer          DELETE          0               INSERT          0               SELECT          75852           UPDATE          0
bmsql_district          DELETE          0               INSERT          0               SELECT          780523          UPDATE          718715
bmsql_history           DELETE          0               INSERT          88512           SELECT          0               UPDATE          0
bmsql_item              DELETE          0               INSERT          0               SELECT          932625          UPDATE          0
bmsql_new_order         DELETE          83930           INSERT          93342           SELECT          83930           UPDATE          0
bmsql_oorder            DELETE          0               INSERT          177272          SELECT          83930           UPDATE          83930
bmsql_order_line                DELETE          0               INSERT          0               SELECT          83930           UPDATE          83930
bmsql_stock             DELETE          0               INSERT          0               SELECT          0               UPDATE          0
bmsql_warehouse         DELETE          0               INSERT          0               SELECT          88512           UPDATE          88512

其中：

st12f40c2513表示 prepared 的key，同时作为游标背后的对应SQL语句；
1 2 SYSDBA 6 127.0.0.1 2023-08-07 10:12:57 ? st12f40c2513 as cursor cur12f40c25236 return 15 {PN=3,GN=1 }
其中：
字段说明
序号字段名类型说明 V11 V12
0 NODEID INTEGER 节点ID X √
1 DB_ID VARCHAR 数据库ID X √
2 USER INTEGER 用户名 X √
3 SESSION_ID VARCHAR 会话ID X √
4 CLIENT_IP DATETIME 客户端IP X √
5 EX_TIME VARCHAR 执行时间 X √
6 SQL_STR VARCHAR 执行SQL X √
7 SQL_PARAS VARCHAR – X √

其中：
st12f40c2513 as cursor cur12f40c25236 return 15 {PN=3,GN=1 }

默认用游标实现。
return 15 表示默认返回15条数据；
PN=3 表示有三个参数数；
GN=1 ，表示group number =1 ,即参数组只有一个；
表示第一个参数，I4表示数据类型，参数值为 41；
表示第二个参数，I4表示数据类型。，参数值为 6；
表示第三个参数，I4表示数据类型，参数值为 912；

m0_38018848

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
虚谷数据库日志文件COMMAND.LOG记录接收到的SQL语句解读(以TPCC为例)

设置虚谷数据库的 reg_command = true，又 tpcc 涉及到增删改查合计 31 种类型的语句AND c_id =?AND c_id =?AND c_id =?AND c_id =?AND d_id =?select pp(?AND c_id =?AND c_id =?AND d_id =?AND d_id =?
复制链接

扫一扫