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

[Martin Sperl]

Can you please share the "tests" you are doing?
I can not remember any such issues when I was using/developping libdbi intensively...

Thanks,
		Martin

On 04.07.2011, at 16:07, 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
>