使用sysbench压测后stmt报错

[xiaomi]

使用sysbench压测后stmt报错

[niubell]

gaea的配置、压测的表结构能提供下吗？看起来是参数个数与参数类型不匹配导致的，我们在本地复现一下试试

[makediff]

create database sbtest;

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=latin1;

或者用这个命令生成表

sysbench /usr/local/Cellar/sysbench/1.0.16/share/sysbench/oltp_read_only.lua \
    --mysql-host=10.2.0.107 \
    --mysql-port=3306 \
    --mysql-user=root \
    --mysql-password=123456 \
    --threads=10 \
    --time=120 \
    --report-interval=10 \
    prepare

namespace的配置

{
    "name": "mytest1",
    "online": true,
    "read_only": true,
    "allowed_dbs": {
        "sbtest": true
    },
    "slow_sql_time": "1",
    "black_sql": [
    ],
    "allowed_ip": null,
    "slices": [
        {
            "name": "slice-0",

            "user_name":"root",
            "password":"123456",
            "master":"10.2.0.107:3307",

            "slaves":null,
            "statistic_slaves": null,
            "capacity": 50,
            "max_capacity": 100,
            "idle_timeout": 60
        }
    ],
    "shard_rules": null,
    "users": [
        {
            "user_name": "test_mytest11_write_user",
            "password": "test_mytest11_write_pwd",
            "namespace": "mytest1",
            "rw_flag": 2,
            "rw_split": 1,
            "other_property": 0
        },
        {
            "user_name": "test_mytest12_read_user",
            "password": "test_mytest12_read_pwd",
            "namespace": "mytest1",
            "rw_flag": 1,
            "rw_split": 1,
            "other_property": 0
        },
        {
            "user_name": "root",
            "password": "123456",
            "namespace": "mytest1",
            "rw_flag": 2,
            "rw_split": 1,
            "other_property": 0
        }
    ],
    "default_slice": "slice-0",
    "global_sequences": null
}

[niubell]

我昨晚抓包看来压测后续数据有的缺失了两个字节的类型数据，我先排查一下，正常请求应该是没问题的，你也可以验证下，如果单纯想看性能建议可以压测非prepare的。

[makediff]

好的，非常感谢

[makediff]

压测后，目前Gaea不报错了，但是system会报错

sysbench /usr/local/Cellar/sysbench/1.0.16/share/sysbench/oltp_read_only.lua \
    --mysql-host=10.2.0.107 \
    --mysql-port=3306 \
    --mysql-user=root \
    --mysql-password=123456 \
    --threads=10 \
    --time=120 \
    --report-interval=10 \
    run
sysbench 1.0.16 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 10
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
(last message repeated 1 times)
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':
(last message repeated 1 times)
FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
FATAL: `thread_run' function failed: ...al/Cellar/sysbench/1.0.16/share/sysbench/oltp_common.lua:432: SQL error, errno = 0, state = '00000':

[niubell]

@makediff 我们也在排查这个问题，抓包来看执行结果跟直连mysql相同，但是不知道为什么压测脚本认为0,00000为错误直接退出了。

[isevenluo]

这个解决了？我也遇到了同样的问题

[han-ian]

这个是proxy没有完全兼容mysql协议, 而sysbench恰好用了这个特性.
2023年了, sysbench 没有发新版, 目前1.0.20还会有这个问题.
master 分支已经修复了这个问题, 怀疑 akopytov/sysbench@ead2689 2021.3.26这个commit “修复"的.

验证: 删除左方代码, 则不会出现该问题.