User:Robe

From PostgreSQL wiki

Revision as of 00:37, 3 March 2010 by Robe (Talk | contribs)

Jump to: navigation, search

Contents

Ad personam

Michael Renner http://amd.co.at/

Scribbles

Logging

After looking into Syslog per suggestions of pgfouine it seems as if Syslog-Logs are much easier to parse and overall more deterministic than "Standard" Logs.

The main advantages are:

  • PID and
  • per-backend Linecounter allow clear identification of interleaved/mangled messages
  • Per-"chunk" identifier allows to identify lines which belong to the same log event.

Standard Syslog Output

Oct  8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-1] 2008-10-08 20:48:26 UTC ERROR:  duplicate key value violates unique constraint "newsgroups_name_key"
Oct  8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-2] 2008-10-08 20:48:26 UTC STATEMENT:  insert into newsgroups
Oct  8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-3]  (name,kategorie,first_art,last_art,last_checked,num_articles,num_files,num_threads,server,days_to_keep)
Oct  8 20:48:26 fst-prod-indexrevdb01 postgres[18134]: [6459-4]  values($1,'--',$2,$3,0,$4,0,0,$5,$6)

Standard Logging Output

2008-10-05 05:29:20 UTC ERROR:  duplicate key value violates unique constraint "newsgroups_name_key"
2008-10-05 05:29:20 UTC STATEMENT:  insert into newsgroups (name,kategorie,first_art,last_art,last_checked,num_articles,num_files,num_threads,server,days_to_keep) values($1,'--',$2,$3,0,$4,0,0,$5,$6)

Syslog-Emulation

With log_line_prefix = '%t postgres[%p]: [%l-1] ' you can achieve the following output:

2008-10-08 21:20:46 UTC postgres[30545]: [12911-1] ERROR:  duplicate key value violates unique constraint "newsgroups_name_key"
2008-10-08 21:20:46 UTC postgres[30545]: [12912-1] STATEMENT:  insert into newsgroups (name,kategorie,first_art,last_art,last_checked,num_articles,num_files,num_threads,server,days_to_keep) values($1
,'--',$2,$3,0,$4,0,0,$5,$6)

Which is already much more deterministic than the defaults.

Ideas

  • Fix Status Quo of VACUUM Full
  • Fix Cluster
  • Add more Aliases for different Encodings
  • Ease Table Partitioning
  • Revisit all non-transactional/WAL logged commands
  • Key Compression for Btree indexes
Personal tools