Re: [libdbi-users] libdbi+mysql speed issues
Olivier Doucet writes: > Performance are now correct :) I checked with valgrind / callgrind, > and function mysql_data_seek is not called anymore. > > You can now patch other drivers, and maybe release a new version ? I've updated the remaining drivers and the driver documentation. Did anyone else have a chance to test the code lately? regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Olivier Doucet was heard to say: >> Do you have any numbers? Would be nice to know what speed gain this >> (almost) one-line optimization effected. > > I've done a benchmark with rrdtool that uses libdbi. > http://tof.canardpc.com/view/31953cab-4329-45e9-9e30-eed2d9148ee1.jpg > max value on X-Axis is equivalent to ~ 35k rows retrieved from MySQL. > > Quite impressive, right ? ;) > Looks like my 30 min effort was well spent then. Thanks for kicking my butt to get this done. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
> libdbi-drivers uses the mysql_config script shipped with MySQL to > obtain the list of required libraries to link against, *unless* you > use the --with-mysql-libdir configure switch. Yes I was using this configure switch. I copied configure line from Red Hat SRPM. > Do you have any numbers? Would be nice to know what speed gain this > (almost) one-line optimization effected. I've done a benchmark with rrdtool that uses libdbi. http://tof.canardpc.com/view/31953cab-4329-45e9-9e30-eed2d9148ee1.jpg max value on X-Axis is equivalent to ~ 35k rows retrieved from MySQL. Quite impressive, right ? ;) Olivier -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Olivier Doucet was heard to say: Hi, > I finally succeeded in compiling both component for my system (I added > flag -lpthread when both linking / compiling or else I have following > error : '/usr/lib64/dbd/libdbdmysql.so: undefined symbol: > pthread_mutex_trylock'). libdbi-drivers uses the mysql_config script shipped with MySQL to obtain the list of required libraries to link against, *unless* you use the --with-mysql-libdir configure switch. Please try and see if libdbi-drivers compiles fine without that switch. Otherwise try manually what "mysql_config --libs" reports. > > Performance are now correct :) I checked with valgrind / callgrind, > and function mysql_data_seek is not called anymore. Do you have any numbers? Would be nice to know what speed gain this (almost) one-line optimization effected. > > You can now patch other drivers, and maybe release a new version ? > Thank you ! > I think I'll be able to finish migrating the drivers tonight as this requires changes in just one line per driver. New version is a different issue as I have to update the documentation and make sure everything is ready for prime time. It would be great if we could find a couple of kind souls who test the current CVS revisions on as many platforms as possible to find showstoppers, if any. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Hello, > I've implemented the suggested changes and checked in the updated > files. If you want to speed test the code, please check out, build, > and install the current cvs revisions of both libdbi and > libdbi-drivers. I finally succeeded in compiling both component for my system (I added flag -lpthread when both linking / compiling or else I have following error : '/usr/lib64/dbd/libdbdmysql.so: undefined symbol: pthread_mutex_trylock'). Performance are now correct :) I checked with valgrind / callgrind, and function mysql_data_seek is not called anymore. You can now patch other drivers, and maybe release a new version ? Thank you ! Olivier -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Markus Hoenicka writes: > Hi, > > I'll have to read the code again a little more thoroughly, but to the > best of my knowledge libdbi emulates MySQL's approach to retrieving > rows from result sets. In order to walk through the rows of e.g. a > PostgreSQL result set you have to retrieve the rows by index > sequentially, so libdbi has to maintain an internal pointer anyway. We > do not have to add one, so there are no extra changes. Also, libdbi > internally already mixes the cursor style and next_row style calls, > because we have to cater for database engines which use either of > these methods without exposing these differences to the libdbi user. > > As for the API change, we have extensive driver API changes between > 0.8.x and the upcoming(TM) 0.9 release anyway, think of the recent > addition of the transaction stuff. You won't be able to keep your > 0.8.x drivers once you switch to libdbi 0.9. You'll probably notice > problems only if you build from cvs regularly (and only if you update > one but not the other), but I expect those users to know what they're > doing. > Hi, I've implemented the suggested changes and checked in the updated files. If you want to speed test the code, please check out, build, and install the current cvs revisions of both libdbi and libdbi-drivers. I've updated the mysql, pgsql, and sqlite3 drivers at this time, but making the remaining drivers compile is trivial. I've added the suggested check to speed up sequential row fetching in mysql. I do see a small but significant decrease in the time required to run gmake check (11.78 vs. 12.51 s), although the test code does not test retrieving boatloads of rows specifically. I'd appreciate if someone with a nice testcase (Olivier?) could give the changes a try and report some numbers. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master Java SE, Java EE, Eclipse, Spring, Hibernate, JavaScript, jQuery and much more. Keep your Java skills current with LearnJavaNow - 200+ hours of step-by-step video tutorials by Java experts. SALE $49.99 this month only -- learn more at: http://p.sf.net/sfu/learnmore_122612 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Mike Rylander was heard to say: > Markus, > > Would it be worth the ugliness of a module-global variable to bury the > value of the row index used in the previous call to > dbd_mysql.c::dbd_goto_row() > inside the MySQL driver itself, side-stepping the function signature change > for other drivers? The benefit of avoiding the API change may not be > outweighed by the potential fragility of naively tracking the state > internally, of course, since in practice folks install and upgrade to new > versions of drivers and the libdbi core at the same time, but then again, > it may. I'm a little nervous about the potential for problems when mixing > direct goto_row() (cursor style) and next_row() calls, but I haven't looked > at the code to see if there's actually an issue there... > Hi, I'll have to read the code again a little more thoroughly, but to the best of my knowledge libdbi emulates MySQL's approach to retrieving rows from result sets. In order to walk through the rows of e.g. a PostgreSQL result set you have to retrieve the rows by index sequentially, so libdbi has to maintain an internal pointer anyway. We do not have to add one, so there are no extra changes. Also, libdbi internally already mixes the cursor style and next_row style calls, because we have to cater for database engines which use either of these methods without exposing these differences to the libdbi user. As for the API change, we have extensive driver API changes between 0.8.x and the upcoming(TM) 0.9 release anyway, think of the recent addition of the transaction stuff. You won't be able to keep your 0.8.x drivers once you switch to libdbi 0.9. You'll probably notice problems only if you build from cvs regularly (and only if you update one but not the other), but I expect those users to know what they're doing. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Markus, Would it be worth the ugliness of a module-global variable to bury the value of the row index used in the previous call to dbd_mysql.c::dbd_goto_row() inside the MySQL driver itself, side-stepping the function signature change for other drivers? The benefit of avoiding the API change may not be outweighed by the potential fragility of naively tracking the state internally, of course, since in practice folks install and upgrade to new versions of drivers and the libdbi core at the same time, but then again, it may. I'm a little nervous about the potential for problems when mixing direct goto_row() (cursor style) and next_row() calls, but I haven't looked at the code to see if there's actually an issue there... --miker On Tue, Jan 8, 2013 at 4:46 AM, Markus Hoenicka < markus.hoeni...@mhoenicka.de> wrote: > Olivier Doucet was heard to say: > > > Hello everyone, > > > > I'm following a quite old topic about libdbi speed issues. > > I was able to track the cause of these issues : The major problem is > > how libdbi goes from one row to another. > > > > RRDTool (the tool that used libdbi and that I was inspecting) is using > > dbi_result_next_row() function (as stated in libdbi documentation > > btw). > > > > This function moves from one row to another with function > > dbi_result_seek_row(), incrementing currentRow index each time. This > > gives a call to dbd_mysql.c::dbd_goto_row() that uses > > mysql_data_seek() each time... > > > > That's why for a query result of 34k rows (yes it happens. No it is > > not a problem in the query itself), we have tens of thousands of call > > to this function (which is very low), and this is definitely not > > needed, because as we use fetch_row(), we automatically move from one > > row to another. Seeking is just a useless task (as internal driver > > does not know where we are, and needs to start from row 0 and seek to > > the given row - where we already were). > > > > I'm absolutely not a libdbi user, and I don't know what could be done > > outside libdbi to not use dbi_result_next_row() and use directly > > RESULT->onn->driver->functions->fetch_row() directly. Is it possible ? > > > > And/or patching dbi_result.c : > > just check RESULT->currowidx near line 102 before calling doing > > goto_row() function and call it only if we are not on the good row. Am > > I right ? > > > > Hi, > > your analysis is pretty much correct. If you look at the comments in > dbd_mysql.c::dbd_goto_row(), the original author of the mysql driver > was well aware of the limitations of his implementation. The reason is > that other database APIs, e.g. PostgreSQL, allow to fetch rows from a > result set by index, whereas the MySQL API assumes that you step > through the rows sequentially. The original design of libdbi appears > to somewhat favor PostgreSQL in this respect. > > Anyway, without having thought about the issue in too much detail, one > possible solution comes to mind. We could modify the driver function > dbd_goto_row() by passing both the wanted row index rowidx and the > current row index currowidx(which libdbi keeps track of anyway). This > would allow drivers to decide whether they have to actually seek the > position. pgsql doesn't have to anyway, and mysql doesn't have to if > rowidx = currowidx+1. This API change would not mandate changes to > existing drivers as they may ignore the additional parameter and keep > working as before, but it may offer options to speed up queries in > some drivers. > > regards, > Markus > > > > -- > Markus Hoenicka > http://www.mhoenicka.de > AQ score 38 > > > > > -- > Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS > and more. Get SQL Server skills now (including 2012) with LearnDevNow - > 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. > SALE $99.99 this month only - learn more at: > http://p.sf.net/sfu/learnmore_122512 > ___ > libdbi-users mailing list > libdbi-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/libdbi-users > -- Mike Rylander | Director of Research and Development | Equinox Software, Inc. / Your Library's Guide to Open Source | phone: 1-877-OPEN-ILS (673-6457) | email: mi...@esilibrary.com | web: http://www.esilibrary.com -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Olivier Doucet was heard to say: > Hi Markus, > > 2013/1/8 Markus Hoenicka : >> We could modify the driver function >> dbd_goto_row() by passing both the wanted row index rowidx and the >> current row index currowidx(which libdbi keeps track of anyway). > > This is one way to fix the problem, I agree. Unfortunately my level in > C is too low to make such huge changes without breaking everything > else :) Anyone willing to create the patch for this ? > I'll commit these changes asap (may take a day or two). Can you build libdbi and libdbi-drivers from the cvs sources? You seem to have a good test case to see if these changes help. > Is there a way, outside libdbi, to fix this problem ? For example, go > over dbd_goto_row() and call fetch_row directly() ? Or maybe the > behaviour is different between database engines ? > I'm afraid you can't do that except if you bypass the abstraction layer altogether and use libmysqlclient natively. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Hi Markus, 2013/1/8 Markus Hoenicka : > We could modify the driver function > dbd_goto_row() by passing both the wanted row index rowidx and the > current row index currowidx(which libdbi keeps track of anyway). This is one way to fix the problem, I agree. Unfortunately my level in C is too low to make such huge changes without breaking everything else :) Anyone willing to create the patch for this ? Is there a way, outside libdbi, to fix this problem ? For example, go over dbd_goto_row() and call fetch_row directly() ? Or maybe the behaviour is different between database engines ? Olivier -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Olivier Doucet was heard to say: > Hello everyone, > > I'm following a quite old topic about libdbi speed issues. > I was able to track the cause of these issues : The major problem is > how libdbi goes from one row to another. > > RRDTool (the tool that used libdbi and that I was inspecting) is using > dbi_result_next_row() function (as stated in libdbi documentation > btw). > > This function moves from one row to another with function > dbi_result_seek_row(), incrementing currentRow index each time. This > gives a call to dbd_mysql.c::dbd_goto_row() that uses > mysql_data_seek() each time... > > That's why for a query result of 34k rows (yes it happens. No it is > not a problem in the query itself), we have tens of thousands of call > to this function (which is very low), and this is definitely not > needed, because as we use fetch_row(), we automatically move from one > row to another. Seeking is just a useless task (as internal driver > does not know where we are, and needs to start from row 0 and seek to > the given row - where we already were). > > I'm absolutely not a libdbi user, and I don't know what could be done > outside libdbi to not use dbi_result_next_row() and use directly > RESULT->onn->driver->functions->fetch_row() directly. Is it possible ? > > And/or patching dbi_result.c : > just check RESULT->currowidx near line 102 before calling doing > goto_row() function and call it only if we are not on the good row. Am > I right ? > Hi, your analysis is pretty much correct. If you look at the comments in dbd_mysql.c::dbd_goto_row(), the original author of the mysql driver was well aware of the limitations of his implementation. The reason is that other database APIs, e.g. PostgreSQL, allow to fetch rows from a result set by index, whereas the MySQL API assumes that you step through the rows sequentially. The original design of libdbi appears to somewhat favor PostgreSQL in this respect. Anyway, without having thought about the issue in too much detail, one possible solution comes to mind. We could modify the driver function dbd_goto_row() by passing both the wanted row index rowidx and the current row index currowidx(which libdbi keeps track of anyway). This would allow drivers to decide whether they have to actually seek the position. pgsql doesn't have to anyway, and mysql doesn't have to if rowidx = currowidx+1. This API change would not mandate changes to existing drivers as they may ignore the additional parameter and keep working as before, but it may offer options to speed up queries in some drivers. regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Hello everyone, I'm following a quite old topic about libdbi speed issues. I was able to track the cause of these issues : The major problem is how libdbi goes from one row to another. RRDTool (the tool that used libdbi and that I was inspecting) is using dbi_result_next_row() function (as stated in libdbi documentation btw). This function moves from one row to another with function dbi_result_seek_row(), incrementing currentRow index each time. This gives a call to dbd_mysql.c::dbd_goto_row() that uses mysql_data_seek() each time... That's why for a query result of 34k rows (yes it happens. No it is not a problem in the query itself), we have tens of thousands of call to this function (which is very low), and this is definitely not needed, because as we use fetch_row(), we automatically move from one row to another. Seeking is just a useless task (as internal driver does not know where we are, and needs to start from row 0 and seek to the given row - where we already were). I'm absolutely not a libdbi user, and I don't know what could be done outside libdbi to not use dbi_result_next_row() and use directly RESULT->onn->driver->functions->fetch_row() directly. Is it possible ? And/or patching dbi_result.c : just check RESULT->currowidx near line 102 before calling doing goto_row() function and call it only if we are not on the good row. Am I right ? Olivier -- Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS and more. Get SQL Server skills now (including 2012) with LearnDevNow - 200+ hours of step-by-step video tutorials by Microsoft MVPs and experts. SALE $99.99 this month only - learn more at: http://p.sf.net/sfu/learnmore_122512 ___ libdbi-users mailing list libdbi-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/libdbi-users
Re: [libdbi-users] libdbi+mysql speed issues
Hi Markus, I definitely can, anecdotal evidence suggests to me that this issue does not occur on postgres (I use rrdtool+postgres similarly and havne't seen the issue), but I'll make some actual tests and report back. -Adam On Jul 9, 2011, at 8:39 AM, markus.hoeni...@mhoenicka.de wrote: > Adam Jacob Muller writes: >> 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, >> > > Hi Adam, > > I can't see any obvious issues with your C code, so I assume the speed > differences really stem from libdbi, one way or another. Unfortunately > I currently lack the time for any deeper analysis, but I'm somewhat > surprised about your results. libdbi has originally been developed as > a wrapper for MySQL and PostgreSQL and should work fairly well with > these database engines (other engines are a *lot* harder to fit). The > only overhead that the mysql driver contains is the column type > analysis and data conversion stuff. Some other engines have to buffer > the data and thus create way more overhead. > > Did you have a chance to verify your results with any of the other > database drivers? > > regards, > Markus > > -- > Markus Hoenicka > http://www.mhoenicka.de > AQ score 38 > > -- > 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 -- 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
[libdbi-users] libdbi+mysql speed issues
Adam Jacob Muller writes: > 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, > Hi Adam, I can't see any obvious issues with your C code, so I assume the speed differences really stem from libdbi, one way or another. Unfortunately I currently lack the time for any deeper analysis, but I'm somewhat surprised about your results. libdbi has originally been developed as a wrapper for MySQL and PostgreSQL and should work fairly well with these database engines (other engines are a *lot* harder to fit). The only overhead that the mysql driver contains is the column type analysis and data conversion stuff. Some other engines have to buffer the data and thus create way more overhead. Did you have a chance to verify your results with any of the other database drivers? regards, Markus -- Markus Hoenicka http://www.mhoenicka.de AQ score 38 -- 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
[libdbi-users] libdbi+mysql speed issues
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
#include
#include
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/100);
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/100);
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
#include
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/100);
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/100);
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
