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

[Tobias Oetiker]

Hi Adam,

I am not using the dbi functions myself, but if you find any fixes,
I would be glad to integrate them

cheers
tobi


Today Adam Jacob Muller wrote:

> 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
>
> _______________________________________________
> rrd-users mailing list
> rrd-users at lists.oetiker.ch
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-users
>
>

-- 
Tobi Oetiker, OETIKER+PARTNER AG, Aarweg 15 CH-4600 Olten, Switzerland
http://it.oetiker.ch tobi at oetiker.ch ++41 62 775 9902 / sb: -9900