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

Reply via email to