PostgreSQL Administration

Finding and fixing expensive queries

Probably the most important thing you can do to improve performance is rewriting queries to run quickly. Explain analyze is your friend. OpenACS also includes tools to track down slow queries, and you can ask PostgreSQL to give you information about queries as well.

First you should install the acs-developer-support package on a development or staging server. In general its not a good idea to keep the develop support tracking features running constantly on a production system, but you can turn it on temporarily to diagnose a problem on a production system. Once Developer Support is installed you can visit /ds/ on your site and turn on the developer support toolbar and database statistics. This will give you a total of the time for all queries for a page in the toolbar at the top of the page. If you click on the timing information you can see a page that lists every query run for that page. It should be easy to spot the slow running query in the list.

Once you find the slow running query you can copy it to your clipboard. Next you want to open a psql session on the database server, or use M-x sql-postgres most in emacs. Type "explain analyze" and paste the query after that. This will tell PostgreSQL to run the query and show the query plan it will use.  The first thing to look for is a "Sequential Scan" on a large table. If the table is small (hundreds instead of thousands of rows, for example)  it is probably cheaper to scan the table than to load an index, but on large tables like "acs_objects". "users", "cr_items", a sequential scan is a sign of trouble.

PostgreSQL does not seem to generate good plans if you do a join with a view. In this case you should try to recreate the query using the tables in the view explicitly. This can speed up many queries. A common example is the cc_users view, or the cr_revisionsx view or the (x) view automatically created for subtypes of cr_revisions.

If you can't figure out why the query plan is slow, post it somewhere on the OpenACS forums or ask for advice in the #openacs irc channel. (https://openacs.org/irc/) 

This will help you is you know which page is slow. If you don't know which is slow, but notice a high load on PostgreSQL on your server. You'll need to turn on the stats collector and command string collector in PostgreSQL. In the postgresql.conf file set

   stats_start_collector true

and

   stats_command_string true

and then do /etc/init.d/postgreql reload or pg_ctl reload to turn it on.

Once this is on you can execute "select * from pg_stat_activity" to see if there are any long running queries. Most queries will finish too quickly to notice in this table. The table includes the start time of the query and the process id of the backend executing the query. Sometimes you'll find a particularly bad query has been running for a very long time. Sometimes hours. If this happens you can stop the query by issuing a SIGINT signal to the process of the backend that is running that query. This will execute a cancel request to the backend and is the only safe way to stop a long running query. Do not kill the process or try to stop AOLserver. If you stop AOLserver the query will continue to run in the PostgreSQL backend process.

• Tracking expensive queries
• How to go about debugging a Postgres performance problem (forum thread)

Tuning PostgreSQL

Tune PostgreSQL. (OPTIONAL). The default values for PostgreSQL are very conservative; we can safely change some of them and improve performance.

1. Change the kernel parameter for maximum shared memory segment size to 128Mb: DAVEB: How current is this? Do modern 2.4 or 2.6 kernels have such low settings? This is highly dependent also on the amount of RAM on your server, most servers have gigabytes of RAM so adjust accordingly.

   [root root]# echo 134217728 >/proc/sys/kernel/shmmax
   [root root]#

   Make that change permanent by editing /etc/sysctl.conf to add these lines at the end:

   # increase shared memory limit for postgres
   kernel.shmmax = 134217728

2. Edit the PostgreSQL config file, /usr/local/pgsql/data/postgresql.conf, to use more memory. These values should improve performance in most cases. (more information) 

   #       Shared Memory Size
   #
   shared_buffers = 15200      # 2*max_connections, min 16
   
   #       Non-shared Memory Sizes
   #
   sort_mem = 32168            # min 32
   
   
   #       Write-ahead log (WAL)
   #
   checkpoint_segments = 3     # in logfile segments (16MB each), min 1
   

   Restart postgres (service postgresql restart) or (/etc/init.d/postgres restart) so that the changes take effect.

Performance tuning resources:

• Managing Kernel Resources about PostgreSQL shared memory and semaphores with specific operating system notes.
• Managing Kernel Resources (development version) This information may be experimental.
• Tuning PostgreSQL for performance
• Optimizing PostgreSQL
• PostgreSQL Performance Tuning

more information about PostgreSQL

• Official PostgreSQL Docs

• User Documentation

Vacuuming multiple databases

If you are frequently creating and dropping various databases, using this in your crontab can help simplify setup:

vacuumdb --all --verbose

 Debugging queries

You can work directly with the database to do debugging steps like looking directly at tables and testing stored procedures. Start emacs. Type M-x sql-postgres. Press enter for server name and use $OPENACS_SERVICE_NAME for database name. You can use C-(up arrow) and C-(down arrow) for command history.

Hint: "Parse error near *" usually means that an xql file wasn't recognized, because the Tcl file is choking on the *SQL* placeholder that it falls back on.