...
Performance Tuning
The EAS databases (mad, sfmaps) need to support 3 sorts of activities
- relatively high IO because it's geospatial
- modest OLTP actvity (mad database)modest number of connections (maybe 20-30 simultaneuous?)from single django web application
- most connections will be from single django web app with application (no connection poolingheavy weight )
- modest number of simultaneous connections (estimate max of 50)
- significant overnight ETL activity (sfmaps, mad databases)
- heavy read processes from geoserver to produce web maps (sfmaps database)postgis is use (high IO)
The host VMs for the DB have 8GB of RAM.
Until we have some real metrics, let's set aside 2GB for the OS and assume we have 6GB for the DB.
Here I discuss the settings in the postgresql.conf file.
The default settings are for low resource environments and will result in relatively poor performance.
These seetings settings are my "best guess" based on some reading (see links below).
We'll need to do some monitoring in PROD to tune these values.
paramter parameter | value | links (below) |
|---|---|---|
shared_buffers | 2GB | 1,2 |
work_mem | 16MB | 2 |
maintenance_work_mem | 128MB | 2 |
wal_buffers | 1MB | 2 |
checkpoint_segments | 6 | 2 |
random_page_cost | 2 | 2 |
seq_page_cost | 1 | 2 |
effective_cache_size | 4GB | 1 |
max_connections | 100 | default |
Do not forget to reload or restart A restart is needed for these configurations to take affect.
...