CPU-intensive query to simulate CPU load in PostgreSQL database

INTRO

In my previous blog post, I shared a CPU-intensive query designed to simulate 100% CPU utilization in an Oracle database, inspired by a question raised on the Oracle-L Mailing List. Building on that, I have now created a similar script for PostgreSQL to achieve the same purpose.

The CPU is inherently designed to process tasks in a time-sliced, serve-and-go model. It prioritizes active workloads, serving them as computational resources become available, and queues tasks in a ready-to-run state when resources are occupied. Testing CPU performance often involves artificially generating workloads that mimic this queuing process. Such tests essentially measure the CPU’s ability to handle demand under simulated contention, where processes either execute immediately or wait their turn in the scheduler’s queue.

Database servers are often benchmarked for Transactions Per Second (TPS) and Input/Output Operations Per Second (IOPS). These benchmarks mostly focus on storage and transaction performance. However, in scenarios such as hardware procurement, capacity planning or bottleneck identification, measuring CPU performance directly can also be a target.

Database performance testing often involves creating controlled workloads to evaluate system limits or optimize configurations. One such scenario is generating a CPU-intensive workload to assess database server CPU performance mostly speed. In this blog post, I will provide a PL/pgSQL script that utilizes pg_background extension to create and execute CPU-bound background worker processes.

No table is required, as no data will be retrieved. Instead of focusing on I/O operations, this test is designed to emphasize CPU usage. The core idea involves executing simple calculations to maximize CPU utilization.

First Build and Install pg_background extension. (PostgreSQL version >= 9.5 and Ensure pg_config is in your PATH)

osmandinc@192 ~ % curl -s -L -o pg_background.zip https://github.com/vibhorkum/pg_background/archive/refs/heads/master.zip
osmandinc@192 ~ % unzip pg_background.zip

osmandinc@192 ~ % docker cp  pg_background-master postgres-latest:/tmp/
osmandinc@192 ~ % docker exec -it postgres-latest bash

root@f06832f9ecfb:/# env
HOSTNAME=f06832f9ecfb
POSTGRES_PASSWORD=InsaneDBAiscool
PWD=/
HOME=/root
LANG=en_US.utf8
GOSU_VERSION=1.17
PG_MAJOR=17
PG_VERSION=17.2-1.pgdg120+1
TERM=xterm
SHLVL=1
PGDATA=/var/lib/postgresql/data
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/lib/postgresql/17/bin
_=/usr/bin/env

root@f06832f9ecfb:/# apt-get update
root@f06832f9ecfb:/# apt-get install -y build-essential libkrb5-dev postgresql-server-dev-17
root@f06832f9ecfb:/# cd /tmp/pg_background-master
root@f06832f9ecfb:/tmp/pg_background-master# make
root@f06832f9ecfb:/tmp/pg_background-master# make install

-- Connect sample database
postgres@f06832f9ecfb:~$ psql -d Adventureworks

-- Create extension
Adventureworks=# create extension pg_background;

--Test it
Adventureworks=# SELECT * FROM pg_background_result(pg_background_launch('SELECT count(*) FROM sales.store')) AS result(count BIGINT);
 count
-------
   701
(1 row)

Script for quickly reaching peak CPU utilization :

The pg_background_launch function requires plain, static SQL strings rather than procedural PL/pgSQL code that relies on variable context. For this reason, I will create a function and then call it. This PostgreSQL function, cpu_intensive_task, simulates CPU load by performing repetitive mathematical calculations. It takes iterations as an input, loops that many times, and computes MOD(num + CAST(SQRT(idx) AS NUMERIC), 13) during each iteration to simulate computational work. The variable num is updated in each loop iteration, continuously performing mathematical operations to utilize CPU cycles. This function is designed to generate CPU load for testing purposes.

Adventureworks=# CREATE OR REPLACE FUNCTION cpu_intensive_task(iterations BIGINT) RETURNS void AS $$
DECLARE
    num NUMERIC := 1;
BEGIN
    FOR idx IN 1..iterations LOOP
        num := MOD(num + CAST(SQRT(idx) AS NUMERIC), 13);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

The max_parallel_workers parameter in PostgreSQL defines the maximum number of parallel worker processes that can be used for query execution. It essentially acts as a CPU usage limit by controlling how many workers can perform parallel processing simultaneously. Since the current session is also counted towards the limit, I will create max_parallel_workers – 1 = 7 background processes to ensure optimal CPU utilization without exceeding the limit.

Adventureworks=# DO $$                                                                                                            
DECLARE
    cpu_count INTEGER;
    loop_iterations BIGINT := 500000000;
BEGIN
    -- Detect the number of parallel workers available
    SELECT current_setting('max_parallel_workers')::INTEGER INTO cpu_count;
    RAISE NOTICE 'Number of CPUs detected: %', cpu_count;

    -- Launch background processes for each CPU core
    FOR i IN 1..cpu_count-1 LOOP
        PERFORM pg_background_launch(
            format('SELECT cpu_intensive_task(%s);', loop_iterations)
        );

        RAISE NOTICE 'Launched CPU-intensive background job %', i;
    END LOOP;

    RAISE NOTICE 'All CPU-intensive jobs launched.';
END $$ LANGUAGE plpgsql;

NOTICE:  Number of CPUs detected: 8
NOTICE:  Launched CPU-intensive background job 1
NOTICE:  Launched CPU-intensive background job 2
NOTICE:  Launched CPU-intensive background job 3
NOTICE:  Launched CPU-intensive background job 4
NOTICE:  Launched CPU-intensive background job 5
NOTICE:  Launched CPU-intensive background job 6
NOTICE:  Launched CPU-intensive background job 7
NOTICE:  All CPU-intensive jobs launched.

Actually, The process completes in 6 – 7 minutes. If you need to cancel the jobs, you may use commands provided below.

SELECT 
  pg_terminate_backend(pid) 
FROM 
  pg_stat_activity 
WHERE 
  state = 'active' 
  AND query ILIKE '%cpu_intensive_task%' 
  and pid <> pg_backend_pid();

Docker stats while the test is going on :

osmandinc@192 ~ % docker stats postgres-latest
CONTAINER ID   NAME              CPU %     MEM USAGE / LIMIT     MEM %     NET I/O         BLOCK I/O       PIDS
f06832f9ecfb   postgres-latest   704.56%   230.7MiB / 7.655GiB   2.94%     181MB / 818kB   140MB / 1.2GB   18
f06832f9ecfb   postgres-latest   701.55%   230.7MiB / 7.655GiB   2.94%     181MB / 818kB   140MB / 1.2GB   18
f06832f9ecfb   postgres-latest   701.91%   230.7MiB / 7.655GiB   2.94%     181MB / 818kB   140MB / 1.2GB   18
...

During the test, CPU usage peaked at 700%, indicating that 7 CPUs were utilized heavily.

Hope it helps.