[collectd] Not seeing postgres data from collectd.

[James Armstrong]

Sebastian Harl wrote:
> Hi,
> 
> On Tue, Mar 09, 2010 at 01:51:24PM -0800, James Armstrong wrote:
>> Many thanks for you assistance.  I come from a MySQL and Oracle
>> background, and don't have much experience with postgres...  So this is
>> a bit of a learning experience for me.
> 
> Sure, no problem :-)
> 
>> Sebastian Harl wrote:
>> One possibility, this query takes a while to return, over 30 seconds.
>> Could it be timing out, and if so, how can I change the timeout for this
>> query without changing other intervals?
> 
> (Leaving out everything else for now, as this seems to be the most
> likely cause for your issue. Will come back to the other information in
> case we don't find any solution.)
> 
> I would not expect the query to time out (it runs fine from an
> interactive client, which should basically be the same). However, it
> will cause RRDtool to generate an empty graph. Do you use the rrdtool
> plugin? Did you really make sure that *no* data at all arrives at the
> server? Does collectd create any RRD files at all ($hostname/ 
> postgresql-dwh/counter-eventcount.rrd)? Is there any data in that file
> (run 'rrdtool fetch path/to/counter-eventcount.rrd MAX')? Did you try to
> store the collected data on the client as well? Does that work fine?


Yes, we use rrdtool.  No data was arriving at the remote machine (none
was in the UDP packet I captured with tcpdump.)  So, I enabled rrdtool
on the local machine -- no data was being captured there, either.

Here's the complete /etc/collectd.conf file (with IP's and passwords
changed for all the obvious reasons!) in case there are conflicts:

BaseDir     "/var/lib/collectd"
PIDFile     "/var/run/collectd.pid"
PluginDir   "/usr/lib64/collectd"
#TypesDB     "/usr/lib64/collectd/types.db"

LoadPlugin "syslog"
LoadPlugin "network"
LoadPlugin "rrdtool"

# common plugins
LoadPlugin "cpu"
LoadPlugin "memory"
LoadPlugin "df"
LoadPlugin "disk"
LoadPlugin "interface"
LoadPlugin "load"
LoadPlugin "ping"
LoadPlugin "swap"
LoadPlugin "users"
LoadPlugin "logfile"
LoadPlugin "postgresql"

# afn plugin
LoadPlugin "processes"

<Plugin "postgresql">
  <Query eventcount>
    Statement "SELECT COUNT(*) AS count FROM events;"
    <Result>
      Type counter
      InstancePrefix "eventcount"
      ValuesFrom "count"
    </Result>
  </Query>

  <Database dwh>
    Host "192.168.X.YY"
    User "dbuser"
    Password "password"
    Query eventcount
  </Database>
</Plugin>

<Plugin "logfile">
  LogLevel "debug"
  File "/var/log/collectd.log"
  Timestamp true
</Plugin>

<Plugin "rrdtool">
  DataDir "/var/lib/collectd/rrd"
  WritesPerSecond 30
</Plugin>

<Plugin "syslog">
  LogLevel "info"
</Plugin>

<Plugin "processes">
  Process "postmaster"
</Plugin>

<Plugin "network">
  Server "192.168.Z.AA" "12346"
  Server "192.168.X.BB" "12346"
</Plugin>

<Plugin "ping">
  Host "209.85.171.100" # google.com
</Plugin>

<Plugin "interface">
       Interface "eth0"
       Interface "eth1"
       Interface "eth2"
       IgnoreSelected false
</Plugin>

<Plugin "df">
       MountPoint "/dev/shm"
       IgnoreSelected true
</Plugin>

<Plugin "disk">
       Disk "/xvd[abcd]/"
       IgnoreSelected false
</Plugin>


> Cheers,
> Sebastian
>