SUNScholar/Optimisations/Database
Back to Optimisations
Contents
Introduction
This wiki page details the optimisation done to the PostgreSQL DB to make it production optimised.
See: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
pgTune
In addition you may want to install pgtune
sudo apt-get install pgtune
Then use as following:
cd /etc/postgresql/9.3/main
sudo pgtune -i postgresql.conf -o postgresql.conf-pgtune
To view the changes, type:
sudo diff postgresql.conf postgresql.conf-pgtune
Then activate the new settings as follows:
sudo cp postgresql.conf-pgtune postgresql.conf
sudo service postgresql restart
DB connection optimisation
As our repository became more popular we noticed that the number database connections increased alarmingly. It is normal web application programming practice to release/sever database connections after a particular programming task on the database is done.
However with a large number of web site visitors in a short period of time the number of database connections available soon ran out, so we adjusted DSpace and PostgreSQL to accommodate more visitors in a short period of time. The symptom is usually a blank screen.
See the screenshot below for a typical connection status on SUNScholar, using the http://psi-probe.googlecode.com tool.
Step 1 - Allow DSpace to make more DB connections
To do this you will need to modify the DSpace configuration to make more connections.
- Go to: http://wiki.lib.sun.ac.za/index.php/SUNScholar/Install_DSpace/S04
- Search the relevant build.properties file for db.maxconnections = 50 change the allowed number of connections to 100.
- And then rebuild Dspace.
150 connections should be a good start to tuning DSpace for DB connections, you can adjust as needed, remember though that the PostgreSQL DB can only take so many connections and that more DB connections requires more server resources!
Step 2 - Enable the PostgreSQL DB to accept more connections
See: http://wiki.postgresql.org/wiki/Number_Of_Database_Connections
Now that DSpace wants to make more connections, you must tell the PostgreSQL DB to accept more connections.
To do this you will need to modify the PostgreSQL DB configuration to accept more connections, see details below.
- Go to: http://wiki.lib.sun.ac.za/index.php/SUNScholar/Prepare_Ubuntu/S06
- Search for the step regarding the number of max DB connections allowed and modify as needed.
- And then rebuild Dspace.
To start with, it is suggested you match the number of DSpace connections made, however it is wise to allow a little of bit of headroom for idle DB connections, say at least another 50 connections.
Vacuum PostgreSQL
PostgreSQL also benefits from regular maintenance such as vacumn, which optimizes the indexes and clears out any deleted data.
Type as follows in a terminal:
sudo -i
Enter the "dspace" user password. Then type:
su - postgres
crontab -e
When asked which editor to use, select "nano.
Then copy and paste the following:
20 4 * * * vacuumdb --analyze dspace > /dev/null 2>&1
