issue with jdbc collection

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

issue with jdbc collection

Marcel Fuhrmann
Hello!

I've created a JDBC datacollection and ran into an issue.
As you can see in the config below there are two queries. RRDs are created for both. The first query is working fine. The second one always puts out "0". And that is not correct. It should bring a value like 6000-7000.
But the SQL statement works correct when I use the psql command or pgadmin.

It's a Postgres 9.4 database and the dc column is defined as 'timestamp(6) without time zone'.

I tried a lot different sql spellings (btw: using :: in a SQL seems not to be allowed: Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "::").
So my questions are:

* Why do I always get zeros?
* Do you know a behaviour like this?
* Are there any restrictions using the provided postgres driver?
* Do I have to escape other chars? (But if something is completely wrong, the log usually throws exceptions)
* Something or another?

Thanks in advance!

---------------------
<service name="RawFilesIn" interval="60000" user-defined="true" status="on">
 <parameter key="collection" value="RawFilesIn"/>
 <parameter key="thresholding-enabled" value="true"/>
 <parameter key="driver" value="org.postgresql.Driver"/>
 <parameter key="user" value="USER"/>
 <parameter key="password" value="PASSWORD"/>
 <parameter key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:PORT/DATABASE"/>
</service>

<collector service="RawFilesIn" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>
---------------------
   <jdbc-collection name="RawFilesIn">
        <rrd step="60">
            <rra>RRA:AVERAGE:0.5:1:2016</rra>
            <rra>RRA:AVERAGE:0.5:12:1488</rra>
            <rra>RRA:AVERAGE:0.5:288:366</rra>
            <rra>RRA:MAX:0.5:288:366</rra>
            <rra>RRA:MIN:0.5:288:366</rra>
        </rrd>
        <queries>
            <query name="pspRawFilesIn" ifType="ignore">
                <statement>
                    <queryString>SELECT count(*) as pspRawFilesIn FROM files</queryString>
                </statement>
                <columns>
                    <column name="pspRawFilesIn" data-source-name="pspRawFilesIn" alias="pspRawFilesIn" type="gauge"/>
                </columns>
            </query>
            <query name="pspRawFilesIn15" ifType="ignore">
                <statement>
                    <queryString>SELECT count(*) as pspRawFilesIn15 FROM files where dc &gt; (current_timestamp - INTERVAL '15 minutes');</queryString>
                </statement>
                <columns>
                    <column name="pspRawFilesIn15" data-source-name="pspRawFilesIn15" alias="pspRawFilesIn15" type="gauge"/>
                </columns>
            </query>
        </queries>
    </jdbc-collection>
-------------------------------------
2017-03-08 15:24:57,493 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.JdbcCollector: Checking availability of group pspRawFilesIn15
2017-03-08 15:24:57,499 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.JdbcCollector: Group pspRawFilesIn15 is  available
2017-03-08 15:24:57,505 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.s.AbstractCollectionResource: Adding attribute org.opennms.netmgt.collectd.jdbc.JdbcCollectionAttribute: JdbcCollectionAttribute pspRawFilesIn15=0 to group AttrGroupType[name=pspRawFilesIn15, ifType=ignore] for null@fs/PSP/pgb-testnode
2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AttributeGroup: Visiting Group AttrGroupType[name=pspRawFilesIn15, ifType=ignore] for null@fs/PSP/pgb-testnode
2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AttributeGroup: AttrGroupType[name=pspRawFilesIn15, ifType=ignore] for null@fs/PSP/pgb-testnode.shouldPersist = true
2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute pspRawFilesIn15=0
2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute pspRawFilesIn15=0
2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute pspRawFilesIn15=0
2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.r.r.MultithreadedJniRrdStrategy: createDefinition: filename [/var/lib/opennms/rrd/snmp/fs/PSP/pgb-testnode/pspRawFilesIn15.rrd] already exists returning null as definition
2017-03-08 15:24:57,528 INFO  [Collectd-Thread-48-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: updating RRD file /var/lib/opennms/rrd/snmp/fs/PSP/pgb-testnode/pspRawFilesIn15.rrd with values '1488983098:0'
--------------------------------------
select count(*) as pspRawFilesIn15 from files where TO_TIMESTAMP(dc::varchar, 'YYYY-MM-DD HH24:MI:SS') > (current_timestamp - INTERVAL '15 minutes');

select count(*) as pspRawFilesIn15 from files where dc::timestamp(6) > (current_timestamp - INTERVAL '15 minutes');

select count(*) as pspRawFilesIn15 from files where dc > (current_timestamp - INTERVAL '15 minutes');

SELECT count(*) as pspRawFilesIn15 FROM files where dc > (now() - interval '15 minutes');

SELECT count(*) as pspRawFilesIn15 FROM files where dc::timestamp > (current_timestamp - INTERVAL '15 minutes')

select count(*) as pspRawFilesIn15 from files where TO_TIMESTAMP(dc::varchar, 'YYYY-MM-DD HH24:MI:SS') > (current_timestamp - INTERVAL '15 minutes');

select count(*) as pspRawFilesIn15 from files where dc::timestamp(6) > (current_timestamp - INTERVAL '15 minutes');

select count(*) as pspRawFilesIn15 from files where dc > (current_timestamp - INTERVAL '15 minutes');

select count(fi.*) as pspRawFilesIn15 from files fi where fi.dc > (current_timestamp - INTERVAL '15 minutes');

select count(*) as pspRawFilesIn15 from files where dc &gt; (current_timestamp - INTERVAL &apos;15 minutes&apos;);

------------------------------------------------------------------------------
Announcing the Oxford Dictionaries API! The API offers world-renowned
dictionary content that is easy and intuitive to access. Sign up for an
account today to start using our lexical data to power your apps and
projects. Get started today and enter our developer competition.
http://sdm.link/oxford
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: issue with jdbc collection

Marcel Fuhrmann
UPDATE:

Those work "fine":
select count(*) as pspRawFilesIn15 from files where dc at time ZONE 'utc' &gt; (current_timestamp - INTERVAL '15 minutes');

and

select count(*) as pspRawFilesIn15 from files where age(current_timestamp, dc) &gt; (INTERVAL '15 minutes');

The problem is, that they need 25-40 seconds to get a response. I don't want to use them because some of the others (see below) only need milliseconds. But atm I don't have other options. As long the other SQLs (which are more efficient) won't run.

Thanks for your help!




> Marcel Fuhrmann <[hidden email]> hat am 8. März 2017 um 15:30 geschrieben:
>
>
> Hello!
>
> I've created a JDBC datacollection and ran into an issue.
> As you can see in the config below there are two queries. RRDs are created for both. The first query is working fine. The second one always puts out "0". And that is not correct. It should bring a value like 6000-7000.
> But the SQL statement works correct when I use the psql command or pgadmin.
>
> It's a Postgres 9.4 database and the dc column is defined as 'timestamp(6) without time zone'.
>
> I tried a lot different sql spellings (btw: using :: in a SQL seems not to be allowed: Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "::").
> So my questions are:
>
> * Why do I always get zeros?
> * Do you know a behaviour like this?
> * Are there any restrictions using the provided postgres driver?
> * Do I have to escape other chars? (But if something is completely wrong, the log usually throws exceptions)
> * Something or another?
>
> Thanks in advance!
>
> ---------------------
> <service name="RawFilesIn" interval="60000" user-defined="true" status="on">
>  <parameter key="collection" value="RawFilesIn"/>
>  <parameter key="thresholding-enabled" value="true"/>
>  <parameter key="driver" value="org.postgresql.Driver"/>
>  <parameter key="user" value="USER"/>
>  <parameter key="password" value="PASSWORD"/>
>  <parameter key="url" value="jdbc:postgresql://OPENNMS_JDBC_HOSTNAME:PORT/DATABASE"/>
> </service>
>
> <collector service="RawFilesIn" class-name="org.opennms.netmgt.collectd.JdbcCollector"/>
> ---------------------
>    <jdbc-collection name="RawFilesIn">
>         <rrd step="60">
>             <rra>RRA:AVERAGE:0.5:1:2016</rra>
>             <rra>RRA:AVERAGE:0.5:12:1488</rra>
>             <rra>RRA:AVERAGE:0.5:288:366</rra>
>             <rra>RRA:MAX:0.5:288:366</rra>
>             <rra>RRA:MIN:0.5:288:366</rra>
>         </rrd>
>         <queries>
>             <query name="pspRawFilesIn" ifType="ignore">
>                 <statement>
>                     <queryString>SELECT count(*) as pspRawFilesIn FROM files</queryString>
>                 </statement>
>                 <columns>
>                     <column name="pspRawFilesIn" data-source-name="pspRawFilesIn" alias="pspRawFilesIn" type="gauge"/>
>                 </columns>
>             </query>
>             <query name="pspRawFilesIn15" ifType="ignore">
>                 <statement>
>                     <queryString>SELECT count(*) as pspRawFilesIn15 FROM files where dc > (current_timestamp - INTERVAL '15 minutes');</queryString>
>                 </statement>
>                 <columns>
>                     <column name="pspRawFilesIn15" data-source-name="pspRawFilesIn15" alias="pspRawFilesIn15" type="gauge"/>
>                 </columns>
>             </query>
>         </queries>
>     </jdbc-collection>
> -------------------------------------
> 2017-03-08 15:24:57,493 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.JdbcCollector: Checking availability of group pspRawFilesIn15
> 2017-03-08 15:24:57,499 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.JdbcCollector: Group pspRawFilesIn15 is  available
> 2017-03-08 15:24:57,505 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.s.AbstractCollectionResource: Adding attribute org.opennms.netmgt.collectd.jdbc.JdbcCollectionAttribute: JdbcCollectionAttribute pspRawFilesIn15=0 to group AttrGroupType[name=pspRawFilesIn15, ifType=ignore] for null@fs/PSP/pgb-testnode
> 2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AttributeGroup: Visiting Group AttrGroupType[name=pspRawFilesIn15, ifType=ignore] for null@fs/PSP/pgb-testnode
> 2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AttributeGroup: AttrGroupType[name=pspRawFilesIn15, ifType=ignore] for null@fs/PSP/pgb-testnode.shouldPersist = true
> 2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.s.AbstractCollectionAttribute: Visiting attribute JdbcCollectionAttribute pspRawFilesIn15=0
> 2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AbstractPersister: Persisting JdbcCollectionAttribute pspRawFilesIn15=0
> 2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.c.a.AbstractPersister: Storing attribute JdbcCollectionAttribute pspRawFilesIn15=0
> 2017-03-08 15:24:57,528 DEBUG [Collectd-Thread-48-of-50] o.o.n.r.r.MultithreadedJniRrdStrategy: createDefinition: filename [/var/lib/opennms/rrd/snmp/fs/PSP/pgb-testnode/pspRawFilesIn15.rrd] already exists returning null as definition
> 2017-03-08 15:24:57,528 INFO  [Collectd-Thread-48-of-50] o.o.n.c.p.r.RrdPersistOperationBuilder: updateRRD: updating RRD file /var/lib/opennms/rrd/snmp/fs/PSP/pgb-testnode/pspRawFilesIn15.rrd with values '1488983098:0'
> --------------------------------------
> select count(*) as pspRawFilesIn15 from files where TO_TIMESTAMP(dc::varchar, 'YYYY-MM-DD HH24:MI:SS') > (current_timestamp - INTERVAL '15 minutes');
>
> select count(*) as pspRawFilesIn15 from files where dc::timestamp(6) > (current_timestamp - INTERVAL '15 minutes');
>
> select count(*) as pspRawFilesIn15 from files where dc > (current_timestamp - INTERVAL '15 minutes');
>
> SELECT count(*) as pspRawFilesIn15 FROM files where dc > (now() - interval '15 minutes');
>
> SELECT count(*) as pspRawFilesIn15 FROM files where dc::timestamp > (current_timestamp - INTERVAL '15 minutes')
>
> select count(*) as pspRawFilesIn15 from files where TO_TIMESTAMP(dc::varchar, 'YYYY-MM-DD HH24:MI:SS') > (current_timestamp - INTERVAL '15 minutes');
>
> select count(*) as pspRawFilesIn15 from files where dc::timestamp(6) > (current_timestamp - INTERVAL '15 minutes');
>
> select count(*) as pspRawFilesIn15 from files where dc > (current_timestamp - INTERVAL '15 minutes');
>
> select count(fi.*) as pspRawFilesIn15 from files fi where fi.dc > (current_timestamp - INTERVAL '15 minutes');
>
> select count(*) as pspRawFilesIn15 from files where dc > (current_timestamp - INTERVAL &apos;15 minutes&apos;);
>
> ------------------------------------------------------------------------------
> Announcing the Oxford Dictionaries API! The API offers world-renowned
> dictionary content that is easy and intuitive to access. Sign up for an
> account today to start using our lexical data to power your apps and
> projects. Get started today and enter our developer competition.
> http://sdm.link/oxford
> _______________________________________________
> Please read the OpenNMS Mailing List FAQ:
> http://www.opennms.org/index.php/Mailing_List_FAQ
>
> opennms-discuss mailing list
>
> To *unsubscribe* or change your subscription options, see the bottom of this page:
> https://lists.sourceforge.net/lists/listinfo/opennms-discuss

------------------------------------------------------------------------------
Announcing the Oxford Dictionaries API! The API offers world-renowned
dictionary content that is easy and intuitive to access. Sign up for an
account today to start using our lexical data to power your apps and
projects. Get started today and enter our developer competition.
http://sdm.link/oxford
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: issue with jdbc collection

Reinhard Kuss
In reply to this post by Marcel Fuhrmann
Hi Marcel,

1) select (current_timestamp - INTERVAL '15 minutes' ) as ts;

returns a timestamp WITH timezone. Your column is

...'timestamp(6) without time zone'

I guess the comparison goes wrong ....


2) slow response:

25-40 sec. is unacceptable for a SQL-response. Consider an index on the
dc-column.




> It's a Postgres 9.4 database and the dc column is defined as
> 'timestamp(6) without time zone'.



--

Best Regards,
Reinhard

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Please read the OpenNMS Mailing List FAQ:
http://www.opennms.org/index.php/Mailing_List_FAQ

opennms-discuss mailing list

To *unsubscribe* or change your subscription options, see the bottom of this page:
https://lists.sourceforge.net/lists/listinfo/opennms-discuss
Loading...