... | ... | @@ -1274,6 +1274,95 @@ including the former were removed by hand. Then a full backup was |
|
|
performed. The reason why the BASE backup was missing is this was
|
|
|
following a failed upgrade (see [tpo/tpa/team#40809](https://gitlab.torproject.org/tpo/tpa/team/-/issues/40809)).
|
|
|
|
|
|
### OOM (Out Of Memory)
|
|
|
|
|
|
We have had situations where PostgreSQL ran out of memory a few times
|
|
|
([tpo/tpa/team#40814][], [tpo/tpa/team#40482][],
|
|
|
[tpo/tpa/team#40815][]). You can confirm the problem by looking at the
|
|
|
node exporter graphs, for example this link will show you the last 4
|
|
|
months of memory usage on materculae:
|
|
|
|
|
|
<https://grafana.torproject.org/d/Z7T7Cfemz/node-exporter-full?orgId=1&var-job=node&var-node=materculae.torproject.org&var-port=9100&from=now-4M&to=now&viewPanel=78&refresh=1m>
|
|
|
|
|
|
The blue "dots" (if any) show the number of times the OOM-killer was
|
|
|
called. If there are no dots, it wasn't called, obviously. You can see
|
|
|
examples of graphs like this in the history of [tpo/tpa/team#40815][].
|
|
|
|
|
|
If you are not sure PostgreSQL is responsible, you should be able to
|
|
|
confirm by looking at the per-process memory graphs established in
|
|
|
[July 2022](https://gitlab.torproject.org/tpo/tpa/team/-/issues/40814#note_2817458). Here's, for example, a graph of the per-process memory
|
|
|
usage on materculae for the past 60 days:
|
|
|
|
|
|
<https://grafana.torproject.org/d/LbhyBYq7k/per-process-memory-usage?orgId=1&var-instance=materculae.torproject.org&var-process=java&var-process=postgres&var-min_size=0&from=now-60d&to=now>
|
|
|
|
|
|
... or a similar graph for processes with more than 2GB of usage:
|
|
|
|
|
|
<https://grafana.torproject.org/d/LbhyBYq7k/per-process-memory-usage?orgId=1&var-instance=materculae.torproject.org&var-process=java&var-process=postgres&var-min_size=2000000&from=now-7d&to=now>
|
|
|
|
|
|
This was especially prominent after the Debian bullseye upgrades where
|
|
|
there is a problem with the JIT compiler enabled in PostgreSQL 13
|
|
|
([Debian bug 1019503]( https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=1019503), [upstream thread](https://www.postgresql.org/message-id/flat/16707-f5df308978a55bf8%40postgresql.org)). So the first thing to
|
|
|
do if a server misbehaves is to disabled the JIT:
|
|
|
|
|
|
sudo -u psql -c 'SET jit TO OFF';
|
|
|
|
|
|
This is specifically what fixed a recurring OOM on Materculae in
|
|
|
September 2022 ([tpo/tpa/team#40815][]).
|
|
|
|
|
|
If that fails, another strategy is to try to avoid using the OOM
|
|
|
killer altogether. By default, the Linux kernel [over commits][]
|
|
|
memory, which means it actually allows processes to allocate more
|
|
|
memory than is available on the system. When that memory is actually
|
|
|
*used* is when problems can occur, and when the OOM killer intervenes
|
|
|
to kill processes using "heuristics" to hopefully kill the right one.
|
|
|
|
|
|
The [PostgreSQL manual](https://www.postgresql.org/docs/current/) actually [recommends disabling that
|
|
|
feature](https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT) with:
|
|
|
|
|
|
sysctl -w vm.overcommit_memory=2
|
|
|
sysctl -w vm.overcommit_ratio=90
|
|
|
|
|
|
To make this permanent, add the setting in `/etc/sysctl.d/`:
|
|
|
|
|
|
echo vm.overcommit_memory=2 > /etc/sysctl.d/no-overcommit.conf
|
|
|
echo vm.overcommit_ratio=90 >> /etc/sysctl.d/no-overcommit.conf
|
|
|
|
|
|
This will keep the kernel from over-allocating memory, limiting the
|
|
|
total memory usage to the swap size plus 90% of the main memory
|
|
|
(default is 50%). Note that the comments about the `oom_score_adj` do
|
|
|
not apply to the Debian package as it already sets a proper score for
|
|
|
the PostgreSQL server.
|
|
|
|
|
|
[tpo/tpa/team#40815]: https://gitlab.torproject.org/tpo/tpa/team/-/issues/40815
|
|
|
[tpo/tpa/team#40814]: https://gitlab.torproject.org/tpo/tpa/team/-/issues/40814
|
|
|
[tpo/tpa/team#40482]: https://gitlab.torproject.org/tpo/tpa/team/-/issues/40482
|
|
|
[over commits]: https://www.kernel.org/doc/Documentation/vm/overcommit-accounting
|
|
|
|
|
|
Concretely, avoiding overcommit will make the caller fail when it
|
|
|
tries to allocate memory. This can still lead to PostgreSQL crashing,
|
|
|
but at least it will give a more useful stack trace that will show
|
|
|
*what* was happening during that allocation.
|
|
|
|
|
|
Another thing to look into is possible bad behavior on the client
|
|
|
side. A client could abuse memory usage by doing multiple `PREPARE`
|
|
|
statements and never executing them. "HOLD cursors" are also
|
|
|
something, apparently.
|
|
|
|
|
|
Finally, PostgreSQL itself can be tweaked, see this part of the
|
|
|
upstream documentation, again:
|
|
|
|
|
|
> In some cases, it may help to lower memory-related configuration
|
|
|
> parameters, particularly [`shared_buffers`][], [`work_mem`][], and
|
|
|
> [`hash_mem_multiplier`][]. In other cases, the problem may be caused
|
|
|
> by allowing too many connections to the database server itself. In
|
|
|
> many cases, it may be better to reduce [`max_connections`][] and
|
|
|
> instead make use of external connection-pooling software.
|
|
|
|
|
|
[`shared_buffers`]: https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS
|
|
|
[`work_mem`]: https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM
|
|
|
[`hash_mem_multiplier`]: https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-HASH-MEM-MULTIPLIER
|
|
|
[`max_connections`]: https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
|
|
|
|
|
|
## Disaster recovery
|
|
|
|
|
|
If a PostgreSQL server is destroyed completely or in part, we need to
|
... | ... | |