July 2nd, 2008 by depesz | Tags: , , , , , , | 7 comments »
Did it help? If yes - maybe you can help me?

Every so often I need to find who exactly is damaging database performance. I mean – I have db connection which does something strange/wrong with the database, but what exactly is responsible?

Switching to separate user for every program, script and developer would solve the problem, but it is not always an option. So, what should I do?

Let's check simple example:

pgdba@10.0.1.7[~]$ ps uxf
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
pgdba     1131  0.0  0.0  19184  1160 pts/2    S    15:29   0:00 su - pgdba
pgdba     1132  3.0  0.0  14628  3852 pts/2    S    15:29   0:00  \_ -su
pgdba     1152  0.0  0.0   8744  1072 pts/2    R+   15:29   0:00      \_ ps uxf
pgdba    31236  0.0  0.0  50644  6400 ?        S    Jun28   0:57 /var/pgsql/pgdba/work/bin/postgres
pgdba    31241  0.0  0.0  16332  1532 ?        Ss   Jun28   0:52  \_ postgres: logger process
pgdba    31261  0.0  0.1  50788 27572 ?        Ss   Jun28   1:42  \_ postgres: writer process
pgdba    31262  0.0  0.0  17404  1512 ?        Ss   Jun28   0:03  \_ postgres: archiver process
pgdba    31264  0.0  0.0  18156  1820 ?        Ss   Jun28   2:48  \_ postgres: stats collector process
pgdba    19058  0.2  0.1  52080 30132 ?        Ss   Jun30   9:19  \_ postgres: user1 db1 10.0.1.5(58895) idle
pgdba    19120  0.2  0.1  52092 30116 ?        Ss   Jun30   9:24  \_ postgres: user1 db1 10.0.1.3(60669) idle
pgdba    30654  0.0  0.1  53220 30172 ?        Ss   Jun30   0:02  \_ postgres: user1 db2 10.0.1.4(34078) idle
pgdba    10682  0.4  0.1  52064 30056 ?        Ss   Jun30  11:44  \_ postgres: user1 db2 10.0.1.4(43996) idle
pgdba    11224  0.4  0.1  52100 30048 ?        Ss   Jun30  11:50  \_ postgres: user1 db2 10.0.1.4(36546) idle
pgdba    11514  0.3  0.1  52092 30128 ?        Ss   Jun30   9:07  \_ postgres: user1 db1 10.0.1.2(34909) idle
pgdba     7521  0.0  0.1  51980 29592 ?        Ss   13:05   0:01  \_ postgres: user1 db2 10.0.1.4(35053) idle
pgdba    30100  0.0  0.0  53076  7480 ?        Ss   15:09   0:00  \_ postgres: user1 db2 10.0.1.4(48970) idle in transaction
pgdba      767  0.0  0.0  51336  2796 ?        Ss   15:27   0:00  \_ postgres: user2 db2 10.0.1.4(57403) idle
pgdba     1151  0.0  0.1  53316 28024 ?        Rs   15:29   0:00  \_ postgres: user1 db1 10.0.1.2(47107) SELECT

OK, we have 2 databases, and 2 users.

One of the connections keeps state “idle in transaction", which I don't like, and I need to check what application it is. Perhaps it is psql of some developer, or some cronjob.

This ps shows source ip of connection (10.0.1.4) and source tcp/ip port number: 48970.

Having this information I can connect to the server (10.0.1.4) and check what it is:

root@10.0.1.4[~]# netstat -ntp | grep 48970
tcp        0      0 10.0.1.4:48970          10.0.1.7:5482           ESTABLISHED29164/perl
tcp        0      0 10.0.1.4:48970          10.0.1.7:5482           ESTABLISHED29164/perl

OK, so now I know more – this is perl program and not psql. And I know process id of the program: 29164. So I can check what exactly it is:

root@10.0.1.4 [~]# ps uww -p 29164
USER          PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
application 29164  0.0  0.1 200496 31904 pts/7    S+   15:04   0:00 perl manage.pl shell

And now I know more – this is actually manage.pl, calling “shell" function.

Knowing this I can find out whether this particular program is “OK" to do long “idle in transaction", or is it not OK, and I should kill it.

  1. 7 comments

  2. # Felipe Nogueira
    Jul 2, 2008

    Hi,

    my comment isn´t about the subject above, i´d like to know what is your opinion about the best linux OS for Postgres ? Fedora ? CentOS ? Which do you use ? and why this choice.

    thank

  3. Jul 2, 2008

    @Felipe Nogueira:
    don’t really care. i usually use debian/ubuntu – but this is only because it is already on the server. i did used redhat, pld, and centos. and i’m practically always compiling from sources, so the distribution doesn’t really matter.

  4. Jul 2, 2008

    @ Depesz, please don’t recommend compiling from source to the newbies. Postgres is plenty confusing when you use your OS’s packaging system 🙂

  5. Jul 3, 2008

    @David Fetter:
    I would say that PostgreSQL is confusing only when you use your OS’s packaging system 🙂

    First PostgreSQL that I used, was compiled by me from sources. Never had any problems with it.

  6. Jul 8, 2008

    @David Fetter:
    Not to be argumentative, but I agree with Depesz. Compiling from source is definitely the way to go. The built offerings from the various Linux distros vary considerably and are generally not very up-to-date. The nice thing about building from source is that you avoid rpm/package hell when newer PG versions trigger dependency problems.

  7. Aug 3, 2008

    Thanks for the post

  8. Jan 26, 2012

    Hi,
    just my first comment on this excellent web page… Compiling from sources was not so difficult, also for me – the newbie in PostgreSQL :-).

Sorry, comments for this post are disabled.