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

[Martin Sperl]

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