Question: What is the difference between P90 and P99 latency in database performance metrics?

Answer

In performance analysis, particularly for databases and network-dependent systems, P90 and P99 are common terminologies that refer to percentiles of response times. "P" stands for percentile, so P90 means the 90th percentile and P99 means the 99th percentile.

P90 latency or the 90th percentile latency, essentially means that 90% of the requests have a latency (response time) that is less than this value. In other words, the remaining 10% of requests take longer than the P90 latency.

P99 latency or the 99th percentile latency, on the other hand, means that 99% of the requests have a latency that is less than this value. The remaining 1% of requests take longer than the P99 latency.

Understanding these metrics is important for assessing the overall performance profile of your system. For instance, if your P99 latency is significantly higher than your P90 latency, it could indicate that some small portion of your queries (those in the 91st-99th percentile range) are particularly slow. This could point towards an issue like inconsistent network conditions, resource contention, or specific complex queries affecting the database performance.

Typically, you might measure and analyze these metrics using a tool like Prometheus with Grafana, which can give visual insights into these metric over time. Here's an example of how you'd query these metrics from a hypothetical latency histogram in Prometheus:

histogram_quantile(0.90, sum(rate(http_request_duration_seconds_bucket[5m])) by (le))
histogram_quantile(0.99, sum(rate(http_request_duration_seconds_bucket[5m])) by (le))

In this code, http_request_duration_seconds_bucket is a histogram metric representing the latency of HTTP requests. The histogram_quantile function calculates the desired percentile (0.90 for P90 and 0.99 for P99), over a 5-minute window.

The goal here is to minimize these latency values, particularly your P99, as that will ensure that even the slowest 1% of your requests are still reasonably fast.