667846 - queries are getting locked on high load

Status: RESOLVED FIXED

(Cloud Services Graveyard :: Server: Sync, defect)

Description

[Tarek Ziadé (:tarek)]

On high load, SQL queries sent through pymysql + gevent to insert wbos into collections by batches get locked. 

We encounter this issue in the load test server and were able to fix it by lowering the size of the batches sent to MySql.

We don't know yet what's really happening, and if the driver is to blame, or if it's a problem on the database side, as the lock happens when the same table gets updated concurrently for the same user.

I am going to work today on the issue to try to it narrow down with a small reproducible test.

I have also prepared in bug 667731 everything needed to try an alternative backend on the load infra.

Comment 1

[Tarek Ziadé (:tarek)]

Attachment: Sample to reproduce the lock

This WSGI server reproduces the issue. If you call it 10 times in a row with "curl http://localhost:8888" it will work on async mode.

If you decomment the gevent call, making it async, it will lock.

Now that I have a reproducible test I am going to try the alternative connector, and if it also fails, dig in the greenlets code

Comment 2

[Tarek Ziadé (:tarek)]

I also have failures (no lock this time) with myconnpy. digging.

btw, select sleep(x) works well, so it might be related to the query in play

Comment 3

[Tarek Ziadé (:tarek)]

Applying this patch in pymysql fixes the issue in my test !

http://groups.google.com/group/pymysql-users/browse_thread/thread/4c03e54d77e38f54#

We need to try this in the load test environment now, with batches of 100

Comment 4

[Tarek Ziadé (:tarek)]

The previous patch did not resolve the lock in all cases, but this other patch found by Pete seem to work prefectly (applied on PyMySQL):

https://bugs.launchpad.net/myconnpy/+bug/711520

We've pushed a custom version on our internal PyPI repo

Comment 5

[Tarek Ziadé (:tarek)]

The load test was a success. 

I'll follow up with the PyMySQL maintainer, and we're using our own patched version in the interim.

Comment 6

[Tarek Ziadé (:tarek)]

Follow-up discussion is here: http://groups.google.com/group/pymysql-users/browse_thread/thread/4bde28d903b06806

Comment 7

[:Atoll]

v2 patch at http://dl.dropbox.com/u/7533709/PyMySQL-0.4-sock-sendall-fix-v2.patch that fixes all four occurrences of sock.send()

Comment 8

[Tarek Ziadé (:tarek)]

Pushed at http://pypi.build.mtv1.svc.mozilla.com/extras/PyMySQL-0.4.2.tar.gz
And Makefile changed at http://hg.mozilla.org/services/server-storage/rev/2b84fb6aaa59

Comment 9

[Pete Fritchman [:petef]]

latest patch in prod.