Re: Maximum Performance Follow-up Question - Mailing list pgsql-admin

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@atc.unican.es [mailto:lamigo@atc.unican.es]
Sent: Thursday, January 24, 2002 10:24 AM
To: Peter T. Brown
Cc: 'Jean Huveneers'; pgsql-admin@postgresql.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