Cacti (home)ForumsRepositoryDocumentation
Cacti: offical forums and support
It is currently Wed Aug 27, 2014 5:59 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 15 posts ] 
Author Message
 Post subject: Xaprb MySQL Templates
PostPosted: Wed Apr 30, 2008 11:08 am 
Offline

Joined: Thu Oct 12, 2006 10:47 am
Posts: 37
I installed the MySQL templates from Xaprb (http://www.xaprb.com/blog/2008/04/27/im ... for-mysql/)

Running on CentOS 5.1
cacti-0.8.7b-2.el5.rf
cacti-spine-0.8.7a-1.el5.rf
mysql-server-5.0.22-2.2.el5_1.1
Using r34 of your the script.

Are the results that spine can return for any specific query limited?

I am seeing the following issue. It appears that the results are being truncated by spine. I can successfully insert the data into the poller_output table, and the script returns the full results when run from the command line.
04/30/2008 10:55:13 AM - SPINE: Poller[0] ERROR: A database insert failed! Error:'1064', SQL Fragment:'INSERT INTO poller_output (local_data_id, rrd_name, time, output) VALUES (2715,'','2008-04-30 10:55:08','Key_read_requests:878717422 Key_reads:40364 Key_write_requests:165797506 Key_writes:32278946 history_list:176 innodb_transactions:1354426669 read_views:1 current_transactions:6 locked_transactions:0 active_transactions:0 pool_size:32768 free_pages:112 database_pages:32613 modified_pages:75 pages_read:57523 pages_created:3233 pages_written:6130784 file_fsyncs:21813020 file_reads:17496 file_writes:24409787 log_writes:20490268 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:0 ibuf_merged:0 ibuf_merges:0 spin_waits:13074779 spin_rounds:18126037 os_waits:274239 rows_inserted:65434 rows_updated:24044417 rows_deleted:3798 rows_read:3238136757 Table_locks_waited:4399105 Table_locks_immediate:157138210 Slow_queries:3 Open_files:180 Ope'
04/30/2008 10:55:08 AM - SPINE: Poller[0] ERROR: A database insert failed! Error:'1064', SQL Fragment:'INSERT INTO poller_output (local_data_id, rrd_name, time, output) VALUES (2746,'','2008-04-30 10:55:04','Key_read_requests:1182906 Key_reads:4146 Key_write_requests:398090 Key_writes:4207 history_list:0 innodb_transactions:1280 read_views:1 current_transactions:1 locked_transactions:0 active_transactions:0 pool_size:512 free_pages:493 database_pages:19 modified_pages:0 pages_read:19 pages_created:0 pages_written:0 file_fsyncs:3 file_reads:25 file_writes:3 log_writes:8 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:0 ibuf_merged:0 ibuf_merges:0 spin_waits:8 spin_rounds:21 os_waits:4 rows_inserted:0 rows_updated:0 rows_deleted:0 rows_read:0 Table_locks_waited:0 Table_locks_immediate:155 Slow_queries:1 Open_files:30 Open_tables:12 Opened_tables:67 innodb_open_files:300 open_files_limit:1024 table_cache:64 Aborted_clients:0 Aborted_'

Successful run from command line:
# php -q /var/www/cacti/scripts/ss_get_mysql_stats.php xxxx
PHP Notice: Undefined variable: config in /var/www/cacti/lib/snmp.php on
line 33
Key_read_requests:1183562 Key_reads:4176 Key_write_requests:398172
Key_writes:4241 history_list:0 innodb_transactions:1280 read_views:1
current_transactions:1 locked_transactions:0 active_transactions:0
pool_size:512 free_pages:493 database_pages:19 modified_pages:0
pages_read:19 pages_created:0 pages_written:0 file_fsyncs:3 file_reads:25
file_writes:3 log_writes:8 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:0 ibuf_merged:0 ibuf_merges:0
spin_waits:7 spin_rounds:20 os_waits:4 rows_inserted:0 rows_updated:0
rows_deleted:0 rows_read:0 Table_locks_waited:0 Table_locks_immediate:200
Slow_queries:1 Open_files:30 Open_tables:12 Opened_tables:72
innodb_open_files:300 open_files_limit:1024 table_cache:64
Aborted_clients:0 Aborted_connects:2 Max_used_connections:3
Slow_launch_threads:0 Threads_cached:0 Threads_connected:1
Threads_created:218 Threads_running:1 max_connections:100
thread_cache_size:0 Connections:240 slave_running:10 slave_stopped:0
Slave_retried_transactions:0 seconds_behind_master:0 slave_lag:0
Slave_open_temp_tables:0 Qcache_free_blocks:0 Qcache_free_memory:0
Qcache_hits:0 Qcache_inserts:0 Qcache_lowmem_prunes:0 Qcache_not_cached:0
Qcache_queries_in_cache:0 Qcache_total_blocks:0 query_cache_size:0
Questions:1069 Com_update:57 Com_insert:112 Com_select:437 Com_delete:0
Com_replace:0 Com_load:0 Com_update_multi:0 Com_insert_select:0
Com_delete_multi:0 Com_replace_select:0 Select_full_join:0
Select_full_range_join:0 Select_range:0 Select_range_check:0 Select_scan:19
Sort_merge_passes:0 Sort_range:0 Sort_rows:0 Sort_scan:0
Created_tmp_tables:19 Created_tmp_disk_tables:0 Created_tmp_files:5
Bytes_sent:254244 Bytes_received:110414895 innodb_log_buffer_size:1048576
unflushed_log:1048576 log_bytes_flushed:43655 log_bytes_written:43655
relay_log_space:47296047 binlog_cache_size:32768 Binlog_cache_disk_use:0
Binlog_cache_use:0 binary_log_space:35894532


I also posted a bugreport to the template creator - but am curious if the issue is a max result size for spine - 1024bytes or similar...


Top
 Profile  
 
 Post subject: Same issue
PostPosted: Fri May 02, 2008 3:12 pm 
Offline

Joined: Fri May 02, 2008 3:04 pm
Posts: 6
I'm seeing the same issue -
SPINE 0.8.7b,
CACTI Version 0.8.7b
Mysql 5.0.27
Fedora Core 5, 2.6.18-1.2200

The poller returns full data when run manually, but the log reports a failed insert as in the previous message.
05/02/2008 01:08:08 PM - SPINE: Poller[0] ERROR: A database insert failed! Error:'1064', SQL Fragment: 'INSERT INTO poller_output ---SNIP --- Table_locks_waited:22408743 Table_locks_immediate:8'


Top
 Profile  
 
 Post subject: Patch
PostPosted: Fri May 02, 2008 3:53 pm 
Offline

Joined: Fri May 02, 2008 3:04 pm
Posts: 6
Testing a fix now.

in cactid.h, change
#define BUFSIZE 1024
to
#define BUFSIZE 32768
or something usefully large. n.b. the related mysql field is limited to 65K characters.


Top
 Profile  
 
 Post subject: Update
PostPosted: Fri May 02, 2008 5:05 pm 
Offline

Joined: Fri May 02, 2008 3:04 pm
Posts: 6
That was a dismal failure. Once I actually grabbed what I think is the right code, I flipped from BUFSIZE to BIG_BUFSIZE and recompiled. The log started filling with:
05/02/2008 02:48:01 PM - SPINE: Poller[0] Host[4] DS[62] WARNING: Result from SCRIPT not valid. Partial Result: file_reads:279638068...
05/02/2008 02:48:01 PM - SPINE: Poller[0] Host[2] DS[12] WARNING: Result from SCRIPT not valid. Partial Result: file_reads:43373942 ...
05/02/2008 02:48:02 PM - SPINE: Poller[0] Host[3] DS[37] WARNING: Result from SCRIPT not valid. Partial Result: file_reads:16503514 ...

I'm backing away and leaving it to someone who knows what they're doing..

Regardless, the default buffer is only 1024, which matches the SQL Fragment length in the log. What's the correct way to resolve this?


Top
 Profile  
 
 Post subject: hack
PostPosted: Thu May 08, 2008 8:40 am 
Offline

Joined: Thu May 08, 2008 8:02 am
Posts: 1
cacti-spine 0.8.7a

--- cacti-spine-0.8.7a.orig/poller.c 2007-11-25 21:40:21.000000000 -0600
+++ cacti-spine-0.8.7a/poller.c 2008-05-08 08:22:32.000000000 -0500
@@ -94,7 +94,7 @@
char query5[BUFSIZE];
char query6[BUFSIZE];
char query7[BUFSIZE];
- char query8[BUFSIZE];
+ char query8[BIG_BUFSIZE];
char query9[BUFSIZE];
char query10[BUFSIZE];
char errstr[BUFSIZE];
@@ -217,7 +217,7 @@
set.poller_interval, host_id);

/* query to add output records to the poller output table */
- snprintf(query8, BUFSIZE,
+ snprintf(query8, BIG_BUFSIZE,
"INSERT INTO poller_output"
" (local_data_id, rrd_name, time, output) VALUES");

@@ -943,7 +943,7 @@

i = 0;
while (i < rows_processed) {
- snprintf(result_string, BUFSIZE, " (%i,'%s','%s','%s')",
+ snprintf(result_string, BIG_BUFSIZE, " (%i,'%s','%s','%s')",
poller_items[i].local_data_id,
poller_items[i].rrd_name,
host_time,

--- cacti-spine-0.8.7a.orig/sql.c 2007-11-25 21:40:21.000000000 -0600
+++ cacti-spine-0.8.7a/sql.c 2008-05-08 08:15:24.000000000 -0500
@@ -48,10 +48,10 @@
int db_insert(MYSQL *mysql, const char *query) {
int error;
int error_count = 0;
- char query_frag[BUFSIZE];
+ char query_frag[BIG_BUFSIZE];

/* save a fragment just in case */
- snprintf(query_frag, BUFSIZE, "%s", query);
+ snprintf(query_frag, BIG_BUFSIZE, "%s", query);

/* show the sql query */
if (set.log_level == 5) {


It's a hack (I dont know C :), but working (except DEBUG) for me.


Top
 Profile  
 
 Post subject: Fail.
PostPosted: Fri May 09, 2008 1:12 am 
Offline

Joined: Fri May 02, 2008 3:04 pm
Posts: 6
ex, thanks for the try!

I'm running into the same trouble I had when I tried to modify it myself. Lots of things like:

05/08/2008 10:55:08 PM - SPINE: Poller[0] Host[47] DS[2290] WARNING: Result from SCRIPT not valid. Partial Result: file_reads:15457 fil...
05/08/2008 10:55:08 PM - SPINE: Poller[0] Host[52] DS[2383] WARNING: Result from SCRIPT not valid. Partial Result: Key_read_requests:44...
05/08/2008 11:08:09 PM - SPINE: Poller[0] Host[40] DS[2006] WARNING: Result from SNMP not valid. Partial Result: ...

Larry, your bribe will be in the mail as soon as I get your snail mail address :)


Top
 Profile  
 
 Post subject: Re: hack
PostPosted: Sun May 11, 2008 2:50 pm 
Offline

Joined: Wed Nov 01, 2006 4:16 pm
Posts: 4
ex wrote:
It's a hack (I dont know C :), but working (except DEBUG) for me.


I just hacked a solution too, very similar to yours. Your version had fewer modification so I tried it too, but it didn't work for me. So I reverted back to this:

Code:
--- sql.c-dist   2008-05-12 04:59:59.000000000 +1000
+++ sql.c   2008-05-12 05:25:52.000000000 +1000
@@ -48,10 +48,10 @@
int db_insert(MYSQL *mysql, const char *query) {
   int    error;
   int    error_count = 0;
-   char   query_frag[BUFSIZE];
+   char   query_frag[BIG_BUFSIZE];

   /* save a fragment just in case */
-   snprintf(query_frag, BUFSIZE, "%s", query);
+   snprintf(query_frag, BIG_BUFSIZE, "%s", query);

   /* show the sql query */
   if (set.log_level == 5) {
@@ -102,10 +102,10 @@
   int    error       = 0;
   int    error_count = 0;

-   char   query_frag[BUFSIZE];
+   char   query_frag[BIG_BUFSIZE];

   /* save a fragment just in case */
-   snprintf(query_frag, BUFSIZE, "%s", query);
+   snprintf(query_frag, BIG_BUFSIZE, "%s", query);

   /* show the sql query */
   SPINE_LOG_DEBUG(("DEBUG: SQL:'%s'", query_frag));


Code:
--- poller.c-dist   2008-05-12 05:11:11.000000000 +1000
+++ poller.c   2008-05-12 05:36:16.000000000 +1000
@@ -94,7 +94,7 @@
   char query5[BUFSIZE];
   char query6[BUFSIZE];
   char query7[BUFSIZE];
-   char query8[BUFSIZE];
+   char query8[BIG_BUFSIZE];
   char query9[BUFSIZE];
   char query10[BUFSIZE];
   char errstr[BUFSIZE];
@@ -217,7 +217,7 @@
         set.poller_interval, host_id);

   /* query to add output records to the poller output table */
-   snprintf(query8, BUFSIZE,
+   snprintf(query8, BIG_BUFSIZE,
      "INSERT INTO poller_output"
      " (local_data_id, rrd_name, time, output) VALUES");

@@ -498,7 +498,7 @@
                     poll_result = strdup("U");
                  }

-                  if (!(query3 = (char *)malloc(BUFSIZE))) {
+                  if (!(query3 = (char *)malloc(BIG_BUFSIZE))) {
                     die("ERROR: Fatal malloc error: poller.c reindex insert!");
                  }
                  query3[0] = '\0';
@@ -509,21 +509,21 @@
                  }else if ((!strcmp(reindex->op, "=")) && (strcmp(reindex->assert_value,poll_result))) {
                     SPINE_LOG_HIGH(("Host[%i] ASSERT: '%s' .eq. '%s' failed. Recaching host '%s', data query #%i\n", host->id, reindex->assert_value, poll_result, host->hostname, reindex->data_query_id));

-                     snprintf(query3, BUFSIZE, "REPLACE INTO poller_command (poller_id, time, action,command) values (0, NOW(), %i, '%i:%i')", POLLER_COMMAND_REINDEX, host->id, reindex->data_query_id);
+                     snprintf(query3, BIG_BUFSIZE, "REPLACE INTO poller_command (poller_id, time, action,command) values (0, NOW(), %i, '%i:%i')", POLLER_COMMAND_REINDEX, host->id, reindex->data_query_id);
                     db_insert(&mysql, query3);
                     assert_fail = TRUE;
                     previous_assert_failure = TRUE;
                  }else if ((!strcmp(reindex->op, ">")) && (strtoll(reindex->assert_value, (char **)NULL, 10) < strtoll(poll_result, (char **)NULL, 10))) {
                     SPINE_LOG_HIGH(("Host[%i] ASSERT: '%s' .gt. '%s' failed. Recaching host '%s', data query #%i\n", host->id, reindex->assert_value, poll_result, host->hostname, reindex->data_query_id));

-                     snprintf(query3, BUFSIZE, "REPLACE INTO poller_command (poller_id, time, action, command) values (0, NOW(), %i, '%i:%i')", POLLER_COMMAND_REINDEX, host->id, reindex->data_query_id);
+                     snprintf(query3, BIG_BUFSIZE, "REPLACE INTO poller_command (poller_id, time, action, command) values (0, NOW(), %i, '%i:%i')", POLLER_COMMAND_REINDEX, host->id, reindex->data_query_id);
                     db_insert(&mysql, query3);
                     assert_fail = TRUE;
                     previous_assert_failure = TRUE;
                  }else if ((!strcmp(reindex->op, "<")) && (strtoll(reindex->assert_value, (char **)NULL, 10) > strtoll(poll_result, (char **)NULL, 10))) {
                     SPINE_LOG_HIGH(("Host[%i] ASSERT: '%s' .lt. '%s' failed. Recaching host '%s', data query #%i\n", host->id, reindex->assert_value, poll_result, host->hostname, reindex->data_query_id));

-                     snprintf(query3, BUFSIZE, "REPLACE INTO poller_command (poller_id, time, action, command) values (0, NOW(), %i, '%i:%i')", POLLER_COMMAND_REINDEX, host->id, reindex->data_query_id);
+                     snprintf(query3, BIG_BUFSIZE, "REPLACE INTO poller_command (poller_id, time, action, command) values (0, NOW(), %i, '%i:%i')", POLLER_COMMAND_REINDEX, host->id, reindex->data_query_id);
                     db_insert(&mysql, query3);
                     assert_fail = TRUE;
                     previous_assert_failure = TRUE;
@@ -534,7 +534,7 @@
                   * 2) the OP code is > or < meaning the current value could have changed without causing
                   *     the assert to fail */
                  if ((assert_fail) || (!strcmp(reindex->op, ">")) || (!strcmp(reindex->op, "<"))) {
-                     snprintf(query3, BUFSIZE, "UPDATE poller_reindex SET assert_value='%s' WHERE host_id='%i' AND data_query_id='%i' and arg1='%s'", poll_result, host_id, reindex->data_query_id, reindex->arg1);
+                     snprintf(query3, BIG_BUFSIZE, "UPDATE poller_reindex SET assert_value='%s' WHERE host_id='%i' AND data_query_id='%i' and arg1='%s'", poll_result, host_id, reindex->data_query_id, reindex->arg1);
                     db_insert(&mysql, query3);

                     if ((assert_fail) &&
@@ -933,7 +933,7 @@
      }

      /* insert the query results into the database */
-      if (!(query3 = (char *)malloc(MAX_MYSQL_BUF_SIZE+BUFSIZE))) {
+      if (!(query3 = (char *)malloc(MAX_MYSQL_BUF_SIZE+BIG_BUFSIZE))) {
         die("ERROR: Fatal malloc error: poller.c query3 oids!");
      }
      query3[0] = '\0';
@@ -943,7 +943,7 @@

      i = 0;
      while (i < rows_processed) {
-         snprintf(result_string, BUFSIZE, " (%i,'%s','%s','%s')",
+         snprintf(result_string, BIG_BUFSIZE, " (%i,'%s','%s','%s')",
            poller_items[i].local_data_id,
            poller_items[i].rrd_name,
            host_time,



Would be great if the developer could fix this fro the next release. I'm not sure if posting here will get a patch moved upstream; I didn't see an obvious way to submit patches to developers.


Top
 Profile  
 
 Post subject: didn't work for me
PostPosted: Thu May 15, 2008 3:24 am 
Offline

Joined: Wed Apr 16, 2008 5:19 am
Posts: 8
Hi

I have problems with Xaprb template too. But different. I explained here, I have no clues to repair yet.

So are there any similar template to monitoring MySQL? which one that it is used by majority of cacti community?

Thanks.


Top
 Profile  
 
 Post subject: Vetting of spine changes
PostPosted: Tue Jun 03, 2008 5:41 pm 
Offline

Joined: Fri May 02, 2008 3:04 pm
Posts: 6
Have any of the developers (TheWitness?) had a chance to look over any of the proposed spine code changes with an eye toward resolving the truncated output issue?


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 05, 2008 4:59 am 
Offline

Joined: Mon Jun 02, 2008 3:47 am
Posts: 1
I had to make a few more changes to make the patch work for me - attached is a svn diff against the tagged 0.8.7a version.


Attachments:
File comment: SVN diff
cacti-spine.patch [10.53 KiB]
Downloaded 293 times
Top
 Profile  
 
 Post subject: Updating to 0.8.7x
PostPosted: Wed Jun 18, 2008 12:12 am 
Offline

Joined: Fri May 02, 2008 3:04 pm
Posts: 6
xaprb, etc al, what does it take to update these templates to be compatible with 0.8.7b (in my case)? I'm definitely not one with the internals of cacti and templating. The most I've done is deconstruct the db schema in order to bulk add several thousand existing RRDs from another system...

I've been running the xaprb templates for a month or so on a test 0.8.6x environment, and I'd love to pull it forward now that the spine kinks have been worked out. Halp?


Top
 Profile  
 
 Post subject: Template improvements
PostPosted: Wed Jun 18, 2008 9:50 am 
Offline

Joined: Thu Mar 20, 2008 12:30 pm
Posts: 22
BTW I think the SVN version of the templates should alleviate the problems of truncation, too. I shortened all the value names to 2 letters to save bytes, and that helped a lot.


Top
 Profile  
 
 Post subject: Re: Updating to 0.8.7x
PostPosted: Wed Jun 18, 2008 9:51 am 
Offline

Joined: Thu Mar 20, 2008 12:30 pm
Posts: 22
gentlemoose wrote:
xaprb, etc al, what does it take to update these templates to be compatible with 0.8.7b (in my case)?


They work fine as-is for me on a fresh CentOS install with Cacti 0.8.7b right out of rpmforge.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jul 24, 2008 6:27 am 
Offline
Cacti User

Joined: Tue Jan 09, 2007 8:28 am
Posts: 205
Location: London, UK
Hi

I'm having some problems with this script... I can run the script manually as root and get correct output. However, I can't run the script when logged in as the cacti user and cacti itself can't run the script either... Both generate the error Can't connect to MySQL

Any ideas?


Top
 Profile  
 
 Post subject: Re: Patch
PostPosted: Wed Aug 06, 2008 2:40 pm 
Offline

Joined: Wed Aug 06, 2008 2:37 pm
Posts: 1
gentlemoose wrote:
Testing a fix now.

in cactid.h, change
#define BUFSIZE 1024
to
#define BUFSIZE 32768
or something usefully large. n.b. the related mysql field is limited to 65K characters.


I changed BUFSIZE in spine.h that seems worked for me. At least this error gone.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 15 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: Google Adsense [Bot], Yahoo [Bot] and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Protected by Anti-Spam ACP Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group