Caching checkbox-Login user_loader

Question

I had it.

@login_manager.user_loader def load_user(id=None): return User.query.get(id) 

This worked fine until I introduced the Flass Principal.

 @identity_loaded.connect_via(app) def on_identity_loaded(sender, identity): # Set the identity user object identity.user = current_user # return if hasattr(current_user, 'id'): identity.provides.add(UserNeed(current_user.id)) # Assuming the User model has a list of roles, update the # identity with the roles that the user provides if hasattr(current_user, 'roles'): for role in current_user.roles: identity.provides.add(RoleNeed(role.name)) 

Adding this caused a serious performance issue. SQLALCHEMY_ECHO showed that a user table was requested each time a static file was uploaded.

 #Warning: Dummy Cache users = {} @login_manager.user_loader def load_user(uid=None): if uid not in users: users[uid] = User.query.get(uid) return users[uid] 

After this experiment, which solved the problem of repeated requests, I realized that I needed to enter the cache in my Flask application. Here are the quesitons.

• How to cache User.query.get(id) ?
• When do I need to clear the user cache?

Answer 1

Old question, but there is no other answer on SO or via google, and it took some time to solve this problem, so maybe this answer will help someone.

SOLUTION TL; DR:

First, you need a cache server, I use flask-caching with redis with the python redis library from redis sudo pip install redis .

Then do a from flask_caching import Cache and then cache = Cache() , which I am doing in another file called extensions.py . This is important if you use the factory application template, because you will need to import the cache later, and this will help avoid circular reference questions for applications with large jars.

After that, you need to register the extension for flash caching in the flash application, which I make as a separate app.py file as follows:

 from flask import Flask from extensions import cache def create_app(config_obj=None): """An application factory""" app = Flask(__name__) app.config.from_object(config_obj) cache.init_app(app, config={'CACHE_TYPE': 'redis', 'CACHE_REDIS_HOST': '127.0.0.1', 'CACHE_REDIS_PORT': '6379', 'CACHE_REDIS_URL': 'redis://127.0.0.1:6379'}) return app 

So, now that cache registered in Flask, it can be imported from extensions.py and used throughout the application without circular reference questions. Going to any file that you use user_loader :

 import pickle from flask import current_user from extensions import cache from models.user_models import User @login_manager.user_loader def load_user(user_id): """Load user by ID from cache, if not in cache, then cache it.""" # make a unique cache key for each user user = 'user_{}'.format(user_id) # check if the user_object is cached user_obj = pickle.loads(cache.get(user)) if cache.get(user) else None if user_obj is None: query = User.query.get(int(user_id)) user_obj = pickle.dumps(query) cache.set(user, user_obj, timeout=3600) return query return user_obj 

Finally, when you log out, you can remove them from the cache:

 @blueprint.route('/logout/') @login_required def logout(): """Logout.""" # remove the user information from redis cache user = 'user_{}'.format(current_user.id) cache.delete(user) # remove the user information from the session logout_user() # Remove session keys set by Flask-Principal for key in ('identity.name', 'identity.auth_type'): session.pop(key, None) flash('You are logged out.', 'info') return redirect(url_for('public.home') 

This seems to work just fine, it reduces the number of SQLAlchemy queries by three page requests for each user and improves my page loading speed by 200 ms in several parts of my application, while eliminating an unpleasant problem reaching the limits of the SQLAlchemy connection pool.

The last important point for this decision. If you change the user object for any reason, for example, if you assign new roles or abilities to the user, you must clear the user object from the cache. For example, as shown below:

 # set the user_id from current_user.id CACHE object user_id = current_user.id # remove the old USER object from cache since you will change it # first set the cache key to user_{id} cache_user = 'user_{}'.format(user_id) # now delete the cache key cache.delete(cache_user) 

BACKGROUND:

My need to cache the user_loader login-flag was due to the fact that I implemented access control list management by expanding the UserMixin and AnonymousUserMixin flag classes UserMixin several class methods such as get_roles and get_abilities . I also use flask-sqlalchemy and backgresql backend, and there is a roles table and features table with relationships to the user object. These user roles and abilities are tested mainly in templates to present different representations based on user roles and abilities.

At some point, I noticed, by opening several browser tabs or simply reloading the browser page in my application, I started getting a TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 error TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30 . Flask-sqlalchemy has settings for SQLALCHEMY_POOL_SIZE and SQLALCHEMY_MAX_OVERFLOW , but increasing these values ​​just masked the problem for me, the error still occurred simply by loading additional tabs or performing additional reboots.

Digging deeper to find out the root cause, I requested postgresql DB from me using SELECT * FROM pg_stat_activity; and found for each query that I accumulated several connections with the idle in transaction state, where the SQL query was clearly associated with the user, role, and ability to check access. These idle in transaction connections caused my DB connection pool to hit capacity.

Further testing showed that caching the checkbox user_loader User object excluded idle in transaction connections, and then even if I left SQLALCHEMY_POOL_SIZE and SQLALCHEMY_MAX_OVERFLOW by default, I no longer tolerated TimeoutError: QueuePool limit . The problem is solved!