[collectd] Not seeing postgres data from collectd.

[James Armstrong]

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.

Sebastian Harl wrote:
> Hi,
> 
> On Mon, Mar 08, 2010 at 01:04:19PM -0800, James Armstrong wrote:
>> We're trying to add a monitor for some table sizes to collectd for a
>> postgres database.  Our configuration is to use a central server to host
>> the data, and various machines send the data to that server.
>>
>> I've added the following to /etc/collectd.conf on a working machine and
>> restarted collectd, but the postgres data is not appearing in the UDP
>> packets.  I have confirmed this with tcpdump.
>>
>> LoadPlugin "postgresql"
>>
>> ...
>>
>> <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 (correct password)
>>     Query eventcount
>>   </Database>
>> </Plugin>
> 
> That config snippet looks fine to me.
> 
>> The connection information for the database works, confirmed with psql.
> 
> What's the exact psql command line you were using to verify that?

 psql -h 192.168.X.YY -U dbuser -d dwh

I enter the password and run the query, and get valid output:

dwh=> SELECT COUNT(*) AS count FROM events;
  count
----------
 23048887
(1 row)


I also see in the logfile this:

[2010-03-05 21:16:05] postgresql: Sucessfully connected to database dwh
(user dbuser) at server 192.168.X.YY:5432 (server version: 8.3.9,
protocol version: 3, pid: 27281)

> Did you verify that the postgresql plugin actually connects to the
> database (select * from pg_stat_activity where datname = 'dwh')?

I see activity:

dwh=> select * from pg_stat_activity where datname='dwh';
 datid | datname | procpid | usesysid | usename  |
current_query                    | waiting |          xact_start
   |          query_start          |         backend_start         |
client_addr  | client_port
-------+---------+---------+----------+----------+-----------------------------------------------------+---------+-------------------------------+-------------------------------+-------------------------------+---------------+-------------
 16389 | dwh     |   27281 |    16385 |   dbuser | SELECT COUNT(*) AS
count FROM events;               | f       | 2010-03-09
21:43:55.771195+00 | 2010-03-09 21:43:55.771195+00 | 2010-03-05
21:16:05.760945+00 | 192.168.10.93 |       38455
 16389 | dwh     |   21765 |    16385 |   dbuser | select * from
pg_stat_activity where datname='dwh'; | f       | 2010-03-09
21:44:00.138973+00 | 2010-03-09 21:44:00.138973+00 | 2010-03-09
21:39:25.109213+00 | 192.168.10.93 |       46765

> Given that nobody else accesses the same table, the following query
> might give you an idea whether the query is executed at all: select
> seq_scan, seq_tup_read from pg_stat_user_tables where relname = 'dwh'.
> That query should return numbers that are increased after each collectd
> interval. Else, you could also configure PostgreSQL to log every query
> to make sure the query is actually executed by collectd.

That returns 0 rows.

> Which version of collectd do you use?

4.8.1-1.centos5

> 
>> There are no error messages in the logfile, we're using the logfile
>> plugin at loglevel debug.
> 
> To make use of loglevel "debug", collectd has to be compiled with
> debugging enabled. Is that the case? You should then see *lots* of
> messages in the log file.

Nope...

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?

> HTH,
> Sebastian
>