It seems to be affecting our largest customers first
Introduction
Warm-up
1️⃣ Have you ever had a production issue where a query suddenly became very slow?
What was the root cause, and how did you investigate it?
2️⃣ What would you do if a critical dashboard endpoint started timing out during peak hours?
Describe the first three steps you would take to diagnose the problem.
3️⃣ Have you ever dealt with rapid data growth in a database?
What strategies did you use (or would you use) to prevent performance degradation over time?
Speaking
Dialog
Bill: Hey, we’re having an issue in production. The dashboard endpoint is timing out. Users are reporting that it either takes forever to load or returns a 504 error.
It seems to be affecting our largest customers first.
Patrick: Alright. Do you have the exact query that the endpoint is running? And what’s the current average execution time compared to normal?
I’d also like to know if this is happening consistently or only during peak hours.
Bill: Yes, I pulled it from the logs. It used to run in about 200 milliseconds. Since this morning, it’s fluctuating between 8 and 12 seconds. The slow query log confirms the spike.
Patrick: That’s a significant degradation. Did anything change recently? Any schema modifications, deployments, or noticeable data growth? Performance issues usually correlate with some recent change.
Bill: We deployed a migration yesterday. We added a new column and updated some constraints. Also, the transactions table grew quite a bit this week due to a marketing campaign. We inserted a large batch of records over the weekend.
Patrick: That could definitely impact performance. Let me check the execution plan. Did you already run an EXPLAIN or EXPLAIN ANALYZE? The actual execution plan will tell us where the time is being spent.
Bill:
I ran EXPLAIN, but I’m not sure how to interpret it. It showed high cost numbers and a lot of rows being processed.
Patrick: No problem. I’m looking at it now. I can see it’s performing a full table scan on the transactions table. That explains the increase in latency. It’s scanning millions of rows before applying the filter.
Vocabulary
[having an issue, timing out, dashboard endpoint, either, run a query, during peak hours, average execution time, data growth, pulled it from, spike, significant degradation, batch of records, full table scan ]
Expression | Portuguese | Explanation (English) | Example Sentence |
|---|---|---|---|
having an issue | estar enfrentando um problema | Experiencing a problem or difficulty. | We’re having an issue with the payment gateway in production. |
timing out | exceder o tempo limite | When a system stops waiting for a response because it took too long. | The API request is timing out after 30 seconds. |
run a query | executar uma consulta | To execute a database command to retrieve or manipulate data. | I ran a query to check how many users signed up today. |
data growth | crescimento de dados | The increase in the volume of stored data over time. | Rapid data growth can impact database performance. |
average execution time | tempo médio de execução | The typical time it takes for a query or process to complete. | The average execution time increased from 200ms to 10 seconds. |
dashboard endpoint | endpoint do painel (dashboard) | A backend route or API that provides data to a dashboard interface. | The dashboard endpoint aggregates metrics from multiple services. |
during peak hours | durante o horário de pico | The busiest time period when system usage is highest. | The system becomes slower during peak hours. |
pulled it from | extrai / peguei de | Retrieved information from a specific source. | I pulled it from the application logs. |
spike | pico / aumento repentino | A sudden and sharp increase. | We saw a spike in database load after the deployment. |
batch of records | lote de registros | A large group of records processed together. | The job inserted a batch of records overnight. |
significant degradation | degradação significativa | A noticeable decline in performance or quality. | There was a significant degradation in response time. |
full table scan | varredura completa da tabela | When the database reads every row in a table to find matching results. | Without an index, the database performs a full table scan.--- |
Exercises
Reading Comprehension Questions
What was the original average execution time of the query, and how much did it increase?
Why did Patrick suspect that the issue might be related to a recent change?
What caused the full table scan, and why is it problematic for large tables?
How did data growth contribute to the performance issue?
Fill-in-the-Blanks
Use the words below to complete the sentences:
Word Bank:
(having an issue, timing out, run a query, data growth, average execution time, dashboard endpoint, during peak hours, pulled it from, spike, batch of records, significant degradation, full table scan)
We are __________________ with the reporting service in production.
The API keeps __________________ after 30 seconds.
I decided to __________________ to check the number of failed transactions.
The system became unstable due to rapid __________________ over the past month.
The __________________ increased from 200ms to 10 seconds.
The __________________ is responsible for loading the metrics page.
The slowdown is worse __________________ when more users are online.
I __________________ the execution plan from the database logs.
We noticed a sudden __________________ in CPU usage.
The job inserted a large __________________ over the weekend.
There was a __________________ in performance after the migration.
Because there was no proper index, the database performed a __________________.
Role-Play Activity
Use the words below to create one role-play (Conversation between two people).
Word Bank:
(having an issue, timing out, run a query, data growth, average execution time, dashboard endpoint, during peak hours, pulled it from, spike, batch of records, significant degradation, full table scan)
