Needed: Simplified guide to optimal memory configuration

From: Todd Landfried <tlandfried(at)viatornetworks(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Needed: Simplified guide to optimal memory configuration
Date: 2005-06-15 09:06:27
Message-ID: B27FF1E9-9BE2-4627-AF7D-46F6AD53CF8F@viatornetworks.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I deeply apologize if this has been covered with some similar topic
before, but I need a little guidance in the optimization department.
We use Postgres as our database and we're having some issues dealing
with customers who are, shall we say, "thrifty" when it comes to
buying RAM.

We tell them to buy at least 1GB, but there's always the bargain
chaser who thinks 256MB of RAM "is more than enough. So here's what I
need--in layman's terms 'cause I'll need to forward this message on
to them to prove what I'm saying (don't ya love customers?).

1. Our database has a total of 35 tables and maybe 300 variables
2. There are five primary tables and only two of these are written to
every minute, sometimes up to a menial 1500 transactions per minute.
3. Our customers usually buy RAM in 256MB, 512MB, 1GB or 2GB. We've
tried to come up with a optimization scheme based on what we've been
able to discern from lists like this, but we don't have a lot of
confidence. Using the default settings seems to work best with 1GB,
but we need help with the other RAM sizes.

What's the problem? The sucker gets s-l-o-w on relatively simple
queries. For example, simply listing all of the users online at one
time takes 30-45 seconds if we're talking about 800 users. We've
adjusted the time period for vacuuming the tables to the point where
it occurs once an hour, but we're getting only a 25% performance gain
from that. We're looking at the system settings now to see how those
can be tweaked.

So, what I need is to be pointed to (or told) what are the best
settings for our database given these memory configurations. What
should we do?

Thanks

Todd

Don't know if this will help, but here's the result of show all:

NOTICE: enable_seqscan is on
NOTICE: enable_indexscan is on
NOTICE: enable_tidscan is on
NOTICE: enable_sort is on
NOTICE: enable_nestloop is on
NOTICE: enable_mergejoin is on
NOTICE: enable_hashjoin is on
NOTICE: ksqo is off
NOTICE: geqo is on
NOTICE: tcpip_socket is on
NOTICE: ssl is off
NOTICE: fsync is on
NOTICE: silent_mode is off
NOTICE: log_connections is off
NOTICE: log_timestamp is off
NOTICE: log_pid is off
NOTICE: debug_print_query is off
NOTICE: debug_print_parse is off
NOTICE: debug_print_rewritten is off
NOTICE: debug_print_plan is off
NOTICE: debug_pretty_print is off
NOTICE: show_parser_stats is off
NOTICE: show_planner_stats is off
NOTICE: show_executor_stats is off
NOTICE: show_query_stats is off
NOTICE: stats_start_collector is on
NOTICE: stats_reset_on_server_start is on
NOTICE: stats_command_string is off
NOTICE: stats_row_level is off
NOTICE: stats_block_level is off
NOTICE: trace_notify is off
NOTICE: hostname_lookup is off
NOTICE: show_source_port is off
NOTICE: sql_inheritance is on
NOTICE: australian_timezones is off
NOTICE: fixbtree is on
NOTICE: password_encryption is off
NOTICE: transform_null_equals is off
NOTICE: geqo_threshold is 20
NOTICE: geqo_pool_size is 0
NOTICE: geqo_effort is 1
NOTICE: geqo_generations is 0
NOTICE: geqo_random_seed is -1
NOTICE: deadlock_timeout is 1000
NOTICE: syslog is 0
NOTICE: max_connections is 64
NOTICE: shared_buffers is 256
NOTICE: port is 5432
NOTICE: unix_socket_permissions is 511
NOTICE: sort_mem is 2048
NOTICE: vacuum_mem is 126622
NOTICE: max_files_per_process is 1000
NOTICE: debug_level is 0
NOTICE: max_expr_depth is 10000
NOTICE: max_fsm_relations is 500
NOTICE: max_fsm_pages is 10000
NOTICE: max_locks_per_transaction is 64
NOTICE: authentication_timeout is 60
NOTICE: pre_auth_delay is 0
NOTICE: checkpoint_segments is 3
NOTICE: checkpoint_timeout is 300
NOTICE: wal_buffers is 8
NOTICE: wal_files is 0
NOTICE: wal_debug is 0
NOTICE: commit_delay is 0
NOTICE: commit_siblings is 5
NOTICE: effective_cache_size is 79350
NOTICE: random_page_cost is 2
NOTICE: cpu_tuple_cost is 0.01
NOTICE: cpu_index_tuple_cost is 0.001
NOTICE: cpu_operator_cost is 0.0025
NOTICE: geqo_selection_bias is 2
NOTICE: default_transaction_isolation is read committed
NOTICE: dynamic_library_path is $libdir
NOTICE: krb_server_keyfile is FILE:/etc/pgsql/krb5.keytab
NOTICE: syslog_facility is LOCAL0
NOTICE: syslog_ident is postgres
NOTICE: unix_socket_group is unset
NOTICE: unix_socket_directory is unset
NOTICE: virtual_host is unset
NOTICE: wal_sync_method is fdatasync
NOTICE: DateStyle is ISO with US (NonEuropean) conventions
NOTICE: Time zone is unset
NOTICE: TRANSACTION ISOLATION LEVEL is READ COMMITTED
NOTICE: Current client encoding is 'SQL_ASCII'
NOTICE: Current server encoding is 'SQL_ASCII'
NOTICE: Seed for random number generator is unavailable

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Dennis Bjorklund 2005-06-15 09:25:37 Re: Needed: Simplified guide to optimal memory configuration
Previous Message K C Lau 2005-06-15 02:46:56 SELECT LIMIT 1 VIEW Performance Issue