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

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 :-).

Leave a comment