Re: [rsyslog] stuck up with bad performance with rsyslog+mysql

David Lang Sat, 15 Jun 2013 00:57:19 -0700

Right, try running that up, I would say run tests at 100, 500, 1000, 1500, 2000,see where the rate if logs being inserted starts to flatten out.

Given the performance of your system, you will probably need to change your testto write 100,000 messages or nore to give you enough time to see the work beingdone :-)


David Lang

On Sat, 15 Jun 2013, Mahesh V wrote:

Hi david,
I did not modify the defaults in the file.
Is this the parameter you are talking about ?
*$ActionQueueDequeueBatchSize* .
The default value is probably 16, so should I increase it to a higher value
? (1000?)
thanks
Mahesh


On Sat, Jun 15, 2013 at 11:09 AM, Mahesh V
<[email protected]>wrote:

Hi david,

I did not modify the defaults in the file.
Would you kindly send me the .conf file modifications for higher batch
size?
thanks
Mahesh


On Fri, Jun 14, 2013 at 8:45 PM, David Lang <[email protected]> wrote:

What batch size did you use? it's worth doing testing with very large
batch sizes. On your system you can do ~100 inserts/sec with a single log
per insert, but I would not be surprised to see you still getting ~100
inserts/sec with 1000 or more logs per insert.


David Lang




On Fri, 14 Jun 2013, Mahesh V wrote:

 much better performance with 7.4 (ryslog+mysql)


-bash-4.1# date; ./a.out; date
Tue Jun 11 18:40:59 IST 2013
Tue Jun 11 18:41:33 IST 2013
< 1 minute for 50000 records.

Radu, I will also try elasticsearch. I think it makes perfect sense to
use
it for my specific requirements
thanks a lot folks
god bless.
Mahesh



On Fri, Jun 14, 2013 at 4:27 PM, Rainer Gerhards
<[email protected]>**wrote:

 On Fri, Jun 14, 2013 at 12:55 PM, Mahesh V

<maheshvenkateshwaran@gmail.**com <[email protected]>

wrote:


 Hi David,

I tried compiling 7.4.0 but it is a major pain.
Even after installing all the requisites, I get the J-SON error and EE
error etc.
Do you have a readymade configure file which I can use by any chance ?
(which takes care of the
J-SON workaround etc)?

for many platforms, they are available on http://www.rsyslog.com - just
klick on the "project" menu item.

HTH
Rainer

 thanks

Mahesh


On Fri, Jun 14, 2013 at 4:22 PM, Rainer Gerhards
<[email protected]>**wrote:

 I have checked the ChangeLog, mysql batching support was added in

7.3.5.

So

7.4.0 should give a major boost in performance.

Rainer


On Fri, Jun 14, 2013 at 11:23 AM, Mahesh V
<maheshvenkateshwaran@gmail.**com <[email protected]>

wrote:


 Hi david,


Here is the data. for operations on rsyslog + mysql and only rsyslog

#include <syslog.h>
#include <unistd.h>
void main()
{
        int i = 0;
        setlogmask (LOG_UPTO (LOG_NOTICE));
        openlog ("exampleprog",  LOG_PID | LOG_NDELAY, LOG_DAEMON);
        for(i = 0; i < 50000; i++) {
                syslog (LOG_NOTICE, "APSIM A tree falls in a forest

%d",

i);

                usleep(200);
        }
        closelog ();
}

1) rsyslog + mysql 50k entries
----------------------------
bash-4.1# date; ./a.out; date
Tue Jun 11 11:06:48 IST 2013
Tue Jun 11 11:14:04 IST 2013

+ another minute more to insert it into mysql
and to query - took about 3 seconds to get all 50k rows.

So rsyslog + mysql took about (8 + 1) minutes to complete

2) only rsyslog (writes to /var/log/127.0.0.1/syslog.log)
------------------------------**------------------------------**
------
bash-4.1# date; ./a.out; date
Tue Jun 11 11:17:42 IST 2013
Tue Jun 11 11:17:55 IST 2013

Inserting only in rsyslog took about 13 seconds.

bash-4.1# cat /var/log/127.0.0.1/syslog.log
Jun 11 11:17:55 localhost exampleprog[16191]: APSIM A tree falls in a
forest 49998
Jun 11 11:17:55 localhost exampleprog[16191]: APSIM A tree falls in a
forest 49999

bash-4.1# date; cat /var/log/127.0.0.1/syslog.log | wc -l; date;
Tue Jun 11 11:18:43 IST 2013
50008
Tue Jun 11 11:18:43 IST 2013

I know that flat file insert is much faster than mysql, however,

mysql

 provides querries which will

make my life a lot easier to debug things.

I am contemplating between using only rsyslog and then extract the

contents

myself (parsing the file
using a script after the whole operation is complete) vis-a-vis

rsyslog +

mysql to insert logs directly

so that I can query later.

The former seems to be the correct option for now.

Kindly advise if anything else can be done.

thanks
Mahesh



On Fri, Jun 14, 2013 at 8:31 AM, David Lang <[email protected]> wrote:

 does this mean that it's now working for you at high rates?


David Lang


On Fri, 14 Jun 2013, Mahesh V wrote:

 Thanks a lot David and all.


regards
Mahesh


On Fri, Jun 14, 2013 at 1:36 AM, David Lang <[email protected]>

wrote:

 On Thu, 13 Jun 2013, Mahesh V wrote:


 Hello David,

Thanks for the reply.

Here is another set of data points.
A simple program (shamelessly copied from internet) which does

nothing

but

inserts in mysql through rsyslog.

#include <syslog.h>
#include <unistd.h>
void main()
{
   int i = 0;
   setlogmask (LOG_UPTO (LOG_NOTICE));
   openlog ("exampleprog",  LOG_PID | LOG_NDELAY, LOG_DAEMON);
   for(i = 0; i < 10000; i++) {
       syslog (LOG_NOTICE, "A tree falls in a forest %d", i);
       usleep(100);
   }
   closelog ();
}

rsyslog.conf
----------------
$SystemLogRateLimitInterval 1
$SystemLogRateLimitBurst 5000
## Optional

program run
-------------------
-bash-4.1#  date; ./a.out; date
Mon Jun 10 13:02:10 IST 2013
Mon Jun 10 13:02:12 IST 2013

Close to 5 minutes later
------------------------------******--------

mysql -u root -p
mysql> select message from SystemEvents;
...
...
imuxsock begins to drop messages from pid 16346 due to

rate-limiting |

 imuxsock lost 1383 messages from pid 16346 due to rate-limiting

 |  A tree falls in a forest 9998

 |  A tree falls in a forest 9999

 +-----------------------------******--------------------------*

*--**--**

-----------+
8619 rows in set (0.01 sec)

I found that out of 10000, some 8616 records were inserted in 2

seconds.

 Wondering why imuxsock dropped messages even though rate

limiting

 value

was

high.


 well, you have the rate limit set to 5000 per second, but you

are

sending

 double that within 2 seconds. It doesn't seem surprising that you

could

 loose ~1400 of those messages.


5000 per second isn't especially high as far as rsyslog is

concerned.

personally, I never turn on rate limiting


 Obviously there would be system limitation, but that should be

the

 only

 criteria.



 raise the limit to something higher than the rate that you are

sending

 logs.



 -bash-4.1# /sbin/rsyslogd -v

 rsyslogd 5.8.10, compiled with:


this in no way gives disk spinning capacity but on a server

with 4

  cores,

would this be the max I can reach?


 no, you just hit the max that you had configured.


go to a very simple configuration, remove any rate limiting, just

put

in

 the minimum needed to read the input and output to a file. on

your

core

 box you will get hundreds of thousands, if not millions, of logs

per

 second

(and really only be using 2-3 cores to do so)

David Lang

 thanks

 Mahesh





On Wed, Jun 12, 2013 at 9:33 PM, David Lang <[email protected]>

wrote:

 On Wed, 12 Jun 2013, [email protected] wrote:

 On Wed, Jun 12, 2013 at 07:44:50PM +0530, Mahesh V wrote:


  Hello,

The software i work, has syslog facility enabled for debug

logs.

  It generates a huge amount of logs (12000 lines in 2

minutes) -

 about

log entries per second and this is put into a mysql backend.
However, I see that after about 2 minutes of run, my

application

  stalls

and if I disable (delete the SystemEvents table in Syslog

database),

my

application continues fine.
My my.cnf and rsyslog.conf is attached along.
Any ideas?

Thanks
Mahesh



 Hi Mahesh,


You sent a lot of software configuration information, but none

on

your

 DB hardware and I/O configurations. It sounds like you do not

have

  enough

capacity there, but this is complete speculation without the

actual

  details.



 expanding on this, you don't provide the details of your

disks.

 transactions/sec sounds like you are doing this on a single

rpm

 disk.

Since MySQL wants to keep the data safe, it does a fsync as

part

of

  each

transaction. With a 7200 rpm disk it is doing just over 100
rotations/sec
and so it can't do fwyncs any faster than that. For high

performance

 database work you need a disk controller card with battery

backed

 cache

 configured for writeback. That way the controller can tell the

OS

the

 write

is finished as soon as it's in the cache (where it will get

written,

 even

if the system crashes, as long as the disks and controller get

power

 again

before the battery dies). This can change the transaction rate

to

 tens

of

thousands per second.

In addition, rsyslog has the ability to write more than one

message

per

 transation (batch mode), but you need to move to rsyslog 7.4

(well

  7.3.5

or
later). Once you do that you should experiment with the batch

size.

  It's

very likely that you will want a batch size somewhere >1000
messages/batch,
but it will depend on how fast your disks are able to keep up.

David Lang

______________________________********_________________
rsyslog mailing list
http://lists.adiscon.net/********mailman/listinfo/rsyslog<http://lists.adiscon.net/******mailman/listinfo/rsyslog>
<

http://lists.adiscon.net/******mailman/listinfo/rsyslog<http://lists.adiscon.net/****mailman/listinfo/rsyslog>

 
<http:**//lists.adiscon.net/****mailman/**listinfo/rsyslog<http://lists.adiscon.net/**mailman/**listinfo/rsyslog>

http://lists.adiscon.net/****mailman/listinfo/rsyslog<http://lists.adiscon.net/**mailman/listinfo/rsyslog>

 
<http:**//lists.adiscon.net/****mailman/**listinfo/rsyslog<http://lists.adiscon.net/**mailman/**listinfo/rsyslog>

http://lists.adiscon.net/**mailman/**listinfo/rsyslog<http://lists.adiscon.net/mailman/**listinfo/rsyslog>

 
<htt**p://lists.adiscon.net/**mailman/**listinfo/rsyslog<http://lists.adiscon.net/mailman/**listinfo/rsyslog>

http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>

 
http://www.rsyslog.com/********professional-services/<http://www.rsyslog.com/******professional-services/>
<

http://www.rsyslog.com/******professional-services/<http://www.rsyslog.com/****professional-services/>

 
<http://**www.rsyslog.com/******professional-services/<http://www.rsyslog.com/****professional-services/>

http://www.rsyslog.com/****professional-services/<http://www.rsyslog.com/**professional-services/>

 
<http://**www.rsyslog.com/****professional-**services/<http://www.rsyslog.com/**professional-**services/>

http://www.rsyslog.com/**professional-**services/<http://www.rsyslog.com/professional-**services/>

 
<http:**//www.rsyslog.com/****professional-services/<http://www.rsyslog.com/**professional-services/>

http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>

 What's up with rsyslog? Follow https://twitter.com/rgerhards

NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED

by a

  myriad

of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST

if

you

 DON'T LIKE THAT.


 ______________________________******_________________

 rsyslog mailing list

http://lists.adiscon.net/******mailman/listinfo/rsyslog<http://lists.adiscon.net/****mailman/listinfo/rsyslog>
<

http://lists.adiscon.net/****mailman/listinfo/rsyslog<http://lists.adiscon.net/**mailman/listinfo/rsyslog>

 
<http:**//lists.adiscon.net/**mailman/**listinfo/rsyslog<http://lists.adiscon.net/mailman/**listinfo/rsyslog>

http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>

 
http://www.rsyslog.com/******professional-services/<http://www.rsyslog.com/****professional-services/>

http://www.rsyslog.com/****professional-services/<http://www.rsyslog.com/**professional-services/>

 
<http://**www.rsyslog.com/**professional-**services/<http://www.rsyslog.com/professional-**services/>

http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>

 What's up with rsyslog? Follow https://twitter.com/rgerhards

NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by

 myriad

 of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST

if

you

 DON'T LIKE THAT.


 ______________________________******_________________

 rsyslog mailing list

http://lists.adiscon.net/******mailman/listinfo/rsyslog<http://lists.adiscon.net/****mailman/listinfo/rsyslog>
<

http://lists.adiscon.net/****mailman/listinfo/rsyslog<http://lists.adiscon.net/**mailman/listinfo/rsyslog>

 
<http:**//lists.adiscon.net/**mailman/**listinfo/rsyslog<http://lists.adiscon.net/mailman/**listinfo/rsyslog>

http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>

 
http://www.rsyslog.com/******professional-services/<http://www.rsyslog.com/****professional-services/>

http://www.rsyslog.com/****professional-services/<http://www.rsyslog.com/**professional-services/>

 
<http://**www.rsyslog.com/**professional-**services/<http://www.rsyslog.com/professional-**services/>

http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>

 What's up with rsyslog? Follow https://twitter.com/rgerhards

NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a

myriad

 of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST

if

you

 DON'T LIKE THAT.


 ______________________________****_________________

rsyslog mailing list
http://lists.adiscon.net/****mailman/listinfo/rsyslog<http://lists.adiscon.net/**mailman/listinfo/rsyslog>
<

http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>

http://www.rsyslog.com/****professional-services/<http://www.rsyslog.com/**professional-services/>

http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>

What's up with rsyslog? Follow https://twitter.com/rgerhards

NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a

myriad

 of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if

you

 DON'T LIKE THAT.


 ______________________________****_________________

rsyslog mailing list
http://lists.adiscon.net/****mailman/listinfo/rsyslog<http://lists.adiscon.net/**mailman/listinfo/rsyslog>
<

http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>

http://www.rsyslog.com/****professional-services/<http://www.rsyslog.com/**professional-services/>
<

http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>

What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a

myriad

of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if

you

 DON'T LIKE THAT.


 ______________________________**_________________

rsyslog mailing list
http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>
http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a

myriad

of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if

you

 DON'T LIKE THAT.


 ______________________________**_________________

rsyslog mailing list
http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>
http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a

myriad

of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you

DON'T LIKE THAT.

 ______________________________**_________________

rsyslog mailing list
http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>
http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a
myriad
of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
DON'T LIKE THAT.

 ______________________________**_________________

rsyslog mailing list
http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>
http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
DON'T LIKE THAT.

 ______________________________**_________________

rsyslog mailing list
http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>
http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
DON'T LIKE THAT.

 ______________________________**_________________

rsyslog mailing list
http://lists.adiscon.net/**mailman/listinfo/rsyslog<http://lists.adiscon.net/mailman/listinfo/rsyslog>
http://www.rsyslog.com/**professional-services/<http://www.rsyslog.com/professional-services/>
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad
of sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you
DON'T LIKE THAT.

_______________________________________________
rsyslog mailing list
http://lists.adiscon.net/mailman/listinfo/rsyslog
http://www.rsyslog.com/professional-services/
What's up with rsyslog? Follow https://twitter.com/rgerhards
NOTE WELL: This is a PUBLIC mailing list, posts are ARCHIVED by a myriad of 
sites beyond our control. PLEASE UNSUBSCRIBE and DO NOT POST if you DON'T LIKE 
THAT.

Re: [rsyslog] stuck up with bad performance with rsyslog+mysql

Reply via email to