Re: Needed: Simplified guide to optimal memory configuration - Mailing list pgsql-performance

For those who provided some guidance, I say "thank you." You comments
helped out a lot. All of our customers who are using the older
release are now very pleased with the performance of the database now
that we were able to give them meaningful configuration settings. I'm
also pleased to see that Frank WIles has taken upon himself the
effort to write this guidance down for folks like me.

Kudos to you all. Thanks again.

Todd


On Jun 15, 2005, at 2:06 AM, Todd Landfried wrote:

> 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>