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.
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
@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.
@ Depesz, please don’t recommend compiling from source to the newbies. Postgres is plenty confusing when you use your OS’s packaging system 🙂
@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.
@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.
Thanks for the post
Hi,
just my first comment on this excellent web page… Compiling from sources was not so difficult, also for me – the newbie in PostgreSQL :-).