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@feystorm.net> To: Syslog-ng users' and developers' mailing list <syslog-ng@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@jp.fujitsu.com> To: syslog-ng@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

Hello Martin. Thank you for the reply. Since, there will be no massive number of records yet, I would like to put into consideration in the future. For , now I will use the perl DBD library. Thanks! Yu Martin Holste さんは書きました:

In my experience, to get the maximum performance, write to a program() destination of Perl (or the scripting language of your choice) which is configured to write to /dev/shm/ (on Linux) or even a normal disk-based file, then do bulk imports using LOAD DATA via the same Perl script. You can get upwards of 100k rows/second that way. I will also add that for basic read/write, Perl is just as fast as any program in C because it is running the same system calls.

Also, check out the new work being done by Mr. Nagy on this list using the MongoDB driver. MongoDB with capped collections has near raw disk speed.

On Mon, Jan 17, 2011 at 9:04 PM, Patrick H. <syslogng@feystorm.net> wrote:

...

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@feystorm.net> To: Syslog-ng users' and developers' mailing list <syslog-ng@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@jp.fujitsu.com> To: syslog-ng@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

______________________________________________________________________________ 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

Make sure that you are preparing the statement outside of the while loop so instead of

# Pick up the sql line from the stdinput while( my $line = <FILE> ) {

 $sth = $dbh->prepare( $line );  $dbh->execute( $line );

}

It should look like $sth = $dbh->prepare('insert into table....'); while( my $line = <FILE> ) { $dbh->execute( $line ); } That saves a lot of CPU cycles.