How You Can Stay Up-To-Date with Adyen Tech
In this post, we highlight how we have been able to track HOT updates and tune Fillfactor with Prometheus and Grafana.
We track HOT updates by tracking the `pg_stat_user_tables` with Prometheus and Grafana. First, before jumping into how we track this, let’s recap a few key points on why write amplification happens and HOT updates can mitigate it:
With these background rules in mind, let's start looking at a few key metrics from the Prometheus Postgres exporter:
Polling `pg_stat_user_tables` (every 5 minutes)
Our first graph below is based on the five(5) minutes deltas or differences between:
From the graph, we can interpret that:
Typically, write amplification occurs around frequently updated tables.
Our next graph is based on the 15 minutes deltas of:
From this graph, we can interpret that over this day, there is a pattern with initially high inserts (yellow), but this is soon dominated by updates(blue) and no deletes.
Also, we can see this pattern of UPDATEs dominating INSERTs over the following week:
Our next graph is based on the total number of dead rows - `pg_stat_user_tables_n_dead_tup`:
From the graph, we can interpret that:
Our next graph is based on the size of pg_xlog (contains Postgres WAL) which we track over a rolling eight(8) hour window.
The key things to highlight here:
i.e. nearly a 20% saving! Not just once, we keep multiple copies of the WAL files plus, of course, backups of these WAL files!
Note: Our prometheus exporter `pg_xlog_position` is now based on `pg_current_wal_lsn` which has replaced `pg_current_xlog_location`: https://github.com/prometheus-community/postgres_exporter/issues/495
Our next graph is based on the combined size of the table `pg_relation_size_table` and related indexes `pg_relation_size_table`.
By running with a low fill factor where each block was created with 15% unused space - 85% FF, we were expecting a larger table and larger storage overhead, but the total size (table and index) was significantly lower. We had up to 0.56 with 100% and 0.46 with 85%!
Combined pg_relation_size_table and pg_relation_size_indexes
Our next graph shows the difference between
The effect of reducing the FF from 100% to 80% around between day 3 and day 4 is much clearer with the DELTA based chart.
Using 100 * pg_stat_user_tables_n_tup_hot_upd/pg_stat_user_tables_n_tup_upd but combined with delta(8h)
It is the responsibility of Database Architects (DBAs) to warn Developers about the dangers of Long Running Queries/Transactions.
However, unless the developers can tangibly see the effect of their LRQ, they are likely to overlook or even forget this important advice. Also how do the developers know ahead when their code is going to hit an edge case and trigger LRQs?
The nice thing with our new dashboards is that the developers can see the impact of problems like this, just like on day 6 shown below and then we can start breaking it down.
Here is some of the awesome feedback from one of our development teams:
After the first FF & HOT update internal presentation at Adyen and rolling out the new dashboards, we were asked to review a background process which was taking too long.
This particular development team had been trying to understand inconsistent job performance for a very long time. They had checked dozens of metrics in the application and infrastructure layers, and had found no obvious clues based on CPU, IO and/or network load in the application or database. The only correlation was this “typically ran slow particularly after large batch INSERTs”?
Fortunately, the new dashboard clearly showed there would be significant gains with a lower FillFactor. We got an immediate 30% time saving by simply reducing FF on the existing table (non partitioned). Since then, the dev team have also refactored logic, reduced LRQs and now the key job is four(4) times faster!
So, we started with an easy win by tuning the FillFactor and then got an even bigger win by breaking down some long running transactions and getting even closer to 100% HOT Updates.
We had some very nice results from tuning FillFactor and we have some fancy graphics to show this, but that's not our main point yet. The bigger win here is that we have a Prometheus/Grafana dashboard which allows various development teams to track the performance of their key tables.
Since giving the first version of this presentation about a couple of months ago, multiple development teams have proactively come with tables which we need to tune either by adjusting FillFactor and/or by tackling very Long Running Transactions.
Finally, it is important to note that there are always exceptional cases. For example, in one case where we needed to heavily update an indexed column, it was good to follow strict normalization rules. We broke down tables with a large number of columns into smaller tables; hence less columns and less indexes.
If you only have one index on a table, even a regular, non-HOT update will only involve two block updates for damage control. However, most of the time, we can optimize our processes to achieve close to 100% HOT updates. This is a great target for our development teams who are definitely up for this challenge!
By submitting this form, you acknowledge that you have reviewed the terms of our Privacy Statement and consent to the use of data in accordance therewith.