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