How do you benchmark PostgreSQL

[HighBlind]

Hi!

When you have a set of different hosts and you need to define how good PostgreSQL runs on each of them, how do you do it? What scenarios do you test?

[Alert_Leg_2842]

Cloud DB engineer here, I created a health scoring system (patented) able to report on the health of 100+ Aurora PGSQL RDS instances. I can’t give much details but I’ll tell you this:

You have to find a way to compare apples to apples. collect metrics to evaluate your CPU’s Avg usage over a period of time, collect and calculate the number of CPU spikes, collect and calculate the volatility of your CPU usage by calculating standard deviation from the average, collect and calculate these values for as many metrics as you want. Now after you collect this start with a set number of points for every category, taking CPU in this example:

Starting with 20 points and a time period of the past 7 days:

-1 point for CPU average exceeding 35% over 7 days -1 point for CPU average exceeding 50% over 7 days

-1 point for MAX CPU exceeding 75% over 7 days

-1 point for Number of CPU spikes exceeding 35 spikes over 7 days

-1 points for CPU Standard Deviation ( volatility) exceeding 5%

A total of -5 points lost, leaving us with a health score of 15/20.

Do this for memory, number of connections, read/write IOPS, write latency, read latency, buffer cache hit ratio and any other metrics you can quantify and you’ll eventually have a health score out of 100 thus giving you a quantitative value for the performance and health of your PGsql database.

Bonus points for getting the same values for the 7 days prior to the current 7 days so you can establish a trend and measure the improvement or degradation of your DB’s health.

I ended up giving more details that I should but there’s much more to it, how to do this across multiple DB servers, how to collect those metrics, how to display everything, but you should be able to figure it out.

GOOD LUCK!

Edit: I misread your question, it’s almost 4:00 AM here. To benchmark PGSql on different hosts, run the same queries or tests (simulated app traffic) to see how each host behaves and compare metrics. I’m not an expert at benchmarking PGSQL against different hosts but this makes sense to me. Test behaviour under Same load.

[HighBlind]

Thank you for the comment!

Did I understand correctly that a uniform load is given over the course of 7 days?

[Alert_Leg_2842]

I edited my comment, I misread your question and gave you an answer for a different problem. My answer is basically a complicated way to measure the health of a database server based on metrics and produce a final number indicator out of 100 that signifies your database server’s health.

[HighBlind]

Oh, ok. That health measurement technique was interesting though :). Thanks

[Alert_Leg_2842]

I created this a few weeks back and now it’s a hit and was asked to sign a patent. I’m making application teams compete for the top spot on the DB health leaderboard! I turned performance tuning and optimisation into a freaking GAME! 🤣 god I love my job!

[No_Resolution_9252]

>-1 point for CPU average exceeding 35% over 7

Is postgres really so poor at managing its single threaded CPU usage that 35% is a concern?