slow database - Mailing list pgsql-performance
From | alemon@tiago.hazor.com.br |
---|---|
Subject | slow database |
Date | |
Msg-id | 1076509411.402a3ae38d9b8@tiago.hazor.com.br Whole thread Raw |
Responses |
Re: slow database
Re: slow database Re: slow database Re: slow database Re: slow database Re: slow database |
List | pgsql-performance |
my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: ================================================================ # # PostgreSQL configuration file # ----------------------------- # # This file consists of lines of the form: # # name = value # # (The '=' is optional.) White space may be used. Comments are introduced # with '#' anywhere on a line. The complete list of option names and # allowed values can be found in the PostgreSQL documentation. The # commented-out settings shown in this file represent the default values. # # Any option can also be given as a command line switch to the # postmaster, e.g. 'postmaster -c log_connections=on'. Some options # can be changed at run-time with the 'SET' SQL command. # # This file is read on postmaster startup and when the postmaster # receives a SIGHUP. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect, or use # "pg_ctl reload". #======================================================================== # # Connection Parameters # #tcpip_socket = false #ssl = false max_connections = 50 superuser_reserved_connections = 2 #port = 5432 #hostname_lookup = false #show_source_port = false #unix_socket_directory = '' #unix_socket_group = '' #unix_socket_permissions = 0777 # octal #virtual_host = '' #krb_server_keyfile = '' # # Shared Memory Size # shared_buffers = 5000 # min max_connections*2 or 16, 8KB each max_fsm_relations = 400 # min 10, fsm is free space map, ~40 bytes max_fsm_pages = 80000 # min 1000, fsm is free space map, ~6 bytes max_locks_per_transaction = 128 # min 10 wal_buffers = 4 # min 4, typically 8KB each # # Non-shared Memory Sizes # sort_mem = 131072 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB # # Write-ahead log (WAL) # checkpoint_segments = 3 # in logfile segments, min 1, 16MB each checkpoint_timeout = 300 # range 30-3600, in seconds # commit_delay = 0 # range 0-100000, in microseconds commit_siblings = 5 # range 1-1000 # fsync = false wal_sync_method = fdatasync # the default varies across platforms: # # fsync, fdatasync, open_sync, or open_datasync wal_debug = 0 # range 0-16 # # Optimizer Parameters # enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false effective_cache_size = 170000 # typically 8KB each random_page_cost = 1000000000 # units are one sequential page fetch cost cpu_tuple_cost = 0.3 # (same) cpu_index_tuple_cost = 0.6 # (same) cpu_operator_cost = 0.7 # (same) default_statistics_target = 1 # range 1-1000 # # GEQO Optimizer Parameters # geqo = true geqo_selection_bias = 2.0 # range 1.5-2.0 geqo_threshold = 2000 geqo_pool_size = 1024 # default based on tables in statement, # range 128-1024 geqo_effort = 1 geqo_generations = 0 geqo_random_seed = -1 # auto-compute seed # # Message display # server_min_messages = fatal # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, log, fatal, # panic client_min_messages = fatal # Values, in order of decreasing detail: # debug5, debug4, debug3, debug2, debug1, # log, info, notice, warning, error silent_mode = false log_connections = false log_pid = false log_statement = false log_duration = false log_timestamp = false #log_min_error_statement = error # Values in order of increasing severity: # debug5, debug4, debug3, debug2, debug1, # info, notice, warning, error, panic(off) #debug_print_parse = false #debug_print_rewritten = false #debug_print_plan = false #debug_pretty_print = false #explain_pretty_print = true # requires USE_ASSERT_CHECKING #debug_assertions = true # # Syslog # #syslog = 0 # range 0-2 #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres' # # Statistics # #show_parser_stats = false #show_planner_stats = false #show_executor_stats = false #show_statement_stats = false # requires BTREE_BUILD_STATS #show_btree_build_stats = false # # Access statistics collection # #stats_start_collector = true #stats_reset_on_server_start = true #stats_command_string = false #stats_row_level = false #stats_block_level = false # # Lock Tracing # #trace_notify = false # requires LOCK_DEBUG #trace_locks = false #trace_userlocks = false #trace_lwlocks = false #debug_deadlocks = false #trace_lock_oidmin = 16384 #trace_lock_table = 0 # # Misc # #autocommit = true #dynamic_library_path = '$libdir' #search_path = '$user,public' datestyle = 'iso, us' #timezone = unknown # actually, defaults to TZ environment setting #australian_timezones = false client_encoding = sql_ascii # actually, defaults to database encoding authentication_timeout = 1 # 1-600, in seconds deadlock_timeout = 100 # in milliseconds #default_transaction_isolation = 'read committed' #max_expr_depth = 1000 # min 10 #max_files_per_process = 1000 # min 25 #password_encryption = true #sql_inheritance = true #transform_null_equals = false #statement_timeout = 0 # 0 is disabled, in milliseconds db_user_namespace = false # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US' LC_MONETARY = 'en_US' LC_NUMERIC = 'en_US' LC_TIME = 'en_US' ================================================================ somebody please knows to give tips to me to increase the performance
pgsql-performance by date: