PostgreSQL – Prevent Heavy Queries from Affecting Performance of Other Databases

cpumulti-tenantperformancepostgresqlpostgresql-performance

I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server.

One of the databases has some computationally intensive queries run on it from time to time. This causes the CPU utilization to jump up to 100% and the sevrer often does not respond to requests.

How to Prevent heavy queries on one database, affecting the performance of other databases on same server? How can I make sure that other databases on that PostGres Instance still respond to requests? Is there a way to prevent one database from consuming all the CPU cycles?

Best Answer

I'll quote a few things from Postgres wiki, Priorities:

Prioritizing users, queries, or databases

PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system facilities to achieve what limited prioritization is possible.

There are three main resources that PostgreSQL users, queries, and databases will contend for:

Memory
CPU
Disk I/O

Of these, disk I/O is commonly a bottleneck for database applications, but that's not always the case. Some schema designs and queries are particularly CPU heavy. Others really benefit from having lots of memory to work with, typically for sorting.

Consider this part in in particular:

Are priorities really the problem?

Before struggling too much with prioritizing your queries/users/databases, it's worthwhile to optimize your queries and tune your database. You may find that you can get perfectly acceptable performance without playing with priorities or taking extreme measures, using techniques such as:

  • Improving your queries
  • Tune autovacuum to reduce bloat
  • Generally polishing your cluster's performance
  • Avoiding use of VACUUM FULL. That eat lots of memory and take forever to scan, wasting disk I/O bandwidth. See the wiki page on VACUUM FULL for more information.

And especially this:

Is CPU really the bottleneck?

People often complain of pegged (100%) CPU and assume that's the cause of database slowdowns. That's not necessarily the case - a system may show an apparent 100% CPU use, but in fact be mainly limited by I/O bandwidth. Consider the following test, which starts 20 `dd' processes, each reading a different 1GB block from the hard disk (here: /dev/md0) at 1GB offsets (run the test as root).

... test follows ...

... which could be confused for a busy CPU, but is really load caused by disk I/O. The key warning sign here is the presence of a high iowait cpu percentage ("%wa"), indicating that much of the apparent load is actually caused by delays in the I/O subsystem. Most of the `dd' processes are in 'D' state - ie uninterruptable sleep in a system call - and if you check "wchan" with "ps" you'll see that they're sleeping waiting for I/O.

Rather than assuming that CPU contention is the issue, it's a good idea to use the available Performance Analysis Tools to get a better idea of where your system bottlenecks really are.

So, I'd say that you should:

  • first, follow the advice above, perform some more analysis tests and make sure that the bottleneck is from the database you suspect and that it's caused by CPU contention (which may well be the case if your queries are so computationally expensive). Or it may be partially true and there's CPU contention but also room for query improvements.

  • follow the advice in the 2nd paragraph above (improve the queries, generally polish the cluster, check and optimize vacuum and autovaccum settings.)
    You should also optimize the various memory settings (mostly work_mem) with different values for the clients that connect to this database that has different workload.

  • If after all those, the issue remains, and there are still CPU or disk IO contention or both, there are some options described in the linked Priorities page that you can try ("renice", prioritize extension, etc).

  • One of the suggestions there (that is certainly worth considering) is separating your databases into 2 clusters (instances), one for the computanionally expensive db and another for all the rest, with the usual workload. See Prioritizing databases by separating into multiple clusters, where it mentions the likely cleaner option:

  • Separate machines

    Your best option is to separate PostgreSQL instances onto different physical machines. They won't compete for resources at all.

Related Question