[syslog-ng] Performance of syslog-ng for Database access (mysql)

[Yu Watanabe]

Hello Patrick

Thank you for the reply.

I will be considering to use the perl DBD driver.

I am considering to write a program like, 

* syslog-ng.conf
program("..../inserttodb.pl")

* in the code
use DBI;

$dbh = DBI->connect(...)

open(FILE, STDIN);

# Pick up the sql line from the stdinput
while( my $line = <FILE> ) {
  
  $sth = $dbh->prepare( $line );
  $dbh->execute( $line );

}

It would be a help if you could give me an advice with the code and configuration file.

Thanks.
Yu

Patrick H. さんは書きました:
>One thing that *might* be faster is to write a perl program to do the 
>inserts. Perl's DBD::mysql driver allows you to use prepared statements 
>and bind handles, which significantly speed up the database side of the 
>operations. Both the command line mysql client and the libdbi mysql 
>driver have to parse every single statement they come across and also 
>perform permission checks on the statement, which slows things down. The 
>tradeoff is that perl isnt the fastest thing in the world, so I cant say 
>if perl+prepared statements is faster or slower than libdbi+parsing.
>
>-Patrick
>
>Sent: Mon Jan 17 2011 19:58:16 GMT-0700 (Mountain Standard Time)
>From: Patrick H. <syslogng at feystorm.net>
>To: Syslog-ng users' and developers' mailing list 
><syslog-ng at lists.balabit.hu>
>Subject: Re: [syslog-ng] Performance of syslog-ng for Database access 
>(mysql)
>> Neither. The performance on both will be about the same (immeasurable 
>> difference). The libdbi mysql is probably better though as it 
>> gracefully handles disconnects and is simpler to configure (than an 
>> external mysql process).
>>
>> -Patrick
>>
>> Sent: Mon Jan 17 2011 19:11:09 GMT-0700 (Mountain Standard Time)
>> From: Yu Watanabe <yu.watanabe at jp.fujitsu.com>
>> To: syslog-ng at lists.balabit.hu
>> Subject: [syslog-ng] Performance of syslog-ng for Database access (mysql)
>>> Hello all.
>>>
>>> I would like to ask question for the performance of syslog-ng when it is accessing to mysql database.
>>>
>>> I am using syslog-ng 3.0.9 and thinking of using either one of the method below for the database connection.
>>>
>>> 1. Using the program driver and fork the "mysql command" to execute the sql.
>>> 2. Use the sql driver to access to the table
>>>
>>> I was wondering which is more efficient for syslog-ng and the server itself (i.e. CPU , memory )
>>>
>>> Any advice would be helpful!
>>>
>>> Thanks,
>>> Yu Watanabe
>>>  
>>>
>>> ______________________________________________________________________________
>>> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
>>> Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
>>> FAQ: http://www.campin.net/syslog-ng/faq.html
>>>
>>>   
>> ------------------------------------------------------------------------
>>
>> ______________________________________________________________________________
>> Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
>> Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
>> FAQ: http://www.campin.net/syslog-ng/faq.html
>>
>>   
>______________________________________________________________________
>
>______________________________________________________________________________
>Member info: https://lists.balabit.hu/mailman/listinfo/syslog-ng
>Documentation: http://www.balabit.com/support/documentation/?product=syslog-ng
>FAQ: http://www.campin.net/syslog-ng/faq.html
>