[Babase] Tuning postgres

Karl O. Pinc babase@www.eco.princeton.edu
Fri, 01 Apr 2005 18:18:52 +0000


This is added to /etc/sysctl.conf:
-----<snip>-----
# Babase postgresql stuff.

# Increase the amount of shared memory to 1GB (units are bytes)
kernel.shmmax = 1073741824
# Allow one process (the postgresql server) to get it all.
kernel.shmall = 1073741824
------<snip>-----

This is the patch to /var/lib/pgsql/data/postgresql.conf
(Note that work_mem is called sort_mem in 7.4)

bash-2.05b# diff -u postgresql.conf.orig postgresql.conf
--- postgresql.conf.orig        2005-03-26 16:04:54.000000000 -0500
+++ postgresql.conf     2005-03-29 12:42:42.000000000 -0500
@@ -56,6 +56,9 @@
 #unix_socket_directory = ''
 #unix_socket_group = ''
 #unix_socket_permissions = 0777        # octal
+
+unix_socket_permissions = 0770 # octal -- allow only Unix postgres and  
root
+
 #rendezvous_name = ''          # defaults to the computer name

 # - Security & Authentication -
@@ -73,9 +76,13 @@

 # - Memory -

-shared_buffers = 1000          # min 16, at least max_connections*2,  
8KB each
+#shared_buffers = 1000         # min 16, at least max_connections*2,  
8KB each
+shared_buffers = 126976         # 1GB - 32MB (2.4.21 kernel max is 1GB  
so allow
+                                # 32MB for others to share.)
 #work_mem = 1024               # min 64, size in KB
+work_mem = 10240                # 10MB
 #maintenance_work_mem = 16384  # min 1024, size in KB
+maintenance_work_mem = 102400  # 100MB-ish
 #max_stack_depth = 2048                # min 100, size in KB

 # - Free Space Map -
@@ -145,6 +152,7 @@
 # - Planner Cost Constants -

 #effective_cache_size = 1000   # typically 8KB each
+effective_cache_size = 32768    # 250MB ((pg shared + kernel disk  
cache)/index)
 #random_page_cost = 4          # units are one sequential page fetch  
cost
 #cpu_tuple_cost = 0.01         # (same)
 #cpu_index_tuple_cost = 0.001  # (same)
@@ -175,6 +183,7 @@
 #log_destination = 'stderr'    # Valid values are combinations of  
stderr,
                                 # syslog and eventlog, depending on
                                 # platform.
+log_destination = 'syslog'     # See below.

 # This is relevant when logging to stderr:
 redirect_stderr = true      # Enable capturing of stderr into log  
files.
@@ -197,6 +206,7 @@

 # These are relevant when logging to syslog:
 #syslog_facility = 'LOCAL0'
+syslog_facility = 'LOCAL4'      # Duke uses this.
 #syslog_ident = 'postgres'



Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein