search

XiaoMi / Gaea

Gaea is a mysql proxy, it's developed by xiaomi b2c-dev team.
Apache License 2.0
2.65k stars 426 forks source link

使用sysbench压测后stmt报错 #26

Open makediff opened 5 years ago

makediff commented 5 years ago

sysbench命令为

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

报错为

[2019-06-04 15:23:37] [gaea_proxy] [localhost] [WARN] [900000001] [github.com/XiaoMi/Gaea/proxy/server.(*Session).Run.func1:session.go:215] [server] Session Run panic error, error: runtime error: index out of range, stack: goroutine 40 [running]:
github.com/XiaoMi/Gaea/proxy/server.(*Session).Run.func1(0xc000380730)
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/session.go:213 +0x176
panic(0x1926bc0, 0x241bb30)
    /usr/local/go/src/runtime/panic.go:522 +0x1b5
github.com/XiaoMi/Gaea/proxy/server.(*SessionExecutor).bindStmtArgs(0xc0006ac000, 0xc0000c0a80, 0xc00067c20a, 0x1, 0x76, 0x0, 0x0, 0x0, 0x0, 0x0, ...)
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/executor_stmt.go:225 +0xeb9
github.com/XiaoMi/Gaea/proxy/server.(*SessionExecutor).handleStmtExecute(0xc0006ac000, 0xc00067c201, 0x13, 0x7f, 0x0, 0x0, 0x0)
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/executor_stmt.go:178 +0x1d4
github.com/XiaoMi/Gaea/proxy/server.(*SessionExecutor).ExecuteCommand(0xc0006ac000, 0xc0006cde17, 0xc00067c201, 0x13, 0x7f, 0x0, 0x0, 0x19ea000, 0xc000183420)
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/executor.go:295 +0x7a1
github.com/XiaoMi/Gaea/proxy/server.(*Session).Run(0xc000380730)
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/session.go:236 +0x2b2
github.com/XiaoMi/Gaea/proxy/server.(*Server).onConn(0xc000384f00, 0x1bc44e0, 0xc000010280)
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/server.go:139 +0x289
created by github.com/XiaoMi/Gaea/proxy/server.(*Server).Run
    /Users/warm/go/src/github.com/XiaoMi/Gaea/proxy/server/server.go:156 +0x17b

直接压测原生的mysql服务没有这个问题。

mysql 是 docker 版 5.7 version。

docker run -d --name sql \
    -e MYSQL_USER=root \
    -e MYSQL_ROOT_PASSWORD=123456 \
    -p 3307:3306 \
    mysql:5.7
niubell commented 5 years ago

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

makediff commented 5 years ago 
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 commented 5 years ago

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

makediff commented 5 years ago

好的，非常感谢

makediff commented 5 years ago

压测后，目前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 commented 5 years ago

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

isevenluo commented 5 years ago

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

han-ian commented 1 year ago

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

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