查看 OceanBase 执行计划

• benchmarksql 使用时存在问题，使用sysbench tpcc 场景进行测试

sysbench

• sysbench 压测基于已有的压测脚本
• 关闭其他指标压测，仅压测 mysql

PRESSURE_CPU=0
PRESSURE_MEMORY=0
PRESSURE_IO=0
PRESSURE_MYSQL=1
MYSQL_PRESSURE_CMD_PATH='/data/mysql/mysql8.0/bin/mysql'
MYSQL_PRESSURE_LUA_PATH="/usr/share/sysbench"
MYSQL_PRESSURE_HOST="10.0.0.107"
MYSQL_PRESSURE_PORT=2883
MYSQL_PRESSURE_USER="root@tenant1"
MYSQL_PRESSURE_PASS="123456"

• MySQL 仅压测 rw 场景

MYSQL_POINT_SELECT=0
MYSQL_RANDOM_POINTS=0
MYSQL_RANDOM_RANGES=0
MYSQL_READ_ONLY=0
MYSQL_UPDATE_INDEX=0
MYSQL_UPDATE_NO_INDEX=0

MYSQL_INSERT=0
MYSQL_DELETE=0
MYSQL_WRITE_ONLY=0
MYSQL_READ_WRITE=1

执行计划

查看 top 10 sql

> SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT 
                FROM oceanbase.gv$ob_sql_audit t1 WHERE tenant_id = 1004 AND 
                IS_EXECUTOR_RPC = 0 AND request_time > (time_to_usec(now()) - 10000000)  AND 
                request_time < time_to_usec (now()) 
                GROUP BY t1.sql_id ORDER BY QPS DESC LIMIT 10;
+----------------------------------+------+------------+
| SQL_ID                           | QPS  | RT         |
+----------------------------------+------+------------+
| 70921627094B876D04315FD77C21F5FE | 3490 |  1000.6057 |
| 7E1FF0CE5912C76B191984028DE49EBA | 2581 |  3532.8260 |
| 6C8667F5A8D653E0AF6CCDDAAFF4915F | 2274 |  3604.4415 |
| 8432FCED79046784C575C596216F6539 | 2232 | 12703.6850 |
| AF9BAC7C311EC65BB24E49AC6FF5681A | 2000 | 12404.7980 |
| 19AAD9F2FE3CE0023298AB83F7E75775 | 1263 | 12653.7474 |
| 1D0BA376E273B9D622641124D8C59264 | 1214 | 10633.9662 |
| 6F3DA42A230FC6343C31B83131AC02B6 |  367 |   753.2289 |
| F76982485FB62D7EDEC0D4588E2F3B39 |  367 |  3685.9700 |
| 03BC3F7C4BD7567DABD25583CA325077 |  359 | 10854.3872 |
+----------------------------------+------+------------+

查看执行计划

SELECT sum(ol_amount) AS sum_ol_amount     FROM bmsql_order_line     WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 2119;
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 = 1 AND s_i_id = 44448     FOR UPDATE;
UPDATE bmsql_stock     SET s_quantity = 50, s_ytd = s_ytd + 1,         s_order_cnt = s_order_cnt + 1,         s_remote_cnt = s_remote_cnt + 0     WHERE s_w_id = 2 AND s_i_id = 70921627094B876D04315FD77C21F5FE;

对 elapsed 时间最长的前三条 sql 进行分析

> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time,
      round(avg(execute_time)) avg_exec_time,
      s.svr_ip,
      s.svr_port,
      s.tenant_id,
      s.plan_id
     FROM gv$ob_sql_audit s
     WHERE 1=1
      and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) )
     GROUP BY sql_id
    order by avg_elapsed_time desc limit 3;

+----------------------------------+----------+------------------+---------------+------------+----------+-----------+---------+
| sql_id                           | count(*) | avg_elapsed_time | avg_exec_time | svr_ip     | svr_port | tenant_id | plan_id |
+----------------------------------+----------+------------------+---------------+------------+----------+-----------+---------+
| 2756036DAFF4423527C4192A80F591E5 |      311 |           417189 |          2293 | 10.0.0.107 |     2882 |      1004 |     356 |
| D3D31C011F9D33282BF87918562043E1 |      247 |           366126 |          1097 | 10.0.0.107 |     2882 |      1004 |     351 |
| 35FEB706D54AC5519AC1549CEF760633 |      305 |           339323 |          1656 | 10.0.0.106 |     2882 |      1004 |    1675 |
+----------------------------------+----------+------------------+---------------+------------+----------+-----------+---------+

• 查询时间最长的一条

obclient [oceanbase]> select distinct query_sql from gv$ob_sql_audit where sql_id='D3D31C011F9D33282BF87918562043E1' \G
*************************** 1. row ***************************
query_sql: UPDATE sbtest4 SET k=k+? WHERE id=?

• 分析执行计划

obclient [sbtest]> explain UPDATE sbtest4 SET k=k+1 WHERE id=200 ;
+-------------------------------------------------------------------------------------------------+
| Query Plan                                                                                      |
+-------------------------------------------------------------------------------------------------+
| =============================================                                                   |
| |ID|OPERATOR  |NAME   |EST.ROWS|EST.TIME(us)|                                                   |
| ---------------------------------------------                                                   |
| |0 |UPDATE    |       |1       |35          |                                                   |
| |1 | TABLE GET|sbtest4|1       |3           |                                                   |
| =============================================                                                   |
| Outputs & filters:                                                                              |
| -------------------------------------                                                           |
|   0 - output(nil), filter(nil)                                                                  |
|       table_columns([{sbtest4: ({sbtest4: (sbtest4.id, sbtest4.k, sbtest4.c, sbtest4.pad)})}]), |
|       update([sbtest4.k=column_conv(INT,PS:(11,0),NOT NULL,cast(sbtest4.k + 1, INT(-1, 0)))])   |
|   1 - output([sbtest4.id], [sbtest4.k], [sbtest4.c], [sbtest4.pad]), filter(nil), rowset=256    |
|       access([sbtest4.id], [sbtest4.k], [sbtest4.c], [sbtest4.pad]), partitions(p0)             |
|       is_index_back=false, is_global_index=false,                                               |
|       range_key([sbtest4.id]), range[200 ; 200],                                                |
|       range_cond([sbtest4.id = 200])                                                            |
+-------------------------------------------------------------------------------------------------+