Re: Maximum Performance Follow-up Question

That helps a great deal! I am learning about the 'run-fast' option in
postgres: Increasing the shared_buffers is critical. Here is a copy of my
postgresql.conf file. I'll make the increases you suggested, but is there
anything else you can recomend?

Thanks again,

Peter

postgresql.conf:

# Connection Parameters
#
tcpip_socket = true
#ssl = false

max_connections = 128 # 1-1024

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

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

#virtual_host = ''

#krb_server_keyfile = ''

#
# Performance
#
sort_mem = 32168
shared_buffers = 15200 # min 16
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
#geqo = true

#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_selection_bias = 2.0 # range 1.5-2.0

#
# GEQO Optimizer Parameters
#
#geqo_threshold = 11
#geqo_pool_size = 0 #default based in tables, range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed

#
# Inheritance
#
#sql_inheritance = true

#
# Deadlock
#
#deadlock_timeout = 1000

# Expression Depth Limitation
#
#max_expr_depth = 10000 # min 10

#
# Write-ahead log (WAL)
#
#wal_buffers = 8 # min 4
#wal_files = 0 # range 0-64
#wal_debug = 0 # range 0-16
#commit_delay = 5 # range 0-1000
#checkpoint_timeout = 300 # range 30-1800

#
# Debug display
#
#silent_mode = false

log_connections = true
log_timestamp = true
log_pid = true

#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

#ifdef USE_ASSERT_CHECKING
#debug_assertions = true
#endif

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

# Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_query_stats = false
#ifdef BTREE_BUILD_STATS
#show_btree_build_stats = false
#endif

#
# Lock Tracing
#
#trace_notify = false
#ifdef LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_spinlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0
#endif

-----Original Message-----
From: lamigo(at)atc(dot)unican(dot)es [mailto:lamigo(at)atc(dot)unican(dot)es]
Sent: Thursday, January 24, 2002 10:24 AM
To: Peter T. Brown
Cc: 'Jean Huveneers'; pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] Maximum Performance Follow-up Question

"Peter T. Brown" wrote:

> But how can Postgres be 'forced' to keep a table in memory? I've noticed
> that on our Dual Pentium4, 1GB RAM machine, the size of the individual
> postgres threads is very small. Top reports it as like 5K or 20K (I
believe
> that's what it means). Shouldn't this number be 100's of MB if postgres is
> properly moving my tables to RAM? I do notice that the system cache is
very
> very large... Is there any way to specify that a certain table should have
> priority for being transferred into RAM? Should I reduce the system cache
> size so that postgres has more room to play with?
>
> Thanks for any help!
>
> here is a top dump:
>

I don't know about linuxes, so I can't tell u about cache, what I can tell u
is if u asign a high number to shared_buffers(I would use 65536 for 1Gb ram
machine)
and a high sort_mem(16384kb on this machine) in postgres.conf u can do that
all the data a query needs could be on memory.
here is sizes for one of our backends(i've no time so i don't leave him to
charge all in memory(659M demanded 15M used) with a 100Mb base(a lot more
with
indexes)

PID PGRP USERNAME PRI SIZE RES STATE TIME WCPU% CPU% COMMAND
97441 94523 lamigo 20 659M 15M sleep 0:00 8.8 8.83
postgre

this is a memory snap shot of a typical load(7 backends with AD-HOC queries)
IRIX64 congrio 6.5 IP25 load averages: 6.04 5.03
3.73
20:19:56
97 processes: 90 sleeping, 7 running
8 CPUs: 25.3% idle, 72.1% usr, 2.5% ker, 0.0% wait, 0.0% xbrk, 0.1%
intr
Memory: 1024M max, 940M avail, 221M free, 2304M swap, 2294M free swap

feel free of making questions
hope it helps
regards