sysbench issue on benchmarking wsrep-cluster.

[Oleksandr Drach]

Dear Community!

I have benchamrked 5-node wsrep cluster with sysbench.

The following sequence of command have been used:

mysql -uroot -prerbi2012 -e "CREATE DATABASE sbtest;"

 sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=localhost --mysql-port=3306 --mysql-user=dbuser --mysql-password=dbpass --oltp-table-size=1000000 prepare

sysbench --test=oltp --db-driver=mysql --mysql-engine-trx=yes --mysql-table-engine=innodb --mysql-host=localhost --mysql-port=3306 --mysql-user=dbuser --mysql-password=dbpass --oltp-table-size=1000000 --num-threads=512 run

mysql -uroot -prerbi2012 -e "DROP DATABASE sbtest;"

For low threads number test works well, but setting --num-threads=512 or sometimes --num-threads=384 causes benchmark fails with error below:

Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 10000

Threads started!

ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry '497125' for key 'PRIMARY'

FATAL: database error, exiting...

ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry '503386' for key 'PRIMARY'

FATAL: database error, exiting...

ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate entry '495800' for key 'PRIMARY'

FATAL: database error, exiting...

Done.

On any wsrep server max_connections        = 600 hence this limitation it cannot be the cause.

Does anyone have a suggestions how to force sysbench successfully perform tests on wsrep-cluster with large number of threads?

Thanks in advance for your help!

[Jay Janssen]

If you are only running this test against a single node, I'd almost wonder if this is a sysbench bug.  

Can you replicate the issue if you shutdown all your nodes except the main one or are you actually doing writes against multiple nodes simultaneously?

--
You received this message because you are subscribed to the Google Groups "codership" group.
To unsubscribe from this group and stop receiving emails from it, send an email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

Jay Janssen, MySQL Consulting Lead, Percona

http://about.me/jay.janssen

Percona Live in Santa Clara, CA  April 22nd-25th 2013

http://www.percona.com/live/mysql-conference-2013/

[Oleksandr Drach]

Thanks for reply, Jay!

Single wsrep-node runs well both tests with 384 and 512 threads.

But 3,4,5-nodes cluster's benchmarks ends with error described.

[Henrik Ingo]

Hi Olek

You need to use --oltp-auto-inc=off when benchmarking against a Galera
cluster. see http://www.openlife.cc/blogs/2011/august/running-sysbench-tests-against-galera-cluster

(Actually, it's not obvious to me the error is because of that, but
still, you need to use it.)

henrik

> --
> You received this message because you are subscribed to the Google Groups
> "codership" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to codership-tea...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

--
henri...@avoinelama.fi
+358-40-8211286 skype: henrik.ingo irc: hingo
www.openlife.cc

My LinkedIn profile: http://www.linkedin.com/profile/view?id=9522559

[Alex Yurchenko]

On 2013-01-30 15:47, Oleksandr Drach wrote:
> Thanks for reply, Jay!
>
> Single wsrep-node runs well both tests with 384 and 512 threads.
> But 3,4,5-nodes cluster's benchmarks ends with error described.

Hi Oleksandr, I think --mysql-ignore-duplicates=1 is what you're
looking for.

[Oleksandr Drach]

Hello Henrik,

"--oltp-auto-inc=off" option was exactly what I've needed.

Your solution works perfectly!

Thanks!

[Oleksandr Drach]

Hi Alex,

Thanks for advice!

But it seems like my version of sysbench (0.4.12-1build2) does not have "--mysql-ignore-duplicates" options.

Anyway problem has been already solved with "--oltp-auto-inc=off" :)

Thanks everyone!

[Alex Yurchenko]

Unfortunately Err1317 can't be entirely avoided in multimaster Galera
operation and sysbench just can't stand it (it should rollback and start
over). There is nothing except decreasing the concurrency (increasing
the number of rows and decreasing the number of threads) that can help
here.

On 2013-03-26 18:14, Sebastien Lefevre wrote:
> hi,
> i have a similar problem but the "oltp-auto-inc=off" dit not solve
> it.
> i have a 3 nodes galera cluster. sysbench 0.4.12 on debian.
>
> Prepare bench:
> sysbench --db-driver=mysql --num-threads=32 --max-time=300
> --max-requests=500000 --mysql-user=appuser --mysql-host=10.xx.xx.xx
> --mysql-port=33306 --mysql-password='xxxxxxxxx' --oltp-auto-inc=off
> --test=oltp --oltp-test-mode=complex --oltp-table-size=5000000
> --mysql-table-engine=innodb prepare
>
> Run bench:
> sysbench --db-driver=mysql --num-threads=32 --max-time=300
> --max-requests=500000 --mysql-user=appuser --mysql-host=10.xx.xx.xx
> --mysql-port=33306 --mysql-password='xxxxxxxxx' --oltp-auto-inc=off
> --test=oltp --oltp-test-mode=complex --oltp-table-size=5000000
> --mysql-table-engine=innodb run
>
> error messages:
> ALERT: failed to execute mysql_stmt_execute(): Err1317 Query
> execution was
> interrupted
> or

> ALERT: failed to execute mysql_stmt_execute(): Err1062 Duplicate
> entry

> '2481816' for key 'PRIMARY'
>
>
> the option "mysql-ignore-duplicates" is unkown
>
> thank for your help.
>
> sebastien

--
Alexey Yurchenko,
Codership Oy, www.codership.com
Skype: alexey.yurchenko, Phone: +358-400-516-011

[Henrik Ingo]

Alex,

What does "Duplicate entry" has to do with multi-master? Yes, there
can be rollbacks due to deadlocks, but sysbench can handle that. I
always thought that --mysql-ignore-duplicates=1 isn't a galera
specific thing but is some sysbench 0.4 specific problem.

With sysbench 0.5 I never had this problem, so again it makes me think
it is not a Galera issue.

henrik

> --
> You received this message because you are subscribed to the Google Groups
> "codership" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to codership-tea...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Alex Yurchenko]

On 2013-03-27 13:53, Henrik Ingo wrote:
> Alex,
>
> What does "Duplicate entry" has to do with multi-master?

Nothing. But Err1317: Query execution was interrupted has. I think
initially it used to return Err1213, but recent changes in InnoDB must
have changed this.

> Yes, there
> can be rollbacks due to deadlocks, but sysbench can handle that. I
> always thought that --mysql-ignore-duplicates=1 isn't a galera
> specific thing but is some sysbench 0.4 specific problem.

--mysql-ignore-duplicates does not seem to work with 0.4 (although I
have a feeling that it used to)

> With sysbench 0.5 I never had this problem, so again it makes me
> think
> it is not a Galera issue.

It is not. But it is a usability issue. As Peter justly noted,
applications/users don't know what to do in case of Err1317. We must
somehow convert it all to Err1213 if we want application to retry, but
in intelligent way.

[Henrik Ingo]

On Wed, Mar 27, 2013 at 3:35 PM, Alex Yurchenko
<alexey.y...@codership.com> wrote:
> On 2013-03-27 13:53, Henrik Ingo wrote:
>> Yes, there
>> can be rollbacks due to deadlocks, but sysbench can handle that. I
>> always thought that --mysql-ignore-duplicates=1 isn't a galera
>> specific thing but is some sysbench 0.4 specific problem.
>
>
> --mysql-ignore-duplicates does not seem to work with 0.4 (although I have a
> feeling that it used to)
>
>
>> With sysbench 0.5 I never had this problem, so again it makes me think
>> it is not a Galera issue.
>
>
> It is not. But it is a usability issue. As Peter justly noted,
> applications/users don't know what to do in case of Err1317. We must somehow
> convert it all to Err1213 if we want application to retry, but in
> intelligent way.

Ok, for that error I agree (assuming it is deadlock originated, but I
will believe your theory that InnoDB has recently changed the error)

But for the duplicate key error it seems it is a real sysbench 0.4 issue?

henrik

[Sebastien Lefevre]

Hi,
i install sysbench 0.5. It's ok now.

Sebastien

[Henrik Ingo]

On Thu, Mar 28, 2013 at 3:28 PM, Sebastien Lefevre <slefe...@gmail.com> wrote:
> Hi,
> i install sysbench 0.5. It's ok now.

Thanks for confirming!

[Oleksandr Drach]

That would be nice to add to Galera FAQ!

Unfortunately, my account details does not work any more, reset password also does not work.

Could someone please assist me with restoring my account and/or adding thiq question to Galera FAQ?

Thanks in advance!

[Oleksandr Drach]

The mentioned QA has been added to Galera FAQ.

[federated_life]

double confirming , writing to 3 nodes simultaneously 

sysbench-0.5 --mysql-user=$USER --mysql-password=$PASSWORD --oltp-test-mode=complex --oltp-reconnect-mode=session --oltp-range-size=500 --oltp-point-selects=10 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --oltp-index-updates=1 --oltp-non-index-updates=1 --oltp-nontrx-mode=select --oltp-connect-delay=10 --oltp-user-delay-min=0 --oltp-user-delay-max=0 --db-ps-mode=auto --debug=off --test=$TEST --oltp-table-size=$TABLESIZE --mysql-db=$DATABASENAME --mysql-table-engine=innodb --oltp-table-name=$TABLENAME --num-threads=$THREADS --max-requests=$MAXREQUESTS --oltp-auto-inc=off --mysql-engine-trx=yes --db-driver=mysql --mysql-ignore-duplicates run

compiled sysbench 0.5 on ubuntu 12

had to specify path to oltp test which was changed in 0.5

// https://answers.launchpad.net/sysbench/+question/156376