Using 'psql ANALYZE' to improve a 5.x performance slowdown on database operations

0 Likes

NOTE: Starting with release 5.5, this FAQ is no longer applicable. However, it is still needed in pre-5.5 releases.

PROBLEM/ABSTRACT:
After some period of time running with 5.x, users are starting to report slower performance with database related commands such as a workspace update.

SOLUTION:
In pre-5.x, administrators would occasionally have to run a maintain reindex on the server to improve database performance. The PostgreSQL database used in AccuRev 5.x has a similar functional command, 'psql ANALYZE', that can improve performance when database tables have grown large enough to impact database functions. This command is run automatically by both the postgres database process as well as the accurev_server process, but in an active user environment, this may not be sufficient to maintain acceptable performance for the user. In this case, you can run the command manually. You can also set up a system task to automatically run it at whatever interval keeps your performance at acceptable levels (see PostgreSQL documentation at http://www.postgresql.org/docs/9.1/static/libpq-pgpass.html for how a task can get the superuser password).

Unlike the 4.x maintain command, running the psql ANALYZE command does NOT require stopping of the AccuRev server process. This means that you can run the command against the database whenever you want. In some databases, this could take several minutes to run, though usually the command runs in just a few minutes or even just several seconds. If a lot of activity is going on at the time that you run it, users could notice some slowness. So if you schedule it to run on a regular basis, you may want to have it run during a slower activity period.

Below are examples of running the command in a Linux bash shell and a Windows CMD shell. You will need to adjust your directory path, superuser and port values as appropriate for your installation. In these examples, the postgres superuser is "postgres" and the port that the postgres process is running on is the default, 5075:

Linux bash shell:
$ export LD_LIBRARY_PATH=/opt/accurev/postgresql/lib:$LD_LIBRARY_PATH
$ /opt/accurev/postgresql/bin/psql -U postgres -d accurev -c "ANALYZE" -p 5075

Windows CMD shell:
C:\Program Files\AccuRev\postgresql\bin\psql -U postgres -d accurev -c "ANALYZE" -p 5075

Comment List
Related
Recommended