Performance question..

Started by Williams, Travis L, NPONSalmost 23 years ago3 messagesgeneral
Jump to latest

All,
I'm looking for ideas on tweaking pgsql.. here is my machine stats

Processor 0 runs at 550 MHz
Processor 1 runs at 550 MHz
Page Size : 4096
Phys Pages: 131072
Total Physical memory = 536870912 (512MB)

This is running on HPUX 11

Below is my postmaster.conf file

#
# Connection Parameters
#
#tcpip_socket = false
tcpip_socket = true
#ssl = false

max_connections = 64

#port = 5432
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777

#virtual_host = ''

#krb_server_keyfile = ''

#
# Shared Memory Size
#
shared_buffers = 128 # 2*max_connections, min 16
#max_fsm_relations = 100 # min 10, fsm is free space map
#max_fsm_pages = 10000 # min 1000, fsm is free space map
#max_locks_per_transaction = 64 # min 10
#wal_buffers = 8 # min 4

#
# Non-shared Memory Sizes
#
#sort_mem = 512 # min 32
#vacuum_mem = 8192 # min 1024

#
# Write-ahead log (WAL)
#
#wal_files = 0 # range 0-64
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600
#fsync = true

#
# Optimizer Parameters
#
#enable_seqscan = true
#enable_indexscan = true
#enable_tidscan = true
#enable_sort = true
#enable_nestloop = true
#enable_mergejoin = true
#enable_hashjoin = true

#ksqo = false

#effective_cache_size = 1000 # default in 8k pages
#random_page_cost = 4
#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025

#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on #tables in query, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed

#
# Debug display
#
#silent_mode = false

#log_connections = false
#log_timestamp = false
#log_pid = false

#debug_level = 0 # range 0-16

#debug_print_query = false
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

# requires USE_ASSERT_CHECKING
#debug_assertions = true

#
# Syslog
#
# requires ENABLE_SYSLOG
#syslog = 0 # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'

#
# Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false

# requires BTREE_BUILD_STATS
#show_btree_build_stats = false

#
# Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false

#
# Lock Tracing
#
#trace_notify = false

# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0

#
# Misc
#
#dynamic_library_path = '$libdir'
#australian_timezones = false
#authentication_timeout = 60 # min 1, max 600
#deadlock_timeout = 1000
#default_transaction_isolation = 'read committed'
#max_expr_depth = 10000 # min 10
#max_files_per_process = 1000 # min 25
#password_encryption = false
#sql_inheritance = true
#transform_null_equals = false

#2Martijn van Oosterhout
kleptog@svana.org
In reply to: Williams, Travis L, NPONS (#1)
Re: Performance question..

On Wed, Jun 11, 2003 at 02:39:05PM -0400, Williams, Travis L, NPONS wrote:

All,
I'm looking for ideas on tweaking pgsql.. here is my machine stats

Looked here at all? :

http://techdocs.postgresql.org/
http://www.argudo.org/postgresql/soft-tuning.html
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Show quoted text

"the West won the world not by the superiority of its ideas or values or
religion but rather by its superiority in applying organized violence.
Westerners often forget this fact, non-Westerners never do."
- Samuel P. Huntington

#3scott.marlowe
scott.marlowe@ihs.com
In reply to: Williams, Travis L, NPONS (#1)
Re: Performance question..

On Wed, 11 Jun 2003, Williams, Travis L, NPONS wrote:

All,
I'm looking for ideas on tweaking pgsql.. here is my machine stats

Processor 0 runs at 550 MHz
Processor 1 runs at 550 MHz
Page Size : 4096
Phys Pages: 131072
Total Physical memory = 536870912 (512MB)

SNIP

# Shared Memory Size
#
shared_buffers = 128 # 2*max_connections, min 16

WAYYYY too small. Try 500 to 2000 for starters. Note that bigger isn't
always better, it's about fitting shared_buffers to your usage. This is
set in 8k blocks, so 1000 is really only about 8 meg. Bigger servers have
settings as high as 32768 which is 256 Megs.

SNIP

# Non-shared Memory Sizes
#
#sort_mem = 512 # min 32
#vacuum_mem = 8192 # min 1024

Try setting your sort mem a little higher. It's measured in k, so 8192
would be 8 megs.

# Write-ahead log (WAL)
#
#wal_files = 0 # range 0-64
#wal_sync_method = fsync # the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#commit_delay = 0 # range 0-100000
#commit_siblings = 5 # range 1-1000
#checkpoint_segments = 3 # in logfile segments (16MB each), min 1
#checkpoint_timeout = 300 # in seconds, range 30-3600
#fsync = true

If you're doing a lot of writing, look at using more than one WAL file and
putting the pg_xlog directory on another drive. You have to shutdown the
postmaster, copy over the pg_xlog dir, move the on in $PGDATA out of the
way, and link to the "new" directory then restart the postmaster.

Also, if you're doing lots of writes, setting a higher commit_delay and
commit_siblings can help.

#effective_cache_size = 1000 # default in 8k pages

If your machine has 512 Meg of ram, you want to see how much
(approximately) is used by the OS as file cache/buffer. Divide that by 8k
and put that number into effective_cache_size.

#random_page_cost = 4

For machines with fast RAID subsystems, random_page_cost may need to be
lowered. somewhere between 1 and 2. If all your dataset fits in memory,
set it to 1. I use 1.4 as a setting on my machine with 1.5 gig.

#cpu_tuple_cost = 0.01
#cpu_index_tuple_cost = 0.001
#cpu_operator_cost = 0.0025

SNIP

That's all I can think of. If you can afford more memory, that would be
your best upgrade right now.