« NTP Configuration | Main | Setting up Amazon EC2 Virtual Machine »
Postgres & Profiling
By Matt | January 25, 2008
***THIS IS A DRAFT***
Configuring a (test) server:
Install Postgres. If doing through yum, do postgresql, postgresql-server, and postgresql-contrib
Create an account for yourself:
su root
su postgres (Yes, you need to be su’d as root to su as postgres w/o password)
createuser [username]
exit
You can now create a database to import into:
createdb [database_name]
And let’s import the backup from the production server:
psql [database_name] < [backup_name]
Special note, exit from the psql interactive shell is either \q or ctrl-d.
A good thing to grab is the pg_statistic table:
echo “select * from pg_statistic” | psql [database_name] > pg_statistic_dump
Capturing Extensive Information on Performance / Queries:
Your mileage may vary a bit as to exact file locations (both the .conf and default log locations), but this is the general gist.
** See next section — it’s probably what you want first **
sudo vi /var/lib/pgsql/data/postgresql.conf
log_statement = ‘all’
log_parser_stats = on
log_planner_stats = on
log_executor_stats = on
log_statement_stats = onlog_destination = ‘stderr’
This will put the logs into:
/var/lib/pgsql/data/pg_log/To make this active:
sudo /etc/init.d/postgresql reloadHowever, you may need to do a restart to stop the logging after backing out the changes to the .conf .
Analyzing the log:
First thing I’m interested is to get an idea of how long queries are running. Grep ‘elapsed’, then sort them descending to get the seconds on the longest running queries and then find those in the log.
Statement Duration:
log_min_duration_statement = 0
[0 = all; otherwise specify milliseconds minimum to log]
Run this for a bit, and take a look to start to get an idea what the normal long running queries are. Then let it run for a long time — like overnight — to get an idea about long running queries.
Topics: Uncategorized | No Comments »
Comments
You must be logged in to post a comment.