We have a proprietary application that we're having trouble scaling due to what we believe is a transaction related contention issue.
When we run a single instance our application performs reasonably well but then bottlenecks with high CPU usage but low query throughput after we have 3 or more operational instances. Each instance performs a proprietary calculation that generates a reasonably small load (100 QPS) with simultaneous reads and writes on a local MySQL db. In a separate stress test written in Node/JS we were able to run 8k+ QPS of very similar composition on the same server with moderate load.
In order to accomplish our business objectives we need the ability to run dozens of such instances in parallel each querying the same db which we then analyze after their completion.
We're currently using Python 3.5 along with the SQLAlchemy db abstraction layer. We believe the problem has something to do with how SQLAlchemy is managing the db connections/sessions and also automatically issuing transaction commits on all INSERT/UPDATE statements which could be causing wide row level table locks for any complex SELECTS that get bundled in the transaction. We've tried to disable this functionality but without much success as it appears that the transactions are automatically enabled on all the available isolation modes. As an alternative we may need to find and implement another high performance abstraction layer to replace SQLAlchemy to resolve the scaling issue.
Note: Due to the sensitive nature of the project we are unable to provide complete access to the code base but will instead work with the consultant to resolve the problems using isolated sample test cases.
Less than 30 hrs/week
Less than 1 month< 1 monthProject LengthDuration
I am willing to pay higher rates for the most experienced freelancers