← Back to team overview

mahara-contributors team mailing list archive

[Bug 1514608] Re: MySQL deadlock issues with simultaneous users

 

Okay, I have been able to consistently replicate the error in JMeter,
after a little tweaking of Russell's JMeter test plan. With a one-second
sleep in place in the ADODB "Execute" method, I get deadlocks with as
few as 4 concurrent users.

Without the delay, I consistently get a deadlock if I do tests of 100
concurrent users.

To make the results easier to scan in JMeter I've also patched my local
Mahara instance so that it returns a 403 error when it displays the
transient login page, and a 500 error when there's an unhandled
exception or error. To do this, put "http_response_code(403);" in the
top of the auth_draw_login_page() method in htdocs/auth/lib.php, and put
"http_response_code(500);" inside the error() and exception() methods in
htdocs/lib/errors.php. Without that, JMeter shows them all as successes,
because there's a 200 response. (I guess I could also probably use a
regex against the response data, but I found this to be easier.)

If I disabled transactions (by putting "return;" as the first line of
db_begin(), db_commit(), and db_rollback() in htdocs/lib/dml.php), then
it finished successfully with the 100 concurrent users. So, that's sort
of the "Cutting the Gordean knot" solution to this problem.

I tried the same test case in Mahara 1.8, which was the first version of
Mahara to use the "mysqli" extension. Prior to that, Mahara didn't do
transactions at all in mysql because it was using the older "mysql"
extension. In 1.8 I also got deadlocks, although it took longer for them
to start.

I also tried running 100 concurrent users through the same test plan in
a Postgres Mahara. Some users did error out due to a strange error
inserting into the view_tags table, but they did not deadlock. So
perhaps if we can tweak InnoDB to act more like Postgresql in regards to
locking, that may be a solution, at least for those who can tweak their
server settings.

Tomorrow I'll take a look at the actual places we're deadlocking in more
fine grain and see if I can do anything about those.

** Attachment added: "copy_collection_test.jmx"
   https://bugs.launchpad.net/mahara/+bug/1514608/+attachment/4517354/+files/copy_collection_test.jmx

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it!
https://bugs.launchpad.net/bugs/1514608

Title:
  MySQL deadlock issues with simultaneous users

Status in Mahara:
  In Progress

Bug description:
  The patch in Bug 1492919 resolved the deadlock issues for some users,
  but others are still reporting that they are getting deadlocks. This
  seems to be an issue unique to MySQL, and it can happen with as few as
  2 or 3 concurrent users.

  It has been reported across all stable versions. Strangely, it seems
  to be a relatively recent issue (i.e. we never heard this with Mahara
  1.8 or 1.7, etc), so this suggests it was triggered by something that
  changed fairly recently. One possible candidate is our switch to the
  mysqli driver (before that Mahara wasn't doing transactions at all, in
  mysql).

  See also:
  * https://mahara.org/interaction/forum/topic.php?id=7309
  * https://mahara.org/interaction/forum/topic.php?id=7432

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1514608/+subscriptions


References