Re: [collectd] Not seeing postgres data from collectd.
Sebastian Harl wrote: Hi, On Wed, Mar 10, 2010 at 10:47:39AM -0800, James Armstrong wrote: Sebastian Harl wrote: On your local machine, are there any files in /var/lib/collectd/rrd/ hostname/postgresql-dwh/? Are there any other files in /var/lib/collectd/rrd/ (on the local machine)? Here is the problem! The data is appearing in /var/lib/collectd/rrd/192.168.X.YY/postgresql-dwh/counter-eventcount.rrd All the other data appears under the hostname directory. So, it appears the postgresql plugin is using the Host from the Database stanza. The database doesn't allow connections through the fully qualified domain name (that we use for the collectd collections.) Is there a way to tell the plugin that data from Host 192.168.X.YY should be associated with Hostname FQDN? Yes, in case you're connecting through a UNIX socket or when connecting to localhost. I suppose, you're running PostgreSQL on the same machine, right? In that case, simply leaving out the Host config option will let the plugin connect to the UNIX socket. So, you're getting values on the local machine now? Do you get the same values on the central server as well? Many thanks for the help so far. I've gotten the database communicating on the unix socket, but the rrd file is filled with no data (nan.) When I run the query manually on the socket, I get output, but it takes 72 seconds to run: count -- 33306648 (1 row) This data is not being collected. Is counter the right type for this? James ___ collectd mailing list collectd@verplant.org http://mailman.verplant.org/listinfo/collectd
Re: [collectd] Not seeing postgres data from collectd.
Hi, On Wed, Mar 10, 2010 at 10:47:39AM -0800, James Armstrong wrote: Sebastian Harl wrote: On your local machine, are there any files in /var/lib/collectd/rrd/ hostname/postgresql-dwh/? Are there any other files in /var/lib/collectd/rrd/ (on the local machine)? Here is the problem! The data is appearing in /var/lib/collectd/rrd/192.168.X.YY/postgresql-dwh/counter-eventcount.rrd All the other data appears under the hostname directory. So, it appears the postgresql plugin is using the Host from the Database stanza. The database doesn't allow connections through the fully qualified domain name (that we use for the collectd collections.) Is there a way to tell the plugin that data from Host 192.168.X.YY should be associated with Hostname FQDN? Yes, in case you're connecting through a UNIX socket or when connecting to localhost. I suppose, you're running PostgreSQL on the same machine, right? In that case, simply leaving out the Host config option will let the plugin connect to the UNIX socket. So, you're getting values on the local machine now? Do you get the same values on the central server as well? HTH, Sebastian -- Sebastian tokkee Harl +++ GnuPG-ID: 0x8501C7FC +++ http://tokkee.org/ Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety. -- Benjamin Franklin signature.asc Description: Digital signature ___ collectd mailing list collectd@verplant.org http://mailman.verplant.org/listinfo/collectd
Re: [collectd] Not seeing postgres data from collectd.
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 ___ collectd mailing list collectd@verplant.org http://mailman.verplant.org/listinfo/collectd
Re: [collectd] Not seeing postgres data from collectd.
Hi James, On Tue, Mar 09, 2010 at 03:36:31PM -0800, James Armstrong wrote: Sebastian Harl wrote: On Tue, Mar 09, 2010 at 01:51:24PM -0800, James Armstrong wrote: 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? 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. For how long did you capture packets? If there query takes over 30 seconds, you'd, obviously, have to capture for at least that amount of time. Your server does receive and collect the other data, right? On your local machine, are there any files in /var/lib/collectd/rrd/ hostname/postgresql-dwh/? Are there any other files in /var/lib/collectd/rrd/ (on the local machine)? Btw., if the query would time out, you should get a warning / error message in the logs. Are there *any* messages related to postgresql in the log file? grep -Fi postgresql /var/log/collectd.log Here's the complete /etc/collectd.conf file (with IP's and passwords changed for all the obvious reasons!) in case there are conflicts: That config looks fine to me. Sorry for being a bit in the dark and possibly asking stupid questions, but I could not reproduce that behavior so far (I'll see if I can find some query that takes such a long time) and your setup looks fine to me. Cheers, Sebastian -- Sebastian tokkee Harl +++ GnuPG-ID: 0x8501C7FC +++ http://tokkee.org/ Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety. -- Benjamin Franklin signature.asc Description: Digital signature ___ collectd mailing list collectd@verplant.org http://mailman.verplant.org/listinfo/collectd
Re: [collectd] Not seeing postgres data from collectd.
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? Did you verify that the postgresql plugin actually connects to the database (select * from pg_stat_activity where datname = 'dwh')? 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. Which version of collectd do you use? 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. HTH, Sebastian -- Sebastian tokkee Harl +++ GnuPG-ID: 0x8501C7FC +++ http://tokkee.org/ Those who would give up Essential Liberty to purchase a little Temporary Safety, deserve neither Liberty nor Safety. -- Benjamin Franklin signature.asc Description: Digital signature ___ collectd mailing list collectd@verplant.org http://mailman.verplant.org/listinfo/collectd
