Unexpected Error message from PXC running sysbench

[Peter Zaitsev]

Running Sysbench on 2 nodes of the cluster at the same time results in this:

root@dpe02:/etc/mysql# sysbench --test=oltp --mysql-user=root --mysql-password="" --oltp-table-size=1000000 --num-threads=32 --init-rng=on --max-requests=0 --oltp-auto-inc=off --max-time=3000 run
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

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
Not using auto_inc on the id column
Threads started!
ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
FATAL: database error, exiting...
Done.

This does not happen running the load on the single load/single server ever in my experience.

This needs to ether be fixed or we should ensure it is clearly documented as error code which can be injected in the cluster in cases it does not happen in stand alone MySQL server or it starts to happen dramatically more frequently.

note Sysbench version is important to repeat this problem.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Yes, I am able to reproduce this with sysbench 0.4.12 but not with sysbench 0.5

""

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 32
Initializing random number generator from timer.

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
Not using auto_inc on the id column
Threads started!
ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was interrupted
FATAL: database error, exiting...
Done.

""

However, since it is not repeatable with sysbench 0.5, it may be something with sysbench itself. I have seen this error during normal sysbench runs with PS as well.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Adding sysbench project since there seems to be a sysbench branch dealing with disconnects like these.

[Alexey Kopytov (akopytov)]

I don't see any differences between 0.4 and 0.5 that would result in such a difference in server behavior.

As in, provided that both 0.4 and 0.5 are executing identical tests (i.e. same types, compatible options, etc.), generated queries should be the same.

Can I have exact command lines that do and do not reproduce the "interrupted query" issue?

[Peter Zaitsev (pz-percona)]

Thanks.

Here is the exact command and output

root@nuc1:/# sysbench --max-requests=0 --max-time=60000000
--num-threads=128 --test=oltp --oltp-table-size=10000000
--mysql-host=192.168.200.1 --mysql-port=33306 --mysql-user=root
--mysql-password=password run
sysbench 0.4.12: multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 128

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
Threads started!
ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution was
interrupted
FATAL: database error, exiting...
Done.

You obviously need to prepare database before that.

On Thu, Jun 26, 2014 at 9:18 AM, Alexey Kopytov <email address hidden>
wrote:

> I don't see any differences between 0.4 and 0.5 that would result in
> such a difference in server behavior.
>
> As in, provided that both 0.4 and 0.5 are executing identical tests
> (i.e. same types, compatible options, etc.), generated queries should be
> the same.
>
> Can I have exact command lines that do and do not reproduce the
> "interrupted query" issue?
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https://bugs.launchpad.net/bugs/1144595
>
> Title:
> Unexpected Error message from PXC running sysbench
>
> Status in Percona XtraDB Cluster - HA scalable solution for MySQL:
> New
> Status in SysBench: a system performance benchmark:
> New
>
> Bug description:
> Running Sysbench on 2 nodes of the cluster at the same time results
> in this:
>
> root@dpe02:/etc/mysql# sysbench --test=oltp --mysql-user=root
> --mysql-password="" --oltp-table-size=1000000 --num-threads=32
> --init-rng=on --max-requests=0 --oltp-auto-inc=off --max-time=3000 run
> sysbench 0.4.12: multi-threaded system evaluation benchmark
>
> No DB drivers specified, using mysql
> Running the test with following options:
> Number of threads: 32
> Initializing random number generator from timer.
>
>
> 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
> Not using auto_inc on the id column
> Threads started!
> ALERT: failed to execute mysql_stmt_execute(): Err1317 Query execution
> was interrupted
> FATAL: database error, exiting...
> Done.
>
> This does not happen running the load on the single load/single server
> ever in my experience.
>
> This needs to ether be fixed or we should ensure it is clearly
> documented as error code which can be injected in the cluster in cases
> it does not happen in stand alone MySQL server or it starts to happen
> dramatically more frequently.
>
> note Sysbench version is important to repeat this problem.
>
> To manage notifications about this bug go to:
>
> https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1144595/+subscriptions
>

--
Peter Zaitsev, CEO, Percona
Tel: [phone] ext 501 Skype: peter_zaitsev...

[Krunal Bauskar (krunal-bauskar)]

"Adding sysbench project since there seems to be a sysbench branch dealing with disconnects like these."

So as per the investigator comment this looks to be an issue with sysbench and doesn't happen with sysbench-0.5 so it would be sysbench bug. Closing it as INVALID on PXC front.

[Alexey Kopytov (akopytov)]

This is not a sysbench bug. This is a difference between regular MySQL server and a Galera node. The latter may fail to execute queries with a variety of errors due to conflicts, nodes getting into desync state, etc.

Both sysbench 0.4 and 0.5 branches now support the --mysql-ignore-errors option to, well, ignore this kind of Galera-specific errors.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1055