多时间维度启停状态绘图：数据库连接使用情况可视化需求

Got it, let's figure out how to visualize your database connection states over time—each connection as a line showing when it's active vs idle. This is a super useful way to spot connection leaks or usage patterns, so here's a practical breakdown:

To map each connection's active/idle cycles, you'll need to break down each connection's lifecycle into discrete active time segments. If a connection goes idle then reactivates later, split that into two separate entries (one for each active window).

Here's a clean, machine-readable format example:

connection_data = [
    {"conn_id": "Conn_1", "start": 0, "end": 4},  # Active from 0-400ms
    {"conn_id": "Conn_1", "start": 7, "end": 12}, # Idle 4-700ms, active again 7-1200ms
    {"conn_id": "Conn_2", "start": 1, "end": 8},
    {"conn_id": "Conn_3", "start": 3, "end": 5},
    {"conn_id": "Conn_3", "start": 9, "end": 15},
    {"conn_id": "Conn_4", "start": 0, "end": 15}  # Always active
]

Each entry uses 100ms units for start and end (matching your requirement), and conn_id ties segments to the same connection.

I'll cover two common approaches: a quick static plot with Python's Matplotlib, and an interactive web-based plot with D3.js for deeper exploration.

Option 1: Static Plot with Matplotlib #

This is perfect for quick debugging or sharing static reports. It draws horizontal lines for each connection's active periods, with gaps representing idle time.

import matplotlib.pyplot as plt

# Use the connection_data from above
unique_conns = list(set(item["conn_id"] for item in connection_data))
# Assign a unique Y-position to each connection
conn_y_pos = {conn: i+1 for i, conn in enumerate(unique_conns)}

# Set up the plot
plt.figure(figsize=(12, 6))

# Draw horizontal lines for each active segment
for item in connection_data:
    y = conn_y_pos[item["conn_id"]]
    plt.hlines(
        y=y, 
        xmin=item["start"], 
        xmax=item["end"], 
        linewidth=4, 
        color='#2ecc71'  # Green for active state
    )

# Customize axes
plt.yticks(list(conn_y_pos.values()), list(conn_y_pos.keys()))
plt.xlabel('Time (100ms units)')
plt.ylabel('Database Connection ID')
plt.title('Database Connection Active States Over Time')
plt.grid(axis='x', linestyle='--', alpha=0.7)

plt.tight_layout()
plt.show()

The result will have each connection on its own row, with green lines showing when it's active—gaps between lines are the idle periods.

Option 2: Interactive Plot with D3.js #

If you need to zoom, pan, or hover to see details (like exact active duration), use D3.js for a web-based interactive visualization.

<!DOCTYPE html>
<html>
<head>
    <title>DB Connection States</title>
    <script src="https://d3js.org/d3.v7.min.js"></script>
    <style>
        .conn-line { stroke: #2ecc71; stroke-width: 10; }
        .axis text { font-size: 12px; }
        .tooltip { background: #333; color: white; padding: 4px 8px; border-radius: 4px; }
    </style>
</head>
<body>
    <div id="chart"></div>
    <script>
        const connectionData = [
            {"conn_id": "Conn_1", "start": 0, "end": 4},
            {"conn_id": "Conn_1", "start": 7, "end": 12},
            {"conn_id": "Conn_2", "start": 1, "end": 8},
            {"conn_id": "Conn_3", "start": 3, "end": 5},
            {"conn_id": "Conn_3", "start": 9, "end": 15},
            {"conn_id": "Conn_4", "start": 0, "end": 15}
        ];

        const width = 800, height = 300;
        const margin = {top: 20, right: 20, bottom: 30, left: 80};

        const svg = d3.select("#chart")
            .append("svg")
            .attr("width", width + margin.left + margin.right)
            .attr("height", height + margin.top + margin.bottom)
            .append("g")
            .attr("transform", `translate(${margin.left},${margin.top})`);

        // Map connections to Y-axis positions
        const uniqueConns = [...new Set(connectionData.map(d => d.conn_id))];
        const yScale = d3.scaleBand()
            .domain(uniqueConns)
            .range([0, height])
            .padding(0.2);

        // Map time units to X-axis
        const xScale = d3.scaleLinear()
            .domain([0, d3.max(connectionData, d => d.end)])
            .range([0, width]);

        // Draw active segments
        svg.selectAll(".conn-line")
            .data(connectionData)
            .enter()
            .append("line")
            .attr("class", "conn-line")
            .attr("x1", d => xScale(d.start))
            .attr("x2", d => xScale(d.end))
            .attr("y1", d => yScale(d.conn_id) + yScale.bandwidth()/2)
            .attr("y2", d => yScale(d.conn_id) + yScale.bandwidth()/2)
            // Add hover tooltip
            .on("mouseover", (event, d) => {
                const tooltip = d3.select("body").append("div")
                    .attr("class", "tooltip")
                    .style("position", "absolute")
                    .style("left", `${event.pageX + 10}px`)
                    .style("top", `${event.pageY - 20}px`)
                    .text(`Conn ${d.conn_id}: ${d.end - d.start}00ms active`);
            })
            .on("mouseout", () => d3.select(".tooltip").remove());

        // Add axes
        svg.append("g")
            .attr("transform", `translate(0,${height})`)
            .call(d3.axisBottom(xScale).tickFormat(d => `${d} x100ms`));

        svg.append("g")
            .call(d3.axisLeft(yScale));
    </script>
</body>
</html>

Save this as an HTML file, open it in a browser, and you'll get an interactive plot where hovering over lines shows the active duration, and you can zoom/pan to focus on specific time windows.

• Always split reactivated connections into separate active segments—don't merge them, or you'll hide idle periods.
• Stick to consistent 100ms units for time values to avoid axis misalignment.
• If you want to highlight idle states explicitly, you can add light gray lines for idle periods between active segments, but gaps are usually clearer for this use case.

内容的提问来源于stack exchange，提问作者Andreas Wederbrand