[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