Appropriate values for sort_mem and shared_buffers

Поиск
Список
Период
Сортировка
От Steve Brett
Тема Appropriate values for sort_mem and shared_buffers
Дата
Msg-id C05E7DA1218ED411BF8A00105AC95A8E05BC7C3A@sv-cntrmail.emis.local
обсуждение исходный текст
Список 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



В списке pgsql-general по дате отправления:

Предыдущее
От: "Sam Liddicott"
Дата:
Сообщение: Startup death!
Следующее
От: Stephane Bortzmeyer
Дата:
Сообщение: Re: Logging SQL queries?