MySQL Bugs: #22142: random hangs on update_key bechmarks with 4-64 threads

Bug #22142	random hangs on update_key bechmarks with 4-64 threads
Submitted:	9 Sep 2006 5:00	Modified:	29 Oct 2008 17:54
Reporter:	Georg Richter	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	5.2	OS:	Any
Assigned to:	Hakan Küçükyılmaz	CPU Architecture:	Any
Tags:	F_UNKNOWN

Description:
random hangs on update_key bechmarks with 4-64 threads

I use sysbench update_key benchmark on 4way xeon box.

How to repeat:
1. prepare
pls see bug #22141

2. run
--test=oltp --mysql-engine-trx=jstar --oltp-table-size=1000000 --mysql-engine-trx=yes --oltp-table-name=sbtest --oltp-test-mode=nontrx --oltp-nontrx-mode=update_key --max-requests=0 --num-threads=4 --oltp-dist-type=sbtest --mysql-db=test --max-time=180 run

please test with --num-threads=4 , 16 , 64
I use script for that.
after 3-4 runs mysqld hangs

I believe this is fixed as of 9/14/06.

Tried with following scenario:
- 8 way Intel EM64T with 8GB RAM. Linux SLES 9 64-bit change set 1.2292, 2006-09-14
- Build with: BUILD/compile-amd64-max --prefix=/usr/local/mysql-5.1 
- sysbench 0.4.7

The reported initialization for sysbench has a minor error. It does not use the Falcon storage engine: --mysql-table-engine=falcon should be used.

I initialized the test table with:
sysbench --test=oltp --mysql-user=root --mysql-table-engine=falcon --mysql-engine-trx=yes --oltp-table-size=1000000 --oltp-table-name=sbtest --init-rng=on --mysql-db=test prepare

After that checked that storage engine for table test.sbtest is of type Falcon and that we have 1 mio rows in that table:
mysql> show create table sbtest\G
*************************** 1. row ***************************
       Table: sbtest
Create Table: CREATE TABLE "sbtest" (
  "id" int(10) unsigned NOT NULL AUTO_INCREMENT,
  "k" int(10) unsigned NOT NULL DEFAULT '0',
  "c" char(120) NOT NULL DEFAULT '',
  "pad" char(60) NOT NULL DEFAULT '',
  PRIMARY KEY ("id"),
  KEY "k" ("k")
) ENGINE=Falcon DEFAULT CHARSET=latin1

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (3.74 sec)

Now we can run the sysbench test. However, the stated test run cannot be ran. There is no --oltp-dist-type=sbtest. I double checked with the developer of sysbench and he stated that there has never been --oltp-dist-type=sbtest. Probably --oltp-dist-type=special was meant.

So we run sysbench like this:

sysbench --test=oltp --mysql-user=root --mysql-table-engine=falcon --mysql-engine-trx=yes --oltp-table-size=1000000 --oltp-table-name=sbtest --oltp-test-mode=nontrx --oltp-nontrx-mode=update_key --max-requests=0 --num-threads=4 --oltp-dist-type=special --mysql-db=test --max-time=180 run

Result:
sysbench v0.4.7:  multi-threaded system evaluation benchmark

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

Doing OLTP test.
Running non-transactional 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!
1 out of 0
2 out of 0
3 out of 0
4 out of 0
5 out of 0
6 out of 0
7 out of 0
...
...
...
... This continues until ...
...
...
23328 out of 0
23329 out of 0
23331 out of 0
23332 out of 0
ALERT: failed to execute mysql_stmt_execute(): Err1296 Got error -1024 'update conflict in table MYSQL.SBTEST' from Falcon
FATAL: database error, exiting...
Done.

Please note that the original bug report has two errors:
1. Initializion of sbtest table was wrong:
/sysbench --test=oltp --mysql-engine-trx=jstar --oltp-table-size=1000000
--mysql-engine-trx=yes --oltp-table-name=sbtest --init-rng=on --mysql-db=test

The first --mysql-engine-trx=jstar is wrong. It should be:
--mysql-table-engine=falcon
otherwise the defaut storage engine will be used (in most cases MyISAM).

2. Run of sysbench was wrong:
--test=oltp --mysql-engine-trx=jstar --oltp-table-size=1000000
--mysql-engine-trx=yes --oltp-table-name=sbtest --oltp-test-mode=nontrx
--oltp-nontrx-mode=update_key --max-requests=0 --num-threads=4
--oltp-dist-type=sbtest --mysql-db=test --max-time=180 run

The is no --oltp-dist-type=sbtest. Correct would be --oltp-dist-type=special.

Regards, Hakan

Cross checked with InnoDB that sysbench is fine on above mentioned platform. sysbench with InnoDB works as expected.

Regards, Hakan

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Can't repeat anymore. Successfully ran mysqlslap with
   --concurrency=1,50,100,150,200,250,300

for Falcon.

Forget my last comment.

Can't repeat with latest Falcon and sysbench v0.5. I tried the following:
sysbench \
  --init-rng=on \
  --mysql-db=test \
  --mysql-engine-trx=yes \
  --mysql-table-engine=falcon \
  --mysql-user=root \
  --oltp-table-name=sbtest \
  --oltp-table-size=1000000 \
  --test=oltp_simple.lua \
prepare

sysbench \
  --max-requests=0 \
  --max-time=180 \
  --mysql-db=test \
  --mysql-engine-trx=yes \
  --mysql-table-engine=falcon \
  --mysql-user=root \
  --num-threads=4 \
  --oltp-dist-type=special \
  --oltp-nontrx-mode=update_key \
  --oltp-table-name=sbtest \
  --oltp-table-size=1000000 \
  --oltp-test-mode=nontrx \
  --test=oltp_simple.lua \
run

I used --num-threads=4, 16, 64 and ran the test 10 times with 64 threads. There were no hangs.