Python check large quantity of requests

Question

I'm using Python script to check if user requested exists. using:

import MySQLdb 
from flask import Flask, request, abort
app = Flask(__name__)
try:
    db = MySQLdb.connect('xxx1','my_username','my_password','my_db_name')
    db1 = MySQLdb.connect('xxx2','my_username','my_password','my_db_name')
    db2=
    db3=
except MySQLdb.OperationalError as e:
    print "Caught an exception : " + str(.message)

@app.route('/')
@app.route('/<path:path>')
def page(path = ''):
user = request.args.get('user', None)
if not mac: 
    abort (403)

cursor = db.cursor()
query = 'Select ID from f_member where Name=%s'
db.commit()
cursor execute(query, (user, ))
row = cursor.fetchone()
cursor.close()

#cursor.db1 here

if row == None and row1 == None:
    abort (403)
return 'OK', 200

if __name__ == '__main__':
    app.run(host=host, port=port)

Then i have 5 nginx servers with this:

location = /auth {
proxy_pass http://xxx.xxx$request_uri;
proxy_pass_request_body off;
proxy_set_header Content_Lenght "";
proxy_set_header X-Real-IP $remote_addr;

So the thing is, this script checks if user is found in one of the databases, if true then access the page. Problem is my user list is now getting up to 5k users. and when i run the .py script it runs so fast (even with errors 403 people who are trying to connect), then broken pipe starts to show up. Seems like it is getting overloaded, is there a better way to handle my script so it runs better and more efficient?

Answer 1

You may use a dictionary for username/id map in your Python program. Basically, when the program starts it will make a query for all the users and populate the map. Afterwards, every 20 seconds or so it will make a query to get the "changes" in f_member to update the dictionary. Lookup for username happens always in this map. If a user name is not found in the map, then only it makes a DB query (and if the user detail is found on DB, update the local map as well). If you don't have millions of users in the table, this approach will work. Otherwise use a LRU cache.

Answer 2

So, after a lengthy comment thread, it appears like your flask instances might be competing for DB resources. There's also another hypothesis that saving your connections off in global scope could have some bad side effects (I could be wrong about this, but I'd be concerned about timeouts, not closing the connections, etc). Here's how I might rewrite it:

import MySQLdb 
from flask import Flask, request, abort
app = Flask(__name__)

def get_db_connection_args():
    try:
        db_args = { 'host':'xxx1', 'user':'my_username', 'passwd':'my_password', 'db':'my_db_name' }
        db1_args = { 'host':'xxx2', 'user':'my_username', 'passwd':'my_password', 'db':'my_db_name' }
        db2_args = { 'host':'xxx3', 'user':'my_username', 'passwd':'my_password', 'db':'my_db_name' }
        db3_args = { 'host':'xxx4', 'user':'my_username', 'passwd':'my_password', 'db':'my_db_name' }
    except MySQLdb.OperationalError as e:
        print "Caught an exception : " + str(.message)
    return (db_args, db1_args, db2_args, db3_args)

@app.route('/')
@app.route('/<path:path>')
def page(path = ''):
    user = request.args.get('user', None)
    #I don't know what mac is...but it was in your original code.
    if not mac:
        abort (403)

    found = False
    db_connection_args = get_db_connection_args()
    for db_connection_arg_dict in db_connection_args:
        if not found:
            db_conn = MySQLdb.connect(**db_connection_arg_dict)
            try:
                cursor = db_conn.cursor()
                cursor.execute('Select ID from f_member where Name=%s', (user, ))
                row = cursor.fetchone()
                if row:
                    found = True
            finally:
                db_conn.close()

    if found:
        return 'OK', 200

    abort (403)

if __name__ == '__main__':
    app.run(host=host, port=port)