Обсуждение: 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
Re: Appropriate values for sort_mem and shared_buffers
От
nconway@klamath.dyndns.org (Neil Conway)
Дата:
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