One of the most important factors when troubleshooting the performance of an application or website, is the performance of the database. Improper indexing or inefficient SQL queries can kill the performance of your application rendering it useless. It is amazing what a simple tweak or change to a SQL statement can do. I have seen more than 100x performance improvements just from a one word change in a query. So how you can identify which queries are running slowly in your system? That is easy, just turn on the logging in your PostgreSQL database. This method has been tested in versions 8.3 and 8.4, but should apply to even 9.x!
Enable slow query logging in PostgreSQL
- Open the file postgresql.conf file in your favorite text editor.
Note: If you are having trouble finding the file, run the command: find / -name postgresql.conf - Look for the line:
#log_min_duration_statement = -1and replace it with:
log_min_duration_statement = 100 - Save the file and reload the PostgreSQL configuration (Don’t worry, it won’t interrupt any active connections)
service postgresql reload - Go to the pg_log folder and open the latest log file. (i.e. /var/lib/pgsql/data/pg_log/)
Setting the value to 100 will log every query that takes 100ms or longer to execute. Depending on the number of slow queries you have, this may be overwhelming, so consider increasing this value.
If you need some assistance in optimizing your slow queries, feel free to post them in the comments section below!
test
hey there sorry about the test comment from before I tried to comment like 3 times and it kept erasing my comments.
anyways, I’m using a mac, and while I was able to find the conf file (with the find command) and update it, I couldn’t find the pg_log dir. do you know where it usually is maybe??
I did have one finding but it was in the data private folder of pg. I also found another conf file there, and at first tried to edit that one, but then I found another one in a more public folder.
can anyone help??
thanks