[rrd-users] RRDTool + libdbi + mysql + speed

[Adam Jacob Muller]

Hello,

The issue persists even on completely memory-based tables (run only as a
synthetic benchmark) its definitely not i/o-bound.

strace does not show libdbi/rrdtool doing -anything- during this period
at all.

I'd also mention that my hacked-up version of rrdtool that uses the
native MySQL functions completely eliminates this issue.

-Adam

On Mon, 2011-07-04 at 14:37 +0200, Martin Sperl wrote:
> It may be primarily be related to the DB - the first time it is run int 
> needs to read the blocks from disk
> on the second run - e.g with your own script it may not need to do that 
> any longer...
> 
> I know that this is the biggest deficiency in the libdbi approach: the 
> DB is usually not optimized and needs to read lots of blocks (in your 
> example worsted case 100k blocks).
> 
> If it is really being CPU-bound on the client, then I am at a loss...
> Does strace show anything that may be of interrest?
> 
> Martin
> 
> On 04.07.2011 05:57, Adam Jacob Muller wrote:
> > Hi Brandon,
> > I've tested variants of the mysql version with both _use_ and _store_ with no appreciable difference.
> >
> > the dbi version appears heavily CPU-bound, with minimal memory usage (my trivial test cases don't actually -keep- the data anywhere).
> >
> > USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
> > root     27941 90.4  0.1  42556 17784 pts/2    S+   23:55   0:16 ./dbi
> >
> >
> > -Adam
> >
> > On Jul 3, 2011, at 10:47 PM, Brandon Phelps wrote:
> >
> >> Just a wild guess here but have you checked memory usage during the
> >> job?  Maybe it has something to do with libdbi using mysql_store_result
> >> for such a large number of rows?  Try switching to mysql_use_result and
> >> see if the problem persists?
> >>
> >> Just a thought, could be way off.
> >>
> >> On 7/3/2011 10:14 PM, Adam Jacob Muller wrote:
> >>> Hi,
> >>> I'm curious if anyone knows of any specific issues with regards to libdbi and MySQL with RRDTool. I have a specific dataset where I pull a significant number of rows from MySQL to draw graphs (think, in the 100,000's of rows). And its extremely slow.
> >>>
> >>> I've specifically isolated this not to query execution time but to libdbi.
> >>>
> >>> to completely isolate the issue, this is a small program I wrote that uses libdbi to read a months worth of samples (~2*60*24*30 = 86400 rows):
> >>>
> >>>   # ./dbi
> >>> dbi_conn_queryf took 0.5319
> >>> dbi_result_next_row took 15.0172
> >>> looped through 71891 rows
> >>>   #
> >>>
> >>>
> >>> This is the same thing, but using the native mysql c-bindings:
> >>>   # ./mysql
> >>> mysql_query took 0.0021
> >>> mysql_fetch_row took 0.5352
> >>> looped through 71891 rows
> >>>   #
> >>>
> >>>
> >>>
> >>> This probably ultimately seems like a libdbi issue, but I thought i'd bring it up here because it seems to have an extraordinary impact upon rrdtool performance and i'm curious if anyone here has seen it.
> >>>
> >>>
> >>> -Adam
> >>> _______________________________________________
> >>> rrd-users mailing list
> >>> rrd-users at lists.oetiker.ch
> >>> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users
> >> _______________________________________________
> >> rrd-users mailing list
> >> rrd-users at lists.oetiker.ch
> >> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users
> > _______________________________________________
> > rrd-users mailing list
> > rrd-users at lists.oetiker.ch
> > https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users
> 
> _______________________________________________
> rrd-users mailing list
> rrd-users at lists.oetiker.ch
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users