Por favor ingresa o regístrese. Julio 21, 2019, 06:16:15 pm

Autor Tema: Herramientas de Monitoreo de PostgreSQL en Linux  (Leído 2676 veces)

0 Usuarios y 1 Visitante están viendo este tema.

Desconectado admin

  • Administrator
  • Newbie
  • *****
  • Mensajes: 18
  • Karma: +0/-0
    • Ver Perfil
Herramientas de Monitoreo de PostgreSQL en Linux
« : Junio 16, 2014, 07:01:07 am »
Postgres Monitoring tools

Bare minimum:
Server CPU, memory, I/O, network usage, and all “slow” queries logged.

More extensive:
System stats:

CPU usage, per-proc if available
Memory usage, including swap
disk usage (in terms of space – pay special attention to database partitions)
disk I/O
disk busy
network stats, including errors (if you have a Cisco network & are friends with the network team, netflow data is cool to have)

If I could have everything I wanted: everything from vmstat and iostat extended data

Pg stats:
number of connections
transactions
idle transactions
commits vs rollbacks
locks
checkpoint frequency
database size
table size (plus bloat, if we can find a good query for it)
index size (same)

If I could have everything I wanted:
everything from pg_stat_database, pg_stat_sys_*, pg_statio_*, and pg_stat_user_activity

Activity logs configured as outlined here.

Then there’s a whole class of things that fall under “How long does it take to…”: do a backup, restore a backup, etc.

postgresql.conf: log settings

One of the first changes I make in a new Postgres install is to enable activity logging. This way, if I encounter some problems down the the road, I’ve already got some data collected that may help me troubleshoot.

Here are my basic settings:

# enable logging, of course:
log_destination = 'stderr'
logging_collector = on

# target directory, on its own mount point if you can do it
log_directory = '/pg_log'

# I just use the default here
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

# log_line_prefix in pgbadger-compatible format
# timestamp [pid]: line_number user application remote_host_&_port database %
log_line_prefix = '%t [%p]: [%l-1] %u %a %r %d %%'

# so I know when people are tinkering
log_statement = 'ddl'

# pre-emptive troubleshooting items
log_checkpoints = on

# log all statements that take longer than 10 minutes
# YMMV here - adjust for your application
log_min_duration_statement = 10min

# keep track of longer-running autovac jobs; may indicate that I need to tweak my autovacuum settings
# again, YMMV
log_autovacuum_min_duration = 10min

# again, I want to keep track of these; can be a harbinger of problems
# you may need to adjust your deadlock_timeout
log_lock_waits = on

# translates an IP to a hostname.
# Useful, but can cause problems on high-activity systems, so use with caution.
log_hostname = on
« Última Modificación: Junio 16, 2014, 07:02:55 am por admin »