opa
Você poderia aumentar o seu shared buffer pra 500 megas : 25% de 2G, o que
evitaria um acesso ao disco maior.
Esse parametro e talvez o mais importante a nivel de tunning de postgresql.
Dependendo do tipo consulta aumentaria tb o work_mem
outra coisa muito comum amigo, as pessoas as vezes nao sabem olhar a saida do
comando free -m:
da um free -m na sua maquina e posta aqui.
----- Original Message -----
From: Rafael Bragatto Gratz
To: Comunidade PostgreSQL Brasileira
Sent: Monday, June 09, 2008 9:47 AM
Subject: Re: [pgbr-geral]Fwd: servidor com 100% de uso de cpu/memória
Xenon 3.0 com 2gb de memória.
On Mon, Jun 9, 2008 at 9:42 AM, Aluisio Gouveia <[EMAIL PROTECTED]> wrote:
Rafael Bragatto Gratz escreveu:
> Boa tarde pessoal, estou com um problema sério em um dos meus clientes.
>
> Há 300 máquinas conectadas (em torno de 2 conexões por máquina) no
> servidor postgres e o servidor está com o processor a 100% de uso e
> memória com 1900gb em uso e está MUITO LENTO.
>
> Se alguém puder me ajudar eu agradeço.
>
> A configuraçào do meu postgre.conf está assim:
>
> *
>
#---------------------------------------------------------------------------
> # CONNECTIONS AND AUTHENTICATION
>
#---------------------------------------------------------------------------
>
> # - Connection Settings -
>
> listen_addresses = 'localhost' # what IP address(es) to
> listen on;
> # comma-separated list of addresses;
> # defaults to 'localhost', '*' = all
> # (change requires restart)
> port = 5432 # (change requires restart)
> max_connections = 1200 # (change requires restart)
> # Note: increasing max_connections costs ~400 bytes of shared memory per
> # connection slot, plus lock space (see max_locks_per_transaction). You
> # might also need to raise shared_buffers to support more connections.
> #superuser_reserved_connections = 3 # (change requires restart)
> #unix_socket_directory = '' # (change requires restart)
> #unix_socket_group = '' # (change requires restart)
> #unix_socket_permissions = 0777 # octal
> # (change requires restart)
> #bonjour_name = '' # defaults to the computer name
> # (change requires restart)
>
> # - Security & Authentication -
>
> #authentication_timeout = 1min # 1s-600s
> #ssl = off # (change requires restart)
> #password_encryption = on
> #db_user_namespace = off
>
> # Kerberos
> #krb_server_keyfile = '' # (change requires restart)
> #krb_srvname = 'postgres' # (change requires restart)
> #krb_server_hostname = '' # empty string matches any keytab entry
> # (change requires restart)
> #krb_caseins_users = off # (change requires restart)
>
> # - TCP Keepalives -
> # see 'man 7 tcp' for details
>
> #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
> # 0 selects the system default
> #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
> # 0 selects the system default
> #tcp_keepalives_count = 0 # TCP_KEEPCNT;
> # 0 selects the system default
>
>
>
#---------------------------------------------------------------------------
> # RESOURCE USAGE (except WAL)
>
#---------------------------------------------------------------------------
>
> # - Memory -
>
> shared_buffers = 256MB # min 128kB or max_connections*16kB
> # (change requires restart)
> #temp_buffers = 8MB # min 800kB
> #max_prepared_transactions = 5 # can be 0 or more
> # (change requires restart)
> # Note: increasing max_prepared_transactions costs ~600 bytes of
> shared memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 2MB # min 64kB
> maintenance_work_mem = 16MB # min 1MB
> #max_stack_depth = 2MB # min 100kB
>
> # - Free Space Map -
>
> max_fsm_pages = 204800 # min max_fsm_relations*16, 6 bytes each
> # (change requires restart)
> #max_fsm_relations = 1000 # min 100, ~70 bytes each
> # (change requires restart)
>
> # - Kernel Resource Usage -
>
> #max_files_per_process = 1000 # min 25
> # (change requires restart)
> #shared_preload_libraries = '' # (change requires restart)
>
> # - Cost-Based Vacuum Delay -
>
> #vacuum_cost_delay = 0 # 0-1000 milliseconds
> #vacuum_cost_page_hit = 1 # 0-10000 credits
> #vacuum_cost_page_miss = 10 # 0-10000 credits
> #vacuum_cost_page_dirty = 20 # 0-10000 credits
> #vacuum_cost_limit = 200 # 0-10000 credits
>
> # - Background writer -
>
> #bgwriter_delay = 200ms # 10-10000ms between rounds
> #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
> #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
> #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
> #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
>
>
>
#---------------------------------------------------------------------------
> # WRITE AHEAD LOG
>
#---------------------------------------------------------------------------
>
> # - Settings -
>
> #fsync = on # turns forced synchronization on or off
> #wal_sync_method = fsync # the default is the first option
> # supported by the operating system:
> # open_datasync
> # fdatasync
> # fsync
> # fsync_writethrough
> # open_sync
> #full_page_writes = on # recover from partial page writes
> #wal_buffers = 64kB # min 32kB
> # (change requires restart)
> #commit_delay = 0 # range 0-100000, in microseconds
> #commit_siblings = 5 # range 1-1000
>
> # - Checkpoints -
>
> #checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
> #checkpoint_timeout = 5min # range 30s-1h
> #checkpoint_warning = 30s # 0 is off
>
> # - Archiving -
>
> #archive_command = '' # command to use to archive a logfile segment
> #archive_timeout = 0 # force a logfile segment switch after this
> # many seconds; 0 is off
>
>
>
#---------------------------------------------------------------------------
> # QUERY TUNING
>
#---------------------------------------------------------------------------
>
> # - Planner Method Configuration -
>
> #enable_bitmapscan = on
> #enable_hashagg = on
> #enable_hashjoin = on
> #enable_indexscan = on
> #enable_mergejoin = on
> #enable_nestloop = on
> #enable_seqscan = on
> #enable_sort = on
> #enable_tidscan = on
>
> # - Planner Cost Constants -
>
> #seq_page_cost = 1.0 # measured on an arbitrary scale
> #random_page_cost = 4.0 # same scale as above
> #cpu_tuple_cost = 0.01 # same scale as above
> #cpu_index_tuple_cost = 0.005 # same scale as above
> #cpu_operator_cost = 0.0025 # same scale as above
> #effective_cache_size = 128MB
>
> # - Genetic Query Optimizer -
>
> #geqo = on
> #geqo_threshold = 12
> #geqo_effort = 5 # range 1-10
> #geqo_pool_size = 0 # selects default based on effort
> #geqo_generations = 0 # selects default based on effort
> #geqo_selection_bias = 2.0 # range 1.5-2.0
>
> # - Other Planner Options -
>
> #default_statistics_target = 10 # range 1-1000
> #constraint_exclusion = off
> #from_collapse_limit = 8
> #join_collapse_limit = 8 # 1 disables collapsing of explicit
> # JOINs
>
>
>
>
#---------------------------------------------------------------------------
> # RUNTIME STATISTICS
>
#---------------------------------------------------------------------------
>
> # - Query/Index Statistics Collector -
>
> #stats_command_string = on
> #update_process_title = on
>
> stats_start_collector = on # needed for block or row stats
> # (change requires restart)
> #stats_block_level = off
> stats_row_level = on
> #stats_reset_on_server_start = off # (change requires restart)
>
>
> # - Statistics Monitoring -
>
> #log_parser_stats = off
> #log_planner_stats = off
> #log_executor_stats = off
> #log_statement_stats = off
>
>
>
#---------------------------------------------------------------------------
> # AUTOVACUUM PARAMETERS
>
#---------------------------------------------------------------------------
>
> autovacuum = on # enable autovacuum subprocess?
> # 'on' requires stats_start_collector
> # and stats_row_level to also be on
> #autovacuum_naptime = 1min # time between autovacuum runs
> #autovacuum_vacuum_threshold = 500 # min # of tuple updates before
> # vacuum
> #autovacuum_analyze_threshold = 250 # min # of tuple updates before
> # analyze
> #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
> # vacuum
> #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
> # analyze
> #autovacuum_freeze_max_age = 200000000 # maximum XID age before
> forced vacuum
> # (change requires restart)
> #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
> # autovacuum, -1 means use
> # vacuum_cost_delay
> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
> # autovacuum, -1 means use
> # vacuum_cost_limit
>
>
>
#---------------------------------------------------------------------------
> # CLIENT CONNECTION DEFAULTS
>
#---------------------------------------------------------------------------
>
> # - Statement Behavior -
>
> #search_path = '"$user",public' # schema names
> #default_tablespace = '' # a tablespace name, '' uses
> # the default
> #check_function_bodies = on
> #default_transaction_isolation = 'read committed'
> #default_transaction_read_only = off
> #statement_timeout = 0 # 0 is disabled
> #vacuum_freeze_min_age = 100000000
>
> # - Locale and Formatting -
>
> datestyle = 'iso, dmy'
> #timezone = unknown # actually, defaults to TZ
> # environment setting
> #timezone_abbreviations = 'Default' # select the set of available
> timezone
> # abbreviations. Currently, there are
> # Default
> # Australia
> # India
> # However you can also create your own
> # file in share/timezonesets/.
> #extra_float_digits = 0 # min -15, max 2
> #client_encoding = sql_ascii # actually, defaults to database
> # encoding
>
> # These settings are initialized by initdb -- they might be changed
> lc_messages = 'Portuguese_Brazil' # locale for system error
> message
> # strings
> lc_monetary = 'Portuguese_Brazil' # locale for monetary
> formatting
> lc_numeric = 'Portuguese_Brazil' # locale for number formatting
> lc_time = 'Portuguese_Brazil' # locale for time formatting
>
> # - Other Defaults -
>
> #explain_pretty_print = on
> #dynamic_library_path = '$libdir'
> #local_preload_libraries = ''
>
>
>
#---------------------------------------------------------------------------
> # LOCK MANAGEMENT
>
#---------------------------------------------------------------------------
>
> #deadlock_timeout = 1s
> #max_locks_per_transaction = 64 # min 10
> # (change requires restart)
> # Note: each lock table slot uses ~270 bytes of shared memory, and
> there are
> # max_locks_per_transaction * (max_connections +
> max_prepared_transactions)
> # lock table slots.
>
>
>
#---------------------------------------------------------------------------
> # VERSION/PLATFORM COMPATIBILITY
>
#---------------------------------------------------------------------------
>
> # - Previous Postgres Versions -
>
> #add_missing_from = off
> #array_nulls = on
> #backslash_quote = safe_encoding # on, off, or safe_encoding
> #default_with_oids = off
> #escape_string_warning = on
> #standard_conforming_strings = off
> #regex_flavor = advanced # advanced, extended, or basic
> #sql_inheritance = on
>
> # - Other Platforms & Clients -
>
> #transform_null_equals = off
>
>
>
#---------------------------------------------------------------------------
> # CUSTOMIZED OPTIONS
>
#---------------------------------------------------------------------------
>
> #custom_variable_classes = '' # list of custom variable class names
> *
>
>
> --
> Rafael Bragatto Gratz
>
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> Projeta Sistemas Orientados Ao Seu Mundo
> www.projetasistemas.com.br <http://www.projetasistemas.com.br>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
Qual a configuração do hardware?
--
Cordialmente;
Aluisio Gouveia
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
--
Rafael Bragatto Gratz
[EMAIL PROTECTED]
Projeta Sistemas Orientados Ao Seu Mundo
www.projetasistemas.com.br
------------------------------------------------------------------------------
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
