Hi,
I'm developing an application that utilizes rrdtool (and the lidbdi interface
into rrdtool) heavily and having some performance issues that seem to center
around libdbi,
# ./dbi
dbi_conn_queryf took 0.5866
dbi_result_next_row took 15.4186
looped through 71891 rows
# ./mysql
MySQL client version: 5.1.56
mysql_query took 0.0021
mysql_fetch_row took 0.5584
looped through 71891 rows
libdbi here appears to simply be spectacularly slow, I assume its something
that I/the rrdtool libdbi are doing wrong. source for dbi.c and mysql.c follows
(yes, i'm a horrible c programmer, I know!).
Thanks,
-Adam
# cat dbi.c
#include <stdio.h>
#include <dbi/dbi.h>
#include <sys/time.h>
int main() {
dbi_conn conn;
dbi_result result;
struct timeval query_start;
struct timeval query_end;
struct timeval query_elapsed;
float real_query_elapsed;
struct timeval fetch_start;
struct timeval fetch_end;
struct timeval fetch_elapsed;
float real_fetch_elapsed=0;
int rowcount;
dbi_initialize(NULL);
conn = dbi_conn_new("mysql");
dbi_conn_set_option(conn, "host", "something");
dbi_conn_set_option(conn, "username", "something");
dbi_conn_set_option(conn, "password", "something");
dbi_conn_set_option(conn, "dbname", "something");
dbi_conn_set_option(conn, "encoding", "UTF-8");
if (dbi_conn_connect(conn) < 0) {
printf("Could not connect. Please check the option settings\n");
return;
}
gettimeofday(&query_start,NULL);
result = dbi_conn_queryf(conn,"SELECT dtime as rrd_time, counter as rrd_value
FROM ifInOctets WHERE id=1920 AND '2011-06-01 00:00:00' < dtime AND dtime <
'2011-06-26 00:00:00' ORDER BY dtime");
gettimeofday(&query_end,NULL);
timersub(&query_end,&query_start,&query_elapsed);
real_query_elapsed=(float)query_elapsed.tv_sec;
real_query_elapsed+=(float)((float)query_elapsed.tv_usec/1000000);
printf("dbi_conn_queryf took %.4f\n",real_query_elapsed);
gettimeofday(&fetch_start,NULL);
rowcount=0;
if (result) {
while (dbi_result_next_row(result)) {
rowcount++;
}
}
gettimeofday(&fetch_end,NULL);
timersub(&fetch_end,&fetch_start,&fetch_elapsed);
real_fetch_elapsed=(float)fetch_elapsed.tv_sec;
real_fetch_elapsed+=(float)((float)fetch_elapsed.tv_usec/1000000);
printf("dbi_result_next_row took %.4f\n",real_fetch_elapsed);
printf("looped through %d rows\n",rowcount);
sleep(30);
return;
}
# cat mysql.c
#include <my_global.h>
#include <mysql.h>
int main(int argc, char **argv)
{
MYSQL *conn;
MYSQL_RES *result;
MYSQL_ROW row;
int num_fields;
int i;
struct timeval query_start;
struct timeval query_end;
struct timeval query_elapsed;
float real_query_elapsed;
struct timeval fetch_start;
struct timeval fetch_end;
struct timeval fetch_elapsed;
float real_fetch_elapsed=0;
printf("MySQL client version: %s\n", mysql_get_client_info());
conn = mysql_init(NULL);
if (conn == NULL) {
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
if (mysql_real_connect(conn, "this", "that", "theotherthing", "redacted", 0,
NULL, 0) == NULL) {
printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
exit(1);
}
gettimeofday(&query_start,NULL);
mysql_query(conn,"SELECT dtime as rrd_time, counter as rrd_value FROM
ifInOctets WHERE id=1920 AND '2011-06-01 00:00:00' < dtime AND dtime <
'2011-06-26 00:00:00' ORDER BY dtime");
result=mysql_use_result(conn);
gettimeofday(&query_end,NULL);
timersub(&query_end,&query_start,&query_elapsed);
real_query_elapsed=(float)query_elapsed.tv_sec;
real_query_elapsed+=(float)((float)query_elapsed.tv_usec/1000000);
printf("mysql_query took %.4f\n",real_query_elapsed);
gettimeofday(&fetch_start,NULL);
i=0;
while ((row = mysql_fetch_row(result))) {
i++;
}
gettimeofday(&fetch_end,NULL);
timersub(&fetch_end,&fetch_start,&fetch_elapsed);
real_fetch_elapsed=(float)fetch_elapsed.tv_sec;
real_fetch_elapsed+=(float)((float)fetch_elapsed.tv_usec/1000000);
printf("mysql_fetch_row took %.4f\n",real_fetch_elapsed);
printf("looped through %d rows\n",i);
}
------------------------------------------------------------------------------
All of the data generated in your IT infrastructure is seriously valuable.
Why? It contains a definitive record of application performance, security
threats, fraudulent activity, and more. Splunk takes this data and makes
sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-d2d-c2
_______________________________________________
libdbi-users mailing list
libdbi-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/libdbi-users
