[syslog-ng] Syslog-Ng MySQL Performance Problem

[Bill Nash]

Batched inserts are totally the way to go. The thing to keep in mind about 
MySQL is that it updates the indexes following completion of each insert, 
be it one row, or 500. If you're doing one insert per log message, you're 
obviously feeling the bottleneck.

MySQL has support for multiple inserts in a single statement. I'm perlish, 
so I do something like this:

#---
my @bulk;

while(1) {		# main loop, whatever it is
 	$line = incoming();	# yes, I just made this up, but pretend
 				# it's whatever socket function you're
 				# using that reads the incoming data
 	push(@bulk, $line);

 	if(scalar @bulk ge 500) {	# got 500 events to store
 		$dbh->do('insert into syslog (message) values (' . join('),(', @bulk) . ')';
 		@bulk = ();		# reset @bulk to empty so it stays in global scope
 	} 
}
#---

You wind up with a query that looks like a mysql multi-insert statement:
insert into syslog (message) values ('foo'), ('bar'), ('hey bob, I broke 
the server'), ('dave, you jerk, stop breaking my server'), ('etc'), 
('etc'), ('etc');

This is a very rough, overly caffienated idea of what you need to do. Be 
mindful of your mysql server's max_packet_size value. Depending on the 
structure of your insert process, I would personally suggest a time cycled 
insert, instead of waiting for a buffer to fill, as I demonstrated above. 
Lower traffic volumes may leave you with content in the buffer for 
unseemly amounts of time. In either case, you can tune parameters as your 
performance demands.

Good luck.

- billn


On Tue, 29 Apr 2008, Hari Sekhon wrote:

> Hi,
>
>   I've had a Syslog-NG Central Logserver -> MySQL setup for nearly 2
> years now but am finding it has developed a performance problem with a
> backed up pipe so I'm thinking of using batched inserts to try to
> improve the db processing performance.
>
> Before I go and code this myself, are there any good generic solutions,
> somebody must have done this batched inserts before, I saw something on
> the mailing list once I think but can't see it now...
>
> Any other suggestions would also be welcome before I go off and do this.
>
> Thanks
>
> -h
>
>