[collectd] New plugin for collectd : write_mysql

Fri May 4 09:07:50 CEST 2012

  

Le 04.05.2012 08:52, Cyril Feraudet a écrit : 

> Le 03.05.2012
20:05, Daniel Hilst a écrit : 
> 
>> On 05/03/2012 03:46 PM, Daniel
Hilst wrote:
>> 
>>> On 02/14/2012 09:54 AM, Cyril Feraudet wrote: 
>>>

>>>> Hi all, Here the write_mysql output plugin patch (from 5.0.2) with
modifications suggested by Octo. By the way, notification are handled,
all memory used is freed on shutdown (thx Valgrind). Regards, Cyril Le
04.01.2012 10:49, Cyril Feraudet a écrit : 
>>>> 
>>>>> Hi Octo, Hi all,
Thanks for your time. It will permit to me to improve this plugin and my
knowledge of C. Le 22.12.2011 01:06, Florian Forster aécrit : 
>>>>>

>>>>>> Hi Cyril, thank you very much for your patches! I'm very sorry,
but I can't give the plugin the thorough review it deserves right now.
But maybe you find the following comments helpful. On Tue, Dec 20, 2011
at 03:03:43PM +0200, Cyril Feraudet wrote: 
>>>>>> 
>>>>>>> -
with_mysql_libs=`$with_mysql_config --libs 2>/dev/null` +
with_mysql_libs=`$with_mysql_config --libs_r 2>/dev/null`
>>>>>> This
looks like a bug fix. Would it make sense to add this change to the 4.10
branch? If so, would you be willing to send a separate patch for
this?
>>>>> You are right, I will submit a patch soon. write_mysql is
often called than mysql plugin and non thread safe library make collectd
crashing quickly. 
>>>>> 
>>>>>>> +CREATE TABLE `data` ( + `id`
bigint(20) NOT NULL auto_increment, + `timestamp` double NOT NULL, +
`host_id` int(11) NOT NULL, + `plugin_id` int(11) NOT NULL, +
`plugin_instance` varchar(255) default NULL, + `type_id` int(11) NOT
NULL, + `typeinstance` varchar(255) default NULL, + `dataset_id` int(11)
NOT NULL, + `value` double NOT NULL,
>>>>>> One of the reasons why I
didn't write a "write_dbi" or "write_mysql" plugin before is because I
was uncertain about the schema to use. Why did you chose to have a
separate table for host, plugin, plugin_instance, type, and
type_instance? I currently think that just having an "identifier" table
with five columns would be a better trade- off between normalization and
complexity.
>>>>> The idea was to save space in `data` table and improve
querying in it. For now, I've 800MB `data` table (MEMORY type) just for
the last value of each metrics (4207 servers / 462,395 metrics) using
REPLACE instead of INSERT statement. I think that SQL join are faster on
numeric id than varchar. But all of this things are questionable and
dependent of the final use. I think about a customizable SQL statement
in plugin configuration. diff --git a/src/write_mysql.c b/src/w
/blockquote> typedef struct host_s host_t; +struct host_s { + char
plugin_instance, type_id, vl->type_instance, dataset_id ); You really
should prepare this statement. Parsing this several thousand times per
second will be a major performance problem. +if (dso->type ==
DS_TYPE_GAUGE
>>>>> 
>>>>>> = NULL) { + rates = uc_get_rate (ds, vl); +
} + if (isnan(rates[i])) { continue; } + len = ssnprintf (query, sizeof
(query), tmpquery, rates[i]); +} +//INFO("toto: %d", toto);
+pthread_mutex_lock(&mutex); +DEBUG("write_mysql plugin: %s", query);
+mysql_real_query(conn, query, len); +pthread_mutex_unlock(&mutex); So
you're inserting each data source in a element? How can a client
distinguish between the different data sources? At least, you should add
a "ds_index" column or so. Thanks again and best There are dataset_id
and type_id in `dataset` for that. Here a query sample to get all
metric
>>>>> right order : select data.timestamp AS date, dataset.name
AS dataset_name, data.value AS value from data, host, plugin, type,
dataset where host.id = data.host_id and plugin.id = data.plugin_id and
type.id = data.type_id and data.dataset_id = dataset.id and host.name =
'eqds3pcold001' and plugin.name = 'interface' and data.plugin_instance =
'eth0' and type.name = 'if_octets' In my case, write_mysql 
>>>>>

>>>>>> to graph it. I've made a more flexible than Ganglia application
based on Collectd at work. My compagny allow me to share all my work on
collectd in GPL like licence. I will Release a beta of my unamed
application soon a possible. Regards, Cyril Feraudet
_______________________________________________ collectd mailing list
collectd at verplant.org [1] collectd at verplant.org>
http://mailman.verplant.org/listinfo/collectd [2]
>>>>
_______________________________________________ collectd mailing list
collectd at verplant.org [3] http://mailman.verplant.org/listinfo/collectd
[4]
>>> Hello, I'm trying to compile collectd 5.0.2 with write_mysql
pluing I've done this: http://pastebin.com/dPc6zgUH [5] I'm on rhel 5.5
x86_64 Any idea?
>> 
>> I have to run libtoolize --force before
compile
>> 
>> here is:
>> tar vxf collectd-5.0.2.tar.bz2
>> cd
collectd-5.0.2
>> patch -p1 -i
../0001-Adding-write_mysql-output-plugin.patch
>> aclocal
>> autoconf
>>
automake
>> libtoolize --force
>> ./configure
>> make
>> make install
all
>> 
>> Now I'm facing this error:
>> error] write_mysql plugin:
Failed to bind param to statement : Statement 
>> not prepared / INSERT
INTO data 
>>
(date,host_id,plugin_id,plugin_instance,type_id,type_instance,dataset_id,value)VALUES

>> (?,?,?,?,?,?,?,?)
>> 
>> What that means????
>> 
>> I tried Replace
"false" without success
>> 
>> Thanks in advance,
>> 
>> Hilst
> 
> This
is an old patch, please use this one : git clone -b cf/perfwatcher
http://github.com/feraudet/collectd.git 
> 
> Regards, 
> 
> Cyril

Here
a ready to build one
https://github.com/downloads/feraudet/collectd/collectd-5.1.0-perfwatcher.tgz


You have to re-create database from collectd/contrib/write_mysql.sql
and take care to the new config block : 

 Host "localhost"
 User
"root"
 Passwd ""
 Database "collectd"
 Port "3306"
 Replace
true

Regards, 

Cyril 
  

Links:
------
[1]
mailto:collectd at verplant.org
[2]
http://mailman.verplant.org/listinfo/collectd
[3]
mailto:collectd at verplant.org
[4]
http://mailman.verplant.org/listinfo/collectd
[5]
http://pastebin.com/dPc6zgUH
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.verplant.org/pipermail/collectd/attachments/20120504/c5f661ba/attachment.html>