Re: tuning

Alejandro:

No soy experto en las opciones de postgresql.conf, pero de todas formas
estimo que tienes que optimizar la definicion de las tablas, indices y
consultas antes de revisar postgresql.conf.

Si puedes, muestranos las tablas, indices y consultas y tal vez te pueda
hacer algunas sugerencias.

Diego.

On mar, 2004-06-08 at 17:52, Alejandro Casanova wrote:

> Este es elarchivo de postgresql.conf
>
> #
> # 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 = true
> #ssl = false
>
> max_connections = 120
> #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 = 480 # min max_connections*2 or 16, 8KB each
> #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
> #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes
> #max_locks_per_transaction = 64 # min 10
> #wal_buffers = 8 # min 4, typically 8KB each
>
> #
> # Non-shared Memory Sizes
> #
> sort_mem = 4096 # 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 = true
> #wal_sync_method = fsync # the default varies across platforms:
> # # fsync, fdatasync, open_sync, or open_datasync
> #wal_debug = 0 # range 0-16
>
>
> #
> # Optimizer Parameters
> #
> #enable_seqscan = true
> #enable_indexscan = true
> #enable_tidscan = true
> #enable_sort = true
> #enable_nestloop = true
> #enable_mergejoin = true
> #enable_hashjoin = true
>
> #effective_cache_size = 1000 # typically 8KB each
> #random_page_cost = 4 # units are one sequential page fetch cost
> #cpu_tuple_cost = 0.01 # (same)
> #cpu_index_tuple_cost = 0.001 # (same)
> #cpu_operator_cost = 0.0025 # (same)
>
> #default_statistics_target = 10 # range 1-1000
>
> #
> # GEQO Optimizer Parameters
> #
> #geqo = true
> #geqo_selection_bias = 2.0 # range 1.5-2.0
> #geqo_threshold = 11
> #geqo_pool_size = 0 # 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 = notice # Values, in order of decreasing detail:
> # debug5, debug4, debug3, debug2, debug1,
> # info, notice, warning, error, log, fatal,
> # panic
> #client_min_messages = notice # 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 = 60 # 1-600, in seconds
> #deadlock_timeout = 1000 # in milliseconds
> #default_transaction_isolation = 'read committed'
> #max_expr_depth = 10000 # 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.UTF-8'
> LC_MONETARY = 'en_US.UTF-8'
> LC_NUMERIC = 'en_US.UTF-8'
> LC_TIME = 'en_US.UTF-8'
>
>
>
>
>
>
> On Tue, 2004-06-08 at 14:31, Diego Gil wrote:
> > Habria que ver como estan definidas las bases de datos y las
> > consultas.
> >
> > Diego.
> >
> > On mar, 2004-06-08 at 17:12, Alejandro Casanova wrote:
> > > hello
> > >
> > > tengo un problema
> > >
> > > tengo un equipo con dos procesadores xeon de 2.8 4 gigas en ram y las
> > > conssultas en postgres son lentas con 9 millones de registros, se demora
> > > hasta un minito , he tratado de de realizar todos los tinung que
> > > encuentro pero ninguno hacelera el proceso.
> > >
> > > espero que me puedan ayudar
> > >
> > >
> > >
> > > saludos alejandro
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 7: no olvides aumentar la configuración del "free space map"
> >
> >
> > Diego A. Gil
> >
> > Director
> >
> > Admin S.A.
> >
> >

Diego A. Gil
Director
Admin S.A.