In another blog post, I described how Linux control groups work and how they are useful for PostgreSQL.
Here I will present my little PostgreSQL plugin pg_cgroups which makes this simple to handle for the DBA.
What is pg_cgroups good for?
If you have a machine dedicated to a single PostgreSQL cluster, you normally do not want to limit PostgreSQL’s resource usage.
But sometimes you have more than one cluster running on a single machine, or you have an application server running on the same machine.
In that case, you may want to prevent one PostgreSQL cluster from using up so many resources that it starves the other programs on the same machine.
To some extent, the CPU and I/O schedulers can take care of that, but it can be nice to have some control. For example, you may want a small, unimportant database to not use up more than 10 percent of your CPU time and I/O bandwidth. And while you can limit PostgreSQL’s memory usage through
work_mem, it might be nice to keep one cluster from blowing other cluster’s data out of the file system cache.
How does pg_cgroups work?
I won’t describe the complete setup here (see the project page for that), but here is a short description how it works:
/etc/cgconfig.confso that an empty control group
/postgresis created at boot time. This control group can be administrated by the
postgresoperating system user.
- Build and install pg_cgroups. This will require the PostgreSQL headers to be installed.
- Add pg_cgroups to
postgresql.confand restart PostgreSQL.
pg_cgroups creates a new control group under
/postgres postgres and moves the postmaster to this group. All PostgreSQL processes will automatically inherit the control group.
Now you can set and adjust resource consumption limits on the fly simply by changing parameters in
postgresql.conf! There is support for limits on memory consumption, CPU time and I/O bandwidth. One particularly nice feature: this is the only way to limit the amount of file system cache that is used for PostgreSQL!
pg_cgroups makes this magic happen by modifying the control group limits whenever the parameter settings are changed.
What alternatives are there?
Of course, pg_cgroups is not the only way to manage Linux control groups. Alternatives include:
Managing control groups “by hand”
You can manage control groups directly via the
/sys/fs/cgroup file system or using libcg tools like
This usually requires
root access to the machine, which DBAs often don’t have. Sure, you can set up a control group hierarchy managed by the
postgres user (like pg_cgroups does), but you still have to memorize the cgroup file hierarchy and commands.
Managing control groups via
systemd services allow setting control group limits via directives in the service file.
This is a great way of setting limits for PostgreSQL, but there are some disadvantages:
- You need
rootto change the settings.
- Changing the settings requires a restart of PostgreSQL.
Docker uses Linux control groups to enforce resource limits. So if you are already running PostgreSQL in a dockerized environment, that may be your first choice.
However, not everybody wants the added complexity of docker.
Is pg_cgroups ready for production?
The software is still in beta.
But since it is fairly simple and is not deeply tangled with PostgreSQL server code (all it does is define a few magic parameters), I don’t expect many nasty bugs.
If you run into trouble, I am happy to help.
I think this nice little piece of software is useful for everybody who runs more than one PostgreSQL cluster per machine. I hope it makes the DBA’s life a better one.