Python server handling simultaneous SQL-requests efficiently

Question

Having a server which should be able to handle hundreds of requests at the same time, I decided to do this in python and currently it is up and running, now I'm willing to optimize this code since I want to make this as lightweight as possible.

Having the following server layout:

Incoming connection
       ||
       \/
 _________________
| Handling server |  <|
|_________________|   |
       ||             |
       \/             |
      FORKING --------/
       ||
       \/
 _________________  30 Queries  ______________
| Dataprocessing  | ---------->| MySQL-Server |  
|_________________| <----------|______________| 
       ||             
       \/       
    RESPONDING & closing connection

What now happens is that if there are x simultaneous incoming connections, the MySQL-server gets really slow for a short amount of time.

Now I am looking for a way to 'merge' different mysql-requests dynamically or to reduce the workload as much as possible.

An example of a question that I could ask for this is:

How can I handle many MySQL-requests in a given timeframe (5 seconds?) while keeping the workload on the MySQL-server as low as possible

pcalcao · Accepted Answer · 2013-05-21 16:29:39Z

3

There is no easy/one-size-fits-all approach to achieve this, so what I suggest are some approaches you might consider adapting to your particular case.

The best way of relieving load on your MySql server is to use a caching layer in between, if that applies in your case, but it depends.

Scenario 1: Lots of select queries

You can use caching if the information you're fetching doesn't get changed very frequently compared to the rate of select queries.

Scenario 2: Lots of insert/update statements

This is trickier, if several statements involve changing the same tuple, you can buffer them in memory for a short time (summing them up, whatever you can do depending on your application logic), and send the aggregated results to MySql instead of each one.

Another possible approach is to check if what's taking the time is processing the actual queries or the establishment of the connections. If it's the later, you could use an approach where you pool several connections and reuse them, this will probably speed things up as well.

edited May 21, 2013 at 16:29

answered May 21, 2013 at 16:24

pcalcao

16k1 gold badge49 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lazykiddy Over a year ago

I'll do what I can with a caching layer inbetween, thanks for your answer.

Collectives™ on Stack Overflow

Python server handling simultaneous SQL-requests efficiently

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related