11456
Reporter: trobertson
Assignee: mdoering
Type: Task
Summary: Setup CLB snapshot on Jawa for UAT
Priority: Blocker
Resolution: Fixed
Status: Closed
Created: 2012-06-21 09:51:51.341
Updated: 2013-08-29 14:45:14.735
Resolved: 2012-06-21 15:26:46.701
Description: Take a snapshot of CLB, and deploy this on Jawa.
- Tune Jawa PostGRES using pgtune. Bear in mind the server also runs tomcat, mysql so be conservative on memory.
- Create a read only account called "portal" for the application to connect with with enough privileges for the checklistbank-ws to operate. Please use the name "portal", as it will be used in all databases.
- Document ALL changes on http://wikka.gbif.org/ServerJawa (account creation, server tuning etc. Please keep backups of old files)]]>
Author: mdoering@gbif.org
Created: 2012-06-21 10:16:11.691
Updated: 2012-06-21 10:16:11.691
pgtune for a Mixed type with expected 50 connections and auto detected memory results in this:
{noformat}
default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 44GB
work_mem = 640MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 15GB
max_connections = 50
{noformat}
with only 30 connections expected and 52GB of free memory expected it is this
{noformat}
default_statistics_target = 50
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 36GB
work_mem = 832MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 13GB
max_connections = 30
{noformat}
As this postgres installation is only used for the single portal checklist bank database 30 connections should be more than enough.
I will configure jawa with the later configs
Author: mdoering@gbif.org
Created: 2012-06-21 10:26:01.781
Updated: 2012-06-21 10:28:52.287
When set to type Data Warehouse we get a slightly different configurations with
{noformat}
default_statistics_target = 100
wal_buffers = 32MB
checkpoint_segments = 64
{noformat}
These make sense for us as its a readonly database.
I will also disable the write ahead logs as much as possible via this, see http://www.postgresql.org/docs/8.4/static/runtime-config-wal.html
{noformat}
fsync=false
synchronous_commit=false
archive_mode=false
{noformat}
final configuration will be
{noformat}
fsync=off
synchronous_commit=off
archive_mode=off
default_statistics_target = 100
maintenance_work_mem = 1GB
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 36GB
work_mem = 832MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 13GB
max_connections = 30
{noformat}
Author: mdoering@gbif.org
Comment: latest checklist bank database copied to db "clb" and readonly user portal created with same password as on boma
Created: 2012-06-21 15:26:40.523
Updated: 2012-06-21 15:26:40.523