Appropriate values for sort_mem and shared_buffers

From: Steve Brett <SBrett(at)e-mis(dot)com>
To: "Pgsql-General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Appropriate values for sort_mem and shared_buffers
Date: 2002-07-18 09:07:33
Message-ID: C05E7DA1218ED411BF8A00105AC95A8E05BC7C3A@sv-cntrmail.emis.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephane Bortzmeyer 2002-07-18 10:16:01 Re: Logging SQL queries?
Previous Message Sam Liddicott 2002-07-18 08:48:12 Startup death!