Strabon

changeset 954:6fac72daf1d3

Added a section for tuning Postgres
author Kostis Kyzirakos <kkyzir@di.uoa.gr>
date Mon Apr 01 15:48:27 2013 +0300 (2013-04-01)
parents c09e97aa4e8b
children 318ed1e6dd14
files README
line diff
     1.1 --- a/README	Sun Mar 31 15:35:24 2013 +0300
     1.2 +++ b/README	Mon Apr 01 15:48:27 2013 +0300
     1.3 @@ -89,6 +89,107 @@
     1.4  You can see some examples in the classes gr.uoa.di.strabon.example.PostgisExample
     1.5  and gr.uoa.di.strabon.example.PostgisExample2.
     1.6  
     1.7 +	Tuning PostgreSQL
     1.8 +	~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
     1.9 +The default settings of Postgres are rather conservative. As a result, parameter 
    1.10 +tuning is neccessary for speeding up Postgres, therefore Strabon. If you are 
    1.11 +using Strabon to compare its performance against your implementation of 
    1.12 +stSPARQL/GeoSPARQL, you are *strongly* encouraged to contact us using the Strabon
    1.13 +Users mailing list for assistance on tuning Postgres.
    1.14 +
    1.15 +You can follow the instructions below for tuning a Postgres server running on an 
    1.16 +Ubuntu machine that is dedicated to PostgreSQL and Strabon.
    1.17 +
    1.18 +1. Append the following text at the end of postgresql.conf. 
    1.19 +*Uncomment* the appropriate lines.
    1.20 +
    1.21 +### RAM
    1.22 +## 4 GB of RAM
    1.23 +#shared_buffers       =  3GB
    1.24 +#effective_cache_size =  3GB
    1.25 +#maintenance_work_mem =  1GB
    1.26 +#work_mem             =  2GB
    1.27 +## 8 GB of RAM
    1.28 +#shared_buffers       =  5GB
    1.29 +#effective_cache_size =  6GB
    1.30 +#maintenance_work_mem =  2GB
    1.31 +#work_mem             =  5GB
    1.32 +## 16 GB of RAM
    1.33 +#shared_buffers       = 10GB
    1.34 +#effective_cache_size = 14GB
    1.35 +#maintenance_work_mem =  4GB
    1.36 +#work_mem             = 10GB
    1.37 +## 24 GB of RAM
    1.38 +#shared_buffers       = 16GB 
    1.39 +#effective_cache_size = 22GB 
    1.40 +#maintenance_work_mem =  6GB 
    1.41 +#work_mem             = 15GB
    1.42 +## 48 GB of RAM
    1.43 +#shared_buffers       = 32GB
    1.44 +#effective_cache_size = 46GB
    1.45 +#maintenance_work_mem =  8GB
    1.46 +#work_mem             = 30GB
    1.47 +## 64 GB of RAM
    1.48 +# contact us to find out!
    1.49 +### HD
    1.50 +## RAID with ordinary 7.200 disks
    1.51 +#random_page_cost = 3.5 #3.0-3.5
    1.52 +## High-End NAS/SAN
    1.53 +#random_page_cost = 2 #1.5-2.5
    1.54 +## Amazon EBS/Heroku
    1.55 +#random_page_cost = 1.3 #1.1-2.0
    1.56 +## SSD array
    1.57 +#random_page_cost = 2.0 #1.5-2.5
    1.58 +### Misc
    1.59 +default_statistics_target    = 10000 
    1.60 +constraint_exclusion         = on 
    1.61 +checkpoint_completion_target = 0.9 
    1.62 +wal_buffers                  = 32MB 
    1.63 +checkpoint_segments          = 64 
    1.64 +### Connections
    1.65 +max_connections              = 10
    1.66 +
    1.67 +2. Append the following lines at the end of /etc/sysctl.conf
    1.68 +*Uncomment* the appropriate lines.
    1.69 +
    1.70 +## 4 GB of RAM
    1.71 +#kernel.shmmax = 3758096384
    1.72 +#kernel.shmall = 3758096384
    1.73 +#kernel.shmmni = 4096
    1.74 +## 8 GB of RAM
    1.75 +#kernel.shmmax = 5905580032
    1.76 +#kernel.shmall = 5905580032
    1.77 +#kernel.shmmni = 4096
    1.78 +## 16 GB of RAM
    1.79 +#kernel.shmmax = 11274289152
    1.80 +#kernel.shmall = 11274289152
    1.81 +#kernel.shmmni = 4096
    1.82 +## 24 GB of RAM
    1.83 +#kernel.shmmax = 17716740096
    1.84 +#kernel.shmall = 17716740096
    1.85 +#kernel.shmmni = 4096
    1.86 +## 48 GB of RAM
    1.87 +#kernel.shmmax = 35433480192
    1.88 +#kernel.shmall = 35433480192
    1.89 +#kernel.shmmni = 4224
    1.90 +## 64 GB of RAM
    1.91 +# contact us to find out!
    1.92 +
    1.93 +3. Aply all changes by executing
    1.94 +
    1.95 +$ sudo sysctl -p
    1.96 +$ sudo /etc/init.d/postgresql restart
    1.97 +
    1.98 +4. Prepare for the next run by issuing the command 
    1.99 +
   1.100 +$ sudo -u postgres psql -c 'VACUUM ANALYZE;' db
   1.101 +
   1.102 +or 
   1.103 +
   1.104 +$ psql -c 'VACUUM ANALYZE;' db
   1.105 +
   1.106 +where db is the name of the Postgres database that Strabon will use.
   1.107 +
   1.108  
   1.109  Developer Guide
   1.110  ~~~~~~~~~~~~~~~
   1.111 @@ -216,10 +317,17 @@
   1.112  
   1.113  Mailing-list
   1.114  ============
   1.115 -Currently, there is only a developers mailing-list, Strabon-devel, for support
   1.116 -and other questions concerning Strabon. To subscribe to the mailing-list, please
   1.117 -visit page http://cgi.di.uoa.gr/~mailman/listinfo/strabon-devel. To post e-mails
   1.118 -to Strabon-devel mailing-list, write to Strabon-devel@di.uoa.gr.
   1.119 +Currently, we maintain the following mailing lists:
   1.120 +
   1.121 +* Strabon-users, is used as a communication channel for Strabon users.
   1.122 +To subscribe to the mailing-list, please visit page 
   1.123 +http://cgi.di.uoa.gr/~mailman/listinfo/strabon-users. To post e-mails
   1.124 +to Strabon-users mailing-list, write to strabon-users@di.uoa.gr.
   1.125 +
   1.126 +* Strabon-devel, is used as a communication channel with the developers
   1.127 +of Strabon. To subscribe to the mailing-list, please visit page 
   1.128 +http://cgi.di.uoa.gr/~mailman/listinfo/strabon-devel. To post e-mails
   1.129 +to Strabon-devel mailing-list, write to strabon-devel@di.uoa.gr.
   1.130  
   1.131  
   1.132  Bugs