How We Reduced a 37‑second API Call to 1.5 s with Flask Profiling & MySQL Tweaks

Background

Our business platform’s settings page was taking an absurd 36 seconds to load, which was unacceptable for users.

Investigating with Chrome Network

Using Chrome’s Network panel we observed that the total request time was 17.67 seconds, with 17.57 seconds spent in the Waiting (TTFB) state. TTFB (Time to First Byte) measures the server’s response latency.

Profiling Flame Graph & Code Analysis

The flame graph revealed that the bottleneck lay in the backend Python + Flask code. The relevant function was get_max_cpus, which spawns a thread per group ID (gid) to fetch CPU max values.

def get_max_cpus(project_code, gids):
    ...
    for gid in gids:
        t = Thread(target=get_max_cpu, args=(...))
        threads.append(t)
        t.start()
    for t in threads:
        t.join()
    return max_cpus

Two problems were identified:

Creating and destroying threads for each web‑API call incurs high overhead; a thread pool would be cheaper.

The data (maximum CPU per gid) has limited value, so loading it eagerly adds unnecessary cost.

First Wave Optimization: Redesign Interaction

We changed the UI to load the CPU max values only on user interaction, eliminating the need for multithreading. The flame graph after this change showed a much healthier distribution.

Second Wave Optimization: MySQL Query Tuning

Further profiling highlighted utils.py:get_group_profile_settings as a database hotspot. Although an index existed, the code performed a separate query for each gid, causing N + 1 queries.

for gid in gids:
    result = session.query(ProfileSetting).filter(ProfileSetting.name == f"{project_code}:{gid}").first()
    ...

We replaced the per‑gid queries with a single batch query using IN and moved the filter outside the loop:

# Batch query
query_results = session.query(ProfileSetting).filter(
    ProfileSetting.name.in_([f"{project_code}:{gid}" for gid in gids])
).all()

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'],
    }

The updated flame graph confirmed a dramatic reduction in database‑related hot spots.

Results

After both optimization waves, the same endpoint’s response time dropped from 37.6 seconds to 1.47 seconds.

Takeaways

Key lessons include:

When possible, remove or defer expensive features.

Batch database queries instead of issuing many single‑row queries.

Use profiling tools to pinpoint real bottlenecks rather than guessing.

Consider the cost‑benefit ratio; further micro‑optimizations may yield diminishing returns.