how about a sysbench-testing-quick-start

[ikarishinjieva]

How about make a sysbench-testing-quick-start, including:

1. dble sample configuration on sysbench table
2. oltp_auto_inc should be off

[irene-coming]

possible errors: run sysbench prepare got error like:

Creating table 'sbtest1'...
Inserting 1000 records into 'sbtest1'
ALERT: mysql_drv_query() returned error 1064 (bad insert sql, sharding column/joinKey:ID not provided,INSERT INTO sbtest1 (k, c, pad)

fix: way 1: add --auto_inc=off（the parameter before sysbench1.0.0 is: --oltp_auto_inc=off） to the sysbench command to not use mysql's auto_increment. way 2: if you really want to use the auto inc: 2.1, if the table is not sharded, then correct the lua, change insert into sbtest1 values(xxxx...) to insert into sbtest1(col-name,xx, xx) values(xxxx...) 2.2, the table is sharded, then use dble's global sequence( the performance may out of your expect)

[irene-coming]

Possible errors:

ALERT: mysql_drv_query() returned error 3009 (the max active Connections size can not be max than maxCon for data host[host_3.hostM3]) for query 'SELECT pad FROM sbtest1 WHERE id=497924'
FATAL: failed to execute function `event': 3

Fix: make the maxCon (attribute of dble's schema.xml/dataHost node) larger. the following formula should be satisfied:

concurrency(of sysbench cmd)*shardings(of the dataHost configured in dble) < maxCon(of the dataHost configured in dble) < max_connections(of mysql)

To be more accurately: concurrency could be the max concurrency dble send to the certain backend dataHost, however, to capture the value dynamically is tedious.

[irene-coming]

sample for how about a sysbench-testing-quick-start: https://github.com/actiontech/dble-docs-cn/blob/master/9.Sysbench_samples/9.0_overview.md

[irene-coming]

possible errors: run sysbench(1.0.11,oltp_read_write.lua) got error like:

FATAL: mysql_stmt_execute() returned error 0 () for query 'SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c'
FATAL: `thread_run' function failed: ./oltp_common.lua:428: SQL error, errno = 0, state = '00000':

details refer to：https://github.com/actiontech/dble/issues/492

Fix: add --db-ps-mode=disable to the sysbench command line

[irene-coming]

possible errors: run sysbench(1.0.11, sysbench-1.0.11/tests/include/oltp_legacy/oltp.lua) got error like:

FATAL: mysql_drv_query() returned error 1003 (Transaction error, need to rollback.Reason:[ errNo:1213 Deadlock found when trying to get lock; try restarting transaction]) for query 'BEGIN'
FATAL: `thread_run' function failed: /opt/sysbench-1.0.11/tests/include/oltp_legacy/oltp.lua:43: db_query() failed

error reason: if lock occures in backend mysql, dble requires an explicit 'rollback' to go on after sending errors to client. possible fix: --mysql-ignore-errors=1213,1003, but due to oltp.lua its own problem, this may lead to endless run. ps: oltp.lua itself is not recommended to use

[irene-coming]

possible errors:

FATAL: mysql_drv_query() returned error 1062 (Duplicate entry '498' for key 'PRIMARY') for query 'INSERT INTO sbtest1 (id, k, c, pad) VALUES (498, 503, '78048930153-94181240857-75597183294-81342632734-65934497887-34807677303-07059064328-72381079605-19115287036-95210524363', '82457926437-09239344213-29678561090-53923192433-97263217683')'

error reason: sysbench usage limit. In sysbench lua files involved: delete+insert to a same id in the same session, for sysbench 1.0.15 it resides in oltp_common.lua:

function execute_delete_inserts
      ......
      stmt[tnum].deletes:execute()
      stmt[tnum].inserts:execute()
      ......

if using --skip-trx=on option and threads>1, you will be very likely to meet such error. Root cause is queries executed in time series as:

thread 1 deletes the primary key
thread 2 deletes the primary key
thread 2 inserts the primary key
thread 1 inserts the primary key (fails because it already exists)

Details pls refer sysbench's related issues: https://github.com/akopytov/sysbench/issues?q=is%3Aissue+duplicate+entry+is%3Aclosed

possible fix:

1. using --mysql-ignore-errors to skip the error
2. modify the lua, to add lock/unlock tables before/after the operation which may effect the performance seriously