Buenas,
Tenho um BI aqui na empresa o qual sempre dá alguns problemas insanos com o pg, porém hoje fiquei na cola dos caras, até pegar o momento exato que ocorre os problemas.
No pg ele lança a seguinte informação nos logs "AVISO: pgstat wait timeout", como se ele não estivesse dando conta do recado.
Acompanhando IO estava tranquilo "
iostat -x 1" porém ele continuava a reclamar.
Pesquisando aqui ali em questão de 15 minutos já tinha encontrado várias pedras para tentar resolver o problema.
Dentre elas a principal de todas "
stats_temp_directory" onde o pq salva algumas estatísticas, onde o ideal seria carregar isso na memoria, e não em disco.
Então para jogar estas estatísticas na ram basta seguir estes passos.
# Diretório
mkdir /var/lib/pgsql_stats_tmp
# Permissão para a pasta geral
chmod 777 /var/lib/pgsql_stats_tmp
# Permissão "sticky-bit", onde todos podem ler, escrever e executar, porém so podem fazer isso com seus arquivos.
chmod +t /var/lib/pgsql_stats_tmp
# No fstab vamos montar o diretório e indicar o quanto de memoria ram ele vai ocupar, nesta caso 2G já esta ótimo.
tmpfs /var/lib/pgsql_stats_tmp tmpfs size=2G,uid=postgres,gid=postgres 0 0# Agora é so reiniciar o serviço e correr para o abraço
/etc/init.d/postgresql-9.2 restart
# FSTAB dos diretórios
/dev/xvda1 / ext4 defaults,noatime,nodiratime,data=writeback,commit=100,nobh 1 1
/dev/xvda3 /db_sadig xfs defaults,noatime,nodiratime,allocsize=512m,barrier=0,inode64,logbufs=8 1 2
# Limites /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
# Sysctl.conf
vm.swappiness = 0 # não usa o swap
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
net.ipv4.tcp_rmem = 4096 262144 4194304
net.ipv4.tcp_wmem = 4096 262144 4194304
vm.dirty_background_ratio = 50
vm.dirty_ratio = 80
fs.file-max = 6815744
fs.aio-max-nr = 104857600
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 0
net.core.netdev_max_backlog = 65536
net.ipv4.tcp_rfc1337 = 1
net.ipv4.ip_local_port_range = 1024 65535
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_no_metrics_save = 1
net.ipv4.tcp_moderate_rcvbuf = 1
net.ipv4.tcp_fin_timeout = 30
net.ipv4.conf.all.arp_notify = 1
# Aqui é feito calculos
# http://www.postgresql.org/docs/9.0/static/kernel-resources.html
# http://helkmut.blogspot.com.br/2011/07/postgresql-tunando-seu-shmmax-e-shmall.html
# http://michael.otacoo.com/postgresql-2/take-care-of-kernel-memory-limitation-for-postgresql-shared-buffers/
# http://serverfault.com/questions/341680/postgresql-performance-adjusting-shmmax-and-shmall
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
# postgresql.conf
#
# ATENCAÇÃO
# Não sou DBA, então pode ter parâmetros fora do normal, que na visão de um DBA estão errados,
# ou deveriam, ter outros valores. Então se você é DBA ou percebeu algum erro comunique-me.
#
#
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 300 # (change requires restart)
shared_buffers = 2048MB # min 128kB
# (change requires restart)
temp_buffers = 512MB # min 800kB
max_prepared_transactions = 300 # zero disables the feature
# (change requires restart)
work_mem = 256MB # min 64kB
maintenance_work_mem = 512MB # min 1MB
synchronous_commit = on # synchronization level;
# off, local, remote_write, or on
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1h # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
random_page_cost = 6.0 # same scale as above
effective_cache_size = 2048MB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
log_truncate_on_rotation = on # If on, an existing log file with the
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
#log_lock_waits = off # log lock waits >= deadlock_timeout
log_statement = 'all' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'Brazil/East'
log_checkpoints = on
stats_temp_directory = '/var/lib/pgsql_stats_tmp'
datestyle = 'iso, dmy'
#intervalstyle = 'postgres'
timezone = 'Brazil/East'
# These settings are initialized by initdb, but they can be changed.
lc_messages = 'pt_BR.ISO88591' # locale for system error message
# strings
lc_monetary = 'pt_BR.ISO88591' # locale for monetary formatting
lc_numeric = 'pt_BR.ISO88591' # locale for number formatting
lc_time = 'pt_BR.ISO88591'
default_text_search_config = 'pg_catalog.english'
default_with_oids = off
escape_string_warning = on
standard_conforming_strings = on
