Re: [cacti-user] cacti SQL queries clogged?

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

I had a very similar problem and could not fix it.  Ultimately I
switched to using the cactid poller and everything started magically
working again.  See http://cacti.net/cactid_info.php

- Scott

> -----Original Message-----
> From: cac...@li...=20
> [mailto:cac...@li...] On Behalf=20
> Of Rocky Zhou
> Sent: Thursday, May 24, 2007 12:25 AM
> To: cac...@li...
> Subject: Re: [cacti-user] cacti SQL queries clogged?
>=20
> And the SNMP works fine:
>=20
> sh$ snmpwalk -v 2c -c demo 192.168.0.98 if
> IF-MIB::ifIndex.1 =3D INTEGER: 1
> IF-MIB::ifIndex.2 =3D INTEGER: 2
> IF-MIB::ifIndex.3 =3D INTEGER: 3
> IF-MIB::ifIndex.4 =3D INTEGER: 4
> IF-MIB::ifDescr.1 =3D STRING: lo
> IF-MIB::ifDescr.2 =3D STRING: eth0
> IF-MIB::ifDescr.3 =3D STRING: eth1
> IF-MIB::ifDescr.4 =3D STRING: sit0
> IF-MIB::ifType.1 =3D INTEGER: softwareLoopback(24)
> IF-MIB::ifType.2 =3D INTEGER: ethernetCsmacd(6)
> IF-MIB::ifType.3 =3D INTEGER: ethernetCsmacd(6)
> IF-MIB::ifType.4 =3D INTEGER: tunnel(131)
> IF-MIB::ifMtu.1 =3D INTEGER: 16436
> IF-MIB::ifMtu.2 =3D INTEGER: 1500
> IF-MIB::ifMtu.3 =3D INTEGER: 1500
> IF-MIB::ifMtu.4 =3D INTEGER: 1480
>=20
>=20
> On 5/24/07, Rocky Zhou <cho...@gm...> wrote:
> >
> > After install SNMP + rrdtool + Cacti and settings, I found=20
> there is no=20
> > graph was shown. The rra/ dir is empty, and these log=20
> messages repeat:
> >
> > 05/24/2007 09:24:54 AM - SYSTEM STATS: Time:292.9215=20
> > Method:cmd.phpProcesses:1 Threads:N/A Hosts:2 HostsPerProcess:2=20
> > DataSources:5 RRDsProcessed:0
> > 05/24/2007 09:29:54 AM - POLLER: Poller[0] Maximum runtime of 292=20
> > seconds exceeded. Exiting.
> > 05/24/2007 09:29:54 AM - SYSTEM STATS: Time:292.8121=20
> > Method:cmd.phpProcesses:1 Threads:N/A Hosts:2 HostsPerProcess:2=20
> > DataSources:5 RRDsProcessed:0
> > 05/24/2007 09:34:54 AM - POLLER: Poller[0] Maximum runtime of 292=20
> > seconds exceeded. Exiting.
> > 05/24/2007 09:34:54 AM - SYSTEM STATS: Time:292.5663=20
> > Method:cmd.phpProcesses:1 Threads:N/A Hosts:2 HostsPerProcess:2=20
> > DataSources:5 RRDsProcessed:0
> > 05/24/2007 09:39:54 AM - POLLER: Poller[0] Maximum runtime of 292=20
> > seconds exceeded. Exiting.
> > 05/24/2007 09:39:54 AM - SYSTEM STATS: Time:292.6765=20
> > Method:cmd.phpProcesses:1 Threads:N/A Hosts:2 HostsPerProcess:2=20
> > DataSources:5 RRDsProcessed:0
> >
> > I opened the DEBUG logging level, and run:
> > /usr/local/bin/php /var/www/html/docs/docs/cacti/poller.php
> >
> > The output is like this:
> > /usr/local/bin/php, ARGS: -q=20
> /var/www/html/docs/docs/cacti/cmd.php 0=20
> > 6] Waiting on 1/1 pollers.
> > Waiting on 1/1 pollers.
> > Waiting on 1/1 pollers.
> > Waiting on 1/1 pollers.
> > Waiting on 1/1 pollers.
> >
> > While the log messages of the cron job is:
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL Cell: "select
> > count(*) from poller_item"
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > id from host where disabled =3D '' order by id"
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL Exec:=20
> "replace=20
> > into settings (name,value) values=20
> > ('path_webroot','/var/www/html/docs/docs/cacti')"
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL Exec:=20
> "truncate=20
> > table poller_time"
> > 05/24/2007 10:10:01 AM - POLLER: Poller[0] DEBUG: About to Spawn a=20
> > Remote Process [CMD: /usr/local/bin/php, ARGS: -q=20
> > /var/www/html/docs/docs/cacti/cmd.php 0 6 ]
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "     SELECT
> > * FROM host     WHERE (disabled =3D ''     AND id >=3D 0    =20
> AND id <=3D 6)
> > ORDER by id"
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "     SELECT
> > * from poller_item     WHERE (host_id >=3D 0     AND host_id=20
> <=3D 6)     ORDER
> > by host_id"
> > 05/24/2007 10:10:01 AM - CMDPHP: Poller[0] DEBUG: SQL Cell:=20
> "    SELECT
> > count(*)    FROM poller_item    WHERE (action=3D2    AND=20
> (host_id >=3D 0    AND
> > host_id <=3D6))"
> > 05/24/2007 10:10:02 AM - CMDPHP: Poller[0] DEBUG: SQL Exec:=20
> "replace=20
> > into settings (name,value) values ('date',NOW())"
> > 05/24/2007 10:10:02 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_id,end_time from poller_time where poller_id =3D 0"
> > 05/24/2007 10:10:02 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_output.output,  poller_output.time, =20
> > poller_output.local_data_id, poller_item.rrd_path, =20
> > poller_item.rrd_name,  poller_item.rrd_num  from
> > (poller_output,poller_item)  where
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000"
> > 05/24/2007 10:10:03 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_id,end_time from poller_time where poller_id =3D 0"
> > 05/24/2007 10:10:03 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_output.output,  poller_output.time, =20
> > poller_output.local_data_id, poller_item.rrd_path, =20
> > poller_item.rrd_name,  poller_item.rrd_num  from
> > (poller_output,poller_item)  where
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000"
> > 05/24/2007 10:10:04 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_id,end_time from poller_time where poller_id =3D 0"
> > 05/24/2007 10:10:04 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_output.output,  poller_output.time, =20
> > poller_output.local_data_id, poller_item.rrd_path, =20
> > poller_item.rrd_name,  poller_item.rrd_num  from
> > (poller_output,poller_item)  where
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000"
> > 05/24/2007 10:10:05 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_id,end_time from poller_time where poller_id =3D 0"
> > 05/24/2007 10:10:05 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_output.output,  poller_output.time, =20
> > poller_output.local_data_id, poller_item.rrd_path, =20
> > poller_item.rrd_name,  poller_item.rrd_num  from
> > (poller_output,poller_item)  where
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000"
> > 05/24/2007 10:10:06 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_id,end_time from poller_time where poller_id =3D 0"
> > 05/24/2007 10:10:06 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_output.output,  poller_output.time, =20
> > poller_output.local_data_id, poller_item.rrd_path, =20
> > poller_item.rrd_name,  poller_item.rrd_num  from
> > (poller_output,poller_item)  where
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000"
> > 05/24/2007 10:10:07 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_id,end_time from poller_time where poller_id =3D 0"
> > 05/24/2007 10:10:07 AM - CMDPHP: Poller[0] DEBUG: SQL=20
> Assoc: "select=20
> > poller_output.output,  poller_output.time, =20
> > poller_output.local_data_id, poller_item.rrd_path, =20
> > poller_item.rrd_name,  poller_item.rrd_num  from
> > (poller_output,poller_item)  where
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000"
> >
> > So the 2 SQL repeat time and time again:
> > select poller_id,end_time from poller_time where poller_id=20
> =3D 0 select =20
> > poller_output.output,  poller_output.time,=20
> > poller_output.local_data_id,  poller_item.rrd_path, =20
> > poller_item.rrd_name, poller_item.rrd_num  from=20
> > (poller_output,poller_item)  where=20
> > (poller_output.local_data_id=3Dpoller_item.local_data_id and
> > poller_output.rrd_name=3Dpoller_item.rrd_name)  LIMIT 10000
> >
> > I don't know what's wrong, I check the tables and found=20
> that they are=20
> > empty(poller_time, poller_output)!
> >
> > apache-2.2.3
> > php-4.4.4
> > mysql-4.1.22
> > net-snmp-5.1.2
> > rrdtool-1.2.23
> > cacti-0.8.6j
> >
> > Thanks.
> >
> --------------------------------------------------------------
> -----------
> This SF.net email is sponsored by DB2 Express Download DB2=20
> Express C - the FREE version of DB2 express and take control=20
> of your XML. No limits. Just data. Click to get it now.
> http://sourceforge.net/powerbar/db2/
> _______________________________________________
> cacti-user mailing list
> cac...@li...
> https://lists.sourceforge.net/lists/listinfo/cacti-user
>=20
>=20