Help! Postgre sql is freaking
December 4, 2008 Category: *nix, Zkušenosti No Comments »
I recently needed to find out, what is my postgres doing. The only statistics i had were process ids and the system load. This is good to find out, that postgres is the problem causer but if the only purpose of the server is to serve as database it is little bit unuseful.
After hours of freaking (as well database freaking as mine) I found some useful queries which I found useful. The most important is query SELECT * FROM pg_stat_activity ORDER BY procpid; which selects all recent queries with a lot of useful information like pid, ip, username and others.
That would be enough, because now we are able to see the problematic queries or kill the thread which is running processor to 100%. However we could appreciate more data about our database.
| Query | Meaning |
|---|---|
| SELECT * FROM pg_stat_activity; | Usage stats |
| SELECT * FROM pg_stat_user_indexes; | Index usage |
| SELECT pid, mode, current_query FROM pg_locks, pg_stat_activity WHERE granted = false AND locktype = ‘transactionid’ AND pid = procpid ORDER BY pid, granted | Locks |
| SELECT * FROM pg_stats | Statistics of database objects |
More information about these queries can be found in the postgres documentation.
Name: Michal Tuláček