[collectd] New plugin for collectd : write_mysql

[Daniel Hilst]

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.
>>>>> ATE TABLE `dataset` (
>>>> Why do you store datasets in the database (in addition to the
>>>> types.db file)? Having the definition in more than one place is bound
>>>> to create inconsistencies at some point
>>> This table is for readonly purpose and to make SQL query independent of
>>> number
>>> of metrics in a type. It also contain type added on the way by a
>>> plugin.
>>>>> diff --git a/src/write_mysql.c b/src/w
>>>> /blockquote> typedef struct host_s host_t; +struct host_s { + char
>>>>> name[DATA_MAX_NAME_LEN]; + int id; + host_t *next_host; +};
>>>> You will be doing a lot of lookups in this cache(es). I think using a
>>>> binary search tree (an implementation if provided in
>>>> src/utils_avltree.h) would improve performance here.
>>> Thanks for the tips, Il will do that.
>>>> int port =
>>>>> tatic int write_mysql_config (const char *key, const char *value) {
>>>>> [...] + } else if (strcasecmp ("Port", key) == 0) { + port = value;
>>>>> You
>>>> a char* to an int. This won't produce what you expect. Use the
>>>> function service_name_to_port_number() from src/common.h. +static
>>>> int wri
>>> Your'e right I will fix it.
>>>>> t (void) { + conn = mysql_init(NULL); + if (mysql_real_connect(conn,
>>>>> host, user, passwd, database, port, NULL, 0) == NULL) { The conn
>>>> be (re-)established in the write() callback, when it is needed.
>>>> Otherwise you will never recover from connection failures. + if
>>>> (!mysql_th
>>> Your'e right, I just issued it. I will fix it too.
>>>>> { + ERROR("write_mysql plugin: mysqlclient Thread Safe OFF"); I wasn't
>>>> s function. Good to know, thanks! +static int add_host_id (
>>>>> me) { [...] + len = ssnprintf (query, sizeof (query), "SELECT id
>>>>> FROM host WHERE name = '%s'", hostname); I think we should
>>>> statement with bound arguments (or whatever the terminology). This
>>>> way (a) MySQL doesn't have to parse and optimize the query each time
>>>> we need to insert a host and (b) quoting is done for us by the mysql
>>>> library. +static int add_host_id (char *
>>> I will looking for. I never used it before.
>>>>> [...] + pthread_mutex_lock(&mutex); [...] + if (row =
>>>>> mysql_fetch_row(result)) { + id = atoi(row[0]); +
>>>>> mysql_free_result(result); + pthread_mutex_unlock(&mutex); [...] +
>>>>> pthread_mutex_unlock(&mutex); + return id; +} Mutex is released twice
>>>> +static int add_plugin_id (char
>>> I'll fix that.
>>>>> { +static int add_type_id (char *typename) { A *lot* of duplicate code
>>>> ite a function like: int add_string (const char *table, const char
>>>> *str); /* returns id on success, less than zero on failure. */
>>>> +static int write_mysql_write(con
>>>>> t *ds, const value_list_t *vl, + user_data_t __attribute__((unused))
>>>>> *user_data) { [...] + len = ssnprintf (tmpquery, sizeof (tmpquery),
>>>>> "INSERT INTO data " +
>>> "(timestamp,host_id,plugin_id,plugin_instance,type_id,typeinstance,dataset_id,value)"
>>>
>>>>> + "VALUES (%.3f,%d,%d,'%s',%d,'%s',%d,%%lf)", CDTIME_T_TO_DOUBLE
>>>>> (vl->time), host_id, + plugin_id, vl->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) { + len = ssnprintf (query, sizeof
>>>>> (query), tmpquery, vl->values[i].gauge); +} else { + if (rates ==
>>>>> 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 in a type in 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 is used to make, for example, sum of cpu used
>>> on several thousand
>>> of server and re-inject it in collectd 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 <mailto:collectd at verplant.org>
>>> http://mailman.verplant.org/listinfo/collectd
>>
>>
>>
>> _______________________________________________
>> collectd mailing list
>> collectd at verplant.org
>> http://mailman.verplant.org/listinfo/collectd
>
> Hello, I'm trying to compile collectd 5.0.2 with write_mysql pluing
>
> I've done this: http://pastebin.com/dPc6zgUH
> 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

-- 
Follow the white rabbit!