[MDEV-675] LP:851404 - Deadlocks occurring with sysbench against mariadb 5.3.1 beta + innodb Created: 2011-09-15  Updated: 2013-01-17  Resolved: 2013-01-17

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug
Reporter: Mark Kirkwood (Inactive) Assignee: Axel Schwenke
Resolution: Cannot Reproduce Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug851404.xml    

 Description   

sysbench 0.4.12 with options:
--mysql-table-engine=innodb
--test=oltp --oltp-table-size=10000000 --oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0
--oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-index-updates=0 --oltp-non-index-updates=0
--max-requests=0 --max-time=300 --num-threads=2, 4, 8. 16, 32

for 2 threads get approx 1200 tps and 0 deadlocks, but for 4, 8, 16, 32 get ~200 tps and steadily increasing numbers of deadlocks (10 up to 297).

Innodb status for deadlocks shows:
------------------------
LATEST DETECTED DEADLOCK
------------------------
110915 18:30:47

      • (1) TRANSACTION:
        TRANSACTION 12F6E6, ACTIVE 28 sec, process no 5598, OS thread id 140207001040640 starting index read
        mysql tables in use 1, locked 1
        LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
        MySQL thread id 70, query id 3110358 localhost mysql updating
        DELETE from sbtest where id=?
      • (1) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 0 page no 54 n bits 144 index `PRIMARY` of table `bench`.`sbtest` trx id 12F6E6 lock_mode X waiting
      • (2) TRANSACTION:
        TRANSACTION 12BBA9, ACTIVE 53 sec, process no 5598, OS thread id 140207769769728 fetching rows
        mysql tables in use 1, locked 1
        36824 lock struct(s), heap size 3586488, 2724783 row lock(s)
        MySQL thread id 62, query id 3066651 localhost mysql Updating
        UPDATE sbtest set k=k+1 where id=?
      • (2) HOLDS THE LOCK(S):
        RECORD LOCKS space id 0 page no 54 n bits 144 index `PRIMARY` of table `bench`.`sbtest` trx id 12BBA9 lock_mode X
      • (2) WAITING FOR THIS LOCK TO BE GRANTED:
        RECORD LOCKS space id 0 page no 39541 n bits 144 index `PRIMARY` of table `bench`.`sbtest` trx id 12BBA9 lock_mode X waiting
      • WE ROLL BACK TRANSACTION (1)

A run with identically configured mariadb 5.2.7 elicits no deadlocks for any of the 2-> 32 threads.



 Comments   
Comment by Mark Kirkwood (Inactive) [ 2011-09-15 ]

Re: Deadlocks occurring with sysbench against mariadb 5.3.1 beta + innodb
Platform is Ubuntu 11.04 64 bit.

Mariadb 5.3.1 built from source:

$ CFLAGS='-g -O2 -fno-strength-reduce' \
CXXFLAGS='-g -O2 -fno-rtti -fno-exceptions -felide-constructors -fno-strength-reduce' \
./configure --prefix=/usr/local/maria/5.3 --enable-local-infile \
--with-plugins=xtradb,pbxt,aria,partition

Non default options in my.cnf:

datadir = /data0/madata/5.3
default_storage_engine = innodb

Comment by Elena Stepanova [ 2012-03-22 ]

Re: Deadlocks occurring with sysbench against mariadb 5.3.1 beta + innodb
Axel,

Do you think there is a problem? Is there anything we need to do about it?

This does look somewhat strange:

UPDATE sbtest set k=k+1 where id=?
36824 lock struct(s), heap size 3586488, 2724783 row lock(s)

2M row locks?..

But the report is pretty old, quite possibly something has changed since then.

Comment by Rasmus Johansson (Inactive) [ 2012-03-22 ]

Launchpad bug id: 851404

Comment by Mark Kirkwood (Inactive) [ 2012-03-22 ]

Re: Deadlocks occurring with sysbench against mariadb 5.3.1 beta + innodb
Retested with 5.3.5ga and cannot reproduce, so ,looks like you guys have fixed this!

Generated at Thu Feb 08 06:30:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.