CentOS 7.4.1708 / RHEL 7.4
MariaDB 10.2.12

goto.satoru
Could you please provide the exact sysbench commands you used (with parameters and all), so that we can repeat your steps exactly.

Hi Johan,

here is the sysbench 1.0.9 commands I exxecuted :

sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=4006 --mysql-user=maxuser --mysql-password=maxpwd --mysql-db=test --range_size=100 --table_size=1000000 --tables=2 --events=0 --time=60 --rand-type=uniform --threads=1 /usr/share/sysbench/oltp_read_write.lua run

Thanks,

Satoru

Hi Satoru,

Thanks, I could confirm the very same behaviour.

Johan

Hi Satoru,
The logged message can be ignored because MaxScale erroneously logs an error whenever the preparation of a prepared statement returns an error. That's wrong as the preparation of a statement can obviously fail because the statement is erroneous. That is the case here,because MariaDB does not yet support having BEGIN in a prepared statement: https://mariadb.com/kb/en/library/prepare-statement/

This is what I get when I try to do that against my server, without having MaxScale in between:

MariaDB [(none)]> prepare stmt from 'begin';

ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

However, there is also some real bug that causes sysbench to hang, because if I run the sysbench command directly against my MariaDB server, it does not hang but produces the expected output.

Johan

Thanks for the update.

Yes, I could run sysbench locally on MariaDB server hosts,
but due to the requirement, I need MaxScale - Master - Slave architecture for testing.

Thanks,

Satoru

If you add --db-ps-mode=disable then prepared statements will not be used and sysbench will work.

That should allow you to continue until we have sorted out why prepared statements do not work.

Johan

Thanks for your advice.

I am now using MaxScale 2.1.13, have no issue with sysbench.

Satoru

But in 2.1 all prepared statements will be sent to master, which means that if sysbench uses such, the result will only reflect the overhead caused by maxscale and you will see no benefits related to the load balancing of maxscale.

Johan

Hi Johan,

Thanks for your comment.
Then I will disable prepared statements with 2.1.

Satoru

Hi Johan,

When I was performing benchmark on mariaDB server 10.11, when the susbench threads reached 2048, I encountered a problem similar to this post.

Follow your suggestion, I add --db-ps-mode=disable and sysbench work.

Regarding your comment a long time ago, I wonder why prepared statements do not work. Thank you very muck!

Xiaotong

What version of MaxScale are you using?

Hi markus,

Sorry, I don't use MaxScale, I just deploy mariadb/server 10.11. I used following sysbench statement to test it.

sysbench oltp_read_only.lua --db-ps-mode=auto --mysql-user=* --mysql-password=* --mysql-db=* --tables=100 --table_size=400000 --report-interval=1 --threads=2048 --mysql-host=127.0.0.1 --mysql-port=54343 --time=300 run --rand-type=uniform --rand-seed=31395

The error is as follows:

Initializing worker threads...

FATAL: Worker threads failed to initialize within 30 seconds!

Xiaotong

Oh, in that case it means that sysbench was unable to open all the database connections within 30 seconds and it gave up. Usually this means that something's slowing down the connection creation on the server.

I'd recommend joining the MariaDB community Slack server or the MariaDB.org Zulip server, people over there (me included) will be able to answer your questions better than in a years old bug report for a different product

Hi markus,

Ok, thank you very much~

Xiaotong