Re: How to cripple a postgres server

I spent some time this morning trying to reproduce your problem, with
not much luck.  I used the attached test program, in case anyone else
wants to try --- it fires up the specified number of connections
(issuing a trivial query on each one, just so that the backend is not
completely virgin) and goes to sleep.  I ran that in one window and did
manual "vacuum full"s in psql in another window.  I was doing the
vacuums in the regression database which has about 150 tables, so there
was an SI overrun event (resulting in SIGUSR2) every third or so vacuum.

Using stock Red Hat Linux 7.2 (kernel 2.4.7-10) on a machine with 256M
of RAM, I was able to run up to about 400 backends without seeing much
of any performance problem.  (I had the postmaster running with
postmaster -i -F -N 1000 -B 2000 and defaults in postgresql.conf.)
Each SI overrun fired up all the idle backends, but they went back to
sleep after a couple of kernel calls and not much computation.

Above 500 backends the thing went into swap hell --- it took minutes of
realtime to finish out the SI overrun cycle, even though the CPU was
idle (waiting for swap-in) most of the time.

I could not see any mode where system CPU percentage was significant.
I did notice the user CPU spiking up at times (this seems to be due to
the deadlock detection algorithm, which gets invoked once a process has
waited "too long" for a lock).

I was doing this with current development sources, but I have no reason
to think that PG 7.2 would act differently.

It's fairly clear from looking at this example that we're handling SI
overrun notification in a very inefficient manner; probably it should
have its own signal so that receiving backends can just read the SI
queue and not bother with scanning pg_listener.  But there's something
else going on in your system that I don't understand.  You're not
swapping and you are seeing 99% system CPU --- what's causing that?
And why don't I see it?

When there are not enough backends to cause swapping, strace'ing a
randomly-chosen backend produces results like this for each SIGUSR2
cycle:

11:40:24.824914 recv(8, 0x83ff600, 8192, 0) = ? ERESTARTSYS (To be restarted)
11:40:32.549941 --- SIGUSR2 (User defined signal 2) ---
11:40:32.550243 gettimeofday({1022686832, 550363}, NULL) = 0
11:40:32.550592 semop(5898249, 0xbfffeba4, 1) = 0
11:40:32.639593 lseek(4, 0, SEEK_END)   = 0
11:40:32.639729 sigreturn()             = ? (mask now [])
11:40:32.639845 recv(8,

On the other hand, with just a few too many backends I get:

11:42:46.913127 recv(8, 0x83ff600, 8192, 0) = ? ERESTARTSYS (To be restarted)
11:43:56.115481 --- SIGUSR2 (User defined signal 2) ---
11:43:56.577389 semop(5767173, 0xbfffebe4, 1) = 0
11:44:00.441212 semop(7438392, 0xbfffebe4, 1) = 0
11:44:00.441306 semop(5767173, 0xbfffeb34, 1) = 0
11:44:03.929909 semop(6455322, 0xbfffeb34, 1) = 0
11:44:03.930026 gettimeofday({1022687043, 930059}, NULL) = 0
11:44:03.931215 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={1, 0}}, {it_interval={0, 0}, it_value={0, 0}}) =
0
11:44:03.931321 semop(5767173, 0xbfffeac4, 1) = -1 EINTR (Interrupted system call)
11:44:04.923771 --- SIGALRM (Alarm clock) ---
11:44:04.923969 semop(5767173, 0xbfffe734, 1) = 0
11:44:05.296190 semop(7602237, 0xbfffe734, 1) = 0
11:44:05.296368 sigreturn()             = ? (mask now [USR2])
11:44:05.296500 semop(5767173, 0xbfffeac4, 1) = 0
11:44:09.216795 setitimer(ITIMER_REAL, {it_interval={0, 0}, it_value={0, 0}}, {it_interval={0, 0}, it_value={0, 0}}) =
0
11:44:09.217014 lseek(4, 0, SEEK_END)   = 0
11:44:09.286696 semop(6127632, 0xbfffeb54, 1) = 0
11:44:09.555040 sigreturn()             = ? (mask now [])
11:44:09.555251 recv(8,

(The number of semops can vary drastically depending on timing --- each
one indicates blocking on an internal lock.  BTW, does anyone know how
to get Linux strace to show the contents of the second arg to semop?)

What does your strace look like?

            regards, tom lane

/*
 * startlots.c
 *
 * Build with libpq (eg, gcc startlots.c -o startlots -lpq).
 *
 * Usage: startlots N to start N backends.  Control-C to exit.
 */
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>

#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
    PQfinish(conn);
    exit(1);
}

static void
start1conn()
{
    char       *pghost,
               *pgport,
               *pgoptions,
               *pgtty;
    char       *dbName;
    PGconn       *conn;
    PGresult   *res;

    /*
     * begin, by setting the parameters for a backend connection if the
     * parameters are null, then the system will try to use reasonable
     * defaults by looking up environment variables or, failing that,
     * using hardwired constants
     */
    pghost = NULL;                /* host name of the backend server */
    pgport = NULL;                /* port of the backend server */
    pgoptions = NULL;            /* special options to start up the backend
                                 * server */
    pgtty = NULL;                /* debugging tty for the backend server */
    dbName = "template1";

    /* make a connection to the database */
    conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

    /* check to see that the backend connection was successfully made */
    if (PQstatus(conn) == CONNECTION_BAD)
    {
        fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
        fprintf(stderr, "%s", PQerrorMessage(conn));
        exit_nicely(conn);
    }

    /* run a transaction */
    res = PQexec(conn, "select version()");
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "select version() command didn't return tuples properly\n");
        PQclear(res);
        exit_nicely(conn);
    }

    PQclear(res);

    /* leave the connection open */
}

int
main(int argc, char**argv)
{
    int        nconn = atoi(argv[1]);

    while (nconn-- > 0)
        start1conn();

    sleep(100000);

    return 0;
}