**[SOLVED***]MySQL issues? graphs quit updating after reboot

[bdbrumm]

Problem: Previously working system (months) quit updating graphs after a reboot.
Symptoms: NaN displayed in current graphs, but historical data (prior to reboot date) is still there.

I worked through the NaN troubleshooting Howto: http://forums.cacti.net/viewtopic.php?t=15136

Very similar to this thread, http://forums.cacti.net/viewtopic.php?t=16210, I found NO INSTANCE
of "rrdtool update" in the DEBUG-level log file. Also per step 3, when I check the poller under "Cacti System Utilities",
neither the view/clear poller/snmp cache entries return anything except a blank screen.

I also noticed that when viewing the devices list, selecting a device generates a red "SNMP error"
at the top of the screen where the basic SNMP host info is normally returned. HOWEVER, I've confirmed
that the snmp utils are working correctly, the devices respond correctly to snmpgets/walks, etc for various OIDs.

I can find no instance of bad paths, permissions, etc.

I'd be willing to do some database checks if someone can walk me through it. The mysql process seems to be
using quite a bit of cpu compared to other cacti boxes in production.

Any valid suggestions?

[gandalf]

Please check table poller_output and verify, that php.ini's memory parm is set to 64 MB instead of 8MB default.
Reinhard

[bdbrumm]

Ok, I cheked the items and changed the limits below to 64MB.

memory_limit = 64M ; Maximum amount of memory a script may consume (8MB)
post_max_size = 64M ; post_max_size = 8M

No change in behavior. Mysql just keeps on running. Currently it's using about 80+% CPU and hasn't stopped since it got called via the poller.

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
2774 mysql 16 0 143m 11m 844 S 10.6 2.4 5:45.32 mysqld

To compare, another production box which has been up for over just a year has used the following:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1875 mysql 15 0 117m 18m 6668 S 0.0 3.7 10:04.55 mysqld


There's quite a bit of data in the poller_output table. I verified this using the "mysqldump cacti poller_output" command.

Clearly there's a mysql issue here...
Are there any mysql commands I should run to check/repair the database?

[gandalf]

You may run a repair on all tables. And if it is huge, truncate table poller_output
Reinhard

[bdbrumm]

I ran mysqlcheck on the database and everything came back OK.
It took a very long time to run the poller_output table. How do I
truncate the table? I didn't find a specific command for this.

Also, at this point I'd be OK with losing all of the historical data, but preserving the devices and graph tree structures. So if someone tells me how I can kill all the old data and start fresh while maintaining my current device list and graph trees, that would be fine also.

-rw-rw---- 1 mysql mysql 452123688 Oct 2 11:38 poller_output.MYD
-rw-rw---- 1 mysql mysql 225424384 Oct 2 11:38 poller_output.MYI

# mysqlcheck cacti
cacti.cdef OK
cacti.cdef_items OK
cacti.colors OK
cacti.data_input OK
cacti.data_input_data OK
cacti.data_input_fields OK
cacti.data_local OK
cacti.data_template OK
cacti.data_template_data OK
cacti.data_template_data_rra OK
cacti.data_template_rrd OK
cacti.graph_local OK
cacti.graph_template_input OK
cacti.graph_template_input_defs OK
cacti.graph_templates OK
cacti.graph_templates_gprint OK
cacti.graph_templates_graph OK
cacti.graph_templates_item OK
cacti.graph_tree OK
cacti.graph_tree_items OK
cacti.host OK
cacti.host_graph OK
cacti.host_snmp_cache OK
cacti.host_snmp_query OK
cacti.host_template OK
cacti.host_template_graph OK
cacti.host_template_snmp_query OK
cacti.poller OK
cacti.poller_command OK
cacti.poller_item OK
cacti.poller_output OK
cacti.poller_reindex OK
cacti.poller_time OK
cacti.rra OK
cacti.rra_cf OK
cacti.settings OK
cacti.settings_graphs OK
cacti.settings_tree OK
cacti.snmp_query OK
cacti.snmp_query_graph OK
cacti.snmp_query_graph_rrd OK
cacti.snmp_query_graph_rrd_sv OK
cacti.snmp_query_graph_sv OK
cacti.user_auth OK
cacti.user_auth_perms OK
cacti.user_auth_realm OK
cacti.user_log OK
cacti.version OK

[bdbrumm]

OK, I searched a bit and found out how to TRUNCATE a table.
Basically, this drops the table and re-adds an empty one, which
is much faster than a delete operation.

-rw-rw---- 1 mysql mysql 452123688 Oct 2 11:38 poller_output.MYD
-rw-rw---- 1 mysql mysql 225424384 Oct 2 11:38 poller_output.MYI

mysql> TRUNCATE TABLE poller_output;
Query OK, 0 rows affected (0.00 sec)

-rw-rw---- 1 mysql mysql 0 Oct 2 14:30 poller_output.MYD
-rw-rw---- 1 mysql mysql 1024 Oct 2 14:30 poller_output.MYI

Now I'm getting data written to the rrd's again and my graphs are starting to re-populate. Woot!

Can anyone PLEASE explain what would cause the poller_output table to grow arbitrarily large,
whereas normally it remains static. I presume its use is temporary as the data is written to the .rrd files.

(...goes to check logs...)

I did another DEBUG level poll cycle and guess what? At the very end of the log file, there is output that shows that CMD.PHP
truncates the table. For some reason, it wasn't happening.

10/02/2006 02:40:18 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "replace into settings (name,value) values ('stats_poller','Time:17.4931 Method:cactid Processes:1 Threads:1 Hosts:22 HostsPerProcess:22 DataSources:5946 RRDsProcessed:2983')"
10/02/2006 02:40:18 PM - CMDPHP: Poller[0] DEBUG: SQL Exec: "truncate table poller_output"
10/02/2006 02:40:18 PM - CMDPHP: Poller[0] DEBUG: SQL Cell: "select count(*) from poller_command"

Perhaps the NaN HowTo should be extended to include this particular variant.

Thanks for the help and input!

NaN HowTo: http://forums.cacti.net/viewtopic.php?t=15136

[gandalf]

Can anyone PLEASE explain what would cause the poller_output table to grow arbitrarily large, whereas normally it remains static. I presume its use is temporary as the data is written to the .rrd files.

Please make sure to increase /etc/php.ini's memory size from 8MB to 64 MB. Else, php may not have enough memory to process the whole of the table, leaving more stuff from one polling circle to the next
Reinhard

[bdbrumm]

Ah, that makes sense and I did that as well (detailed previously).

>> Ok, I cheked the items and changed the limits below to 64MB.
>> memory_limit = 64M ; Maximum amount of memory a script may consume (8MB)
>> post_max_size = 64M ; post_max_size = 8M

There is another thread detailed in this issue which had the
same problem. Likely root cause is the memory_limit setting.
Please add this situation to your excellent NaN HowTo.

[gandalf]

Yep, good point. Will be there in a minute
Reinhard