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 <[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 4 > 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 > >>>>>>> 100 > >>>>>>> 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. 100 > >>>>> transactions/sec sounds like you are doing this on a single 7200 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> > >>>>> <htt**p://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://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.
