Optimizing a Slow Settings Page: Profiling, Threading, and MySQL Improvements in a Python Flask Backend

Background: A business platform’s settings page took up to 36 seconds to load, which was unacceptable for users.

Investigation: Using Chrome’s Network panel the team discovered that most of the latency (≈17.57 s) was spent in the Waiting (TTFB) phase. A flame‑graph profile confirmed that the bottleneck lay in the backend Python + Flask code.

First wave of optimization – UI redesign and threading removal: The original implementation launched a new Thread for each gid to fetch CPU‑max values, causing high thread‑creation cost and unnecessary concurrency. The solution was to change the feature to load data on user interaction instead of on page load, and to drop the per‑gid threading entirely.

Second wave – MySQL query optimization: The code queried the database inside a loop, issuing one query per gid. This resulted in many round‑trips and repeated ORM object creation. The improved version batches the queries with an IN clause and moves the filter outside the loop, reducing ORM overhead and eliminating repeated getAttr calls.

def get_group_profile_settings(project_code, gids):
    ProfileSetting = unpurview(sandman.endpoint_class('profile_settings'))
    session = get_postman_session()
    # batch query
    query_results = session.query(ProfileSetting).filter(
        ProfileSetting.name.in_(project_code + ':' + gid for gid in gids)
    ).all()
    profile_settings = {}
    for result in query_results:
        if not result:
            continue
        result = result.as_dict()
        gid = result['name'].split(':')[1]
        profile_settings[gid] = {
            'tag_indexes': result.get('tag_indexes'),
            'interval': result['interval'],
            'status': result['status'],
            'profile_machines': result['profile_machines'],
            'thread_settings': result['thread_settings']
        }
    return profile_settings

Results: After applying both waves of optimization, the endpoint’s response time dropped from 37.6 seconds to 1.47 seconds, as shown by the final flame‑graph screenshots.

Further considerations: Front‑end rendering can be improved by showing loading placeholders, additional profiling may uncover more GetAttr overhead, and in extreme cases a rewrite in Go could be explored. However, the team notes that the current 1.47 s performance is already acceptable for large projects, and further gains may have low ROI.