A few things about the MySQL server itself, I have eliminated bandwidth,
proc speed, disk I/O as potential bottlenecks. The obvious bottleneck
is the MySQL server.
For a temporary solution, I have placed an rsyslog relay on the MySQL
server. So:
Client_message -> local_datacenter_relay -> remote_datacenter_relay ->
MySQL_server
The messages are traveling much faster (kudos to the socket programming
there) as the remote relay writes to a local MySQL server. I do not
believe this to be an optimal solution. In an earlier email, Rainer
mentions and I quote:
"You could try to run the action on its own queue and with multiple
workers. That could (could!) improve performance. But it is just a
guess. Do you have any chance to see how long the query takes inside the
SQL engine?"
MySQL will run about 4000 inserts per second (constant speed). I am
willing to try what Rainer suggests; however, I am unsure how to direct
specific actions to act on a queue. Any help s appreciated. I know I
could add the two following lines and create worker threads:
$ActionQueueWorkerThreads 20
$MainMsgQueueWorkerThreads 20
Would I have to add additional lines to the config. My config once
again looks like so:
$ModLoad immark
$ModLoadd imudp
$UDPServerAddress 172.16.12.138
$UDPServerRun 514
$ModLoad imtcp
$ModLoad imuxsock
$ModLoad imklog
$ModLoad ommysql.so
$template template1,"CALL
SAT2_RSYSLOG_EVENT_INSERT('%timestamp:::date-mysql%',
'%timegenerated:::date-mysql%', '%syslogfacility%',
syslogpriority%',
'%hostname%', '%syslogtag%', '%msg%')", sql
$WorkDirectory /rsyslog/work
$ActionQueueType LinkedList # use asynchronous processing
$ActionQueueFileName dbq # set file name, also enables disk mode
$ActionResumeRetryCount -1 # infinite retries on insert failure
*.* >172.16.2.238,rsyslog,syslogwriter,topsecret;template1
I would hope that there is an easy solution as my next idea is to write
some type of daemonized process that can insert messages from a pool of
MySQL connections. I can achieve this in C but would rather hopefully
find a solution inside of the configuration.
Daniel M. Anson
Linux Systems Engineer
Rackspace Managed Hosting
[email protected]
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of RB
Sent: Thursday, January 15, 2009 1:11 PM
To: rsyslog-users
Subject: Re: [rsyslog] Baclogged files to disk are pretty slow
Many more measurements are needed before declaring a conclusive cause,
but on the surface it seems that your bottleneck is not rsyslog or the
sending server but the database itself. Comments below.
On Thu, Jan 15, 2009 at 10:28, Daniel Anson <[email protected]>
wrote:
> I have been dealing with this problem for a few days now and perhaps I
> will be able to solicit some advice or help. Here is the issue. I
have
> an rsyslog relay writing to a remote database server and caching to
> disk. The write to the database uses a MySQL stored procedure that
can
> write about 4000 records per second. The rsyslog.conf parts are set
up
Is that 4000 TPS burst or sustained speed?
> If I turn off the database, in this case I turned it off for almost a
> day, it backlogs nearly a 1 GB worth of information. The problem is
Roughly how many records?
> that it takes nearly 6 hours to catch back up from this. While
catching
> up, it only uses about 1% of the proc. Bandwidth is not an issue as
the
What's the processor and disk load look like on your MySQL server?
> fibre link is only about 50% saturated. Is there a way to force
Presuming 50% is your bps, what was your PPS? Depending on how large
your average event/transaction are, you may never see 100% due to
small packets.
> not. Is there a way to force rsyslogd to use a pool of MySQL
> connections or intiate a new connection each time a record is written?
Ranier confirmed my suspicion that rsyslog executes a single
transaction per event, which is (as he also notes) sub-optimal for
performance. Batching really should be about the same logic as the
MARK functionality: every N foo, output "bar".
Multiple actions per transaction (batching) is a classic query tuning
technique and can be approached many ways, but you probably need to
verify your database I/O is indeed the bottleneck.
_______________________________________________
rsyslog mailing list
http://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com
Confidentiality Notice: This e-mail message (including any attached or
embedded documents) is intended for the exclusive and confidential use of the
individual or entity to which this message is addressed, and unless otherwise
expressly indicated, is confidential and privileged information of Rackspace.
Any dissemination, distribution or copying of the enclosed material is
prohibited.
If you receive this transmission in error, please notify us immediately by
e-mail
at [email protected], and delete the original message.
Your cooperation is appreciated.
_______________________________________________
rsyslog mailing list
http://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com
