On looking at an issue that some of our mysql stats graphs have stopped graphing, I have noted:
The Data Input Method for the data sources in question is set to use 'scripts/ss_get_mysql_stats_55.php' - but whilst attempting to debug, calling spine manually it seems to be calling 'scripts/ss_get_mysql_stats.php'...
Relevant row from the db:
| 98 | 80e998630b4ceb3ca92acd31450a6c94 | 5.5 Get MySQL Stats/InnoDB Buffer Pool Activity IM | <path_php_binary> -q <path_cacti>/scripts/ss_get_mysql_stats_55.php --host <hostname> --items ae,af,ag --user <username> --pass <password> --port <port> | 1 |
And an example of said check not using the correct command - note that this should have been the above script:
01/21/2013 03:59:09 PM - SPINE: Poller[0] Host[748] DS[11254] SCRIPT: /usr/bin/php -q /local/cacti/scripts/ss_get_mysql_stats.php 10.101.42.37 , output: Key_read_requests:42121677 Key_reads:40617 Key_write_requests:8611514 Key_writes:6132025 history_list:13 innodb_transactions:250610204 read_views:1 current_transactions:3 locked_transactions:0 active_transactions:0 pool_size:2555904 free_pages:702910 database_pages:1850099 modified_pages:0 pages_read:675304 pages_created:1175098 pages_written:37540005 file_fsyncs:13488857 file_reads:639204 file_writes:31595666 log_writes:5533830 pending_aio_log_ios:0 pending_aio_sync_ios:0 pending_buf_pool_flushes:0 pending_chkp_writes:0 pending_ibuf_aio_reads:0 pending_log_flushes:0 pending_log_writes:0 pending_normal_aio_reads:0 pending_normal_aio_writes:0 ibuf_inserts:1469 ibuf_merged:1469 ibuf_merges:498 spin_waits:1776204 spin_rounds:19595772 os_waits:1013478 rows_inserted:22678664 rows_updated:14398301 rows_deleted:15931318 rows_read:522366091 Table_locks_waited:1 Table_locks_immediate:62856081 Slow_queries:3 Open_files:67 Open_tables:92 Opened_tables:143 innodb_open_files:2000 open_files_limit:15010 table_cache:6000 Aborted_clients:3545229 Aborted_connects:5 Max_used_connections:6 Slow_launch_threads:0 Threads_cached:1 Threads_connected:5 Threads_created:6 Threads_running:4 max_connections:3000 thread_cache_size:60 Connections:4596863 slave_running:0 slave_stopped:0 Slave_retried_transactions:0 slave_lag:0 Slave_open_temp_tables:0 Qcache_free_blocks:1 Qcache_free_memory:268402576 Qcache_hits:3 Qcache_inserts:118 Qcache_lowmem_prunes:0 Qcache_not_cached:9298696 Qcache_queries_in_cache:7 Qcache_total_blocks:21 query_cache_size:268435456 Questions:19450467 Com_update:27653257 Com_insert:24871935 Com_select:5538948 Com_delete:5891315 Com_replace:305 Com_load:0 Com_update_multi:0 Com_insert_select:0 Com_delete_multi:976 Com_replace_select:0 Select_full_join:0 Select_full_range_join:0 Select_range:268 Select_range_check:0 Select_scan:5970322 Sort_merge_passes:0 Sort_range:0 Sort_rows:43436 Sort_scan:84 Created_tmp_tables:3759958 Created_tmp_disk_tables:39 Created_tmp_files:6 Bytes_sent:1197723486670 Bytes_received:446112292062 innodb_log_buffer_size:8388608 unflushed_log:8388608 log_bytes_flushed:354545171771 log_bytes_written:354545171771 relay_log_space:1825558713 binlog_cache_size:32768 Binlog_cache_disk_use:4396731 Binlog_cache_use:32442122 binary_log_space:0
And an example of one of many SQL inserts to poller_output using the 'scripts/ss_get_mysql_stats.php' script being truncated:
3:59:13 PM - SPINE: Poller[0] DEBUG: SQL:'INSERT INTO poller_output (local_data_id, rrd_name, time, output) VALUES (11269,'','2013-01-21 15:59:09','Key_read_requests:42121677 Key_reads:40617 Key_write_requests:8611514 Key_writes:6132025 history_list:13 innodb_transactions:250610204 read_views:1 current_transactions:3 locked_transactions:0 active_transactions:0 pool_size:2555904 free_pages:702910 database_pages:1850099 modified_pages:0 pages_read:675304 pages_created:1175098 pages_written:37540005 file_fsyncs:13488857 file_reads:639204 file_writes:31595666 log_writes:5533830 pending_aio_log_ios:0 pending_aio_sync_ios:0 pending_buf_pool_flushes:0 pending_chkp_writes:0 pending_ibuf_aio_reads:0 pending_log_flushes:0 pending_log_writes:0 pending_normal_aio_reads:0 pending_normal_aio_writes:0 ibuf_inserts:1469 ibuf_merged:1469 ibuf_merges:498 spin_waits:1776204 spin_rounds:19595772 os_waits:1013478 rows_inserted:22678664 rows_updated:14398301 rows_deleted:15931318 rows_read:522366091 Table_locks_waited:1 Table_locks_immediate:62856081 Slow_queries:3 Open_files:67 Open_tables:92 Opened_tables:143 innodb_open_files:2000 open_files_limit:15010 table_cache:6000 Aborted_clients:3545229 Aborted_connects:5 Max_used_connections:6 Slow_launch_threads:0 Threads_cached:1 Threads_connected:5 Threads_created:6 Threads_running:4 max_connections:3000 thread_cache_size:60 Connections:4596863 slave_running:0 slave_stopped:0 Slave_retried_transactions:0 slave_lag:0 Slave_open_temp_tables:0 Qcache_free_blocks:1 Qcache_free_memory:268402576 Qcache_hits:3 Qcache_inserts:118 Qcache_lowmem_prunes:0 Qcache_not_cached:9298696 Qcache_queries_in_cache:7 Qcache_total_blocks:21 query_cache_size:268435456 Questions:19450467 Com_update:27653257 Com_insert:24871935 Com_select:5538948 Com_delete:5891315 Com_replace:305 Com_load:0 Com_update_multi:0 Com_insert_select:0 Com_delete_multi:976 Com_replace_select:0 Select_full_join:0 Select_full_range_join:0 Select_range:268 Select_range_check:0 Select_scan:5970322 Sort_merge_passes:0 Sort_range:0 Sort_rows:43436 Sort_scan:84 Created_tmp_tables:3759958 Created_tmp_disk_tables:39 Created_tmp_files:6 Bytes_sent:1197723486670 Bytes_received:446112292062 innodb_log_buffer_size:8388608 unflushed_log:8388608 log_bytes_flushed:354545171771 log_bytes_written:354545171771 relay_log_space:1825558713 binlog_cache_size:32768 Binlog_cache_disk_use:4396731 Binlog_cache_use:32442122 binary_log_space:0'),(11270,'','2013-01-21 15:59:09','Key_read_requests:42121677 Key_reads:40617 Key_write_requests:8611514 Key_writes:6132025 history_list:13 innodb_transactions:250610204 read_views:1 current_transactions:3 locked_transactions:0 active_transactions:0 pool_size:2555904 free_pages:702910 database_pages:1850099 modified_pages:0 pages_read:675304 pages_created:1175098 pages_written:37540005 file_fsyncs:13488857 file_reads:639204 file_writes:31595666 log_writes:5533830 pending_aio_log_ios:0 pending_aio_sync_ios:0 pending_buf_pool_flushes:0 pending_chkp_writes:0 pending_ibuf_aio_reads:0 pending_log_flushes:0 pending_log_writes:0 pending_normal_aio_reads:0 pending_normal_aio_writes:0 ibuf_inserts:1469 ibuf_merged:1469 ibuf_merges:498 spin_waits:1776204 spin_rounds:19595772 os_waits:1013478 rows_inserted:22678664 rows_updated:14398301 rows_deleted:15931318 rows_read:522366091 Table_locks_waited:1 Table_locks_immediate:62856081 Slow_queries:3 Open_files:67 Open_tables:92 Opened_tables:143 innodb_open_files:2000 open_files_limit:15010 table_cache:6000 Aborted_clients:3545229 Aborted_connects:5 Max_used_connections:6 Slow_launch_threads:0 Threads_cached:1 Threads_connected:5 Threads_created:6 Threads_running:4 max_connections:3000 thread_cache_size:60 Connections:4596863 slave_running:0 slave_stopped:0 Slave_retried_transactions:0 slave_lag:0 Slave_open_temp_tables:0 Qcache_free_blocks:1 Qcache_free_memory:268402576 Qcache_hits:3 Qcache_inserts:118 Qcache_lowmem_prunes:0 Qcache_not_cached:9298696 Qcache_queries_in_cache:7 Qcache_total_blocks:21 query_cache_size:268435456 Questions:19450467 Com_update:27653257 Com_insert:24871935 Com_select:5538948 Com_delete:5891315 Com_re01/21/2013 03:59:13 PM - SPINE: Poller[0] Host[748] DEBUG: HOST COMPLETE: About to Exit Host Polling Thread Function
Tried rebuilding the poller cache using the cli to no avail.
As an aside, the insert into poller_output also seems to fail for these items, effectively being truncated before finishing it's insert statement - meaning obviously that the graphs are receiving no data.
So, I guess this is a twofold question: 1) Has anyone seen an issue where even though the data input method seems to be defined correctly, that it ends up calling the wrong script, and 2) is there some sort of maximum buffer size that is being hit when returning items from 'scripts/ss_get_mysql_stats.php' that then breaks putting data into poller_output?
This is admittedly an old version of cacti-spine, version 0.8.7e according to the cacti page - however dpkg seems to indicate that this is actually a different revision...
# dpkg -l | grep spine
ii cacti-spine 0.8.7a-3.3 Multi-Threading poller for cacti
If these items do have bugs open for them that have been patched it would be appreciated to know at which revision items are fixed, as the company will need this so I can put a decent case forward for upgrading/patching. I'm in the process of building a replacement for this server with the latest revision of cacti-spine on it (from wheezy's repository), which i'm going to have to convert the rrd's from 32 to 64 bit - but obviously the business is going to want to know that issues we're currently facing are addressed in the latest and greatest
Unfortunately further details about the current installation are thin on the ground - done before my time, i'm just the guy that's been given the short straw of fixing it.
