Обсуждение: Appropriate values for sort_mem and shared_buffers

Hi,

I have a calendar and reporting system written in PHP that runs off a
postgresql db.

We've recently upgraded the server to 7.2 and I have the following question.

Machine spec is P3 666 with 512 meg.
The server handles around 30-50 concurrent users and approx 200 diffferent
logins(people) a day generating a total count of logins around the 500 mark.
It is used constantly 24/7. As some of the departments insist (oh how I have
tried to convince them otherwise!!!) on viewing 3 months of data at a time
some of the queries produce large datasets that are passed back to the
clients.

What I need to know is whether the start parameters I have are reasonable...

The values I currentlly have are the following with the bold values being
the ones I am concerned about:

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 off
NOTICE:  silent_mode is off
NOTICE:  log_connections is on
NOTICE:  log_timestamp is on
NOTICE:  log_pid is on
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 11
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 2
NOTICE:  max_connections is 128
NOTICE:  shared_buffers is 1024
NOTICE:  port is 5432
NOTICE:  unix_socket_permissions is 511
NOTICE:  sort_mem is 512
NOTICE:  vacuum_mem is 8192
NOTICE:  max_files_per_process is 1000
NOTICE:  debug_level is 0
NOTICE:  max_expr_depth is 10000
NOTICE:  max_fsm_relations is 100
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 1000
NOTICE:  random_page_cost is 4
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 unset
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 European 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

Many thanks in advance,

Steve Brett

Sorry, I don't have an answer. But I'm wondering what command you used
to get the notices?

Thanks.
-Tony

> 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

On Thu, Jul 18, 2002 at 01:38:17PM -0700, Tony Reina wrote:
> Sorry, I don't have an answer. But I'm wondering what command you used
> to get the notices?

SHOW ALL;

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC